2. (30 points) This problem is based on Elmasri’s company database. For each of

Question

2. (30 points) 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. Feel free to create views or to save queries (to help answer a more complex query) or to create other tables (eg: using the INTO command discussed in class) if that helps you. If you are using views or subqueries, show the SQL to create the views and subqueries. (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. 3. (10 points) This problem is based on Elmasri’s company database, In each of these SQL queries, there is a mistake. • Explain clearly and briefly what the mistake is. Please note that I am not looking for a minor SQL syntax error here, but rather you have to find a significant semantic (logic) error. • Give the correct SQL for specifying the query. (a) Find the names of the employees who do not work on any project. (SELECT FNAME, LNAME FROM EMPLOYEE ) EXCEPT (SELECT FNAME,LNAME FROM EMPLOYEE, WORKS_ON WHERE EMPLOYEE.SSN = WORKS_ON.ESSN ); (b) Find the social security number of employee with the lowest salary. SELECT SSN FROM EMPLOYEE WHERE SALARY <= ANY (SELECT SALARY FROM EMPLOYEE); Extra Credit 4: This problem is based on Elmasri’s company database. Note that you will first need to create the relevant tables. Create a report which gives the name and salary of all thise employees who have at least 2 dependents and who work on at least 2 projects. Turn in a hardcopy of the report definition, and a hardcopy of a sample report. You also need to submit the soft copy on blackboard. Extra Credit 5: Implement any three of the Problem 2 queries in a different DBMS than the one you used for Problem 1. For example, if you used Microsoft Access for Problem 1, you could do the implementation for this Extra Credit Problem in MySQL or Oracle or SQL Server. Note that you will first need to create the relevant tables. Turn in a hardcopy of the tables, a hardcopy of the SQL, a hardcopy of the results of each of the query

Explanation / Answer

2)

(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;

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

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