product pid,name, pdesc) customer cid, cname country) order (pid, cid, qty Trans

Question

product pid,name, pdesc) customer cid, cname country) order (pid, cid, qty Translate the following queries into the indicated language. Use your knowledge of integrity constraints to simplify the queries. Do not use the keyword INNER JOIN. Prefer simpler queries to nested queries, to aggregates, to algebraic queries and other complicated answers, if possible and unless otherwise indicated. Question 11. (4 marks) (Algebra) Find the name of the customers who have ordered some products and the name of the products. Do not use Join operators (a);prefer Cartesian Product (x). Feel encouraged drawing the query as a tree. Question 12. (4 marks) (SQL) Find the name of the customers who have ordered on average per order strictly more than the average quantity per order ordered by customers in their country. The average quantity per order is the average of the quantities of individual orders.

Explanation / Answer

11.answer)

cname,pname ( product.pid=order.pid ^ order.cid=customer.cid (order × product × customer))

First it finds the cross product of 3 tables order and product and customer .After that it finds the tuples which are having product.pid=order.pid and order.cid=customer.cid.Finally it finds the cname and pname from above tuples.

12 answer)

CREATE VIEW V_Customer
AS SELECT customer.cid,avg(qty) from customer,order group by customer.cid order by country;

select cname from customer,V_Customer where (select avg(qty) from order)<V_customer.avg(qty) and V_customer.cid=customer.cid;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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