Implement any three of the Problem queries in a different DBMS .you could do the

Question

Implement any three of the Problem queries in a different DBMS .you could do the implementation for this Problem in MySQL or Oracle or SQL Server. Note that you will first need to create the relevant tables.

This problem is based on Elmasri's company database. For each of the following, specify the query using SQL : this is a paper and pencil problem, you do not need to implement these queries, but if you want to implement them, you are free to do so.

(a) What are the names of the projects on which the combined hours (i.e. the total of all the hours of all the employees who work on that project) are more than 50.
(b) Specify the following view in SQL: A view that has the department name and the number of locations for that department for each department. Please note that here you have to use the CREATE VIEW" statement.
(c) List the names of the projects such that every employee works on that project.
(d) How many employees from the Administration department have at least three dependents.
(e) What is the name of the department whose employees have the lowest average salary.

Explanation / Answer

(a)

SELECT pname

FROM project,works_on

WHERE pnumber=pno

GROUP BY pnumber
  HAVING SUM(hours) > 50;

(b)

CREATE VIEW department_locations AS
SELECT dname, COUNT(dl.dlocation) as Number_of_locations
FROM department d,dept_locations dl
WHERE d.dnumber=dl.dnumber
GROUP BY dname;

(c)

SELECT PNAME
FROM PROJECT
WHERE NOT EXISTS ( SELECT SSN
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT *
FROM WORKS_ON
WHERE PNUMBER=PNO AND ESSN=SSN ) )

(d)

SELECT fname,lname
FROM employee, department
WHERE ( SELECT COUNT(*) FROM dependent
WHERE ssn=essn ) >= 3 AND
dno = dnumber
AND dname='Administration';

(e)

SELECT dname, AVG(employee.salary)

FROM
department, employee
WHERE dno = dnumber
GROUP BY dname
ORDER BY 1 LIMIT 1;

Please give Thumbs up,if it helped you :)

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

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