Homework Assignment 6 Due: Mar. 29, 2018 at 11:59 PM Note: Please type your solu

Question

Homework Assignment 6 Due: Mar. 29, 2018 at 11:59 PM Note: Please type your solution in notepad/notepad++ and save it as “HW6YourName.sqI", otherwise I won't grade it. This assignment is designed to let you have a hands-orn experience writing advanced SQL query statements- subqueries and complex joins. 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 of the fact that any SQL query returns a table, which can then be the starting point of another SQL query. Complex Joins: the 'where' clause can become quite complex with conditions. many joins and related and' and or Consider the following database schema for Greensboro Shop. Items(itemID string, name string, description string, qtylInStock integer) Departments(deptID string, 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, teINumber string, salary real) Carries(deptID string, itemID string) Supplie(itemID string.suplD 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

I have small doubt on Q3. What is mean of Best Deal? I wrote quote as per I understood...Please give me comment, if it is need to change...

1)
SELECT *
FROM (Suppliers S JOIN Suppies SP ON S.supID = SP.supID) JOIN Items I ON I.itemID = SP.itemID
ORDER BY S.Sname;

2)
SELECT (MAX(salary) - MIN(salary)) AS DIFFERENCE FROM Departments WHERE name='Information Technology';

3)
SELECT state, name AS SupplierName
FROM Suppliers
GROUP BY state, name;

4)
SELECT name AS ItemName, itemID
FROM Items I
WHERE 2>(SELECT COUNT(*)
FROM Supplie S
WHERE I.itemID = S.itemID);

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

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