数据库原理及应用第2版(雷景生编著)课后习题答案第3章
1、A。
π运算符对应的是投影操作，而投影操作是对一个关系进行垂直分割，消去某些列，并重新按排列的操作。则由定义可知，例如π2,4（S）表示关系S中的第二列和第四列组成了一个新的关系，新关系的第一列为S中的第二列，新关系的第二列为S中的第四列，由此可知，π运算最初的作用就是一个选择的作用，选择出被需要的列来组成一个新的关系，故答案A正确。FROM言下之意即为“从···来”，与π运算语义不符，故答案B不正确。WHERE代表的是条件，与选择无关，故答案C不正确。GROUPE BY代表将结果按一定规则进行分组，与π运算无任何关系，故答案D不正确。
2、C。
σ运算符对应的是选择操作，而选择操作是对一个关系进行水平切割，选取符合条件的元组的操作。则由定义可知，σ运算只选取符合条件的元组，即与WHERE代表的条件相符合，故答案C正确。
3、C
当我们使用SQL Server Management Studio时可知，当我们输入一个正确的SELECT语句时，输出出来的是一个我们需要的表格，所以答案C正确。
4、C。
在课本3.5.1这节中可知，RDBMS执行CREATE VIEW语句的结果只是把视图的定义存入数据字典，并不执行其中的SELECT语句。故答案C正确。
5、C。
定义视图：SQL语言用CREATE VIEW 命令建立视图
故答案C正确。
6、B。
由书3.3.4一节可知，集合操作主要包括并操作、交操作和差操作结果表的列数必须相同，对应项的数据类型也必须相同，所以当两个子查询的结果结构完全一致时，才可以执行并、交、差操作，故答案B正确。
7、C。
HAVING必须和GROUP BY连用，一般结构为：
SELECT FROM
GROUP BY HAVING
当存在HAVING语句时，GROUP BY语句必须存在，故答案C正确。
8、B。
由3.3.1中P.65（4）条字符匹配的查询可知，“%”代表任意长度（长度可以为0）的字符串，“-”代表任意单个字符。故答案B正确。
9、D。
已知BETWEEN···AND是闭区间，所以数据是60~100的闭区间，故答案D正确。
10、A。
删除数据的一般格式：
DELETE FROM
[WHERE ]
由3.4.3可知，DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元祖。如果省略WHERE子句，表示删除表中全部元组，但表的定义仍在数据字典中，即DELETE语句删除的是表中的数据，而不是关于表的定义。故答案A正确。
二、综合题
1、⑴、创建客户表Customers
Create table Customers(
Cid char(6) primary key,
Cname varchar(20) not null,
City varchar(20) not null
);
⑵、创建代理人表Agents
Create table Agents(
Aid char(4) primary key,
Aname varchar(20) not null,
City varchar(20) not null
);
⑶、创建产品表Products
Create table Products(
Pid char(4) primary key,
Pname varchar(20) not null,
Quantity int,
Price float(2)
);
⑷、创建订单表Orders
Create table Orders(
Ord_no char(4) primary key,
Months smallint not null,
Cid char(5) foreign key references Customers(Cid),
Aid char(4) foreign key references Agents(Aid),
Pid char(4) foreign key references Products(Pid),
Qty int,
Amount float(2)
);
2、⑴、select c#,cname from C
where teacher=’LIU’;
⑵、select s#,sname from S
where sex=’男’ and age>23;
⑶、select cname,teacher from C
where c# in(select c# from SC where s#=’S3’);
⑷、select sname from S
where sex=’女’ and s# in(select distinct s# from SC
where c# in(select c# from C
where teacher=’LIU’) );
⑸、select distinct c# from C
where c# not in(select distinct c# from sc
where s# in (select s# from s where sname=’WANG’));
⑹、select distinct a.s# from SC as a,SC as b
where a.s#=b.s# and a.c#!=b.c#;
or:
select distinct s# from SC group by s# having count(cno)>2;
⑺、select c#,cname from c
where not exists(select * from s where not exists(
select * from sc where s#=s.s# and c#=c.c#);
or:
select c#,cname from c
where c# in(select c# from sc
group by c#
having count(sno)=(select count(*) from s);
⑻、select distinct s# from sc a
where not exists(select * from c where teacher=’LIU’
and not exists(select * from sc as b
where b.s#=a.s# and b.c#=c.c#));
3、⑴、select * from Orders
where Cid=’C006’
⑵、select distinct Cname from Customers c,Orders o
where c. Cid=o. Cid and Pid=’P01’
⑶、select Cname from Customers c,Orders o,Products p
where c. Cid=o. Cid and p. pid=o.pid and Price=0.50 and Qty>500;
⑷、select distinct Cname from Customers c,Orders o
where c. Cid=o.cid and Pid!=’P01’;
⑸、select distinct c. Cid, Cname,a.Aid,Aname,a.City
from Customers c,Agent a,Orders o
where c.Cid=o.Cid and a.Aid=o.Aid and c.City=a.City
⑹、select distinct Pid from Orders o,Customers c, Agents a
where o.Aid=a.Aid and c.Cid=o.Cid and c.City=’南京’ and a.City=’北京’
⑺、select distinct c.Cid from Orders o,Customers c,products p
where p.Pid=o.Pid and c.Cid=o.Cid and Price=1.00;
4、⑴、select * from Orders
where Qty between 500 and 800
⑵、select Pname 产品名称,Price 单价 from Products
where Pname like ‘%水%’
⑶、select Months 月份,count(*) 订单数,sum(Qty) 总订货数量,sum(Amount)总金额
from Orders
Group by Months
Order by Months desc;
⑷、select * from Orders
where Cid=(
select Cid from Customers
where Cname=’王_’） and Months=’1’
Order by Qty desc
⑸、select Months from Orders
where Cid in(
select Cid from Customers
where City=’上海’
)
Group by Months
having sum(Qty)>2000;
⑹、select o.pid 产品编号,pname 产品名称,sum(Qty) 总订货数量,sum(Amount) 总金额
from Orders o,products p
where o.pid=p.pid
group by o.pid;
⑺、select distinct o.Cid,Cname from Orders o,Customers c
where o.Cid=c.Cid and Aid not in(
select Aid from Agents
where City=’北京’) and Pid not in (
select Pid (
select Pid from Products
where Pname=’笔袋’)
⑻、select Ord_no from Orders
where Qty>All(select Qty from Orders
where Months=’3’)
or:
select Ord_no from Orders
where Qty>l(select max(Qty) from Orders
where Months=’3’)
⑼、insert into Products
values(‘P20’,’粉笔’,25000,1.50)
⑽、update Products
set Price=Price*1.1
where Price>1.00
⑾、update Orders
set Qty=2000
where Aid=(
select Aid from Agents
where City=’上海’) and Pid=(
select Pid from Products where Pname=’笔袋’)
⑿、update Orders
set Aid=’A05’
where Aid=’A06’ and Pid=’P01’ and Cid=’C006’
⒀、delete from orders
where Cid=’C006’;
go
delete from Customers
where Cid=’C006’;
⒁、delete from Orders
where Pid=(
select Pid from Products
where Pname=’尺子’) and Months=’3’
⒂、create view v_Agent(代理人姓名,产品名称,产品单价)
as
select Aname,pname,Price
from Agents a,Products p,orders o,customers c
where c.city=’上海’ and a.aid=o.aid and p.pid=o.pid and c.cid=o.cid;
⒃、create view product(产品名称,总订货数量,总金额)
as
select Pname,sum(Qty),sum (Amount)
from Products p,Orders o
where p.Pid=o.Pid and Price>1.00
group by Pname
作者：九九小白菜
