LAB MANUAL

22MCA HCP2.4 :
DATABASE MANAGEMENT SYSTEM LAB
A1:

1. Create a table for the following relation schema

EMPLOYEE (Emp_no, E_name, E_address, E_ph_no, Dept_no, Dept_name,Job_id , Salary)
2. Add a new column; HIREDATE to the existing relation.
1. Change the datatype of JOB_ID from char to varchar2.
2. Change the name of column/field Emp_no to E_no.
3. Modify the column width of the job field of emp table

A2:

1. Create a table
EMPLOYEE (Emp_no, E_name, E_address, E_ph_no, Dept_no, Dept_name,Job_id , Salary)
2. Write SQL queries for following question: 1
1. Insert aleast 10 rows in the table.
2. Display all the information of EMP table.
3. Display the record of each employee who works in department D10.
4. Update the city of Emp_no-12 with current city as Nagpur.
5. Display the details of Employee who works in department MECH.
6. Delete the email_id of employee James.
7. Display the complete record of employees working in SALES Department.

A3:

1. Create a table EMPLOYEE (Emp_no, E_name, E_address, E_ph_no, Dept_no, Dept_name,Job_id,

Designation , Salary)

Write SQL statements for the following query.

1. List the E_no, E_name, Salary of all employees working for MANAGER.
2. Display all the details of the employee whose salary is more than the Sal of any ANALYST.
3. List the employees in the ascending order of Designations of those joined after 1981.
4. List the employees along with their Experience and Daily Salary.
5. List the employees who are either ‘CLERK’ or ‘ANALYST’ .
6. List the employees who joined on 1-MAY-81, 3-DEC-81, 17-DEC-81,19-JAN-80 .
7. List the employees who are working for the Deptno 10 or20.
8. List the Enames those are starting with ‘S’ .
9. Dislay the name as well as the first five characters of name(s) starting with ‘H’
10. List all the emps except ‘PRESIDENT’ & ‘MGR” in asc order of Salaries.
11. Display all the dept numbers available with the dept and emp tables avoiding duplicates.
12. Display all the dept numbers available with the dept and emp tables.

2 | 22MCA HCP2.4DBMS
13. Display all the dept numbers available in emp and not in dept tables and vice versa

A4: Implementation of different types of constraints

1. Create a table called EMP with the following structure.

EMPNO NUMBER (6) ENAME VARCHAR2 (20) JOB VARCHAR2 (10) DEPTNO NUMBER (3) SAL
NUMBER (7,2)
2. Allow NULL for all columns except ename and job
3. Add constraints to check, while entering the empno value (i.e) empno > 100.
4. Define the field DEPTNO as unique. 4. Create a primary key constraint for the table(EMPNO).
5. Write queries to implement and practice constraints.

A5: Implementation of different types of Joins

1. Consider the following schema:

Sailors (sid, sname, rating, age)

Boats (bid, bname, color)
Reserves (sid, bid, day(date))
2. Find all information of sailors who have reserved boat number 101.
3. Find the name of boat reserved by Bob.
4. Find the names of sailors who have reserved a red boat, and list in the order of age.
5. Find the names of sailors who have reserved at least one boat.
6. Find the ids and names of sailors who have reserved two different boats on the same day.
7. Find the ids of sailors who have reserved a red boat or a green boat.
8. Find the name and the age of the youngest sailor.
9. Count the number of different sailor names.
10. Find the average age of sailors for each rating level.
11. Find the average age of sailors for each rating level that has at least two sailors.

A6: Study & Implementation of  Group by & Having Clause  Order by Clause  Indexing

For EMPLOYEE table

1. Display total salary spent for each job category.

2. Display lowest paid employee details under each manager.
3. Display number of employees working in each department and their department name.
4. Display the details of employees sorting the salary in increasing order.
5. Show the record of employee earning salary greater than 16000 in each department.
6. Write queries to implement and practice the above clause.

3 | 22MCA HCP2.4DBMS
A7: Implementation of  Sub queries  Views

Consider the following schema:

Sailors (sid, sname, rating, age)
Boats (bid, bname, color)
Reserves (sid, bid, day(date))
Write sub-query statement for the following queries.
1. Find all information of sailors who have reserved boat number 101.
2. Find the name of boat reserved by Bob.
3. Find the names of sailors who have reserved a red boat, and list in the order of age.
4. Find the names of sailors who have reserved at least one boat.
5. Find the ids and names of sailors who have reserved two different boats on the same day.
6. Find the ids of sailors who have reserved a red boat or a green boat.
7. Find the name and the age of the youngest sailor.
8. Count the number of different sailor names.
9. Find the average age of sailors for each rating level.
10. Find the average age of sailors for each rating level that has at least two sailors.

A7: Implementation of Database Backup & Recovery Commands.  Implementation of Rollback,

Commit, Save point.

1. Write a query to implement the save point.

2. Write a query to implement the rollback.
3. Write a query to implement the commit

A9: Creating Database/ Table Space  Managing Users: - Create User, Delete User  Managing Passwords

 Managing roles: - Grant , Revoke

1. Create user and implement the following commands on relation (Emp and Dept).
2. Develop a query to grant all privileges of employees table into departments table.
3. Develop a query to grant some privileges of employees table into departments table.
4. Develop a query to revoke all privileges of employees table from departments table.
5. Develop a query to revoke some privileges of employees table from departments table.

A10: PL/SQL programs

1. Write a PL/SQL block to find the maximum number from given threenumbers.

4 | 22MCA HCP2.4DBMS
2. Write a PL/SQL program to find the total and average of 4 subjects and display the grade
3. Write a PL / SQL program to check whether the given number is prime or not.
4. Write a PL/SQL program to accept a string and remove the vowels from the string. (When ‘hello’
passed to the program it should display ‘Hll’ removing e and o from the world Hello).

A11: Procedures and Functions

1) calculate the net salary and year salary if da is 30% of basic, hra is 10% of basic and pf is 7% if
basic salary is less than 8000, pf is 10% if basic sal between 8000 to160000.
2) write a pl/sql function accept date of birth as "dd-mm-yyyy" and sum all digits tillyou get single
digit number to show as he luckynumber.

A12: TRIGGERS

1. Create a row level trigger for the customers table that would fire for INSERT or UPDATE or
DELETE operations performed on the CUSTOMERS table. This trigger will display the salary
difference between the old values and new values:
2. Convert employee name into uppercase whenever an employee record is inserted or updated.
Trigger to fire before the insert orupdate.

CASE STUDY

ROADWAY TRAVELS “Roadway Travels” is in business since 1977 with several buses connecting different
places in India. Its main office is located in BIJAPUR.

 The company wants to computerize its operations in the following areas:

 Reservations Ticketing Cancellations Reservations : Reservations are directly handeled by
booking office.
 Reservations can be made 60 days in advance in either cash or credit.
 In case the ticket is not available, a wait listed ticket is issued to the customer. This ticket is
confirmed against the cancellation.
 Cancellation and modification: Cancellations are also directly handed at the booking office.
Cancellation charges will be charged.
 Wait listed tickets that do not get confirmed are fully refunded.

5 | 22MCA HCP2.4DBMS
6 | 22MCA HCP2.4DBMS