Using the Company Database Schema and Company Database design following queries

Question

Using the Company Database Schema and Company Database design following queries in sQL Query 1: Retrieve the First and Last name of all the managers who work on the Project located in Houston and controlled by the department they manage. Also list the name of the department they manage. Query 2: Retrieve the First and Last name of all the managers who work on Project located in Houston and has no dependents Query 3:List First Name of all the managers and name of the dependent if they have any. Also list the name of the department they manage

Explanation / Answer

If you have any doubts, please give me comment...

-- 1)

SELECT fname, lname

FROM employee m, department d, project p, works_on w

WHERE m.ssn = d.mgrssn AND d.dnumber = dnum AND p.pnumber = w.pno AND plocation="Houston";

-- 2)

SELECT fname, lname

FROM employee m, department d, project p, works_on w

WHERE m.ssn = d.mgrssn AND d.dnumber = dnum AND p.pnumber = w.pno AND plocation="Houston" AND m.ssn NOT IN(

SELECT essn

FROM dependent

);

-- 3)

SELECT fname, dependent_name, dname

FROM employee m, department d, dependent dep

WHERE m.ssn = d.mgrssn AND m.ssn = dep.essn;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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