数据库实验三 数据查询二

第1关：多表查询

use library;

 #代码开始

 #第一题

select txm,jyrq,hsrq from borrow,reader where borrow.dzzh=reader.dzzh and reader.xm="王颖珊";

 #第二题

select dzzh,jyrq,hsrq from borrow,book where borrow.txm=book.txm and book.sm="李白全集";

 #第三题

select reader.dzzh,xm,dhhm,book.txm,sm,borrow.jyrq from reader,book,borrow where reader.dzzh=borrow.dzzh and book.txm=borrow.txm and isnull(hsrq);

 #代码结束

第2关：多表查询及统计分组

use library

 #代码开始

 #第一题

select sm,count(*)jycs  from borrow left join book on book.txm=borrow.txm 

group by sm 

order by count(*) desc,sm desc;

 #第二题

select sm,count(*)jycs  from borrow left join book on book.txm=borrow.txm 

group by sm having(count(*)>=2)

order by count(*)  desc,sm desc;

 #第三题

select cbs,count(*)jycs from borrow left join book on book.txm=borrow.txm 

group by cbs

order by count(*) desc,cbs desc;

 #第四题

select xm,count(*)jycs from borrow left join reader on borrow.dzzh=reader.dzzh 

group by xm

order by  count(*) desc,xm desc;

 #第五题

 select xm,count(*)jycs from borrow left join reader on borrow.dzzh=reader.dzzh 

where sf="研究生"group by xm order by count(*) desc,xm desc;

 #代码结束

第3关：子查询

use library;

#代码开始

#答案1

select sm from book where cbs=(select cbs from book where sm="李白全集")and sm!="李白全集";

#答案2

select sm,sj from book where sj>(select avg(sj) from book);

 

#答案3

select txm,sm,sj from book where sj=(select max(sj) from book);

 

#答案4

select txm,sm,sj from book where sj=(select min(sj) from book);

 

 #代码结束

 

第4关：多表子查询

use library;

#代码开始

#题目1

select distinct reader.dzzh,xm

from reader,borrow

where reader.dzzh = borrow.dzzh

order by reader.dzzh asc;

#题目2

select book.txm, sm

from book left join borrow

on book.txm = borrow.txm

where jyrq is null;

#题目3

select distinct reader.dzzh, xm

from reader,borrow

where reader.dzzh = borrow.dzzh and txm in(

select borrow.txm

from reader,borrow,book

where reader.dzzh = borrow.dzzh and borrow.txm = book.txm and xm = "孙思旺") and xm !="孙思旺"

order by reader.dzzh asc;

#题目4

 select distinct sm

 from borrow,book

 where borrow.txm = book.txm and dzzh in(

     select dzzh

     from borrow,book

     where borrow.txm = book.txm and sm ="李白全集"

 )and sm !="李白全集"

 order by sm asc;

 #代码结束