Interactive SQL

1. Get cities of agents booking an order from customer c002 (Join, Projection and Restriction).

2. Get pids of products ordered through any agent who makes at least one order for a customer in Kyoto. NOTE: this is not the same as asking for pids of products ordered by a customer in Kyoto (hint: Customers Table once and Orders table twice).

3. Find cids of customers who did not place an order through a03 (hint: use NOT EXISTS or NOT IN; May also see adbc, Misconceptions: do not use NOT =) .

4. Name of products that were not ordered through agents _a04 (hint: use NOT EXISTS or NOT IN; May also see adbc, Misconceptions: do not use NOT =)

5. Find cids of customers who have the largest discount and those who have the smallest discount. Must be only one query. (Hint: Use UNION).

6. Find aids and anames of agents with aname beginning with the letter J or B who do not place orders for a Product located in New York (Hint: Use LIKE clause or )

7. List the cname and the sum of dollars that each customer ordered. (Functions, GROUP BY)

PRODUCTS CUSTOMERS cid cecity c01 TipTop Duluth 10.00 002 Basics Dalla12.00 e003 Allied Dalas 8.00 004 ACME Duluch 8.00 c006 ACME Kyoto 0.00 pnanecity quantity price disent p01cmbDallas400 0.50 p02 | brush | Newark | 203000 0.50 p03 azor Duluth 150600 100 p04 | pen | Duluth | 125300 1.00 pOS pencil Dallas 221400 1.00 p06 folder Dalias 123100 2.00 p07 case Newark 100500 1.00 ORDERS AGENTS 1011 0001 0 40.00 1012 an c0 p01100045000 1019 | feb e001. | a02 P02 400 180.00 1017 feb 006p03600540.00 1018 eb 00103 600 40.00 1023 m 001 04 500 450.00 1022 marp6720.00 1025 | apr | c001 | a05 | p07 |.800 | 720.00 | 1013. | jan | c002 | .03 | p03 1000 | 880.00 1026mayp3 704.00 1015 an c003.03 pOS 1200 1104.00 1014 jan 03pOS 12001104.00 1021 eb00406 p1 1000 460.00 1016 | jan | c006 a011 pol 1000 500.00 1020feb00603 600600.00 1024 mar c006 06 p 800400.00 percent 01 Smith New York 6 02 Jones Newark 03 rown Tokyo 04 Gray New York a06 Smith Dallas