Based on company database (a) Explain what would be retrieved by the following q

Question

Based on company database (a) Explain what would be retrieved by the following query SELECT DISTINCT PNUMBER FROM PROJECT WHERE PNUMBER IN (SELECT PNUMBER FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM-DNUMBER AND MGRSSN = SSN AND LNAME = 'Smith') OR PNUMBER IN (SELECT PNO FROM WORKS ON, EMPLOYEE WHERE ESSN = SSN AND LNAME = 'Smith'); (b) Explain what would be retrieved by the following query select distinct pnumber from project, department, employee, works_on where (dnum= dnumber and mgrssn=ssn and lname='smith') or (Pnumber = pno and essn = ssn and lname='smith'); (c) Write a SQL statement to retrieve a list of all project names of the projects, which involve an employee whose last name is 'Smith' as a worker on the project, or as a manager of the department that controls the project (d) Write a SQL to retrieve a list of employees and the projects they are working on, ordered by the employee's department, and within each department ordered alphabetically by employee last name (e) Write a SQL statement to retrieve the project number, project name, and the number of employees who work on that project for each project. (f) Explain a view, and explain the view created by the following SQL statement: CREATE VIEW WORKS ON AS SELECT FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS ON WHERE SSN = ESSN AND PNO-PNUMBER;

Explanation / Answer

The Answers for your question are given below clearly:

a) The Explanation for your query is given below
the given query has two select queries that means inside the select query another sub query select query is present so first the subquery will be executed and it will retrieve PNUMBER from PROJECT TABLE, DEPARTMENT TABLE AND EMPLOYEE TABLE for only DNUM EQUALS DNUMBER AND MGRSSN EQUALS SSN AND LNAMEEQUALS SMITH and finally the main select query is used to select only distinct that means different PNUMBER values from the select sub query.

b) this query is also similar to the query i answered in a) but this query is used to retrieve distinct values of pnumber from the three tables project, department and employee and the condition for this three tables is that dnum=dnumber and essn=ssn and lname=smith.

c)select PNAMES
from projects
where LNAME='smith'.

Hope This Helps, if you have any doubts Please comment i will get back to you, thank you and please thumbs up

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

---

---

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