Instructions Try to answer al he questions using what you have learned in class
ID: 3755953 • Letter: I
Question
Instructions Try to answer al he questions using what you have learned in class . When writing a query, write the query in a way that it would work over all possible database instances and not just for the given example instance! Consider the following database schema and example instance: Employee Customer cid enamecity state Chase New Yo 102 CitiNw York NY A103 TD BankBoston MA A104MotoChicagoIL A105DisneyOrlandoFL eid ename dob Chris 01/03/1986 100,000 A101 rk NY 0012 Edward /24/1973 120,000 0013 Anna 08/12/199280,000 0014 Kim 08/26/198695,000 0015 Carol 04/15/1988 97,000 Product pid pname unit_ price C17812 Chair C23453 PC C34451Mae 200 1.200 1,300 Sales Manages eid cid pid quantity 0011 A101 0011 A102 C17812 0012 A102 C23453 D013 A101 C34451 200 100 50 0013 0017 0012 0013 Hints: .Relation Sales stores the sales information: the salesman (empoee ID) who is in charge of the ordered . The attributes eid, cid and pid of relation Sales are the foreign keys to relations Employee (salesman) .Relation Manages stores the "managinginformation in the company (e.g., the employee with eidl is the Underlined attribute(s) form the primary key of a relation. product, the produet ID, the eustomer ID, and the quantities of ordered produets. Customer and Product, respectively. direet manager of the employee with eide) All the IDs' are stringsExplanation / Answer
Answer:
Please find the SQL Queries (written in MSSQL) and screenshot. Please let me know in case of anyu questions.
Screenshot:
SQL Queries:
--Find the IDs of all the products, with the total ordered quantity in 'Chicago' greater than the total ordered quantity in 'Boston'.
Select ChicagoSales.PID from
(Select pid,Sum(Quantity) as Qty from Sales JOIN customer on Sales.cid = Customer.cid
where customer.city = 'Chicago' group by pid) as ChicagoSales JOIN
(Select pid,Sum(Quantity) as Qty from Sales JOIN customer on Sales.cid = Customer.cid
where customer.city = 'Boston' group by pid) as BostonSales ON ChicagoSales.pid = BostonSales.pid
where ChicagoSales.Qty > BostonSales.Qty
--List each of the employees and the total number of employees directly managed by him/her (if no one directly managed, then return 0).
Select eid1 as Manager,Count(eid2) as NoOfEmployees
from Manages group by eid1
--For every product, return the name of the customer who ordered the highest quantity (from all the employees).
Select product.pname, customer.cname from
(Select sales.pid,sales.cid, MAX(sales.quantity) Sales1Qty from
Sales group by sales.pid,sales.cid) as Sales1
JOIN
(Select sales.pid,MAX(sales.quantity) Sales2Qty from
Sales group by sales.pid) as Sales2 ON Sales2.pid = Sales1.pid and Sales2.Sales2Qty = Sales1.Sales1Qty
JOIN Customer On Sales1.cid = customer.cid
JOIN product on Sales1.pid = product.pid and Sales2.pid = product.pid
order by 1,2
--List all the customers (cid and cname) which have completed orders with Chris and all the employees directly managed by him.
Select customer.cid,customer.cname from
customer join Sales on sales.cid = customer.cid
Join Manages on Manages.eid2 = sales.eid
Join employee on employee.eid = Manages.eid1
where employee.ename = 'Chris'