数据库原理及应用 清华大学出版社 96页课后练习题

1、用sql语句创建表

/*创建客户表Customers*/
Create table Customers(
Cid char(4) primary key,
Cname varchar(20) not null,
City varchar(20) not null
)

/*创建代理人表Agents*/
Create table Agents(
Aid char(3) primary key,
Aname varchar(20) not null,
City varchar(20) not null
)

/*创建产品表Products*/
Create table Products(
Pid char(3) primary key,
Pname varchar(20) not null,
Quantity int,
Price decimal(6,2)
)

/*创建订单表Orders*/
Create table Orders(
Ord_no char(4) primary key,
Month smallint not null,
Cid char(4) foreign key references Customers(Cid),
Aid char(3) foreign key references Agents(Aid),
Pid char(3) foreign key references Products(Pid),
Qty int,
Amount decimal(6,2)
)


insert into Customers(Cid,Cname,City)
values('C001','詹三','杭州');

insert into Customers(Cid,Cname,City)
values('C002','王勇','上海');

insert into Customers(Cid,Cname,City)
values('C003','李晓红','上海');

insert into Customers(Cid,Cname,City)
values('C004','赵子凡','杭州');

insert into Customers(Cid,Cname,City)
values('C006','钱立','南京');

insert into Agents(Aid,Aname,City)
values('A01','赵龙','北京');
insert into Agents(Aid,Aname,City)
values('A02','张建国','深圳');
insert into Agents(Aid,Aname,City)
values('A03','李林','广州');
insert into Agents(Aid,Aname,City)
values('A04','陈娟','北京');
insert into Agents(Aid,Aname,City)
values('A05','林子','杭州');
insert into Agents(Aid,Aname,City)
values('A06','吴文俊','上海');

insert into Products(Pid,Pname,Quantity,Price)
values('P01','笔袋',111400,5.50);
insert into Products(Pid,Pname,Quantity,Price)
values('P02','尺子',203000,0.50);
insert into Products(Pid,Pname,Quantity,Price)
values('P03','橡皮',150600,0.50);
insert into Products(Pid,Pname,Quantity,Price)
values('P04','水笔',125300,1.00);
insert into Products(Pid,Pname,Quantity,Price)
values('P05','铅笔',221400,1.00);
insert into Products(Pid,Pname,Quantity,Price)
values('P06','涂改液',123100,2.00);
insert into Products(Pid,Pname,Quantity,Price)
values('P07','水彩笔',100500,1.00);


insert into Orders(Ord_no,Month,Cid,Aid,Pid,Qty,Amount)
values('1011',1,'C001','A01','P01',1000,5500.00);

insert into Orders(Ord_no,Month,Cid,Aid,Pid,Qty,Amount)
values('1012',2,'C001','A01','P01',1000,5500.00);

insert into Orders(Ord_no,Month,Cid,Aid,Pid,Qty,Amount)
values('1019',2,'C001','A02','P02',400,200.00);

insert into Orders(Ord_no,Month,Cid,Aid,Pid,Qty,Amount)
values('1017',2,'C001','A06','P03',600,300.00);

insert into Orders(Ord_no,Month,Cid,Aid,Pid,Qty,Amount)
values('1018',2,'C001','A03','P04',600,600.00);

insert into Orders(Ord_no,Month,Cid,Aid,Pid,Qty,Amount)
values('1023',3,'C001','A04','P05',500,500.00);


insert into Orders(Ord_no,Month,Cid,Aid,Pid,Qty,Amount)
values('1022',3,'C001','A05','P06',400,800.00);


insert into Orders(Ord_no,Month,Cid,Aid,Pid,Qty,Amount)
values('1025',4,'C001','A05','P07',800,800.00);


insert into Orders(Ord_no,Month,Cid,Aid,Pid,Qty,Amount)
values('1013',1,'C002','A03','P03',1000,500.00);



insert into Orders(Ord_no,Month,Cid,Aid,Pid,Qty,Amount)
values('1026',5,'C002','A05','P03',800,400.00);

insert into Orders(Ord_no,Month,Cid,Aid,Pid,Qty,Amount)
values('1015',1,'C003','A03','P05',1200,1200.00);

insert into Orders(Ord_no,Month,Cid,Aid,Pid,Qty,Amount)
values('1014',3,'C003','A03','P05',1200,1200.00);

insert into Orders(Ord_no,Month,Cid,Aid,Pid,Qty,Amount)
values('1021',2,'C004','A06','P01',1000,5500.00);

insert into Orders(Ord_no,Month,Cid,Aid,Pid,Qty,Amount)
values('1016',1,'C006','A01','P01',1000,5500.00);

insert into Orders(Ord_no,Month,Cid,Aid,Pid,Qty,Amount)
values('1020',2,'C006','A03','P07',600,600.00);

insert into Orders(Ord_no,Month,Cid,Aid,Pid,Qty,Amount)
values('1024',3,'C006','A06','P01',800,4400.00);

2、（1）查询订单数量在500-800的订单数量

select * from orders
where qty between 500 and 800;

（2）查询产品名称中含有“水”字的产品名称与单价。

select pname,price from products
where pname like '%水%';

（3）查询每个月的订单数、总订货数量以及总金额，要求赋予别名，并按月份降序排列。

select month,count(*)订单数,sum(qty)总订货数量,sum(amount)总金额  from orders
group by month
order by month desc ;

（4）查询姓王且名字为两个字的客户在1月份的订单情况，并按订货数量降序排列。
嵌套查询完成：

select * from orders
where cid in (select cid from customers
              where cname like '王_') and month=1
order by qty desc ;

连接查询完成：

select orders.* from orders,customers
where orders.Cid=Customers.cid and cname like '王_' and month=1
order by qty desc ;

（5）查询上海客户总订货数量超过2000的订货月份。

select month from orders
where cid in (select cid from customers
				where city ='上海')
				group by month having sum(qty)>2000;

（6）查询每个产品的产品编号、产品名称、总订货数量以及总金额。

select orders.pid,pname,sum(qty),sum(amount) from orders,products
where orders.pid=products.pid
group by Orders.pid,pname;

（7）查询没有通过北京的代理订购笔袋的客户编号与客户名称。

select distinct orders.cid,cname from orders,Customers,Agents,Products
where Customers.Cid=Orders.Cid and Agents.Aid=Orders.Aid and Products.pid=Orders.pid
 and agents.city !='北京' and pname!='笔袋';

或

select distinct orders.cid,cname from orders,Customers
where Customers.Cid=Orders.cid and aid not in(select aid from agents where city='北京')
	and pid not in(select pid from Products where pname='笔袋');

（8）查询这样的订单号，该订单的订货数量大于3月份所有订单的订货数量。
使用带All谓词的子查询

select Ord_no  from orders
where month <> 3 and 
				qty > all(select qty from Orders where month='3');

使用聚集函数

select Ord_no  from orders
where month <> 3 and 
				qty > (select max(qty) from Orders where month='3');

（9）向产品表中增加一个产品，名称为粉笔，编号为p20,单价为1.5，销售数量为25000

insert into products
values('P20','粉笔',25000,1.5);

（10）将所有单价大于1.00的产品单价增加10%

update Products
set Price=Price*1.1
where Price>1.00;

（11）将所有由上海代理商代理的笔袋的订货数量改为2000

update orders
set qty=2000
where aid in (select aid from Agents where city='上海')
 and pid in(select pid from products where pname='笔袋');

（12）将由A06供给C006的产品P01改为由A05供应，请做必要的修改。

update orders
set aid='A05'
where aid='A06' and pid='P01' and cid='C006' ;

（13）从客户关系中删除C006记录，并从供应情况中删除相应的记录。

delete from orders
where Cid='C006';
go
delete from Customers
where Cid='C006';

（14）删除3月份订购尺子的所有订单情况。

delete from orders
where pid in (select pid from Products where pname='尺子') and month=3;

（15）为上海的客户建立一个代理情况视图，包括代理人姓名，产品名称以及产品单价。

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;

（16）创建一个视图，要求包含单价大于1.00的所有产品的产品名称、总订货数量以及总金额。

create view s1(产品名称,总订货数量,总金额)
as select pname,sum(qty),sum(amount)
from products,orders
where products.pid = orders.pid and price>1.00
group by pname;

感悟：在查询语句中，使用的最多的就是连接查询与嵌套查询，个人跟喜欢连接查询，因为结构化特征更明显，但也有不适用的时候，比如例7，当涉及的表太多时，结构化查询就显得麻烦，不如连接查询。exists和not exists很少很少使用，因为可以用别的语句替换，反而降低了出错概率，了解用法即可。注意：聚集函数要给出分类依据，及group by，今天有好几处查询少了group by。