Name :

Class :
Division :
Roll No :
Semester :
Subject:
Mobile No :
Email Id :
Practical 1
1. Create a table pay inside database emp whose column structure is shown below:

Empno – small int – primary key

Name – text of length 20

Age – int

City – text of length 10

Salary – decimal value upto w decimal places

Date of birth (DOB) – date

2. Write MySQL statements for the following:

a. Add a column ‘gender’ of character type of size 1 column to this table.
b. Add a column ‘bonus’ decimal (7,2) to this table after the salary column with default
value 2000.

c. Change the size of column ‘name’ to varchar (25)

d. Change the name of the column ‘bonus’ to ‘mbonus’

e. Change the name of the column ‘dob’ to ‘d_birth’

f. Delete the column mbonus

g. Rename the table pay to EPAY

h. Delete the table EPAY

i. Delete the database emp

Practical - 2
1. Create a table inside the database INFO called MY_EMPLOYEE containing the columns
employee id smallint which will be the primary key, last name varchar (25), first name
varchar (25), userid varchar (8), and salary decimal (9,2).

2. Add a row to the table MY_EMPLOYEE without naming the columns. Use your own data,
but give employee id as 1.

3. Add another row to the table MY_EMPLOYEE using the column names. Use your own data
but give employee id as 2.

4. Add another 3 rows of data together to the table MY_EMPLOYEE. Use your own data but
give employee id as 3,4,5.
5. Change the last name of employee id 3 to Mehta in the table MY_EMPLOYEE.

6. Change the salary of all the employees to 40000 if the salary is below 40000 in the
table MY_EMPLOYEE.

7. Delete the row of employee id 5 in the table MY_EMPLOYEE.

Practical – 3

1. Create table EMPLOYEES contains the fields emp_id smallint not null, fname
varchar(20), lname varchar(20), email varchar(25), phoneno varchar(10),
hire_date date, jobid varchar(10), salary decimal(8,2), commission decimal(6,2),
manager_id smallint, dept_id smallint, where emp_id is the primary key.

2. Add 1 row of data to this table. Then add another 6 rows.

3. Display all the rows in this table.

4. To display last name and salary of all employees earning more than 14,000.

5. To display last name and department id for employee id 3.

6. To display last name and salary for all employees whose salary is in the range 15000
to 24000.

7. To display last name, job_id and hire date of employees hired between Feb 20,
1998 and May 1, 1998 in the ascending order of hire date.

8. To display the last name and department of the employees in department 101 or
103 in the alphabetic order of last name.

9. To display the last name and salary of employees who earn from 15000 to 24000
and are in department 101 or 103. Label the columns as Employee and monthly
Salary respectively.

10. To display the last name and hire date of every employee who was hired in 2001.

11. To display the last name and job type of all employees who do not have a manager.
12. Display the last name salary and commission of all employees who earn commissions
in the descending order of salary and commission.

13. Display the last names of all employees where the third letter of the name is an ‘h’.

14. To display the last name of the employees who have an ‘a’ and an ‘e’ in their
last name.

15. To display the last name, jobid and salary of the employees whose jobid is sales
rep or store clerk and whose salary is not equal to 21000, 25000 or 37000.

16. To display the last name, salary and commission of all the employees
whose commission amount is below 2000.
Practical - 4
1. Create 3 tables with the fields specified:
a. Emp: empid primary key, fname, lname, email, phone, hire_date, jobid,
salary, commission, managerid(can store repeated values), deptid(can store
repeated values) and add 10 records
b. Dept: deptid primary key, deptname, managerid, locationid(can store
repeated values), Manager name and add 5-6 records
c. Location: locationid primary key, street, pincode, city, state, countryid and add 5-
6 records
2. Write a query to display last name, dept id and dept name for all the employees.

3. Create a unique list of all the jobs that are in dept 101. Also display the locationid for that
department.

4. Display the last name, dept name, location id and city for those who earn commission

5. Display last name and dept name of those employees who have ‘a’ in their last name

6. Write a query to display last name, jobid, dept id and dept name for all the employees who
are from Mumbai.
7. Display last name, dept name and location id for those employees who do not have manager
id sorted in the ascending order of employee id.

8. Display employee’s last name and first name as Employee and manager name as manager .
Practical - 5
Consider the table Emp with the fields empid primary key, fname, lname, email, phone,
hire_date, jobid, salary, commission, managerid(can store repeated values), deptid(can store
repeated values) populated with 10 records and answer the following:

1. Display highest salary as maximum and lowest salary as minimum

2. Display sum and average of the salary. Label the columns as Sum of Salary and Average of
Salary respectively.
3. Display min, max, sum and average of the salary for each job type.

4. Display the number of people with the same job id.

5. Count the distinct number of managers. Label the column name as Number of Managers.

6. Display the difference between Max and Min salary as Range of Salary.

7. Display the manager number and the salary of the lowest paid employee for that manager.
Exclude anyone whose manager is not known. Exclude any group where min salary is
16000 or more. Sort the output in descending order of salary.
Practical - 6
Use the table emp created earlier and solve the following:

1. Display Employee ID, Last name of all the employees who earn more than average
salary. Sort the result in descending order of salary.

2. Display the last name and hire date of any employee in the same department as
‘Gandhi’. Exclude ‘Gandhi’.

3. Display the employee ID and last name of all employees who work in a department with
any employee whose last name contains ‘w’.
4. Display last name, department id and job id of all employees whose department location id
is 1700.

5. Display the last name and salary of every employee who reports to ‘Gadiwalla’.

6. Display department number, last name, jobid of every employee in the accounts department.
Practical - 7
1. Create a worksheet containing a table with the headings, Name, Age,
Department(Sales/HR/Admin/Production) and Bonus.
2. Populate the table with 15 records.

3. Sort the data in alphabetical order of Name.

a. Click on Home Tab and then click on Sort And Fitler tab.
b. After clicking sort and filter tab, then click on Sort A to Z.
 c. After clicking the Sort A to Z tab, the data will be sorted in alphabetical order,
as show below.

4. Sort the data in alphabetical order of Department then in ascending order of

age.

a. Click on Home Tab and then click on Sort and Filter Button.
b. After that click on custom sort.
c. Fill the value of level 1 as follows: Sort by: Department, Sort ON: Cell
Values, Order: A to Z.
d. Add one more level to sort.
 e. Fill the value of level 2 as follows: Sort by: Age, Sort on: Cell Values, Order:
Smallest to Largest.
f. After filling the values, click on ok and then the data will be
sorted accordingly.

5. Calculate department wise subtotal of bonus displaying total bonus for every
department.

a. Click on Data Tab and then click on subtotal in the outline column.
b. After that fill in the values as follows: At each change in: Department, Use
function: Sum, Add subtotal to: Bonus.
c. Click on OK to add subtotal to the Data.
6. Display only the summary of subtotal only.

a. Click on minus (“-“) sign beside the row number.

b. After clicking the minus sign, the excel will only display the summary of
subtotal of the data.

7. Remove all subtotals.

a. In order to remove the subtotal, click on Data tab.

b. Click on Subtotal in the outline column.
c. Click on Remove all.
d. The Subtotal will be removed from the data.
Practical – 8
Pivot Tables
Prepare a worksheet containing the Name, Join Date, Department and Salary
in
Columns A, B. C, and D respectively from rows 1 to 31 the first row contains
the headings and the remaining rows contain data. Use your own data.
Carry out the following operations on this worksheet.
1. Save the worksheet under your name followed by your roll no., question
no. eg MONA1232
2. Prepare another Pivot table on the same data taking Department in
the row area and Average Salary and Maximum Salary in the data area.
3. Prepare a Pivot table report containing Join Date in the row area and
Sum of Salary and Minimum Salary in the data area.
Part 1: To create a new file and save it.
Step 1: Open a new workbook and type the data as per instructions given in
the question.
Part 2: Pivot Table 1
To create Pivot Table containing Department, Average Salary and Maximum
Salary.

Conclusion: From this pivot table, we can draw the following conclusions:
a) The minimum salary of each department is indicated against the
department name.
b) The total salary of each department is indicated against the
department name.
Part 3: Pivot Table 2
To create Pivot Table containing Joining Date, Sum of Salary and Minimum
Salary.
Practical - 9
1. Given the following data calculate Future Value:

Data Description

12% Annual Rate of Interest

12 Number of Payments

-1000 Amount of Payment

? Future Value

2. A person is thinking of buying an annuity that pays 500 at the end of every
month for the next 20 years. The cost of annuity is 60000. Money paid out will
earn 8%. Determine whether it’s a good investment.
3. Calculate the amount to be saved every month to have 50000 at the end of 18
years on the investment which gets interest at the rate of 6% pa
4. Calculate the interest part at the end of 1 month by considering the investment as
monthly investment. Also calculate the interest part at the end of 1 year by
considering the investment as yearly investment.
PER 1
Rate 10%
PV -8000
FV 0
NPER 3 YEARS
5.
Rate p a 12%
pmt -100
pv -1000
fv 10000
type 1
 PAYMENT AT
THE BEGINNING
OF THE MONTH
NPER ?
PAYMENT AT
THE END OF
? THE MONTH

? IF FV IS 0

6.
PV 8000
FV 0
NPER 4 YEARS
PMT -200 PER MONTH
RATE(MONTHL Y)
?
RATE PER
ANNUM ?
Practical – 10

1. Calculation of Simple Interest

2. Calculation of Compound Interest

3. Calculation of Depreciation