Write the following querys using SQL: 1) Retrieve pname and price of each produc

Question

Write the following querys using SQL:

1) Retrieve pname and price of each product ordered in March in the quantity of more than 600 units per order

2) Give all (aname, pname) pairs where the agent places an order for the product.

3) Get (pid, qty) pairs of values for each order that a customer from Dallas placed through an agent located in Dallas.

4) Get pid values of products ordered by any customer who never ordered product p04.

5) List all cities where either a customer who ordered product p01 or an agent who placed an order for p01 is located.

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

ANSWER:

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

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

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);

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.