HW # 2 answers -.doc

HW # 2 answers - 1) List name and city of all customers. (Coffee: Projection) Select cname, city from customers; 2) List products in descending order by price (Coffee: SQL - JavaApplets - Constructing SQL Queries advance - Order By) Select * from products order by price desc; 3) List customers that ordered ‘p01’ or ‘p02’ (Coffee: Restriction) Select cid from orders where pid = ‘p01’ or pid = ‘p02’ 4) List customers that ordered ‘p01’ and ‘p02’ (Need to use INTERSECT or 2 Virtual tables of ORDERS). Select * from customers where cid in (select cid from orders where pid = ‘p01’) And cid in (select cid from orders where pid = ‘p02’); 5) List the name of each agent and the sum of orders (in dollars) that each agent ordered (Coffee: SQL - JavaApplets - Constructing SQL Queries advance - Functions and Group BY) select a.aname, sum (dollars) Dollars from agents a, orders o where a.aid = o.aid group by a.aname; 6) Insert a new row in customers with cid = ‘c007’, ‘Coke’, ‘Atlanta’, 7 (Coffee: SQL - JavaApplets - Data Definition Language and Data Manipulation Language) Insert into customers (cid, cname, city, discnt) values (‘c007’, ‘Coke’, ‘Atlanta’, ‘7.00’); 7) List the name each customers and the total amount ordered by the customers If the customers didn’t place an order, list them also. Select c.cname, sum (o.dollars) Dollars From customers c, orders o Where c.cid = o.cid (+) Group by cname; 8) List name of customers that ordered all the products. Select cname from customers c where not exists (Select * from products p where not exists (select * from orders o where o.cid = c.cid and o.pid = p.pid)); 9) List all pair of products that are in the same city (Coffee: SQL - More SQL (Visual Basic) - Join with 1 table Select p1.pid, p2.pid From products p1, products p2 Where p1.pid p2.pid and p1.city = p2.city; List the three most expensive products (one sql statement. Warning: HARD. Use NOT EXISTS) Select *