数据库系统简明教程 第三章关系数据库 课后习题

题目链接http://blog.csdn.net/cnlht/article/details/13020739#comments

--创建SPJ数据库及插入数据

*/

--创建数据库

create database SPJ;

go

use SPJ;

go

--创建数据库表

create table S (

SNO char(9) primary key,

SNAME char(20),

STATUS char(10),

CITY char(20)

);

create table P(

PNO char(9) primary key,

PNAME char(20),

COLOR char(10),

WEIGHT smallInt

);

create table J(

JNO char(9) primary key,

JNAME char(20),

CITY char(20)

);

create table SPJ(

SNO char(9),

PNO char(9),

JNO char(9),

QTY smallInt,

primary key (SNO,PNO,JNO),

foreign key (SNO) references S(SNO),

foreign key (PNO) references P(PNO),

foreign key (JNO) references J(JNO)

);

--插入数据

insert into S values('S1','精益','20','天津');

insert into S values('S2','盛锡','10','北京');

insert into S values('S3','东方红','30','北京');

insert into S values('S4','丰泰盛','20','天津');

insert into S values('S5','为民','30','上海');

insert into P values('P1','螺母','红',12);

insert into P values('P2','螺栓','绿',17);

insert into P values('P3','螺丝刀','蓝',14);

insert into P values('P4','螺丝刀','红',14);

insert into P values('P5','凸轮','蓝',40);

insert into P values('P6','齿轮','红',30);

insert into J values('J1','三建','北京');

insert into J values('J2','一汽','长春');

insert into J values('J3','弹簧厂','天津');

insert into J values('J4','造船厂','天津');

insert into J values('J5','机车厂','唐山');

insert into J values('J6','无线电厂','常州');

insert into J values('J7','半导体厂','南京');

insert into SPJ values('S1','P1','J1',200);

insert into SPJ values('S1','P1','J3',100);

insert into SPJ values('S1','P1','J4',700);

insert into SPJ values('S1','P2','J2',100);

insert into SPJ values('S2','P3','J1',400);

insert into SPJ values('S2','P3','J2',200);

insert into SPJ values('S2','P3','J4',500);

insert into SPJ values('S2','P3','J5',400);

insert into SPJ values('S2','P5','J1',400);

insert into SPJ values('S2','P5','J2',100);

insert into SPJ values('S3','P1','J1',200);

insert into SPJ values('S3','P3','J1',200);

insert into SPJ values('S4','P5','J1',100);

insert into SPJ values('S4','P6','J3',200);

insert into SPJ values('S4','P6','J4',300);

insert into SPJ values('S5','P2','J4',200);

insert into SPJ values('S5','P3','J1',100);

insert into SPJ values('S5','P6','J2',200);

insert into SPJ values('S5','P5','J4',500);

1、通过SPJ数据库完成课后针对SPJ的查询练习

（1）

select SNO as 供应工程J1零件的供应商号码SNO from SPJ where JNO='J1';

（2）

select SNO as 供应工程J1零件P1的供应商号码SNO fromSPJ where JNO='J1' and PNO='P1';

（3）

select SNO as 供应工程J1零件为红色的供应商号码SNO from SPJ,P where SPJ.JNO='J1' and P.COLOR='红';

（4）

select SPJ.JNO as 没有使用天津供应商生产的红色零件的工程号JNO

from SPJ,S,P,J

where SPJ.SNO=S.SNO and SPJ.PNO=P.PNO andSPJ.JNO=J.JNO and S.CITY!='天津' and P.COLOR='红' and J.CITY!='天津';

（5）

select JNO as 至少用了供应商S1所供应的全部零件的工程号JNO

from SPJ

where SNO='S1';

2、根据SPJ数据库用SQL语句完成以下查询。

1）查询“天津”的供应商详细信息。

*/

select * from S where CITY='天津';

/*

2）查询不是“天津”的供应商代码。

*/

select * from S where CITY<>'天津';

/*

3）查询供应工程 J1零件的供应商代码。

*/

select distinct SNO as 供应商代码

from SPJ

where JNO='J1';

/*

4）查询供应商S1供应过的商品代码。

*/

select distinct PNO as 商品代码

from SPJ

where SNO='S1';

/*

5）查询供应商S1供应工程J1的零件种类有几种。

*/

select COUNT(*) as 零件种类

from SPJ

where SNO='S1' and JNO='J1';

/*

6）查询供应商S1供应工程J1的各种零件的零件代码和其数量和。

*/

select PNO as 零件代码,SUM(QTY) as 数量和

from SPJ

where SNO='S1' and JNO='J1'

group by PNO;

/*

7）查询J1工程使用的零件种类有几种。

*/

select COUNT(*) as 零件种类

from P

where EXISTS (

 select * from SPJ where JNO='J1' and P.PNO=SPJ.PNO

);

/*

8）查询J1工程使用的各种零件代码和其数量和。

*/

select PNO as 零件代码,SUM(QTY) as 数量和

from SPJ

where SPJ.JNO='J1'

group by PNO;

/*

9）查询J1工程使用的零件总数大于300的零件代码和数量。

*/

select PNO as 零件代码,SUM(QTY) as 数量

from SPJ

where SPJ.JNO='J1'

group by PNO

having SUM(QTY)>300;

/*

10）查询J1工程使用的各种零件，其数量至少大于J3使用的S2供应的所有零件数量和的，零件代码和数量。

 --J3使用S2供应的零件数为0

*/

select PNO as 零件代码,SUM(QTY) as 数量

from SPJ A

where A.JNO='J1'

group by PNO

having SUM(QTY)>(

select SUM(QTY)

from SPJ B

where B.JNO='J3' and B.SNO='S2'

);

/*

11）查询J1工程使用的各种零件，其数量至少大于J3使用的同类数量和的，零件代码和数量。

*/

select PNO as 零件代码,SUM(QTY) as 数量

from SPJ A

where A.JNO='J1'

group by A.PNO

having SUM(A.QTY)>ALL(

select SUM(B.QTY)

from SPJ B

where B.JNO='J3' and B.PNO=A.PNO

);

/*

12）查询至少使用了J1工程所用的几类零件的工程的工程号。

*/

select distinct JNO as 工程号

from SPJ a

where JNO<>'J1' and Not Exists (

       select*

       fromSPJ b

       whereb.JNO='J1' and b.PNO not in (

              select PNO

              from SPJ c

              where c.JNO=a.JNO

       )

);

/*

13）查询至少使用了所有红色零件的工程号。

*/

select JNO as 工程号

from SPJ a

where Not Exists (

       select*

       fromP

       whereCOLOR='红' and PNO NotIn (

              select PNO

              from SPJ b

              where b.JNO=a.JNO

       )

);

/*

14）查询使用了全部红色零件的工程号。

*/

select JNO as 工程号

from SPJ a

where Not Exists (

       select*

       fromP

       whereCOLOR='红' and PNO NotIn (

              select PNO

              from SPJ b

              where b.JNO=a.JNO

       )

);

/*

15）查询工程所在地与供应其零件的供应商在同一个城市的工程号和供应商号。

*/

select distinct JNO, SNO

from SPJ a

where EXISTS (

       select*

       fromS b

       whereb.SNO=a.SNO and EXISTS (

              select *

              from J c

              where a.JNO=c.JNO and c.CITY=b.CITY

       )

);

/*

16）查询没有使用天津供应商生产的红色零件的工程号JNO。

*/

select distinct JNO

from SPJ SPJ1

where JNO NOT IN (

       selectdistinct JNO

       fromSPJ a

       whereEXISTS (

              select *

              from S b1,P b2

              where b1.CITY='天津' and b2.COLOR='红' and b1.SNO=a.SNO and b2.PNO=a.PNO

       )

);

/*

17）查询至少使用了供应商S1供应的全部零件的工程号JNO。

*/

--(情况一：所使用的零件是S1供应的）

select distinct JNO

from SPJ SPJ1

where Not Exists (

       select* --S1供应的全部零件

       fromSPJ SPJ2

       whereSPJ2.SNO='S1' and SPJ2.PNO not in (

              select PNO

              from SPJ SPJ3

              where SPJ3.JNO=SPJ1.JNO and SPJ3.SNO='S1'

       )

);

--(情况二：所使用的零件不一定是S1供应的）

select distinct JNO

from SPJ SPJ1

where Not Exists (

       select* --S1供应的全部零件

       fromSPJ SPJ2

       whereSPJ2.SNO='S1' and SPJ2.PNO not in (

              select PNO

              from SPJ SPJ3

              where SPJ3.JNO=SPJ1.JNO and SPJ3.SNO='S1'

       )

);

/*

18）查询使用了全部上海产的零件的工程号JNO。

*/

--(情况一：不一定是上海供应商供应的零件）

select distinct JNO

from SPJ SPJ1

where Not Exists (

       select*

       fromS S2,SPJ SPJ2

       whereS2.CITY='上海' andSPJ2.SNO=S2.SNO and SPJ2.PNO Not In (

              select PNO

              from SPJ SPJ3

              where SPJ3.JNO=SPJ1.JNO

       )

);

--(情况二：是上海供应商供应的零件）

select distinct JNO

from SPJ SPJ1

where Not Exists (

       select*

       fromS S2,SPJ SPJ2

       whereS2.CITY='上海' andSPJ2.SNO=S2.SNO and SPJ2.PNO Not In (

              select PNO

              from SPJ SPJ3

              where SPJ3.JNO=SPJ1.JNO andSPJ3.SNO=SPJ2.SNO

       )

);

/*

19）查询没有使用J2所使用的所有零件的工程号JNO。

*/

select JNO

from SPJ SPJ1

where SPJ1.JNO<>'J2' and Not Exists (

       select*

       fromSPJ SPJ2

       whereSPJ2.JNO='J2' and SPJ2.PNO In (

              select PNO

              from SPJ SPJ3

              where SPJ3.JNO=SPJ1.JNO

       )

);