SQL上机作业(2)

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. 找出所有供应商的姓名和所在城市。

from S;
7. 找出所有零件的名称、颜色、重量。

select pname,color,weight
from P;
8. 找出使用供应商S1所供应零件的工程号码。

select jno
from SPJ
where sno='s1';
9. 找出工程项目J2使用的各种零件的名称及其数量。

select ,SPJ.qty
from P,SPJ
where P.pno=SPJ.pno and SPJ,jno=‘j2’;
10. 找出上海厂商供应的所有零件号码。

select distinct pno
from SPJ
where sno in (select sno
from S
where city=‘上海’);
11. 找出使用上海产的零件的工程名称。

select jname
from S,SPJ,J
where SPJ.sno=S.sno and SPJ.jno=J.jno and S.city='上海'; 方法二
selcet jname
from J
where jno in (select jno
from SPJ,S
where SPJ.sno=S.sno and S.city=’上海’);
12. 找出没有使用天津产的零件的工程号码。

select jno
from J
where jno not in
(select jno
from SPJ
where SPJ.jno=J.jno and
sno in (select sno
from S
where city='天津'));
13. 把全部红色零件的颜色改为蓝色。

update p
set color='蓝'
where color='红';
14. 由S5供给J4的零件P6改为由S3供应，请作必要的修改。

update spj
set sno='s3'
where sno='s5' and pno='p6' and jno='j4';
15. 从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录。

delete
from s
where sno='s2';
delete
from spj
where sno='s2';
16. 请将（S2，J6，P4，200）插入供应情况关系。

insert
into spj(sno,jno,pno,qty)
values('s2','j6','p4',200);。