数据库系统原理 | 查询作业2

整理自博主本科《数据库系统原理》专业课自己完成的实验课查询作业，以便各位学习数据库系统概论的小伙伴们参考、学习。

*文中若存在书写不合理的地方，欢迎各位斧正。

专业课本：

​

​

————

本次实验使用到的图形化工具：Heidisql

​

上一篇：数据库系统原理 | 查询作业1-优快云博客

---

目录

使用EDUC数据库进行查询

1.查询课程总个数。 

2.计算学号为“200215121”的学生平均成绩。

3.查询被选修的课程的个数。 

4.查询学号为“200215121”的学生所考试的课程中的最高分数。

5.求每门课的平均分。要求输出课程号和平均分 

6.求每个选课的男学生的学号和 最低分和平均分

7.求每个选课的年龄大于20岁的学生的学号和平均分。要求只输出平均分小于60的。 

8.查询每一门课的间接先修课（即先修课的先修课），输出：课程号、先修课的课程号、先修课的先修课的课程号。 

9. 查询与“李勇”在同一个系学习的学生（输出结果中不包含李勇本人）。（使用自身连接）

10.查询至少选修了两门课的学生的学号。（使用自身连接和分组查询两种方法实现） 

使用spj数据库进行查询

1.在spj表中，如果供应数量qty是null。代表供应商给某项目供应的某零件的数量不确定。查询有那些供应商给哪些项目供应的哪些零件是不确定数量的。要求给出供应商名，项目名和零件名（需要多个表连接）。 

2.查询重量小于40的零件的零件号，零件名和零件颜色。并按照零件颜色排序降序排列，颜色相同的，按照零件号升序排列。 

3.找出最重的三种零件的零件号和零件名。 

4.查询零件共有几种颜色。 

5.如果每种零件取一个(零件号不同就是不同种的零件)，那么所有种类的零件的总重量是多少。 

6.查询红色零件中最轻的那种零件的重量是多少。 

7.查询蓝色零件的平均重量。 

8.求每种颜色的平均重量和最重的重量。要求输出颜色和平均重量和最重的重量。 

9.在spj表中，计算每个工程项目所被供应的零件的总个数。输出：工程项目号和零件总个数总 个数。

10.计算每个供应商供应的零件的总个数。要求输出：供应商名和零件总个数。

11.所有城市所在地是‘天津’的工程项目被供应的零件的总个数。输出：工程项目名和零件总个数。 

12.统计汇总每个供应商提供给每个工程项目的零件的总个数。输出：供应商号和工程项目号和零件总个数。 

13.查询有哪些供应商所供应的零件总数超过了1000个。输出供应商号和零件总数。 

14.查询有那些零件的供应量小于500.输出零件号，零件名称和供应量。 

15.在S表中查询，和供应商S1在同一个城市的其他供应商的供应商号和供应商名。（用自身连接） 

---

使用EDUC数据库进行查询

---

EDUC数据库建库建表代码：

create database educ;
use educ;
CREATE TABLE Student
(
Sno CHAR(9) NOT NULL PRIMARY KEY,
Sname CHAR(20),
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
 
CREATE TABLE Course
(
Cno CHAR(4) NOT NULL PRIMARY KEY,
Cname CHAR(40) NOT NULL,
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);
 
CREATE TABLE SC
(
Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY(Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
 
INSERT INTO Student VALUES('200215121','李勇','男',20,'CS');
INSERT INTO Student VALUES('200215122','刘晨','女',19,'CS');
INSERT INTO Student VALUES('200215123','王敏','女',18,'MA');
INSERT INTO Student VALUES('200215125','张立','男',19,'IS');
INSERT INTO Student VALUES('200215124','张立','男',19,'IS');
 
INSERT INTO Course VALUES('2','数学',null,2);
INSERT INTO Course VALUES('6','数据处理',null,2);
INSERT INTO Course VALUES('7','pascal语言','6',4);
INSERT INTO Course VALUES('5','数据结构','7',4);
INSERT INTO Course VALUES('4','操作系统','6',3);
INSERT INTO Course VALUES('1','数据库','5',4);
INSERT INTO Course VALUES('3','信息系统','1',4);
 
INSERT INTO SC VALUES('200215121','1',92);
INSERT INTO SC VALUES('200215121','2',85);
INSERT INTO SC VALUES('200215121','3',88);
INSERT INTO SC VALUES('200215122','2',90);
INSERT INTO SC VALUES('200215122','3',80);

1.查询课程总个数。 

SELECT COUNT(*)
FROM course

2.计算学号为“200215121”的学生平均成绩。

SELECT AVG(grade)
FROM sc
WHERE sno=200215121;

3.查询被选修的课程的个数。 

SELECT COUNT(DISTINCT cno)
FROM sc

4.查询学号为“200215121”的学生所考试的课程中的最高分数。

SELECT MAX(grade)
FROM sc
WHERE sno=200215121;

5.求每门课的平均分。要求输出课程号和平均分 

select Cno,AVG(grade) as 平均分
from SC
group by Cno

6.求每个选课的男学生的学号和 最低分和平均分

select sc.Sno,MIN(Grade) as 最低分,AVG(grade) as 平均分
from Student,SC
where Student.Sno = SC.Sno and Ssex = '男'
group by SC.Sno

7.求每个选课的年龄大于20岁的学生的学号和平均分。要求只输出平均分小于60的。 

select sc.sno,AVG(grade) as 平均分
from SC,Student
where SC.Sno = Student.Sno and Sage > 20
group by SC.Sno
having AVG(Grade) <60

8.查询每一门课的间接先修课（即先修课的先修课），输出：课程号、先修课的课程号、先修课的先修课的课程号。 

SELECT FIRST.Cno,FIRST.Cpno,SECOND.Cpno
FROM course FIRST,course SECOND
WHERE FIRST.Cpno=SECOND.Cno;

9. 查询与“李勇”在同一个系学习的学生（输出结果中不包含李勇本人）。（使用自身连接）

SELECT S1.Sname,S1.Sno,S1.Sdept
FROM student S1,student S2
WHERE S1.Sdept=S2.Sdept AND S2.Sname='李勇' AND S1.Sname<>'李勇';

10.查询至少选修了两门课的学生的学号。（使用自身连接和分组查询两种方法实现） 

自身连接

SELECT DISTINCT sc1.Sno
FROM sc sc1,sc sc2
WHERE sc1.Sno=sc2.Sno AND sc1.Cno<>sc2.Cno;

分组查询

SELECT sno,COUNT(cno)
FROM sc
GROUP BY sno HAVING COUNT(cno)>=2;

---

使用spj数据库进行查询

spj数据库建库建表代码：

create database spj;
use spj;
CREATE TABLE S
(
SNO CHAR(4) NOT NULL PRIMARY KEY,
SNAME VARCHAR(20),
STATUS SMALLINT,
CITY VARCHAR(20)
);
 
 
CREATE TABLE P
(
PNO CHAR(4) NOT NULL PRIMARY KEY,
PNAME VARCHAR(20),
COLOR VARCHAR(10),
WEIGHT SMALLINT
);
 
CREATE TABLE J
(
JNO CHAR(4) NOT NULL PRIMARY KEY,
JNAME VARCHAR(20),
CITY VARCHAR(20)
);
 
CREATE TABLE SPJ
(
SNO CHAR(4) NOT NULL,
PNO CHAR(4) NOT NULL,
JNO CHAR(4) NOT NULL,
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','丰泰盛',20,'上海');
 
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',200);
INSERT INTO SPJ VALUES('S5','P2','J4',100);
INSERT INTO SPJ VALUES('S5','P3','J1',200);
INSERT INTO SPJ VALUES('S5','P6','J2',200);
INSERT INTO SPJ VALUES('S5','P6','J4',500);
INSERT INTO SPJ VALUES('S1','P1','J2',500);

1.在spj表中，如果供应数量qty是null。代表供应商给某项目供应的某零件的数量不确定。查询有那些供应商给哪些项目供应的哪些零件是不确定数量的。要求给出供应商名，项目名和零件名（需要多个表连接）。 

SELECT s.SNO,j.JNO,p.PNO
FROM s,p,j,spj
WHERE s.SNO=spj.SNO AND p.PNO=spj.PNO AND j.JNO=spj.JNO AND qty IS NULL ;

2.查询重量小于40的零件的零件号，零件名和零件颜色。并按照零件颜色排序降序排列，颜色相同的，按照零件号升序排列。 

SELECT p.PNO,p.PNAME,p.COLOR 
FROM p,spj
WHERE p.PNO=spj.PNO AND qty<40
ORDER BY pno,color DESC ;

3.找出最重的三种零件的零件号和零件名。 

SELECT p.PNO,p.PNAME,weight
FROM p 
ORDER BY weight DESC LIMIT 3;

4.查询零件共有几种颜色。 

SELECT COUNT(DISTINCT color) 
FROM p

5.如果每种零件取一个(零件号不同就是不同种的零件)，那么所有种类的零件的总重量是多少。 

SELECT SUM(weight)
FROM p;

6.查询红色零件中最轻的那种零件的重量是多少。 

SELECT weight
FROM p
WHERE color='红'
ORDER BY weight LIMIT 1;

7.查询蓝色零件的平均重量。 

SELECT AVG(weight)
FROM p
WHERE color='蓝';

8.求每种颜色的平均重量和最重的重量。要求输出颜色和平均重量和最重的重量。 

SELECT color,AVG(weight),MAX(weight)
FROM p
GROUP BY color;

9.在spj表中，计算每个工程项目所被供应的零件的总个数。输出：工程项目号和零件总个数总 个数。

SELECT jno,SUM(qty)
FROM spj
GROUP BY jno;

10.计算每个供应商供应的零件的总个数。要求输出：供应商名和零件总个数。

SELECT spj.SNO,s.SNAME,SUM(qty)
FROM spj,s
WHERE spj.SNO=s.SNO
GROUP BY spj.SNO;

11.所有城市所在地是‘天津’的工程项目被供应的零件的总个数。输出：工程项目名和零件总个数。 

SELECT SUM(qty)
FROM s,spj
WHERE s.SNO=spj.SNO AND city='天津';

12.统计汇总每个供应商提供给每个工程项目的零件的总个数。输出：供应商号和工程项目号和零件总个数。 

SELECT sno,jno,SUM(qty)
FROM spj
GROUP BY sno,jno

13.查询有哪些供应商所供应的零件总数超过了1000个。输出供应商号和零件总数。 

SELECT sno,SUM(qty) 
FROM spj
GROUP BY sno HAVING SUM(qty)>1000;

14.查询有那些零件的供应量小于500.输出零件号，零件名称和供应量。 

SELECT p.PNO,p.PNAME,qty 
FROM spj,p
WHERE spj.PNO=p.PNO AND qty<500

15.在S表中查询，和供应商S1在同一个城市的其他供应商的供应商号和供应商名。（用自身连接） 

SELECT s2.SNO,s2.SNAME,s2.CITY
FROM s s1,s s2
WHERE s1.CITY=s2.CITY AND s1.SNAME='精益';