sql sever 例题 七表联查

总结:借鉴了「吸喵的小同志」大佬的文章,做了一下,我的写法与他不太一样,

而且有一个表没有用我删除了,改了几个不合理的地方

create table aa (
cNo Int  primary key,--客户id
cName Varchar(10), --客户姓名
address Varchar(20),--区
city Varchar(20),--市
state Varchar(20),--省
zip Varchar(100),--邮政编码
referred Int --推荐人id
--买家信息表
);
insert into aa(cNo,cName,address,city,state,zip,referred)values
(1001,'张三','白下区','南京','江苏',210000,null),
(1002,'李四','徐汇区','上海','上海',110000,null),
(1003,'王五','朝阳区','北京','北京',110000,null),
(1004,'赵六','北大街','无锡','江苏',110000,null),
(1005,'李三','平江路','苏州','江苏',110000,null),
(1006,'陈三','升州路','南京','江苏',110000,null),
(1007,'朱三','三牌楼','合肥','安徽',110000,1003),
(1008,'梁三','徐汇区','上海','上海',110000,null),
(1009,'宫三','徐汇区','上海','上海',110000,1003),
(1010,'马三','海淀区','北京','北京',110000,null),
(1011,'刘三','崇文区','北京','北京',110000,null),
(1012,'齐三','宣武区','北京','北京',110000,null),
(1013,'陆三','鼓楼区','南京','江苏',110000,1006),
(1014,'陈三','雨花区','南京','江苏',110000,null),
(1015,'钱三','新桥村','无锡','江苏',110000,null),
(1016,'高三','利农村','无锡','江苏',110000,1010),
(1017,'郝三','杨浦区','上海','上海',110000,null),
(1018,'黄三','三家庄','合肥','安徽',110000,null),
(1019,'黄四','常青镇','合肥','安徽',110000,1003),
(1020,'梁四','余老庄','合肥','安徽',110000,null);

create table  bb(
bNo varchar(10) not null primary key, --书的id
title Varchar(300), --书的名称
pubdate date, --出版的时间
pubid Int,  --出版社id
cost Numeric(5,2),  --成本
retail Numeric (5,2), --售价
category Varchar (12) --类型
--货物信息表
);

insert into bb(bNo,title,pubdate,pubid,cost,retail,category)values
('1059831198','一天中10分钟的身体成分','2001-01-21',4,18.75,30.95,'健康'),
('0401140733','米奇的复仇','2001-12-12',1,14.2,22,'家庭生活'),
('4981341710','用牙签造车','2002-08-18',2,37.8,59,'儿童'),
('8843172113','数据库的实现','1999-06-04',3,31,55,'计算机'),
('3437212490','用蘑菇烹饪','2000-02-28',4,12.5,19.95,'烹饪'),
('3957136468','数据库法宝','2001-11-30',3,47,75,'计算机'),
('1915762492','电脑手册','2001-01-21',3,21,25,'计算机'),
('9959789321','电子商务的道路','2002-03-02',2,37,54,'计算机'),
('2491748320','教育孩子轻松方法','2000-07-17',5,48,89.95,'家庭生活'),
('0299282519','烹饪之路','2000-09-11',4,19,28,'烹饪'),
('8117949391','大熊和小宝贝','2001-11-08',5,5.32,8.95,'儿童'),
('0132149871','如何最快的制作比萨','2002-11-11',4,17.85,29.95,'自助'),
('9247381001','如何成为经理人','1999-05-09',1,15.4,31.95,'商务'),
('2147428890','最短小精悍的诗','2001-05-01',5,21.85,39.95,'文学');

create table  cc(
orderq int not null primary key, --订单号
cNO int, --买家id
orderdate date, --购买时间
shipdate date, --发货时间
shipstreet varchar(18), --发货地址 区
shipcity varchar(15), --市
shipstate varchar(20), --省
shipzip int --邮政编码
--订单表1
);
drop table cc

insert into cc(orderq,cNO,orderdate,shipdate,shipstreet,shipcity,shipstate,shipzip)values
(1000,1005,'2003-03-31','2003-04-02','奥体大街','南京','江苏',210019),
(1001,1010,'2003-03-31','2003-04-01','和会街','南京','江苏',210001),
(1002,1011,'2003-03-31','2003-04-01','南京路','上海','上海',201100),
(1003,1001,'2003-04-01','2003-04-01','南京路','上海','上海',201100),
(1004,1020,'2003-04-01','2003-04-05','欧风街','无锡','江苏',214002),
(1005,1018,'2003-04-01','2003-04-02','南大街','常州','江苏',213000),
(1006,1003,'2003-04-01','2003-04-02','洋人街','重庆','重庆',400015),
(1007,1007,'2003-04-01','2003-04-04','洋人街','重庆','重庆',400015),
(1008,1004,'2003-04-02','2003-04-03','欧风街','无锡','江苏',214002),
(1009,1005,'2004-04-03','2004-04-05','南大街','常州','江苏',213000),
(1010,1019,'2004-04-03','2004-04-04','和平街','北京','北京',100000),
(1011,1010,'2003-04-03','2003-04-05','长安街','北京','北京',100000),
(1012,1017,'2005-04-03',null,'北京东路','南京','江苏',210001),
(1013,1014,'2005-04-03','2005-04-04','德化街','郑州','河南',450007),
(1014,1007,'2003-04-03','2003-04-05','和平街','北京','北京',100000),
(1015,1020,'2005-03-31',null,'欧风街','无锡','江苏',214002),
(1016,1003,'2003-03-31',null,'长阳街','苏州','江苏',215500),
(1017,1015,'2005-03-31','2005-04-05','太合路','合肥','安徽',230001),
(1018,1001,'2003-03-31',null,'江苏路','南京','江苏',210001),
(1019,1018,'2003-03-31',null,'青阳路','合肥','安徽',230001),
(1020,1008,'2003-03-31',null,'梅山新村','合肥','安徽',230001);

create table  dd(
oNo Int not null, --订单号
item Int not null,--没毛用不用管
bNo varchar(10), --书本的id
quantity Int --购买的数量
--订单表2
);

insert into dd(oNo,item,bNo,quantity)values
(1000,1,'3437212490',1),
(1001,1,'9247381001',1),
(1001,2,'2491748320',1),
(1002,1,'8843172113',2),
(1003,1,'8843172113',1),
(1003,2,'1059831198',1),
(1003,3,'3437212490',1),
(1004,1,'2491748320',2),
(1005,1,'2147428890',1),
(1006,1,'9959789321',1),
(1007,1,'3957136468',3),
(1007,2,'9959789321',1),
(1007,3,'8117949391',1),
(1007,4,'8843172113',1),
(1008,1,'3437212490',2),
(1009,1,'3437212490',1),
(1009,2,'0401140733',1),
(1010,1,'8843172113',1),
(1011,1,'2491748320',1),
(1012,1,'8117949391',1),
(1012,2,'1915762492',2),
(1012,3,'2491748320',1),
(1012,4,'0401140733',1),
(1013,1,'8843172113',1),
(1014,1,'0401140733',2),
(1015,1,'3437212490',1),
(1016,1,'2491748320',1),
(1017,1,'8117949391',1),
(1018,1,'3437212490',2),
(1018,2,'8843172113',1),
(1019,1,'0401140733',1),
(1020,1,'3437212490',1);

create table ff(
authorId Varchar(4) not null primary key, --作者 id
authorName Varchar(20) --作者
--作者表
);

insert into ff(authorId,authorName)values
('S100','薛明'),
('J100','陈洪'),
('A100','张二'),
('M100','刘虎'),
('K100','陈康'),
('P100','王立'),
('A105','吴明'),
('B100','洪海'),
('P105','李强'),
('W100','宫立'),
('W105','龚佩'),
('R100','张青'),
('F100','钟汉'),
('W110','韩青');

create table ee(
bNo varchar(10) not null, --书本的id
authorId Varchar(4) not null --作者id
--书与作者id表
);

insert into ee(bNo,authorId)values
('1059831198','S100'),
('1059831198','P100'),
('0401140733','J100'),
('4981341710','K100'),
('8843172113','P105'),
('8843172113','A100'),
('8843172113','A105'),
('3437212490','B100'),
('3957136468','A100'),
('1915762492','W100'),
('1915762492','W105'),
('9959789321','J100'),
('2491748320','R100'),
('2491748320','F100'),
('2491748320','B100'),
('0299282519','S100'),
('8117949391','R100'),
('0132149871','S100'),
('9247381001','W100'),
('2147428890','W105');

drop table j

create table jj(
pubId Int not null primary key, --出版社id
pubName Varchar (23), --出版社名称
contact Varchar (15),  --出版社联系人
phone Varchar (12) --出版社联系人的电话
--出版社表
);

insert into jj(pubId,pubName,contact,phone)values
(1,'新华出版社','陈明','000-714-8321'),
(2,'扬子出版社','张译','010-410-0010'),
(3,'人民教育出版社','元力','800-555-1211'),
(4,'北京大学出版社','郑爽','800-555-9743'),
(5,'机械工业出版社','陆华','800-555-8284');

1.查询儿童和烹饪种类的所有图书（两种方式查询） in or 。
select * from bb where category in('儿童','烹饪');
select * from bb where category ='儿童'or category = '烹饪';

2.查询所有图书信息 , 按照出版社 ID (pubid)从小到大，出版日期从大到小排序。
select * from bb order by pubid asc,pubdate desc
3.查询由其他客户向其推荐书店的所有客户
select a.cno,a.cname,b.cno,b.cname from aa a,aa b where a.cno=b.referred
4.查询售价在 20 到 50 元之间的图书信息（用 2 种方式实现）
select * from bb where retail between 20 and 50
select * from bb where retail >20  and retail <50
5.查询不属于计算机的图书信息（用2 种方式实现）。
select * from bb where category <>'计算机'
6.查询居住在上海和南京的顾客信息，并按照姓名升序排列。(市)
select * from aa where city in ('上海','南京') order by cname asc
7.查询发货城市在北京和常州的订单信息，并按照发货日期升序排列
select * from cc where shipcity in('北京','常州') order by shipdate asc
8.查询发货地点以“南”开头的的订单信息，并按照发货日期升序排列
select * from cc where shipstreet like'南%' or shipcity like'南%' or shipstate like'南%' order by shipdate asc
9.查询书名中含有‘ _‘的图书信息，并按照出版日期升序排列
select * from bb where title like '%_%';
10.显示所有图书的毛利（百分数）的列表，毛利应该显示为一个没有小数位的整数（就是乘以 100），
 后面带有百分号（例如： 0.2793=28%）
 select title, concat(round((retail-cost)/retail,2)*100,'%') from bb
11.确定哪些订单还没发货以及下达这些订单的客户的姓名，将结果按下达订单的日期排序
select cc.*,aa.cname from  (select * from cc where shipdate is null
) cc left join aa on cc.cno=aa.cno and shipdate is null order by orderdate asc
12.列出已经购买了计算机种类的所有人的客户号和姓名以及书名和订单号

select aa.cno,aa.cname,bb.title,dd.ono from aa,bb,dd,cc where aa.cno=cc.cno and cc.orderq=dd.ono and dd.bno=bb.bno and bb.bno in(select bno from bb where category='计算机'
)

13.确定李三已经购买了哪些书
select title from aa,bb,dd,cc where aa.cno=cc.cno and cc.orderq=dd.ono and dd.bno=bb.bno and aa.cname='张三'
14.确定销售给王五的每一本书的利润。将结果按订单日期排序。 如果订购了多本书， 那么将结果按利润的降序排列
select title,(retail-cost) from aa,bb,dd,cc where aa.cno=cc.cno and cc.orderq=dd.ono and dd.bno=bb.bno and aa.cname='王五'

15.哪一本书是由叫洪海的作者编写的？
select title from bb where bno in(select bno from ff,ee where ee.authorid=ff.authorid and authorname='洪海'
)
16.得到居住在南京并且订购了计算机图书的所有客户的列表。
select aa.* from aa,bb,cc,dd where aa.cno=cc.cno and cc.orderq=dd.ono and dd.bno=bb.bno and bb.bno in(select bno from bb where category ='计算机'
) and aa.city='南京'
17.查询所有订单购买的图书书名。 （ JOIN 方法）
select bb.title from dd left join bb on dd.bno =bb.bno
18.查询所有图书列表，及被哪些顾客购买以及该顾客的信息。
select * from  (select cc.cno,bb.title,dd.bno from bb,cc,dd where cc.orderq=dd.ono and dd.bno=bb.bno 
) l left join aa on l.cno=aa.cno
19.查询所有作者编写了哪些图书。 （JOIN 方法）
select x.authorname,bb.title from (select ff.authorname,ee.bno from ee,ff where ee.authorid=ff.authorid
) x left join bb on x.bno=bb.bno
20.查询所有作者编写了哪些图书以及购买该图书的顾客信息。
select * from (select x.authorname,bb.title from (select ff.authorname,ee.bno from ee,ff where ee.authorid=ff.authorid
) x left join bb on x.bno=bb.bno) k left join (select l.title,aa.* from  (select cc.cno,bb.title,dd.bno from bb,cc,dd where cc.orderq=dd.ono and dd.bno=bb.bno 
) l left join aa on l.cno=aa.cno) z on z.title=k.title
21.得到所有居住在南京的顾客以及他订购图书的信息
select m.cname,n.* from (select * from aa where city ='南京'
) m,(select cc.cno,bb.* from bb,cc,dd where cc.orderq=dd.ono and dd.bno=bb.bno 
) n where m.cno=n.cno
22.得到订单年在 2003 和 2004 年的订单信息以及顾客姓名。
select k.*,aa.cname from (select * from cc where year(orderdate)=2003 or year(orderdate)=2004
) k left join aa on k.cno=aa.cno
23.所有的图书书名，及平均成本，平均售价，最低成本，最高成本,最低售价，最高售价，总成本,
总售价，书的总数
select title,avg(cost),avg(retail),min(cost),max(cost),min(retail),max(retail),sum(cost),sum(retail),count(*)from bb group by title
24.订单中所有的图书书名，及其销售的数量
select a.title,count(a.quantity) from (select bb.title,dd.quantity from dd left join bb on bb.bno=dd.bno 
) a group by a.title
25.查询每个图书种类的数量
select category,count(category) from bb group by category
26.查询每个出版社出版的图书数量
select jj.pubname,count(bb.pubid) from bb,jj where bb.pubid=jj.pubid group by jj.pubname
27.确定哪些书的成本低于同一类图书的平均成本
select bb.title from bb,(select category,avg(cost) k from bb group by category
) l where bb.category=l.category and bb.cost<l.k
28.确定哪些订单将发到与订单 1014 相同的城市

select * from cc where cno in(select cno from aa where city=(select city from cc,aa where aa.cno=cc.cno and orderq=1014
)) and orderq<>1014
29.确定哪些订单的总应付款项比订单 1003 更高

select * from dd where ono in (select a.oNo from (select ono,sum((retail*quantity)) 应付款 from dd,bb where dd.bno=bb.bno group by ono
) a,(select sum((retail*quantity)) 应付款 from dd,bb where dd.bno=bb.bno and ono=1003
) b where a.应付款>b.应付款)

30.列出发货延迟时间最长的客户所在城市

select city from aa where cno=(select l.cno from (select cno,datediff(day,orderdate,shipdate) d from cc  
) l where l.d=(select max(k.k) from (select cno,datediff(day,orderdate,shipdate) k from cc 
) k))
31.确定哪些客户订购了销售最便宜的图书。

select cname from aa,cc,dd where aa.cno=cc.cno and cc.orderq=dd.ono and dd.bno=(select bno from bb where retail=(select min(retail) from bb
))
32.确定多少个不同的客户订购了张二编著的图书。

select distinct cname from aa,cc,dd where aa.cno=cc.cno and cc.orderq=dd.ono and dd.bno in (select bno from ee,ff where ee.authorId=ff.authorId and ff.authorName='张二'
)

33.确定谁订购了陈洪编写的成本最高的图书

select distinct cname from aa,cc,dd where aa.cno=cc.cno and cc.orderq=dd.ono and dd.bno=(select bno from bb where cost=(select max(cost) from bb where bno in(select bno from ee,ff  where ee.authorId=ff.authorId and ff.authorName='陈洪'
)) and bno in(select bno from ee,ff  where ee.authorId=ff.authorId and ff.authorName='陈洪'
))

34.确定 2003 年 3 月到 4 月订购图书的顾客信息。
select * from aa where cno in(select distinct cno from cc where (year(orderdate)=2003 and month(orderdate)=3) or (year(orderdate)=2003 and month(orderdate)=4)
)
35.确定销售量最高的图书信息

select * from bb where bno=(select a.bno from (select bno,sum(quantity) b from dd group by bno
) a where a.b=(select max(a.b) from (select bno,sum(quantity) b from dd group by bno
) a))
36.确定订单中发往南京的信息
select * from cc where cno in(select cno from aa where city ='南京'
)
37.确定出版计算机类图书的出版社信息
select * from jj where pubid in (select distinct pubid from bb where category='计算机'
)
38.确定出售价在 20 至 50 元之间图书的出版社信息
select * from jj where pubid in(select distinct pubid from bb where  retail between 20 and 50
)
39.确定还没有发货出去的其顾客信息
select *  from aa where  cno in(select distinct cno from cc where shipdate is null
)