Extra Credit Assignment Due: April 7,2018 at 11:59 PM Note: Please type your sol

Question

Extra Credit Assignment Due: April 7,2018 at 11:59 PM Note: Please type your solution in notepad/notepad++ and save it as “ExtraYourName.sql", otherwise I won't grade it. This is an extra credit assignment, you have a hands-on experience writing advanced SQL query statements subqueries and complex joins. which is designed to let Subqueries: one way to nest or a cascade query is to stick a query in the where clause, find the names of employees worked at the department Research. This is a powerful way to take advantage o any SQL query returns a table, which can then be the starting point of another SQL query f the fact that Complex Joins: the 'where' clause can become quite complex with many joins and related 'and' and 'or conditions. Consider the following database schema for Greensboro Shop Items(itemID string, name string, description string, qtyInStock integer) Departments deptIDstring, name string, location string, telNumber string, budget real, manager string) Suppliers(supID string, name string, address string, city string, state string) Orders(ordID string,ordDate date, shipAddress string) Employees(empID string, firstName string lastName string, ssn string, telNumber string salary real) Carries(deptID string, itemID string) SupplieitemID string,supID string, price real) Includes(itemID string, ordID string, qty integer) WorksIn(deptID string, emplD string) In this assignment, your task is to write SQL queries that answer the following questions based on the database GShop in the ANSI-style JOIN. Each question should be answered by one SQL statement only. The result of each statement must not contain duplicates, but you should use

Explanation / Answer

1)
SELECT I.itemID
FROM Items I, Departments D
WHERE I.deptID = D.deptID AND D.name = 'Junior Clothing' AND
I.itemID IN(
SELECT I1.itemID
FROM Departments D1, Items I1
WHERE I1.deptID = D1.deptID AND && I1.name = 'Girls Teen Clothing'
);

3)
SELECT itemID
FROM Carries
WHERE deptID IN(
SELECT deptID
FROM Departments
WHERE name = 'Junior Clothing') AND
deptID NOT IN(
SELECT deptID
FROM Departments
WHERE name = 'Girls Teen Clothing'
);

4)
SELECT name as DepartmentName
FROM Department
WHERE deptID IN(
SELECT deptID
FROM (SELECT T1.deptID, COUNT(*) as no_of_items
FROM Carries C
GROUP BY deptID) AS T1
WHERE no_of_items=(
SELECT COUNT(*)
FROM Carries C1
WHERE T1.deptID = C1.deptID)
);

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

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