Instructions Try to answer al he questions using what you have learned in class
ID: 3753119 • 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 instanoe: Employee Customer eid ename 0011 0012 Edward7/24/1973 120,000 0013 Anna 08/12/1992 80.000 0014 Kim 08/26/1986 95.000 0015 Carol 04/15/1988 97,000 cnametystate New York NY Citi New York NY A103 TD BankBoston MA A104MotoChicagoIL A105DisneyOrlandoFL dob Chris 01/03/1986 100,000 A102 Product pid pname unit_ price C17812 Chair C23453 PC C34451Mae 200 1.200 1,300 Sales Manages eidi eid2 0011 A102 C17812 D012 A102 23453 0013 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
1)
-- Since sales table is related with Employee and Product table via eid and pid it is required
-- to perform joins on these tables to get results
-- Further We need employees who deal with Mac only so where condition is required
SELECT e.ename AS ename FROM Employee e
INNER JOIN Sales s ON s.eid = e.eid
INNER JOIN Product p ON p.pid = s.pid
WHERE p.pname = 'Mac'
2)
--Since the fields ename, cname, pname and quantity are from three tables
--Employee, Customer and Sales so joining them is required
-- Further we need customers from NY state only so where conditio is requried
SELECT e.ename AS ename, c.cname AS cname, p.pname AS pname,s.quantity AS quantity
FROM Employee e
INNER JOIN Sales ON s.eid = e.eid
INNER JOIN Product ON s.pid = p.pid
WHERE c.state='NY'
3)
SELECT p.pid as pid , p.pname as pname from PRODUCT per
INNER JOIN Sales s ON s.pid = p.pid
INNER JOIN manages m ON e.eid = m.eid1
INNER JOIN Customer c ON c.cid = s.cid
WHERE m.eid2 = '0013' AND s.quantity > 300
4)
--Since Sales is the only table which connects Employee and Customer
--hence needs to be joined. Further, there is a mention of city and therefore
--Customer table is also joined.
--Finally the where conditions are used to filter city and salary as per problem statement
SELECT e.eid AS eid from Employee eid
INNER JOIN Sales s ON s.eid = e.eid
INNER JOIN Customer c ON c.cid = s.cid
WHERE c.city = 'Chicago' AND
e.salary > 100000
6)
SELECT p.pname AS pname, p.unit_price AS unit_price FROM Product p
INNER JOIN ON Sales s.pid = p.pid
INNER JOIN ON Custoer c.cid = s.cid
WHERE c.cname != 'Disney'
7)
SELECT e.ename AS ename, c.state AS state, SUM(s.quantity) AS total_sales FROM Employee eid
INNER JOIN Sales s ON s.eid = e.eid
INNER JOIN Customer c ON c.cid = s.cid
WHERE e.ename='Edward'
8)
SELECT e.eid AS eid, p.pid AS pid, AVG(s.quantity) AS avg_sales FROM Employee e
INNER JOIN Sales s.eid = e.eid
INNER JOIN Customer c c.cid = s.cid
10)
SELECT p.pid as pid from Product p
INNER JOIN Sales s s.pid = p.pid
INNER JOIN Customer c c.cid = s.cid
WHERE AVG(s.quantity) < 1000