Write the following query using SQL: 1) Find all (cid, pid) combinations where t

Question

Write the following query using SQL:
1) Find all (cid, pid) combinations where the customer never ordered the product.

2) List all distinct pairs of pid values for products located in the same city.

Make sure to eliminate the pairing of pid with itself (e.g., (p01, p01)), as well as "symmetric" pairs: if a pair (p02, p07) is already selected, then (p07, p02) should notbe selected as well.

3) Find pid values of products that were never ordered by any customer in the database

4) Find pid value(s) of most expensive product(s) in the database.

5)Get (aid, cid) of agents who placed any order for a cus­tomer who ordered product p07.

Note: An agent in question did NOT necessarily place an order for p07 itself; for example, this query should return the pairs (c006, a01) and (c006, a06), in addition to (c006, a03).

Customers Products cname discount name uantit c001 TipTo Duluth10.00 Dallas 111400 brush Newark 203000 razor Duluth 150600 Duluth 125300 221400 123100 case Newark 100500 Basics cDallas ACMEDuluth encil Agents city percent aname Orders SmithNew York JonesNewark BrownTokyo ordnomonth 1000 450.00 1000 450.00 400 180.00 600 540.0 600 540.00 500 450.00 400 720.00 800 720.00 1000 880.00 800 704.00 1200 1104.00 1200 1104.00 1000 460.00 1000 500.00 600 600.00 800 400.00 New York Otasi 0 8

Explanation / Answer

1.

Select Customers.cid,Products.pid from Customers,Orders,Products where Customers.cid != Orders.cid and Orders.pid != Products.pid;

2.

Select p1.pid,p2.pid from Products p1,Products p2 where p1.pid != p2.pid and p1.city = p2.city;

3.

Select Products.pid from Products,Orders,Customers where Customers.cid != Orders.cid and Orders.pid != Products.pid;

4.

Select pid from Products where price = (Select max(price) from Products);

5.

Select Agents.aid,Customers.cid from Agents,Orders,Customers where Agents.aid = Orders.aid and Orders.cid = Customers.cid and Customers.cid = (Select cid from Customers ,Orders,Products.where Orders.pid = 'p07');

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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