mysql数据库练习

要用的表和数据:

CREATE TABLE S(
	sno CHAR(4),
	sname CHAR(10),
	statu SMALLINT,
	city CHAR(8)
);
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,'上海');

CREATE TABLE P(
	pno CHAR(4),
	pname CHAR(10),
	color CHAR(10),
	weight SMALLINT
);
INSERT INTO p VALUE('p1','螺母','红',12);
INSERT INTO p VALUE('p2','螺拴','绿',17);
INSERT INTO p VALUE('p3','螺丝刀','蓝',14);
INSERT INTO p VALUE('p4','螺丝刀','红',14);
INSERT INTO p VALUE('p5','凸轮','蓝',40);
INSERT INTO p VALUE('p6','齿轮','红',30);

CREATE TABLE j(
	jno CHAR(4),
	jname CHAR(10),
	city CHAR(10)
);

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','半导体厂','南京');

CREATE TABLE spj(
	sno CHAR(4),
	pno CHAR(10),
	jno CHAR(10),
	qty SMALLINT
);

INSERT INTO spj VALUE('s1','p1','j1',200);
INSERT INTO spj VALUE('s1','p1','j3',100);
INSERT INTO spj VALUE('s1','p1','j4',700);
INSERT INTO spj VALUE('s1','p2','j2',100);

INSERT INTO spj VALUE('s2','p3','j1',400);
INSERT INTO spj VALUE('s2','p3','j2',200);
INSERT INTO spj VALUE('s2','p3','j4',500);
INSERT INTO spj VALUE('s2','p3','j5',400);
INSERT INTO spj VALUE('s2','p5','j1',400);
INSERT INTO spj VALUE('s2','p5','j2',100);

INSERT INTO spj VALUE('s3','p1','j1',200);
INSERT INTO spj VALUE('s3','p3','j1',100);

INSERT INTO spj VALUE('s4','p5','j1',100);
INSERT INTO spj VALUE('s4','p6','j3',300);
INSERT INTO spj VALUE('s4','p6','j4',200);

INSERT INTO spj VALUE('s5','p2','j4',100);
INSERT INTO spj VALUE('s5','p3','j1',200);
INSERT INTO spj VALUE('s5','p6','j2',200);
INSERT INTO spj VALUE('s5','p6','j4',500);

实验内容：
1.	建立一个数据库，数据库名称自定。
2.	在数据库中建立教材70-71页的四个表，并在各个表中插入记录。（这个步骤建议用SQL语句实现，并将SQL语句以文本文件的形式保存，便于以后使用。为提高效率，可以几个同学合作，每个同学负责建立一个表及插入表中的记录，命令运行没有错误后，提供给其他同学使用。）
3.	用SQL实现如下查询：
1）	找出所有供应商的姓名和所在城市

select  SNAME,CITY
from S

2）	找出所有零件的名称，颜色，重量

select  PNAME,COLOR,WEIGHT
from P

3）	找出使用供应商S1所供应零件的工程号码

select  JNO
from SPJ
where SNO='S1'

4）	找出工程项目J2使用的各种零件的名称及其数量

select  PNO,QTY
from SPJ
where JNO='J2'


5）	找出上海厂商供应的所有零件号码

select DISTINCT SPJ.PNO
from SPJ,S
where SPJ.SNO='S5' AND S.SNO=SPJ.SNO 



6）	找出使用上海产的零件的工程名称

select DISTINCT J.JNAME
from S,J,SPJ
where J.JNO=SPJ.JNO AND S.SNO=SPJ.SNO AND S.CITY='上海'



7）	找出没有使用天津产的零件的工程号码
select J.JNO
from J
where NOT EXISTS(
   select *
   from S,SPJ
   where S.CITY='天津' and s.SNO=SPJ.SNO
);

select J.JNO
from J
where jno not in(
   select jno
   from SPJ
   where sno in
   (  select sno 
       from s
	   where city='天津'
   )
);



8）	找出供应工程J1零件的供应商号码

select distinct S.SNO
from S,SPJ
where s.sno=spj.sno and spj.JNO='J1'

9）	找出供应工程J1零件P1的供应商号码
select distinct S.SNO
from S,SPJ
where s.sno=spj.sno and spj.JNO='J1' and SPJ.PNO='p1'



10）	找出供应工程J1零件为红色的供应商号码
select distinct SNO
from P,SPJ
where P.PNO=spj.pno and spj.JNO='J1' and SPJ.PNO='p1' and p.COLOR='红'

11）	找出没有使用天津供应商生产的红色零件的工程号码
select distinct jno
from spj
where sno not in
(select sno
from s
where city='天津') and PNO not in
(  select p.PNO
   from p
   where COLOR='红'
);

select distinct SPJ.JNO
from s,p,spj
where s.CITY!='天津' AND p.COLOR!='红' and s.SNO=spj.SNO 


12）	找出至少使用了供应商S1提供的所有零件的工程号码 

Select distinct jno 
from spj spjx 
where not exists(
select * 
from spj spjy 
where spjy.sno='s1' and not exists(
select * 
from spj spjz 
where spjz.sno=spjy.sno and 
spjz.pno=spjy.pno))



13）	找出没有使用天津产的零件的工程名称

select jname
from J
where not exists
( select *
  from SPJ
  where spj.JNO=j.JNO and (SPJ.SNO='S1' OR SPJ.SNO='S4')
  )

14）	找出供应工程J1零件的供应商名称
select distinct SNAME
from S,SPJ
where JNO='J1' and SPJ.SNO=S.SNO

15）	找出供应工程J1零件P1的供应商名称
select distinct SNAME
from S,SPJ
where JNO='J1' and SPJ.SNO=S.SNO and PNO='P1'


16）	找出供应工程J1零件为红色的供应商名称
select JNAME
from J
where JNO in
( select JNO
  from SPJ
  where PNO in
  ( select PNO
    FROM P
	WHERE COLOR='红' 
  )
)

select distinct JNAME
from P,J,SPJ
where COLOR='红' and P.PNO=SPJ.PNO and SPJ.JNO=J.JNO


17）	找出没有使用天津供应商生产的红色零件的工程名称

select JNAME
from J
where JNO not in
( select JNO
  from S,P,SPJ
  where COLOR='红' and CITY='天津' and P.PNO=SPJ.PNO AND S.SNO=SPJ.SNO 
)


18）	找出至少使用了供应商S1提供的所有零件的工程名称 
SELECT DISTINCT JNAME
FROM J,SPJ SPJX
WHERE NOT EXISTS
(  SELECT *
   FROM SPJ SPJY
   WHERE SPJY.SNO='S1' and
   NOT EXISTS 
   ( SELECT *
     FROM SPJ SPJZ
	 WHERE SPJZ.SNO=SPJX.SNO AND
     SPJZ.PNO=SPJX.PNO
   )
)



19）	统计一共有多少种零件

SELECT COUNT(PNO)
FROM P

20）	统计每个工程所使用的零件种类

SELECT JNAME,COUNT(DISTINCT PNO)
FROM J,SPJ
WHERE J.JNO=SPJ.JNO 
GROUP BY JNAME 


21）	统计每个工程所使用的零件数量

SELECT JNAME,COUNT(*)
FROM J,SPJ
WHERE J.JNO=SPJ.JNO 
GROUP BY JNAME 




22）	找出提供零件种类超过了2种的供应商号码
SELECT SPJ.SNO
FROM S,SPJ
WHERE S.SNO=SPJ.SNO 
GROUP BY SPJ.SNO
HAVING COUNT(distinct SPJ.PNO)>2


23）	找出提供零件种类超过了2种的供应商名称
SELECT SNAME
FROM S,SPJ
WHERE S.SNO=SPJ.SNO 
GROUP BY SNAME
HAVING COUNT(distinct SPJ.PNO)>2


24）	找出为3个以上的工程提供零件的供应商号码
SELECT SPJ.SNO
FROM S,SPJ
WHERE S.SNO=SPJ.SNO 
GROUP BY SPJ.SNO
HAVING COUNT(distinct JNO)>3



25）	找出为3个以上的工程提供零件的供应商名称
SELECT SNAME
FROM S,SPJ
WHERE S.SNO=SPJ.SNO 
GROUP BY SNAME
HAVING COUNT(distinct JNO)>3