SQL

Customer Table (cname), agent table (aid), product table (pid), order table (ordno)

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

2. List the cname and the sum of dollars for each customer that has ordered more than US1,000 ( HAVING)

3. List the customers with the two smallest discounts (Hint: use NOT EXISTS).

4. List customers that didn' t have any orders (Hint: use NOT IN).

5. List the name each customers and the total amount ordered by the customer. If the customers didnt place an order, list them also. (see Functions, Group By and Outer Join)

6. List name of customers that ordered all the products. (Animating SQL through Procedural Code Basic, Division)

7. List all pair of products that are in the same city (Join with 1 table)

8. ) List the three most expensive products (Use NOT EXISTS or rownum)

ResuitsMessages cname city cid 2 Basics Dallas c002 12 3 Allied las c003 8 ACME Duluth c004 8 5 ACME Kyoto c006 0 aid aname city 1a01Smith New York 6 2 a02 Jones Newark 3 a03 Brown Tokyo 7 4a04 Gray New York6 5 a05 Otasi Duluth 5 6 a06 Smith Dallas 5