In this assignment, your task is to write SQL queries that answer the following

Question

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 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 departments may be renamed as "Départment Name." Please put your SQL query statements and their results into a SQL script file. Run your SOL statements 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. For the Information Technology department, find the difference between the highest salary and lowest salary. 2. 3. Find the name of all suppliers who locate in the same state as Best Deals. Note that Best Deals is a supplier name. Your output result shbuld exclude Best Deals. 4. Find the name and id of all items that supply by less than two suppliers. Use a nested query

Explanation / Answer

Please create SQL Schema as given in attachment.

Please paste error at comment, if you are getting any error.

SELECT S.supID, S.name
FROM (Suppliers S INNER JOIN Supplie U ON S.supID = U.supID)
INNER JOIN Items I ON U.itemID = I.itemID
ORDER BY S.name;

SELECT (MAX(salary) - MIN(salary)) AS difference
FROM (Employee E INNER JOIN Works IN W ON W.empID = E.empID)   
INNER JOIN Departments D ON D.deptID = W.deptID
WHERE D.name = 'Information Technology';

SELECT S.name
FROM Suppliers S
WHERE S.city IN (
SELECT city
FROM Suppliers
WHERE name = 'Best Deals'
);

SELECT I.name, I.itemID
FROM Items I
WHERE 2>(
SELECT COUNT(*)
FROM Supplie U
WHERE U.itemID = I.itemID
);

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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