Lab Practical Exercises

Assignment 1
Objective: Creating templates in MS word and saving for distribution.
Task: Create a document that looks like a screenshot and save as a template. Follow
below-given instructions:

Instructions:
• Apply page borders as displayed in the screenshot. Apply border
colour as per your choice.
• Page orientation must be landscape.
• Give your desired formatting for school name, school address, and text
written in the certificate.
• Save your file as a template and give the name certificate template.
Assignment 2
Objective: Changing the template from default to custom
Task: Open MS Word change the default template to custom template using the
following formatting:
 • Font: Verdana, Font Size: 14 pt, Font colour: Automatic
• Main Headings in the template: Font – Verdana, Size – 24 pt, Font
colour – Blue
• Heading level 2: Font – Verdana, Size – 20 pt, Font colour – Maroon
• Heading level 3: Font – Verdana, Size – 18 pt, Font colour – Red

Electronic Spreadsheet

Excel Fundamentals:
Excel is a tool that allows you to enter quantitative data into an electronic
spreadsheet to apply one or many mathematical computations. These
computations ultimately convert that quantitative data into information.
The information produced in Excel can be used to make decisions in both
professional and personal context.

The Excel Workbook

Shortcut keys:
CTRL] + [Page Up] Activate the next sheet in the workbook
[CTRL] + [Page Down] Activate the previous sheet in the workbook
Insert a row, column or range before the curren
[CTRL] + [+]
selection
Delete a row, column or range before the curren
[CTRL] + [-]
selection
[CTRL] + [ALT] + [=] Zoom in by 15%
[CTRL] + [ALT] + [-] Zoom out by 15%
[F2] Edit the active cell
[ENTER] or [RETURN] Confirm a cell edit
[ESC] Cancel a cell edit
[CTRL]+[ENTER] Apply cell edit to multiple selection
[CTRL] + [Arrow] Jump to the end of the range
[CTRL]+[Page Up or Page
Jump to the top or bottom of the sheet
Down]
[CTRL]+[Home] Jump to the top left of the sheet (cell A1)
[CTRL]+[End] Jump to the last used row and column.
[CTRL] + [SHIFT]+ [Arrow] Select adjoining cells in the direction chosen
[CTRL]+[A] select adjoining cells in all directions
[SHIFT] + [Spacebar] Select entire row
[CTRL] + [Spacebar] Select entire column
[ALT]+[=]
What-If Analysis is the process of changing the values in cells to see how
those changes will affect the outcome of formulas on the worksheet. Three
kinds of What-If Analysis tools come with Excel: Scenarios, Goal Seek, and
Data Tables.

To enable the what-if analysis tool go to the Data menu tab and
click on the What-If Analysis option under the Forecast section.
Scenario Manager
o A scenario can have multiple variables, but it can
accommodate only up to 32 values.
o You can also create a scenario summary report, which
combines all the scenarios on one worksheet. For example,
you can create several different budget scenarios that
compare various possible income levels and expenses, and
then create a report that lets you compare the scenarios side-
by-side.
o Goal Seek
o Goal Seek is useful if you want to know the formula's result
but unsure what input value the formula needs to get that
result.
o Goal Seek can be used only with one variable input value. If
you have more than one variable for input values, you can
use the Solver add-in.
o Data Table
o A Data Table is a range of cells where you can change values
in some of the cells and answer different answers to a
problem.
o It works only with one or two variables, but it can accept many
different values for those variables.
o Now below are the following steps for setting up the initial
values for Scenarios:
o Step 1: Define the cells that contain the input values.
o Step 2: Name the cells Metals_name and Cost.
o Step 3: Define the cells that contain the results.
o Step 4: Name the result cell Total_cost.
o Step 5: place the formula in the result cell
 o

Now click on the What-If Analysis.

After clicking on the Add button, the add scenario dialog box
appears again.
 o Similarly in the scenario name box, create scenario 2 and
scenario 3.
o Select the prevent changes.
o And click on the Ok
Select Scenario summary under Report type and click Ok. Scenario

Summary report appears in a new worksheet. You will get the

following Scenario summary report.

You can observe the following in the Scenario Summary report:

o Changing Cells: Enlists all the cells used as changing cells.
 o Result Cells: Displays the result cell specified.
o Current Values: It is the first column and enlists the values of
that scenario selected in the Scenario Manager Dialog box
before creating the summary report.

Goal Seek box produces the following result.

Mail merge
Do you have a long list of names and addresses that you need to send letters to?
The Mail Merge process combines a Word document with a data source to
quickly create letters that feel personal.
Set up and Choose Document Type
To begin the mail merge process, you first need to choose what sort of
document you want to create.
1. Click the Mailings tab.
2. Click the Start Mail Merge button.
3. Select Step-by-Step Mail Merge Wizard.
 The Mail Merge pane appears on the right, ready to walk you
through the mail merge.
4. Select a type of document to create.
5. Click Next: Starting document.
The Mail Merge wizard advances to the next step.
Select a Document
This next step is to select a starting document.
1. Select a starting document.

You can use the current document as the basis for the mail merge,
or you can select a template or existing document instead.
2. Click Next: Select recipients.
The Mail Merge wizard moves on to step 3.
Select Recipients
Now, you will need to choose where you’ll get your list of addresses from. This
example uses an existing list from a database, but you can also select Outlook
contacts or manually create your own list.
1. Select Use an existing list.
2. Click Browse.
3. Select your data file.

A data file of mail merge recipients can be in a database file, an

Excel spreadsheet, another Word document, or other types of data
files.
4. Click Open.

The Mail Merge Recipients dialog box displays the addresses that
will be used. If you’re using an Excel spreadsheet as a data source,
you may also be prompted to select a worksheet containing the
addresses.

If there's an address you don't want to use, you can uncheck it.
5. Make sure the right recipients are selected and click OK.
6. Click Next: Write your letter.
The Mail Merge wizard moves on to step 4.
Write Your Letter
After the main document is set and the recipient list is connected and edited,
you are ready to insert the merge fields in the document. The merge fields are
placeholders in the document for unique information from the recipients list.
When you put a merge field in the main document, information from that field
will appear for the document that is unique to that recipient.
1. Click where you want the information.
2. Select one of the placeholder options.

You can add merge fields from the wizard, or from the Write & Insert
Fields group on the ribbon:
o Address Block: This is a combination of fields to insert the

names and addresses of recipients.

o Greeting Line: This is a combination of fields to insert the

recipient’s name in the greeting line.

o Insert Merge Field: When you click this button, a list of

additional merge fields you can insert appears.

3. Customize the placeholder.
4. Click OK.
5. (Optional) Repeat steps to add each merge field you want to
include.
6. Click Next: Preview your letters.
The Mail Merge wizard moves on to step 5.
Preview Your Mail Merge
Sometimes, it is helpful to see what the data will look like once it has been
inserted into a document, instead of only viewing the merge field names.
You can easily preview how the mail merge will appear before finishing the mail
merge. This is encouraged to make sure the results appear as you want them to.
1. Use the arrow buttons in the Mail Merge pane to preview each
merged document.
 You can also use the arrow buttons in the Preview Results group on
the ribbon.
Click the Find Recipient button in the Preview Results group or in
the Mail Merge pane to search for a specific recipient.
2. Click Next: Complete the merge.

The Mail Merge wizard moves on to the final step.

Complete the Merge
Once you’ve added the list of recipients and filled out a document with merge
fields, the last step is to finish the merge by making a separate version of the
document for each recipient.
There are a couple of different ways you can finish the mail merge:
• Edit Individual Documents: Puts the results of the mail merge in a new

document. You are free to edit the results of the mail merge and save
and print them, just like any other document.
• Print Documents: Merges records and sends them directly to the

printer.
1. Select the option you want to use to finish the mail merge.
You can also click the Finish & Merge button on the ribbon and
select a merge option there.

Introduction To Database Management System

Database is a collection of inter-related data which helps in efficient retrieval,

insertion and deletion of data from database and organizes the data in the form of
tables, views, schemas, reports etc.

• Data Manipulation Languages (DML)

• Structured Query Language (SQL)
• Data Definition Language (DDL)
• Primary Key – Each file has a unique key. Using the Primary Key, a specific file
can be identified
• Foreign Key – The relation between a field in one table and component
identified by a primary key can be detected using a Foreign Key
 • EXP.N0:1a DATA DEFINITION LANGUAGE
• [DDL]
• AIM:
• To execute the DATA DEFINITION LANGUAGE (DDL) commands.
• CREATING A TABLE
• SYNTAX:
• create table <table name> (column 1 datatype 1,
• ……., column n datatype n);
• EXPLANATION:
• Create command is used to create a table in the database.
• EXAMPLE:
• SQL> create table student (VT number, name varchar (10));
• OUTPUT:
• Table created.
• DESC COMMAND
• SYNTAX:
• desc <table name>;
• EXPLANATION:
• This command is used to view the structure of the table.
• EXAMPLE:
• SQL> desc student;
OUTPUT:
NAME NULL? TYPE
VT NUMBER
NAME VARCHAR (10)
ALTERING A TABLE
1. MODIFY
SYNTAX:
alter table <table name> modify (column datatype, …);
EXAMPLE:
SQL> alter table student modify (name varchar (20));
OUTPUT:
Table altered.
TRUNCATING A TABLE
SYNTAX:
truncate table <table name>;
EXPLANATION:
This command is used to delete all records stored in a table, but
the structure of the table is retained.
EXAMPLE:
SQL> truncate table student;
OUTPUT:
Table truncated.
DROPPING A TABLE
SYNTAX:
drop table <table name>;
EXPLANATION:
This command is used to remove a table from the database.
EXAMPLE:
SQL> drop table student;
OUTPUT:
Table dropped.
RESULT:
Thus, the data definition language commands were executed and their
outputs were verified

DATA MANIPULATION LANGUAGE

AIM:
To execute the DATA MANIPULATION LANGUAGE (DML) commands.
INSERT COMMANDS
1. INSERT A ROW WITH VALUES FOR COLUMNS IN A TABLE.
SYNTAX:
insert into <table name> values (value1, value2,……, value n);
EXPLANATION:
This insert command is used to add rows to a table in the database.
EXAMPLE:
SQL> insert into student values (4169, “ Riya‟);
OUTPUT:
1 row created.
SELECT COMMANDS
1. SELECT ALL ROWS FROM A TABLE.
SYNTAX:
select * from <table name>;
EXPLANATION:
This select command is used to retrieve all the values stored in the table.
EXAMPLE:
SQL> select * from student;
OUTPUT:
VT NAME
4169 RIYA

SELECT USING WHERE COMMAND.

• SYNTAX:
• select <field name> from <table name> where <search condition>;
• EXPLANATION:
• This select command is used to retrieve the particular field values,
• stored in the table, which satisfy a required condition.
• EXAMPLE:
• SQL> select name from student where vt=4169;
• OUTPUT:
• NAME
• RAMEEZE
• UPDATE COMMAND
• SYNTAX:
• update <table name> set column1=expression, column 2=expression, ……,
• column n=expression where <search condition>;
• EXPLANATION:
 • This command is used to update (changing values in) one or two columns of
a row in a table. Specific rows can be updated based on some condition.

EXAMPLE:

SQL> update student set name=‟ Riya ‟ where vt =4169;

OUTPUT:

1 row updated.

SQL> select * from student;

VT NAME

4169 RIYA

DELETE COMMAND

SYNTAX:

delete from <table name> where <search condition>;

EXPLANATION:

This command is used to delete a row from a table.

EXAMPLE:

SQL> delete from student where vt =4169;

OUTPUT:

1 row deleted.

SQL> select * from student;

no rows selected.
RESULT:

• Thus, the data manipulation language commands were executed and their
• outputs were verified.
MS-Access is application software for managing the databases. It is
released by Microsoft in November 1992.

Create a Form with the Form Wizard in Microsoft Access

The Form Wizard gives you more control over your results than one-click forms do.
The wizard lets you make decisions about certain aspects of a form's design and
produces a form based on your instructions. To create a form based on a single
table using the Form Wizard, follow these nine steps.

This feature works the same in all modern versions of Microsoft Access: 2010, 2013,
and 2016.

1. On the Create tab in the Forms group, click Form Wizard. The wizard starts.
2. From the Tables/Queries drop-down list, select the table (or query) to base
the form on. The fields for the selected table load in the Available Fields list
box.
3. Move the fields to include on the form from the Available Fields list box to
the Selected Fields list box. To do so, double-click a field name to move it or
highlight the field name and click >. To move all fields at once, click >>.
4. Click Next >.

5. Select the layout for the form. Your options are "Columnar", "Tabular",
"Datasheet", and "Justified".

Tip: Select each of the options to see a preview of the form layout before
you make a final selection.
 6. Click Next >.

7. Enter a title for the form.

8. Select an option for the view you want to open the form in. Your options are:
o Open the form to view or enter information (opens in Form view).
o Modify the form's design (opens in Design view).
Click Finish. The form loads in the view you selected.
Create a Report with the Report Wizard in Microsoft
Access

What is a report?

1. A report is the final outcome of any computerized system. Suppose you are giving online order
from any online shopping website after completion of the payment and delivery you will get an
invoice of the order. This invoice is one of the example of report.On the Create tab in
 the Reports group, click Report Wizard. The wizard starts.

2. From the Tables/Queries drop-down list, select the table (or query) to base the report on. The
fields for the selected table load in the Available Fields list box.
3. Move the fields to include on the report from the Available Fields list box to the Selected
Fields list box. To do so, double-click a field name to move it or highlight the field name and
 click >. To move all fields at once, click >>.

4. Click Next >.

5. To group records on the report by a particular field, highlight the field in the list box and click >.

6. Add more grouping levels if desired. You can use the arrows to change the order of the grouping
levels if needed.
7. When you finish defining how you want records grouped, click Next >.
8. In the first drop-down list, select the field to sort records by. By default, records will be sorted in
ascending order by the field you select. If you want to sort in descending order, click
the Ascending button to change its label to "Descending".

9. You can specify up to four levels of sorting. When you finish specifying sorting options,

click Next >.

10. In the Layout field, select the format of the report. Your options are "Stepped", "Block", and
"Outline". (Try the options to see a preview of the report layouts.)
11. In the Orientation field, select whether to lay out the report in portrait or landscape mode.
12. If you want all fields to fit on a single page, ensure the Adjust the field width so all fields fit
on a page check box is marked.

13. Click Next >.

14. Enter a title for the report.
15. Select an option for the view you want to open the report in. Your options are:
o Preview the report (opens in Print Preview mode).
o Modify the report's design (opens in Design view).
16. Click Finish. The report loads in the view you selected.
In this section, we select the Blank Database option for storing the
data. After clicking the option, the following screen will display
which gives the default name of the file.
Access will switch to Query Design view. In the Show Table dialog box that
appears, select the table you want to run a query on. We are running a query on
our customers, so we'll select the Customers table.
so we'll include the First Name, Last Name, Street Address, City, and Zip
Code fields.