MANAV RACHNA UNIVERSITY, FARIDABAD
Department of Computer Science and Technology
Course: B.Tech. CSE Semester: III
Session: July-Dec 2024 Subject: Database Management System, CSH202B-P
________________________________________________________________________
Lab 8 – Nested co related query and views
SQL and Inner/Natural/Outer/Cross Join
SQL
Lab Outcome: Students will be able to write nested co related queries (sub
querying) and create views – using SQL statement to retrieve rows and also use
appropriate kind of join for combining tuples from two or more relations
1. Find the employee names and the salary drawn for the employees drawing
maximum salary in each department.
2. Find the employee name and address of the employees who are working for
more than a total of 8 hours.
3. Create a view for retrieving department name, average salary, maximum salary
and minimum salary drawn by its employees.
4. Create a view that gives information about employee number, employee name,
salary and address with address ‘Ggn’. Also display the contents of the view
created.
5. Create a view that gives information about client number, client name and
address with address ‘Delhi’ (with restriction of Delhi clients only). Also
display the contents of the view created.
6. Create a view which is based on the details regarding employee name, salary,
dept name, dept. location, for the employees who are located at Fbd or Delhi.
Also display the contents of the view created.
7. Create a view which is based on the details regarding the Department head
names, their corresponding department names, their salary (for the department
heads having salary more than Rs. 20000) for the departments having average
salary more than 15000. Also display the contents of the view created.
8. Create a view which is based on the details regarding the employee’s name,
employee’s salary (in order of their salary) for the employees who are working
on more than one the projects Also display the contents of the view created.
9. Show the resulting salaries if every employee working on the ‘cd’ project is
given a 10 percent raise.
10.Retrieve a list of employees and the projects they are working on, ordered by
department name and, within each department, ordered alphabetically by
employee name.
11.Retrieve the employee name along with manager name (if any) for the
employees working on project name ‘cd’.
12.Retrieve the name of each employee who works on any of the projects ordered
by client number ‘c02’.
13.List the employee name, manager name (if any), and employee salary for each
employee who works in the ‘finance’ department.
14.List the project name, number of employees, and total hours worked per week
on the project for each project with more than one employee working on it.
15.Display the department name, dept. head name, and manager’s salary for every
department.
16.Count the total number of employees whose salaries exceed 10,000 in each
department, but only for departments where more than two employees work.
17.Display employee name along with his dependent name, if any.
18.Display the manager name along with their subordinate employee name.
