SQL上机作业(2)
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
SQL上机作业（2）
一.针对SQL上机作业（1）第一题定义的三个表（book，card，borrow），用SQL语句实现如下查询：（注：藏书量=库存量+借出数）
1．求藏书种数、总册数、最高价、最低价。
select count(category)as category,sum(total)as total,
max(price) as maxpric,min(price)as minpric
from book
group by book.category;
select count(*),sum(total),max(price),min(price)
from book;
2．列出藏书在十本以上的书（书名、作者、出版社、年份）。
select title,author,press,year
from book
where total>10;
3．哪些出版社的总藏书超过100种。
select press ,count(distinct category)
from book
group by press
having count(distinct category)>100;
4．目前已借出多少册书？
select sum(total)-sum(stock)
from book;
5．年份最久远的书。
select title
from book
group by title
having min(year)<=all(select min(year)
from book
group by title);
6．“数据库系统原理教程，王珊编，清华大学出版社，1998年出版”还有几本？
select title ,stock
from book
where title='数据库系统原理教程'and press='清华大学出版社'
and author='王珊' and year='1998';
7．哪一年的图书最多？
select year
from book
group by year
having sum(total)>=all(select sum(total)
from book
group by total);
8．平均每本借书证的借书册数。
select count(*)/count(cno)
from borrow
where return_date is not null;
9．今年未借过书的借书证。
Select cno
From borrow
Where year(borrow_date)<'2009';
10．哪个系的同学平均借书最多？
create view CB
as
select o,department
from card,borrow
where o=o;
select department
from CB
group by department
having count(department)>all(select department
from CB
group by department);
11．最近两年都未被借过的书。
select title
from book
where bno not in(select bno
from borrow
where year(borrow_date)between'2007' and '2009');
12．今年那种书出借最多？
select category ,(sum(total)-sum(stock))
from book
group by category
having (sum(total)-sum(stock))>all(select (sum(total)-sum(stock))
from book
group by category);
二、针对SQL上机作业（1）第二题定义的四个表（S，P，J，SPJ），用SQL语句实现如下查询：
1．求供应工程J1零件的供应商号码SNO；
from SPJ
where jno='j1';
2．求供应工程J1零件P1的供应商号码SNO；
select distinct sno
from SPJ
where jno='j1'and pno='p1';
3．求供应工程J1零件为红色的供应商号码SNO；
方法一：复合条件连接查询
select sno
from SPJ,P
where SPJ.pno=P.pno and SPJ.jno='j1' and P.color='红'; 方法二：嵌套查询
select sno
from SPJ
where jno='j1'and
pno in
(select pno
from P
where color='红');
4．求没有使用天津供应商生产的红色零件的工程号JNO；select distinct jno
from SPJ
where jno not in
(select jno
from SPJ,P,S
where S.sno=SPJ.sno and
P.pno=SPJ.pno and
S.city='天津' and
P.color='红');
5．求至少用了供应商S1所供应的全部零件的工程号JNO。select distinct jno
from SPJ fst
where not exists
(select *
from SPJ snd
where snd.sno='s1' and
not exists
(select *
from SPJ trd
where trd.jno=fst.jno and
trd.pno=snd.pno));
6. 找出所有供应商的姓名和所在城市。