实验六 连接查询

发布时间:2024年01月03日

??????打开“实验四”中的SPJ数据库,完成以下查询

  1. 求所在的城市为直辖市的供应商的名称。
  2. 求供应红色零件的供应商号,零件号和数量。
  3. 没有使用天津供应商生产的红色零件的工程号JNO。
  4. 求供应数量超过300的供应信息,包括供应商名,零件名,项目名和供应数量。
  5. 至少使用了供应商S1所供应的全部零件的工程号JNO。(项目的供应商不一定是S1,但是该项目一定要涵盖s1能提供的所有零件号。)
  6. 找出使用供应商S1所提供零件的工程号码。(项目的供应商不一定是s1,但使用了与s1能提供的零件相同的零件)
  7. 找出工程项目J2使用的各种零件的名称及其重量。
  8. 找出上海厂商供应的所有零件号码。
  9. 找出使用上海产的零件的工程名称。
  10. 找出没有使用天津产的零件的工程号码。
(1)	求所在的城市为直辖市的供应商的名称。
SELECT
SNAME
FROM S
WHERE CITY IN('北京','天津','上海','重庆');

(2)	求供应红色零件的供应商号,零件号和数量。
SELECT
SNO 供应商号,
SPJ.PNO 零件号,
QTY 数量
FROM SPJ,P
WHERE SPJ.PNO=P.PNO AND P.COLOR='红';

(2)	求供应红色零件的供应商号,零件号和数量。
--分下组,统计数量
SELECT
SNO 供应商号,
SPJ.PNO 零件号,
SUM (QTY) 总数量
FROM SPJ,P
WHERE SPJ.PNO=P.PNO AND P.COLOR='红'
GROUP BY SNO,SPJ.PNO;

(3)	没有使用天津供应商生产的红色零件的工程号JNO。
--思路:先找出使用了天津供应商生产的红色零件的工程号JNO
SELECT 
JNO
FROM J
WHERE JNO NOT IN
(SELECT
DISTINCT J.JNO
FROM S,P,J,SPJ
WHERE S.SNO=SPJ.SNO
AND P.PNO=SPJ.PNO
AND J.JNO=SPJ.JNO
AND S.CITY='天津'
AND P.COLOR='红');

(4)	求供应数量超过300的供应信息,包括供应商名,零件名,项目名和供应数量。
SELECT SNAME,PNAME,JNAME,QTY
FROM SPJ,S,P,J
WHERE QTY>300 and 
SPJ.SNO=S.SNO and 
SPJ.PNO=P.PNO and 
SPJ.JNO=J.JNO;

(5)	至少使用了供应商S1所供应的全部零件的工程号JNO。(项目的供应商不一定是S1,但是该项目一定要涵盖s1能提供的所有零件号。)

-1.先查询出供应商S1所供应的零件集合U={P1,P2}
--2.再查询使用P1零件的工程号集合U1,和使用P2零件的工程号集合U2, U1与U2取交集就是所求结果。
SELECT DISTINCT PNO
FROM SPJ
WHERE SNO='S1';

SELECT JNO
FROM SPJ WHERE PNO='P1' INTERSECT SELECT JNO FROM SPJ WHERE PNO='P2';


(6)	找出使用供应商S1所提供零件的工程号码。(项目的供应商不一定是s1,但使用了与s1能提供的零件相同的零件)
SELECT DISTINCT JNO
FROM SPJ
WHERE PNO IN(
SELECT DISTINCT PNO
FROM SPJ
WHERE SNO='S1');

(7)	找出工程项目J2使用的各种零件的名称及其重量。
SELECT	DISTINCT P.PNO,PNAME,WEIGHT
FROM P,SPJ
WHERE P.PNO=SPJ.PNO AND SPJ.JNO='J2';

(8)	找出上海厂商供应的所有零件号码。
SELECT DISTINCT PNO
FROM SPJ,S
WHERE SPJ.SNO=S.SNO AND S.CITY='上海';

(9)	找出使用上海产的零件的工程名称。
SELECT JNAME
FROM J,S,SPJ
WHERE S.CITY='上海' AND J.JNO=SPJ.JNO AND S.SNO=SPJ.SNO;

(10)	找出没有使用天津产的零件的工程号码。
SELECT DISTINCT JNO
FROM J
WHERE J.JNO NOT IN
(SELECT DISTINCT  JNO
FROM SPJ,S
WHERE S.CITY='天津' AND S.SNO=SPJ.SNO);

打开“实验四”中的学生-课程数据库(S_T),完成以下查询

  1. 查询‘IS’系学生的学号、所选课程名称及该门课程的成绩。
  2. 查询‘CS’系成绩不及格的学生姓名。
  3. 查询每一门的课程的间接先修课程。
  4. 查询所有的学生的选课情况,要求没有选课的情况也能在结果显示出来。
  5. 查询每个学生超过他选修课程平均成绩的课程号,课程名称及成绩。
  6. 查询‘IS’系的学生以及‘数据库系统原理’成绩在70~80之间的学生。
  7. 用两种方法实现:选修了001课程或002课程的学生学号。
  8. 查询至少选修了学生001课程和002课程的学生学号。
(1)	查询‘IS’系学生的学号、所选课程名称及该门课程的成绩。
--先插入一条数据到SC中
INSERT INTO SC
VALUES ('202215125','7','79');

--查询‘IS’系学生的学号、所选课程名称及该门课程的成绩。
SELECT S.Sno,Cname,Grade
FROM Student S,Course C,SC
WHERE C.Cno=SC.Cno AND SC.Sno=S.Sno AND S.Sdept='IS';
 
(2)	查询‘CS’系成绩不及格的学生姓名。
SELECT Sname
FROM Student,SC
WHERE Grade<60 AND SC.SNO=Student.SNO AND Sdept='CS';
 
(3)	查询每一门的课程的间接先修课程。
SELECT
C1.Cno,C2.Cpno
FROM Course C1, Course C2
WHERE C1.Cpno=C2.Cno;

(4)	查询所有的学生的选课情况,要求没有选课的情况也能在结果显示出来。
SELECT 
Student.*,
SC.CNO
FROM Student LEFT JOIN SC
ON SC.Sno=Student.Sno;

(5)	查询每个学生超过他选修课程平均成绩的课程号,课程名称及成绩。
SELECT X.Sno,X.Cno,Cname,Grade
FROM Course C,SC X
WHERE X.Cno=C.Cno AND 
Grade>(SELECT AVG(Grade)
FROM SC Y
GROUP BY Y.Sno
HAVING X.Sno=Y.Sno
);

(6)	查询‘IS’系的学生以及‘数据库系统原理’成绩在70~80之间的学生。
SELECT S.Sno
FROM Student S,Course C,SC
WHERE  (Grade BETWEEN 70 AND 80) AND S.Sdept='IS' AND C.Cname='数据库系统原理' 
AND SC.Cno=C.Cno AND SC.Sno=S.Sno;

方法二代码如下:(用交集操作)
--查询‘IS’系的学生以及‘数据库系统原理’成绩在70~80之间的学生。
SELECT S.Sno
FROM Student S,SC
WHERE S.Sdept='IS' AND SC.Grade BETWEEN 70 AND 80
INTERSECT
SELECT Sno
FROM Course C,SC
WHERE C.Cno=SC.Cno AND C.Cname='数据库系统原理';
 
(7)	用两种方法实现:选修了001课程或002课程的学生学号。
方法一代码如下:
--用两种方法实现:选修了001课程或002课程的学生学号。
SELECT DISTINCT Sno
FROM SC
WHERE Cno IN('1','2');或者WHERE Cno='1' or Cno='2';

方法二代码如下:
--用两种方法实现:选修了001课程或002课程的学生学号。
SELECT Sno
FROM SC
WHERE Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Cno='2'

(8)	查询至少选修了学生001课程和002课程的学生学号。
方法一代码如下:
--查询至少选修了学生001课程和002课程的学生学号。
SELECT SC1.Sno
FROM SC SC1, SC SC2
WHERE SC1.Sno=SC2.Sno AND SC1.Cno='1' AND SC2.Cno='2';

方法二代码如下:
--查询至少选修了学生001课程和002课程的学生学号。
SELECT Sno
FROM SC
WHERE Cno='1'
INTERSECT
SELECT Sno
FROM SC
WHERE Cno='2'

方法三代码如下:
--查询至少选修了学生001课程和002课程的学生学号。
SELECT Sno
FROM SC
WHERE SC.Cno='1' AND 
Sno IN(SELECT Sno FROM SC WHERE SC.Cno='2');

文章来源:https://blog.csdn.net/s1ms1mpleple/article/details/135297315
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。