Specify the SQL queries on the COMPANY relational database schema shown below: 1

Question

Specify the SQL queries on the COMPANY relational database schema shown below:

1) List the names of all employees who have a dependent with the same first name as themselves.

2) For each project, list the project name and the total hours per week (by all employees) spent on that project.

3) retrieve the names of all employees who work on every project.

4) retrieve the names of all employees who do not work on any project.

5) for each department, retrieve the department name and the average salary of all employees workin gin that department.

6) list the last names of all department managers who have no dependents.

Explanation / Answer

Solution:

1)

SELECT Fname, Lname

FROM EMPLOYEE, DEPENDENT

WHERE EMPLOYEE.Fname= DEPENDENT.Dependent_name

2)

SELECT SUM(Hours)

FROM PROJECT, WORKS_ON

WHERE PROJECT.Pnumber=WORKS_ON.Pno

GROUP BY PROJECT.Pnumber

3)

There are many methods to find this, use count to find the employee who has a count =6 in WORKS_ON table

SELECT Ssn

FROM EMPLOYEE, WORKS_ON

WHERE COUNT(ESsn) = 6 AND EMPLOYEE.Ssn= WORKS_ON.ESsn

4)

SELECT FNAME, LNAME, SsN

FROM EMPLOYEE, WORKS_ON

WHERE COUNT(ESsN) = 0 AND EMPLOYEE.Ssn= WORKS_ON.ESsn

5)

SELECT Dname, AVG(Salary)

FROM EMPLOYEE, DEPARTMENT

WHERE EMPLOYEE.Dno= DEPARTMENT.Dnumber

GROUP BY Dnumber.

6)

SELECT LNAME

FROM EMPLOYEE, DEPARTMENT

WHERE DEPARTMENT.Mgr_ssn= EMPLOYEE.Ssn

AND

NOT EXISTS (SELECT Ssn

                        FROM DEPENDENT, DEPARTMENT

                        WHERE DEPARTMENT.Mgr_ssn= DEPENDENT .Essn)

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.