DBS-Lab 3 Solved

EMPLOYEE(Fname,Minit,Lname,Ssn,Bdate,Address,Sex,Salary,Super_ssn,Dno) 

DEPARTMENT(Dname,Dnumber,Mgr_ssn,Mgr_start_date) 

DEPT_LOCATIONS( Dnumber, Dlocation) 

PROJECT(Pname,Pnumber,Plocation,Dnum) 

WORKS_ON(Essn,Pno,Hours) 

DEPENDENT(Essn,Dependent_name.Sex,Bdate,Relationship) 

 

 

SSN is  social security number which is unique for each individual 

Super_ssn is supervisor ssn number  

 

 

1.     Assign constraints to the above schema (primary and foreign key constraints) and justify. 

2.     Retrieve the names of all employees who do not have supervisors.  

3.     select the Essn s of all employees who work the same (project, hours) combination on some project that employee ‘John Smith’ (whose Ssn = ‘123456789’) 

4.     Retrieve the name of each employee who has a dependent with the same first name and is the same sex as the employee. 

5.     Retrieve the names of employees who have no dependents. 

6.     List the names of managers who have at least one dependent. 

7.     Retrieve the Social Security numbers of all employees who work on project numbers 1, 2, or 3. 

8.     Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary. 

9.     find the sum of the salaries of all employees of the ‘Research’ department, as well as the maximum salary, the minimum salary, and the average salary in this department. 

10.  retrieve the names of all employees who have two or more dependents 

11.  for each department, retrieve the department number, the number of employees in the department, and their average salary. 

12.  For each project, retrieve the project number, the project name, and the number of employees who work on that project. 

13.  For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project. 

14.  For each project, retrieve the project number, the project name, and the number of employees from department 5 who work on the project. 

15.  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. 

16.  For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department. 

17.  Suppose that we want the number of male employees in each department making more than $30,000, rather than all employees Can we specify this query in SQL? 

18.  Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees. 

19.  Retrieve the names of all employees whose supervisor’s supervisor has ‘888665555’ for Ssn . 

20.  Retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the company. 

21.  Write a query to find the ssn of second highest salaried employee 

22.  Retrieve top 3 Highest salaries records from Employee table 

23.  Retrieve department wise max salary 

24.  List dept no., Dept name for all the departments in which there are no employees in the department. 

25.  Retrieve employee name as a concatenation of Fname , lname and minit 

26.  Retrieve employee names whose first names is of  5  char  length 

27.  Retrieve average salary of female employees 

28.  Retrieve employees who works on all projects handled by department no 5 

29.  Retrieve employees who works on all projects in chennai 

30.  Retrieve employees who dont work on any project  

31.  For each project list project name and total hours(by all employees) spent on that project  

32.  Find the names of all employees who work on atleast one project located in chennai but whose department has no location in chennai 

33.  Display names of all employees who work on some project controlled by dept no. 10 

34.  Retrieve SSN , name of a supervisor who supervises highest no of employees 

35.  Find SSN and first name of supervisor who supervises at least 2 projects   in ascending order of number of employees under him 

36.  Display the employees whose salary exceeds the department managers salary that employee(s) work for  

37.  Display employee names who either work for research department or supervised by an employee from research department  

38.  Display employee names who either works for research department or supervise and employee from research department  

39.  Find all employees who were born during the 1950s 

40.  Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ProductX project. 

41.  Count the number of distinct salary values in the database. 

42.  Retrieve employees who works on atleast one project from each department  

43.  Retrieve the names of employees who work on all the projects that  “John Smith”’ works on. 

44.  List  project numbers for projects that involve an employee whose last name is ‘Smit’’, either as a worker or as a manager of the department that controls the project 

For every project located in  “Chennai” , list the project number, the controlling department number, and the department manager’s last name, birth date, and address