CS-256 DBMS LAB
CYCLE – I
________________________________________________________________________
I. Implement create table, alter table and drop table statements
II. Illustrate insert, delete and update statements
III. Simple queries: selection, projection, sorting on a simple table
1. Write a query to retrieve the details of all employees working in the company.
2. Write a query to retrieve the names and salaries of all employees.
3. Write a query to retrieve all salary values.
4. Write a query to retrieve distinct salary values.
5. Write a query to show resulting salaries if every employee working in
department 5 is given a 10 percent raise.
6. Write a query to retrieve birth date and address of the employee(s) whose
name is ‘John B Smith’.
7. Write a query to retrieve all employees whose address is in Houston, Texas.
8. Write a query to retrieve all employees who were born during the 1950s.
9. Write a query to retrieve all employees whose salary is between $30,000 and
$40,000.
10. Write a query to retrieve names and salaries of employees in the descending
order of their salaries
11. Write a query to retrieve the names of all employees who do not have
supervisors.
II. Multi-table queries(JOIN OPERATIONS, GROUP BY CLAUSE, HAVING CLAUSE, AGGREGATE
FUNCTIONS)
1. Write a query to retrieve name and address of all employees who work for the
‘Research’ department.
2. Write a query to retrieve employee’s first and last name and first and last name of his or
her immediate supervisor.
3. Write a query to retrieve list of employees and the projects they are working on,
ordered by department and with in each department, ordered alphabetically by last
name, first name.
4. For every project located in ‘Stafford’, list the project number, the controlling
department number and the department manager’s last name, birth date.
5. Without using a nested query, retrieve the names of employees who have at least one
dependent.
6. Find the sum of the salaries of all employees, the maximum salary, the minimum salary
and the average salary.
7. Find the sum of the salaries of all employees, the maximum salary, the minimum salary
and the average salary of all employees of the ‘Research’ department.
8. Count the number of employees working in the ‘Research’ department.
9. For each department, retrieve the department number, the number of employees in the
department and their average salary.
10. For each project, retrieve the project number, Project name and the number of
employees who work on that project.
11. For each project on which more than two employees work, retrieve the project number,
project name and the number of employees who work on the project.
12. For each project, retrieve the project number, Project name and the number of
employees from department 5 who work on the project.
III. Nested queries
1. Write a nested query to retrieve the name of each employee who has a dependent with
the same sex as the employee.
2. Write a nested query to retrieve the name of each employee who has a dependent with
the same sex as the employee using exists.
3. Write a query to show resulting salaries if every employee working on ‘ProductX’ project
is given a 10 percent raise.
4. For each department that has more than five employees, retrieve the department
number and the number of its employees who are making more than $40,000.
5. Write a nested query to retrieve the names of employees who have no dependents.
6. Write a nested query to list the names of managers who have at least one dependent.
7. Write a nested query to retrieve the names of all employees who have two or more
dependents.
8. Write a nested query to retrieve the SSNs of all employees who work on project number
1, 2 or 3.
9. Write a nested query to retrieve the names of all employees whose salary is greater
than the salary of all the employees in department 5.
IV. Set Oriented Operations
1. Write a query to make a list of all project numbers for projects that involve an employee
whose last name is ‘Smith’, either as a worker or as a manager of the department that
controls the project.
2. Write a query to retrieve the SSNs of employees who worked on projects 1 or 2 but not
on 3.
3. Write a query to retrieve the names of employee who worked on all the projects
controlled by department 5.
