III Semester

DATABASE MANAGEMENT SYSTEMS LAB

Course Code : C3-P                                         

Course Title : DATABASE MANAGEMENT SYSTEMS LAB

Hours: 30

Credits: 1

1. Draw ER diagram for hospital administration
2. Creation of college database and establish relationships between tables
3. Relational database schema of a company is given in the following

Questions to be performed on above schema

1. Create above tables with relevant Primary Key, Foreign Key and other constraints
2. Display all the details of all employees working in the
3. Display ssn, lname, fname, address of employees who work in department no
4. Retrieve the Birthdate and Address of the employee whose name is ‘Franklin T. Wong’
5. Retrieve all distinct salary values
6. Retrieve all employee names whose address is in ‘Bellaire’
7. Retrieve all employees in department 5 whose salary is between 50,000 and 60,000(inclusive)
8. Retrieve the names of all employees who do not have supervisors
9. For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name, address, and birth
10. For each employee, retrieve the employee’s name, and the name ofhis or her immediate supervisor.
11. Retrieve all combinations of Employee Name and Department Name
12. Make a list of all project numbers for projects that involve an employee whose last name is ‘Narayan’ either as a worker or as a manager of the department that controls the
13. Increase the salary of all employees working on the ‘ProductX’ project by 15%. Retrieve employee name and increased salary of these
14. Retrieve a list of employees and the project name each works in, ordered by the employee’s department, and within each department ordered alphabetically by employee first
15. Select the names of employees whose salary does not match with salary of any employee in department
16. Retrieve the employee numbers of all employees who work on project located in Bellaire, Houston, or
17. Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary. Display with proper
18. Find the sum of the salaries and number of employees of all employees of the ‘Marketing’ department, as well as the maximum salary, the minimum salary, and the average salary in this
19. Select the names of employees whose salary is greater than the average salary of all employees in department
20. Delete all dependents of employee whose ssn is ‘123456789’.
21. Perform a query using alter command to drop/add field and a constraint in Employee table.
22. Create a Function in PL/SQL to find factorial of given number
23. Create a Stored Procedure to increase salary ofall employees by 3% and display their modified details