SQL Review Exercise Spring 2015 Given a company database schemas as below: EMPLO
ID: 638852 • Letter: S
Question
SQL Review Exercise Spring 2015 Given a company database schemas as below: EMPLOYEE Lname Ssn Bdate Address Sex Salary Super ssn no Frame Minit DEPARTMENT Dname Dnumbe Mgr ssn Mgr start date DEPT LOCATIONS Dnumber Dlocation PROJECT name Pnumber Plocation Dnum WORKS ON Essn Pno Hours Figure 3.7 DEPENDENT Referential integrity constraints displayed Essn Dependent name Sex Bdate Relationship on the COMPANY relational database schema. 1. Retrieve the average salary, minimum and maximum salary of the male employees. 2. Retrieve the first name, last name, and SSN of each employee along with the department name he/she is working for 3. Retrieve the number of employees of department 4. 4. Retrieve the names of employees who work on the project "ProjectX". 5. Retrieve the first name and last name of the male employee who has the highest salary 6. Retrieve the first names and last names ofthe department managers who DON'T have a dependent 7. For each project, list its name, its controlling department's name, and its controlling department manager's first name and last name. 8. For the department that controls the most number of projects, list its name 9. For each department, list its name along with the names of projects that it controls. 10. Retrieve the names and SSNs of employees who have at least two dependents.Explanation / Answer
1.SELECT AVG(salary) FROM Employee.
2.Select Fname,Lname,SSN,Dept from Employee where works_on.essn=Employee.SSN
3.Select Fname from Employee where dept=4.
4.Select Fname Where Project.Pname="Projectx" and Employee.Dno=Project.Dnum,
5,Select Fname,Lname From Employee Where Sex="M" and Salary>10000.
6.Select Fname,Lname From Employee where Department.Desg=Manager.
7.Select Fname,Lname From Employee where Department.ssn=Employee.ssn,Department.Dname.
8.Select MAX(Project.Dno) From Projects.
9.Select Department Where Projects.Dnum="5" From Department.Dname.
10.Select From Employee.Fname Where Depedent.Essn=Employee.SSN
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.