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 siring, description string, gtyInStock integer) artments(depIDing, name string, location string, telNumber string, budget real, manager string) Suppliers(supID string, name siring, address string, city string, state string) Orders(ordID string.ordDate date, shipAddress string) E mplo sempl ) string, stame string, ast ame string,?nstring, tel umber string, salary real) Carrics(dcptlDstring, itemlD string) Supplie(itemlD siring, suplD string, price real) Includes(itemIDstring, ordlD string, qty integer) WorksIn(deptD sring, emplD siring) 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 deparments may be renamed as "Deparlment Name." Please put your SQL query statements and their results into a SQL script file. Run your SQL 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 2. For the Information Technology department, find the difference between the highest salary and lowest salary

Explanation / Answer

1.select S.SupID,S.name,I.name
from Suppliers S
inner join Supplie
on S.supID=Supplie.SupID
inner join Items I
on Supplie.ItemId=I.ItemID
order by S.name

2.select (max(salary)-min(salary))as difference
from Employees E
inner join Departments D
on E.tclNumber=D.tclNumber
where D.name='Information Technology';

3.select S.name from Suppliers S
where S.city=(select city from Suppliers where name='Best Deals')

4.select S.name,itemid
from Suppliers S
inner join Supplie
on S.supID=Supplie.SupID
inner join Items I
on Supplie.ItemId=I.ItemID
where supId in(select supId from suppliers
having count(*)>2)

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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