DBMSLab

Common Database Systems Lab Programs

Consider the following schema:
Table Name: Employee
Attribute Data Type
First Name VARCHAR2(15)
Mid Name CHAR(2)
Last Name VARCHAR2(15)
SSN Number CHAR(9)
Birthday DATE
Address VARCHAR2(50)
Sex CHAR(1)
Salary NUMBER (7)
Supervisor SSN CHAR(9)
Department Number NUMBER (5)
Table Name: Department
Attribute Data Type
Department Name Varchar2(15)
Department Number Number(5)
ManagerSSN CHAR(9)
ManageStartDate DATE
Table Name: Project
Attribute Data Type
Project Name VARCHAR2(15)
Project Number NUMBER(5)
Project Location VARCHAR2(15)
Department Number NUMBER(5)
Data For Employee Table
FName Mini
t LName SSN BDate Address Sex Salary SuperSSN DepNo
Doug E Gilbert 554433221 09-JUN-60 11 S 59 E, Salt
Lake City, UT M 80000 NULL 3
Joyce PAN 543216789 07-FEB-78 35 S 18 E, Salt
Lake City, UT F 70000 NULL 2
Frankin T Wong 333445555 08-DEC-45 638 Voss,
Houston, TX M 40000 554433221 5
Jennifer S Wallace 987654321 20-JUN-31 291 Berry,
Bellaire, TX F 43000 554433221 4
Johny B Smith 123456789 09-JAN-55 731 Fondren,
Houston, TX M 30000 333445555 5
Ramesh K Narayan 666884444 15-SEP-52 975 Fire Oak,
Humble, TX M 38000 333445555 5
Joyce A English 453453453 31-JUL-62 5631 Rice,
Houston, TX F 25000 333445555 5
James E Borg 888665555 10-NOV-27 450 Stone,
Houston, TX M 55000 543216789 1
Alicia J Zelaya 999887777 19-JUL-58 3321 Castle,
Spring, TX F 25000 987654321 4
Ahmad V Jabbar 987987987 29-MAR-
59
980 Dallas,
Houston, TX M 25000 987654321 4
Data For Department table
DName DepNo MgrSSN MgrStartDate
Manufacture 1 888665555 19-JUN-71
Administration 2 543216789 04-JAN-99
Headquarter 3 554433221 22-SEP-55
Finance 4 987654321 01-JAN-85
Research 5 333445555 22-MAY-78
Data For Project
PName PNumber Plocation DepNo
ProjectA 3388 Houston 1
ProjectB 1945 Salt Lake City 3
ProjectC 6688 Houston 5
ProjectD 2423 Bellaire 4
ProjectE 7745 Sugarland 5
ProjectF 1566 Salt Lake City 3
ProjectG 1234 New York 2
ProjectH 3467 Stafford 4
ProjectI 4345 Chicago 1
ProjectJ 2212 San Francisco 2
Exercise-I: (outcome: b,i, c and k)
1. Insert the data given above in employee, department and project tables. –b,i
2. Retrieve all the employees’ information for a particular department number – k
3. Get Employee name along with his SSN and Supervisor SSN. – k
4. Retrieve the employee names whose bdate is ’29-MAR-1959’. – k
5. Get salaries of the employees without duplications. – k
6. Retrieve the MgrSSN, MgrStartDate of the manager of ‘Research’ department. – k
7. Change the department number of an employee having fname as ‘Joyce’ to 3 – b,i
8. Alter Table department add column ContactNo of NUMBER data type and insert
values into this column only. – b,i
9. Change table department by modifying the size of field ContactNo. – b,i
10. Modify the field name ContactNo of departments table to MobileNo. – b,i
11. Change name of Table Department to DEPT. – c
12. Alter Table department by removing column MobileNo. – b,i
13. Create a table COPYOFDEPT as a copy of the table DEPT. – c
14. Remove the rows from COPYOF DEPT table with department number as 5. – b,i
15. Remove COPYOF DEPT table. – c
Exercise: II ( outcome : b,i)
Aim: To know how the constraints are used to make table contain valid data.
Table Name: Employee
Attribute Data Type Constraint
First Name Varchar (15) Not Null
Mid Name Char(2)
Last Name Varchar (15) Not Null
SSN Number Char (9) Primary Key
Birthday Date
Address Varchar (50)
Sex Char(1) Sex In (M,F,m,f)
Salary Number (7) Default 800
Supervisor SSN Char (9) Foreign Key Employee (SSN) on
delete set null
Department number Number(5) Foreign key to department
number of department table on
delete cascade
Table Name : Department
Attribute Data type Constraint
Department Name Varchar(15) Not Null
Department number Number(5) Primary key
Manager SSN Char (9) Foreign key-Employee (SSN) on
delete set null
Manage start date Date
Table Name : Dept_locations
Attribute Data type Constraint
Department Number Number(5) Department (dep no) on delete
cascade
Department Location Varchar (15)
Table Name: Project
Attribute Data type Constraint
Project Name Varchar2(15) Not Null
Project number Number(5) Primary key
Project Location Varchar2(50)
Department Number Number(5) Foreign Key –Department (dep
no ) on delete set null
Table Name: Works_On
The combination of Employee SSN and Project Number must be a Primary Key
Attribute Data type Constraint
Employee SSN Char (9) Foreign Key
Employee (SSN) on delete cascade
Project number INT(5) Foreign Key project ( Pnumber ) on
delete cascade
Hours Decimal (3,1) Not null
Name: Dependent
The combination of Employee SSN and Dependent Name must be a Primary Key.
Attribute Datatype Constraint
Employee Char (9) Foreign Key- Employee (SSN) on delete
cascade
Dependent Name Varchar(15)
Sex Char(1) Check Sex in (M,F,m,f)
Birthday Date
Relationship Varchar(8)
Data for table – Dept_Locations
Dep No D Location
1 Houston
1 Chicago
2 New York
2 San Francisco
3 Salt Lake City
4 Stafford
4 Bellaire
5 Sugarland
5 Houston
Data for Table – Works_On
ESSN Pno Hours
123456789 3388 32.5
123456789 1945 7.5
666884444 3388 40.0
453453453 7745 20.0
453453453 2212 20.0
333445555 7745 10.0
333445555 6688 10.0
333445555 4345 35.0
333445555 2212 28.5
999887777 2212 11.5
543216789 2212 17.0
554433221 1945 21.5
Data for Table – Dependent
ESSN Dependent_name Sex Bdate Relationship
333445555 Alice F 05-Apr-76 Daughter
333445555 Theodore M 25-Oct-73 Son
333445555 Joy F 03-May-48 Spouse
987654321 Abner M 29-Feb-32 Spouse
123456789 Alice F 31-Dec-78 Daughter
123456789 Elizabeth F 05-may-57 Spouse
Execute the following Queries on the Db to note the violations integrity constraints by any
of the following operations
1. Insert (‘Robert’, ‘F’, ‘Scott’, ‘987987987 ‘, ’21-JUN-42’, ‘2365 Newcastle Rd, Bellaire, TX’,
M, 58000, ‘888665555’, 1 ) into EMPLOYEE.
2. Insert (‘Ramez’, ‘F’, ‘Scott’, ‘ ‘, ’21-JUN-42’, ‘2365 Newcastle Rd, Bellaire, TX’, M, 58000,
‘888665555’, 1 ) into EMPLOYEE.
3. Insert ( ‘677678989’, null, ‘40.0’ ) into WORKS_ON.
4. Insert ( ‘453453453’, ‘John’, M, ’12-DEC-60′, ‘SPOUSE’ ) into DEPENDENT
5. Insert ( ‘343453453’, ‘Varun’,’’, ’12-DEC-60′, ‘SON’ ) into DEPENDENT
6. Delete WORKS_ON tuples with ESSN= ‘333445555’.
7. Modify MGRSSN and MGRSTARTDATE of the DEPARTMENT tuple with DNUMBER=5 to
‘123456789’ and ’01-OCT-88′, respectively.
Alter the tables to
1. Enforce Foreign Keys using Alter command [if not done earlier].
2. Remove foreign key defined on SuperSSN and enforce it again using Alter table
command.
3. Make name of Project as Unique and sex of employee as not null.
4. Make salary of employee to accept real values.
Exercise: III (outcome: e)
Operators and Functions
Aim: To understand different operators and types of functions in SQL
Execute the following queries based on the schema specified in exercise 1
1. Find the female employee names having salary greater than Rs.25000.
2. Find the employee names whose salary falls in the range of 30000 and 70000.
3. Find all the employees who have no supervisor.
4. Display the bdate of all employees in the format ‘DDthMonthYYYY’.
5. Get the employee names whose bdate not later than 1978.
6. Retrieve the employee names whose first name start with ‘J’ and have 5 characters in
total.
7. Find the employee details whose middle initial is null.
8. Get the male employee details whose address contains sub string ‘Houston’.
9. Display the department names that ends with ‘e’.
10. Display the names of all the employees having supervisor with any of the following
SSN 554433221, 333445555.
11. Display all the department names in upper case and lower case.
12. Display the first four characters and last four characters of the department names
using substring function.
13. Display the substring of the address (starting from 5th position to 11 th position) of all
employees.
14. Display the Mgrstartdate on adding two months to it.
15. Display the age of all the employees rounded to two digits.
16. Find the last day and next day of the month in which each manager has joined.
17. Print a substring ‘aman’ from the string ‘Ramana’.
18. Replace the string ‘na’ from ‘Ramana’ by ‘sri’.
19. Print the length of all the department names.
20. Print the system date in the format 25 th May 2014.
21. Display the date after 8 months from current date.
22. Display the next occurrence of Friday in this month.
23. Display the project location padded with **** on left side.
24. Remove the word ‘Project’ from the project name and display it.
25. Select the SSN of the employee whose dependent name is either Michael or Abner.
Exercise: IV (outcome: e)
Group Functions
1. How many different departments are there in the ‘employee’ table
2. For each department display the least and highest employee salaries along with
department name.
3. Print the number of projects on which each employee is working on.
4. Retrieve total number of hours spent on projects by each employee.
5. Count the number of employees over 30 age.
6. Display the department name which contains more than 3 employees.
7. Calculate the average salary of employees by department and age.
8. Count the number of dependents for each employee.
9. List out the employees based on their seniority.
10. List out the employees who works in ‘manufacture’ department group by first name
Exercise: V (outcome: k )
Sub Query and View
Aim: to understand the concept of Sub queries and logical tables in oracle
1. Display the employee who is getting highest salary in the department Research.
2. Find the employees who earn the same salary as the minimum salary for each
department.
3. Retrieve the employees whose salary is greater than average salary of department 2.
4. List out the employee that has got maximum number of dependents.
5. Find out the project name having least number of employees working on it.
6. Find minimum average salary for each department.
7. Create a view to display the employee details who is working in either Finance or IT
department.
8. Create a logical table to store male employee details ranging salary between 20000
and 30000.
9. Create a logical table to store employee number who works on maximum number of
projects.
Exercise: VI (outcome: k)
Joins
Aim: To understand how to relate and access data from multiple tables.
6. Consider the schema given in exercise 2, and execute the following queries
1. Display the names of all employees in department 5 who work more than 10 hours
per week on ProductX project.
2. List the names of all employees who have a dependent with the same first name as
themselves.
3. Find the names of all the employees who are directly supervised by ‘Franklin Wong’.
4. Retrieve the names of all who do not work on any project.
5. Find the names and addresses of all employees who work on at least one project
located in Houston but whose department has no location in Houston.
6. Display the names of all managers who have no dependents.
7. List the employee’s names and the department names if they happen to manage a
department.
8. For each department retrieve project number, and project name.
9. For each project, list the project name and the total hours per week (by all
employees) spent on that project.
10. Get the names of the employees who have 2 or more dependents.
Mini Project (Start after CAT-I) (outcome: m)
Choose a Mini Project and apply the data base concepts as given below.
1. Draw ER Diagram
2. ER-to -Relational Mapping
3. Table Creation
4. Establish the relationship between relevant tables
5. Apply Normalization (if necessary)
6. Create GUI
7. Establish Connection between front end and back end as Oracle (Choose any front
end tool like VB,VC++, .NET ,Java etc.,)
8. Prepare Project Report
9. Demonstration & Presentation(PPT)
Sample Projects:
1. Library Management System
2. Airline Reservation System
3. Hospital Management System
4. Proctor Management System
5. Inventory Management System
Sample ER –to-Relational Mapping for reference:
Database Systems Lab
Cycle sheet –II – PL/SQL
Consider the following schema for PL/SQL programming:
Table Name: Employee
Attribute Data Type
First Name VARCHAR(15)
Mid Name CHAR(2)
Last Name VARCHAR(15)
SSN Number CHAR(9)
Birthday DATE
Address VARCHAR(50)
Sex CHAR(1)
Salary NUMBER (7)
Supervisor SSN CHAR(9)
Department Number NUMBER (5)
Table Name: Department
Attribute Data Type
Department Name Varchar(15)
Department Number Number(5)
ManagerSSN CHAR(9)
ManageStartDate DATE
Exercise – VII: (outcome: e)
Aim: To understand the concept of Anonymous Blocks of PL/SQL.
1. Write a PL/SQL block to change address of a particular employee by taking his/her
employee number interactively.
2. Write a PL/SQL block to display number of employees for each department.
3. Write a program to delete employee details who are having age >60.
4. Write a PL/SQL block to display employees who are top three earners in the
company.
5. Write a PL/SQL to delete a records whose basic salary is <20000 from Emp table.
Exercise – VIII: (outcome: m )
Aim: To know the usage of different sequential and conditional control structures in
PL/SQL blocks.
1. Write a PL/SQL block to find the greatest of three numbers.
2. Write a PL/SQL code to print the employee’s cadre based on their basic scales as
given below (hint use: case selector….)
Basic Scale Cadre
45000 Manager
35000 Supervisor
25000 Worker
Exercise –IX : (outcome: c and m)
Aim: To understand the concepts of Iterations and Subprogram (Procedures and
Functions) of PL/SQL.
Iterations (outcome: c)
1. Write a PL/SQL code to print even number ranging between 1 and 100 in reverse
order.
2. Create a PL/SQL code to check whether the given number is palindrome or not.
Functions (outcome: m )
3. Write a function to find out average, maximum and minimum salary for a given
Department name.
4. Write a PL/SQL to find the factorial of the given number using function.
Procedure (outcome: c)
5. Write a procedure to accept an employee name and display his department
details.
Exercise – X: (outcome: m )
Cursor
Aim: To understand implicit and explicit cursor in PL/SQL
1. Retrieve the employee details that have no supervisors using cursors.
2. Write a cursor program to display manager details for each department.

 

No votes yet.
Please wait...

FOR THE NERDS