数据库原理及应⽤第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
select distinct fromwhere 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
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论