[Solved] DBS Lab3-Primary and foreign key constraints

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 supervisors 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
45. For every project located in Chennai , list the project number, the controlling department number, and the department managers last name, birth date, and address.