数据库第三章所有例题参考答案.doc

11级信管，保密，图档上机考试题目与参考答案3.3Simple Select Statements1. EXAMPLE 3.3.1find the aid values and names of agents that are based in New York. select aid, aname from agents where city=New York;2. EXAMPLE 3.3.3Retrieve all pid values of parts for which orders are placed.select distinct pid from orders;3. EXAMPLE 3.3.4retrieve all customer-agent name pairs, (cname, aname), where the customer places an order through the agent. select distinct ame,agents.anamefrom customers,orders,agentswhere customers.cid=orders.cid and orders.aid=agents.aid;4. EXAMPLE 3.3.6all pairs of customers based in the same city. select c1.cid, c2.cidfrom customers c1, customers c2where c1.city = c2.city and c1.cid c2.cid;5. EXAMPLE 3.3.7find pid values of products that have been ordered by at least two customers. select distinct x1.pidfrom orders x1, orders x2where x1.pid = x2.pid and x1.cid x2.cid;6. EXAMPLE 3.3.8Get cid values of customers who order a product for which an order is also placed by agent a06. select distinct y.cidfrom orders x, orders ywhere y.pid = x,pid and x.aid = a06;3.4Subqueries7. EXAMPLE 3.4.1Get cid values of customers who place orders with agents in Duluth or Dallas. select distinct cid from orderswhere aid in (select aid from agentswhere city= Duluth or city = Dallas)8. EXAMPLE 3.4.2to retrieve all information concerning agents based in Duluth or Dallas (very close to the Subquery in the previous example). select * from agentswhere city in (Duluth, Dallas );or select *from agents where city = Duluth or city = Dallas;9. EXAMPLE 3.4.3to determine the names and discounts of all customers who place orders through agents in Duluth or Dallas. select distinct cname, discnt from customerswhere cid in (select cid from orders where aid in (select aid from agents where city in (Duluth, Dallas );10. EXAMPLE 3.4.4to find the names of customers who order product p05. select distinct cname from customers, orders where customers.cid = orders.cid and orders.pid = p05or select distinct cname from customers where p05 in (select pid from orders where cid = customers.cid);11. EXAMPLE 3.4.5Get the names of customers who order product p07 from agent a03.select distinct cname from customerswhere cid in (select cid from orders where pid = p07 and aid = a03)12. EXAMPLE 3.4.6to retrieve ordno values for all orders placed by customers in Duluth through agents in New York. select ordno from orders x where exists (select * from customers c, agents a where c.cid = x.cid and a.aid = x.aid and c.city = Duluth and a.city=New York);13. EXAMPLE 3.4.7find aid values of agents with a minimum percent commission. select aid from agents where percent = (select min(percent) from agents);14. EXAMPLE 3.4.8find all customers who have the same discount as that of any of the customers in Dallas or Boston. select cid, cname from customerswhere discnt = some (select discnt from customers where city = Dallas or city = Boston); 15. EXAMPLE 3.4.9Get cid values of customers with discnt smaller than those of any customers who live in Duluth. select cid from customers where discnt all (select discnt from customers where city = Duluth);16. EXAMPLE 3.4.10Retrieve all customer names where the customer places an order through agent a05. select distinct ame from customers c where exists (select * from orders x where c.cid = x.cid and x.aid = a05);or select distinct ame from customers c, orders x where c.cid = x.cid and x.aid = a05 ;17. EXAMPLE 3.4.11Get cid values of customers who order both products p01 and p07.select distinct cid from orders xwhere pid = p01 and exsits (select * from orders where cid = x.cid and pid = p07);or select distinct x.cid from orders x, orders y where x.pid = p01 and x.cid = y.cid and y.pid = p07;18. EXAMPLE 3.4.12Retrieve all customer names where the customer does not place an order through agent a05.select distinct ame from customers cwhere not exists (select * from orders x where c.cid = x.cid and x.aid = a05);19. EXAMPLE 3.4.13retrieving all customer names where the customer does not place an order through agent a05, but using the two equivalent NOT IN and ALL predicates in place of NOT EXISTS. select distinct ame from customers c where c.cid not in (select cid from orders where aid = a05);or select ame from customers c where c.cid all (select cid from orders where aid = a05);20. EXAMPLE 3.4.14Find cid values of customers who do not place any order through agent a03.select distinct cid from orders x where not exists (select * from orderswhere cid = x.cid and aid = a03);or select cid from customers c where not exists (select * from orderswhere cid = c.cid and aid = a03);21. EXAMPLE 3.4.15Retrieve the city names containing customers who order product p01.select distinct city from customers where cid in (select cid from orders where pid = p01);or select distinct city from customers where cid =some (select cid from orders where pid = p01);or select distinct city from customers c where exsits (select * from orders where cid = c.cid and pid = p01);or select distinct city from customers c, orders x where x.cid = c.cid and x.pid = p01;or select distinct city from customers c where p01 in (select pid from orders where cid = c.cid);3.5UNION Operators and FOR ALL Conditions22. EXAMPLE 3.5.1to create a list of cities where either a customer or an agent, or both, is based.select city from customers union select city from agents;23. EXAMPLE 3.5.2Get the cid values of customers who place orders with all agents based in New York.select c.cid from customers c where not exsits (select * from agents awhere a.city = New York and not exsits (select * from orders xwhere x.cid = c.cid and x.aid = a.aid);24. EXAMPLE 3.5.3Get the aid values of agents in New York or Duluth who place orders for all products costing more than a dollar.select aid from agents a where (a.city = New York or a.city = Duluth) and not exsits (select p.pid from products pwhere p.price 1.00 and not exsits (select * from orders xwhere x.pid = p.pid and x.aid = a.aid);25. EXAMPLE 3.5.4Find aid values of agents who place orders for product p01 as well as for all products costing more than a dollar. select a.aid from agents a where a.aid in (select aid from orders where pid = p01) and not exsits (select p.pid from products p where p.price 1.00 and not exsits (select * from orders xwhere x.pid = p.pid and x.aid = a.aid);or select distinct y.aid from orders y where y.pid = p01 and not exsits (select p.pid from products p where p.price 1.00 and not exsits (select * from orders x where x.pid = p.pid and x.aid = y.aid);26. EXAMPLE 3.5.6Find pid values of products supplied to all customers in Duluth. select pid from products pwhere not exsits (select c.cid from customers cwhere c.city = Duluthand not exists(select * from orders xwhere x.pid = p.pid and x.cid = c.cid);3.7 Set Functions in SQL27. EXAMPLE 3.7.1determine the total dollar amount of all orders. select sum(dollars) as totaldollars from orders28. EXAMPLE 3.7.2To determine the total quantity of product p03 that has been ordered.select sum(qty) as TOTAL from orders where pid=p0329. EXAMPLE 3.7.4Get the number of cities where customers are based. select count(distinct city) from customers30. EXAMPLE 3.7.5List the cid values of alt customers who have a discount less than the maximum discount. select cid from customerswhere discnt (select max(discnt) from customers)31. EXAMPLE 3.7.6Find products ordered by at least two customers. select p.pid from products pwhere 2 100037. EXAMPLE 3.8.4Provide pid values of all products purchased by at least two customers.select distinct pid from ordersgroup by pidhaving count(distinct cid) = 23.9 A Complete Description of SQL Select38. EXAMPLE 3.9.1List all customers, agents, and the dollar sales for pairs of customers and agents, and order the result from largest to smallest sales totals. Retain only those pairs for which the dollar amount is at least equal to 900.00.select ame, c.cid, a.aname, a.aid, sum(dollars) as casalesfrom customers c, orders o, agents awhere c.cid = o.cid, and a.aid = o.aidgroup by ame, c.cid, a.aname, a.aidhaving sum(o.dollars) = 900.00order by casales desc39. EXAMPLE 3.9.2listed the cid values of all customers with a discount less than the maximum discount. select cid from customerswhere discnt = all (select discnt from customers dwhere d.cidc.cid)41. EXAMPLE 3.9.4Retrieve all data about customers whose cname begins with the letter “A”. select * from customers where cname like A%42. EXAMPLE 3.9.5Retrieve cid values of customers whose cname does not have a third letter equal to “%”. select cid from customers where cname not like _%43. EXAMPLE 3.9.6Retrieve cid values of customers whose cname begins “Tip_” and has an arbitrary number of characters following.select cid from customers where cname like TIP_%44. EXAMPLE 3.9.7Retrieve cid values of customers whose cname starts with the sequence “ab”. select cid from customers where cname like ab%3.10 Insert, Update, and Delete Statements45. EXAMPLE 3.10.1Add a row with specified values to the orders table, setting the qty and dollars columns null. insert into orders (ordno, month, cid, aid, pid)values (1107, aug, c006, a04, p01)46. EXAMPLE 3.10.2Create a new table called swcusts of Southwestern customers, and insert