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

11级信管，保密，图档上机考试题目与参考答案
3.3Simple Select Statements
1.EXAMPLE 3.3.1
find 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.3
Retrieve all pid values of parts for which orders are placed.
select distinct pid from orders;
3.EXAMPLE 3.3.4
retrieve all customer-agent name pairs, (cname, aname), where the customer places an order through the agent.
select distinct ame,agents.aname
from customers,orders,agents
where customers.cid=orders.cid and orders.aid=agents.aid;
4.EXAMPLE 3.3.6
all pairs of customers based in the same city.
select c1.cid, c2.cid
from customers c1, customers c2
where c1.city = c2.city and c1.cid < c2.cid;
5.EXAMPLE 3.3.7
find pid values of products that have been ordered by at least two
customers.
select distinct x1.pid
from orders x1, orders x2
where x1.pid = x2.pid and x1.cid < x2.cid;
6.EXAMPLE 3.3.8
Get cid values of customers who order a product for which an order is also placed by agent a06.
select distinct y.cid
from orders x, orders y
where y.pid = x,pid and x.aid = ‘a06’;
3.4Subqueries
7.EXAMPLE 3.4.1
Get cid values of customers who place orders with agents in Duluth or Dallas.
select distinct cid from orders
where aid in (select aid from agents
where city= ‘Duluth’ or city = ‘Dallas’)
8.EXAMPLE 3.4.2
to retrieve all information concerning agents based in Duluth or Dallas (very close to the Subquery in the previous example).
select * from agents
where city in (‘Duluth’, ‘Dallas’ );
or select *from agents
where city = ‘Duluth’ or city = ‘Dallas’;
9.EXAMPLE 3.4.3
to determine the names and discounts of all customers who place orders through agents in Duluth or Dallas.
select distinct cname, discnt from customers
where cid in (select cid from orders where aid in
(select aid from agents where city in (‘Duluth’, ‘Dallas’ ))); 10.EXAMPLE 3.4.4
to find the names of customers who order product p05.
select distinct cname from customers, orders
where customers.cid = orders.cid and orders.pid = ‘p05’
or select disti nct cname from customers where ‘p05’ in
(select pid from orders where cid = customers.cid);
11.EXAMPLE 3.4.5
Get the names of customers who order product p07 from agent a03. select distinct cname from customers
where cid in (select cid from orders where pid = ‘p07’ and aid = ‘a03’) 12.EXAMPLE 3.4.6
to 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.7
find aid values of agents with a minimum percent commission.
select aid from agents where percent = (select min(percent) from agents);
14.EXAMPLE 3.4.8
find all customers who have the same discount as that of any of the customers in Dallas or Boston.
select cid, cname from customers
where discnt = some (select discnt from customers
where city = ‘Dallas’ or city = ‘Boston’);
15.EXAMPLE 3.4.9
Get 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.10
Retrieve 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.ai d = ‘a05’ ;
17.EXAMPLE 3.4.11
Get cid values of customers who order both products p01 and p07. select distinct cid from orders x
where 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.12
Retrieve all customer names where the customer does not place an order through agent a05.
select distinct ame from customers c
where not exists (select * from orders x
where c.cid = x.cid and x.aid = ‘a05’);
19.EXAMPLE 3.4.13
retrieving 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.14
Find cid values of customers who do not place any order through agent a03.
select distinct cid from orders x
where not exists (select * from orders
where cid = x.cid and aid = ‘a03’);
or
select cid from customers c
where not exists (select * from orders
where cid = c.cid and aid = ‘a03’);
21.EXAMPLE 3.4.15
Retrieve 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 Conditions 22.EXAMPLE 3.5.1
to 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.2
Get 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 a
where a.city = ‘New York’ and not exsits
(select * from orders x
where x.cid = c.cid and x.aid = a.aid));
24.EXAMPLE 3.5.3
Get 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 p
where p.price > 1.00 and not exsits
(select * from orders x
where x.pid = p.pid and x.aid = a.aid));
25.EXAMPLE 3.5.4
Find 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 x
where 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.6
Find pid values of products supplied to all customers in Duluth.
select pid from products p
where not exsits
(select c.cid from customers c
where c.city = ‘Duluth’and not exists
(select * from orders x
where x.pid = p.pid and x.cid = c.cid));
3.7 Set Functions in SQL
27.EXAMPLE 3.7.1
determine the total dollar amount of all orders.
select sum(dollars) as totaldollars from orders
28.EXAMPLE 3.7.2
To determine the total quantity of product p03 that has been ordered. select sum(qty) as TOTAL from orders where pid=’p03’
29.EXAMPLE 3.7.4
Get the number of cities where customers are based.
select count(distinct city) from customers
30.EXAMPLE 3.7.5
List the cid values of alt customers who have a discount less than the maximum discount.
select cid from customers
where discnt < (select max(discnt) from customers)
31.EXAMPLE 3.7.6
Find products ordered by at least two customers.
select p.pid from products p
where 2 <=
(select count(distinct cid) from orders where pid=p.pid)
图档的学生的上机考查的考题到此为止
___________________________________________________________ ___________________________________________________________ 信管，保密的学生上机考查还包括下面的题目
32.EXAMPLE 3.7.7
Add a row with specified values for columns cid, cname, and city (c007, Windix, Dallas, null)to the customers table.
insert into customers(cid, cname, city)
values (‘c007’, ‘Windix’, ‘Dallas’)
33.EXAMPLE 3.7.9
After inserting the row (c007, Windix, Dallas, null) to the customers table in Example 3.7.7, assume that we wish to find the average discount of all customers.
select avg(discnt) from customers
3.8 Groups of Rows in SQL
34.EXAMPLE 3.8.1
to calculate the total product quantity ordered of each individual product by each individual agent.
select pid, aid, sum(qty) as TOTAL from orders
group by pid, aid
35.EXAMPLE 3.8.2
Print out the agent name and agent identification number, and the product name and product identification number, together with the total quantity each agent supplies of that product to customers c002 and c003.
select aname, a.aid, pname, p.pid, sum(qty)
from orders x, products p, agents a
where x.pid = p.pid and x.aid = a.aid and x.cid in (‘c002’, ‘c003’)
group by a.aid, a.aname, p.pid, p.pname
36.EXAMPLE 3.8.3
Print out all product and agent IDs and the total quantity ordered of the product by the agent, when this quantity exceeds 1000.
select pid, aid, sum(qty) as TOTAL from orders
group by pid, aid
having sum(qty) > 1000
37.EXAMPLE 3.8.4
Provide pid values of all products purchased by at least two customers. select distinct pid from orders
group by pid
having count(distinct cid) >= 2
3.9 A Complete Description of SQL Select
38.EXAMPLE 3.9.1
List 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 casales
from customers c, orders o, agents a
where c.cid = o.cid, and a.aid = o.aid
group by ame, c.cid, a.aname, a.aid
having sum(o.dollars) >= 900.00
order by casales desc
39.EXAMPLE 3.9.2
listed the cid values of all customers with a discount less than the maximum discount.
select cid from customers
where discnt < (select max(discnt) from customers)
40.EXAMPLE 3.9.3
Retrieve the maximum discount of all customers.
select max(discnt) from customers;
select distinct discnt from customers c
where discnt >= all (select discnt from customers d
where d.cid<>c.cid)
41.EXAMPLE 3.9.4
Retrieve all data about customers whose cname begins with the letter “A”.
select * from customers where cname like ‘A%’
42.EXAMPLE 3.9.5
Retrieve 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.6
Retrieve 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.7
Retrieve 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 Statements 45.EXAMPLE 3.10.1
Add 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.2
Create a new table called swcusts of Southwestern customers, and insert into it all customers from Dallas and Austin.
create table swcusts (
cid char(4) not null,
cname varchar(13),
city varchar(20),
discnt real);
insert into swcusts
select * from customers
where city in (‘Dallas’, ‘Austin’)
47.EXAMPLE 3.10.3
Give all agents in New York a 10% raise in the percent commission they earn on an order.
update agents set percent = 1.1 * percent where city = ‘New York’
48.EXAMPLE 3.10.4
Give all customers who have total orders of more than $1000 a 10% increase in the discnt.
update agents set percent = 1.1 * discnt
where cid in
(select cid from orders group by cid having sum(dollars) > 1000) 49.EXAMPLE 3.10.6
Delete all agents in New York.
delete from agents where city = ‘New York’
50.EXAMPLE 3.10.7
Delete all agents who have total orders of less than $600.
Delete from agents where aid in
(select aid from orders
Group by aid
Having sum(dollars)<600)
51.EXAMPLE 3.11.2
Retrieve the names of customers who order products costing $0.50. delete from agents where aid in
(select aid from orders group by aid having sum(dollars)<600)
（完）。