This assignment is designed to let you have a hands-on experience writing advanc

Question

This assignment is designed to let you have a hands-on 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 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 deptD string, name string, location string, lelNumber string, budget real, manager string) Suppliers supID string, name string, address string, city string, state string) Orders ordlD string.ordDate datc, shipAddress string) Employccs(cmplD string. firstNamc string, lastNauc string, ssn string. telNumber string, salary real) Carrics deptiD string, itemlD string) SupplietitemlD string, supID strimg, price real) Includes(itemID siring, ordlDtring, qty integer) WorksIn(deptD string, smpID siring) In this assignment, your task is to write SQL queries that answer the following questions based on the database GShop in the ANSI-stylc JOIN. Each question should be answered by one SQL statement only. The result of each statement must not contain duplicates, but you should use the SQL keyword distinct only when necessary. For clarity, each output column should be properly renamed. For example, the output column of name from the table departmenis may be renamed as "Department Name." Please put your SQL query statements and their results into a SQL script file. Run your SOL sttements on the MySQL Database Server. Make sure that your SQL script file does not contain any syntax errors before submitting to the Assignment Link - Homework 6. 1. Find a list of suppliers and the items that supply, alphabetically by the supper's name

Explanation / Answer

1) Three tables are joined on common attributes to get the supplier name and the items they supply. Appropriate aliases are used where necessary.

SELECT sr.name AS 'Supplier Name', i.name AS 'Item Name'
FROM Suppliers AS sr
INNER JOIN Supplies AS s
ON sr.supID = s.supID
INNER JOIN Items AS i
ON i.itemID = s.itemID;

2) To get the difference between the salary aggregate function max and min are used. Their difference is calculated. In where clause department name information technology is mentioned to select the salary of this department only.

SELECT (MAX(e.salary) - MIN(e.salary)) AS 'Salary diff'
FROM Employees AS e
INNER JOIN WorksIn AS w
ON w.empID = e.empID
INNER JOIN Departments AS d
ON d.deptID = w.deptID
WHERE d.name = 'Information Technology';

3) Subquery is used in where clause to get the city of supplier Best deal. Supplier of this city is fetched.

SELECT *
FROM Suppliers
WHERE city = (SELECT city FROM Suppliers WHERE name = 'Best Deal')
AND name <> 'Best Deal';

4) Aggregate function COUNT is used to count the suppliers of each item. Group by clause is used to group the result by item. In having clause all the items are selected with less than 2 suppliers.

SELECT i.itemID, i.name AS 'Item Name', COUNT(s.supID)
FROM Items AS i
INNER JOIN Supplie AS s
ON s.itemID = i.itemID
GROUP BY itemID, name
HAVING COUNT(s.supID) < 2;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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