(a) Explain the following query in English (based on the company database) SELEC

Question

(a) Explain the following query in English (based on the company database) SELECT FNAME, LNAME, SSN FROM EMPLOYEE WHERE NOT EXISTS (SELECT * FROM WORKS_ON B WHERE (B.PNO IN (SELECT PNUMBER FROM PROJECT WHERE DNUM = 5) AND NOT EXISTS (SELECT * FROM WORKS_ON C WHERE C.ESSN = SSN AND C.PNO = B.PNO)) ); (b) Modify the query to list employees who work on any of the projects controlled by department number 5. (c) Write SQL statements (in MySQL) to get intersection, difference, and union sets of the following two tables. SELECT PNAME FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME='Wong'; SELECT PNAME FROM PROJECT, WORKS_ON, EMPLOYEE WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME='Smith'; (d) Based on the company database, write a SQL statement to retrieve the project number, project name, and number of employees who work on that project with a condition that at least two employees must work on each project. (e) Explain why updates on materialized view are often difficult to perform. (f) Create a view named “WhoWorks” that includes tuples of the employees SSN, first name, last name, birth date, project name, and number of hours (for an employee works

Explanation / Answer

a.First of all The inner queries get executed first
1)It displays firstname,lastname,ssn of the employees
who work on projects with department=5

b.SELECT FNAME, LNAME, SSN FROM EMPLOYEE
WHERE EXISTS (SELECT * FROM WORKS_ON B WHERE
(B.PNO IN (SELECT PNUMBER FROM PROJECT
WHERE DNUM=5)
AND EXISTS (SELECT * FROM WORKS_ON C
WHERE C.ESSN = SSN AND C.PNO = B.PNO)) );

5c.
create table tmp1(pname varchar(25));
create table tmp2(pname varchar(25));

insert into tmp1(pname)

SELECT DISTINCT PNAME
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME=’SMITH’;

insert into tmp2(pname)

SELECT PNAME
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME=’SMITH’;

intersection:

select pname from tmp2 where
pname in (select pname from tmp1);

difference:

select pname from tmp2 where pname not
in (select pname from tmp1);

union:

SELECT PNAME
FROM PROJECT, DEPARTMENT, EMPLOYEE, WORKS_ON
WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME=’SMITH’

UNION

SELECT PNAME
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME=’SMITH’

5d.
select Project_number,Project_name,count(employees) employee
having count(*)>=2

5e.
1)Materialized view are the view that are built by joining multiple tables
by performing aggregation functions
2)These Materialized views are often used as temporary tables for efficient usage of them

5f.
create view WhoWorks
as
select ssn,firstname,lastname,birthdate,project name,sum(hours) as number_of_hours
from Employees
group by ssn,firstname,lastname,birthdate,project name

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

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