数据库实验 MOOC – 数据库系统概论 – 实验操作之单表查询 多表查询

  • Post author:
  • Post category:其他




实验一

学生表:Student(Sno,Sname,Ssex,Sage,Sdept)

课程表:Course(Cno,Cname,Cpno,Ccredit)

学生选课表:SC(Sno,Cno,Grade)

注:性别为enum(‘男’or’女’),请使用KingBase语句进行答题,语句后要添加分号,否则语句视为错误


题目链接



1. 查询全体学生的详细信息!(要求输出全部列)

SELECT *
FROM Student;



2. 查询课程的详细信息(要求输出全部列)

SELECT *
FROM Course;



3. 查询所有女生的姓名(输出列为:SNAME)

SELECT Sname SNAME
FROM Student
WHERE Ssex='女';



4. 查询至少选修了一门课程的学生学号(请在结果中除去重复值,输出列为:SNO)。

SELECT DISTINCT Sno SNO
FROM SC
GROUP BY Sno
HAVING COUNT(*)>=1;



5. 查询年龄不在20~23之间的学生姓名、系别和年龄,并按照年龄降序排列(输出列为:SNAME, SDEPT,SAGE)

SELECT Sname SNAME,Sdept SDEPT,Sage SAGE
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23
ORDER BY Sage DESC;



6. 查询所有姓曹的学生的姓名、学号和性别(输出列为:SNAME,SNO,SSEX)。

SELECT Sname SNAME,Sno SNO,Ssex SSEX
FROM Student
WHERE Sname LIKE '曹%';



7. 查询“信息学院”、“理学院”和“经济学院”学生的姓名和性别(输出列为:SNAME,SSEX)

SELECT Sname SNAME,Ssex SSEX
FROM Student
WHERE Sdept='信息学院' OR Sdept='理学院' OR Sdept='经济学院';



8. 查询每个同学选课的数目,并按学号升序排列,(输出列为SNO,选课数目SUM)

SELECT Sno SNO,COUNT(*) SUM
FROM SC
GROUP BY Sno;



9. 统计不同系的人数(输出列为:SDEPT、人数)

SELECT Sdept SDEPT,COUNT(*) 人数
FROM Student
GROUP BY Sdept;



实验二



1. 查询学生姓名,对应的课程名和成绩(输出列为:SNAME,CNAME,GRADE)

SELECT Sname SNAME,Cname CNAME,Grade GRADE
FROM SC JOIN Student S ON SC.Sno=S.Sno JOIN Course C ON C.Cno=SC.Cno;



2. 查询每个学生的学号、姓名及其选修课程的名称和成绩(包括没有选修课程的学生情况(输出列为:SNO, SNAME, CNAME, GRADE)。

SELECT S.Sno SNO,Sname SNAME,Cname CNAME,Grade GRADE
FROM SC  RIGHT JOIN Student S ON SC.Sno=S.Sno JOIN Course C ON C.Cno=SC.Cno;



3. 查询其他系中比“信息学院”某一学生年龄小的学生姓名和年龄(输出列为:SNAME, SAGE)。

SELECT Sname SNAME,Sage SAGE
FROM Student
WHERE Sdept<>'信息学院' AND Sage<SOME(SELECT Sage FROM Student WHERE Sdept='信息学院');



4. 查询选修了全部课程的学生姓名(输出列为:SNAME)

SELECT Sname
FROM Student
WHERE NOT EXISTS(
					SELECT *
          FROM Course
          WHERE NOT EXISTS(
								SELECT *
                FROM SC
                WHERE Sno= Student.Sno
                AND Cno= Course.Cno));

截屏2020-04-25 下午8.33.05

EXISTS如果子查询结果不为空,当前行被选择

NOT EXISTS如果子查询结果为空,当前行被选择



5. 查询选了经济控制论的学生中,成绩比名字为赵古韵的学生好的学生信息(输出列为STUDENT表全部列)

SELECT *
FROM Student
WHERE Sno IN(SELECT Sno 
							FROM SC JOIN Course C ON SC.Cno=C.Cno 
							WHERE Cname='经济控制论' AND Grade>(SELECT Grade 
																								FROM SC JOIN Student S ON SC.Sno=S.Sno JOIN Course C ON C.Cno=SC.Cno
																								WHERE Sname='赵古韵' AND Cname='经济控制论'));



6. 统计每个学生所选课程的平均成绩(输出列为:SNAME,AVGSCORE)

SELECT Sname SNAME,AVG(Grade) AVGSCORE
FROM SC JOIN Student S ON SC.Sno=S.Sno
GROUP BY Sname;



·7. 查询信息学院年龄在21岁以下的男生每个人所修课程的总学分,并按总学分进行升序排序(输出列为学号SNO、总学分CREDITSUM)

下面做法判错

SELECT SC.Sno SNO,SUM(Ccredit) CREDITSUM
FROM SC JOIN Student S ON SC.Sno=S.Sno JOIN Course C ON C.Cno=SC.Cno
WHERE Sdept='信息学院' AND Sage<21 AND Ssex='男' 
GROUP BY SC.Sno
ORDER BY CREDITSUM;



8. 查询各门课程的最高成绩的学生的姓名及其成绩(输出列为CNO,SNAME,GRADE)

SELECT SC.Cno CNO,S.Sname SNAME,SC.grade GRADE
FROM SC, student S,
(select Cno, MAX(grade) grade FROM SC GROUP BY CNO) t1
WHERE SC.Sno = S.Sno
AND SC.Cno = t1.Cno
AND SC.grade = t1.grade;



9. 查询选修了2012001001号学生所选修的全部课程的学生的姓名(输出列SNAME)

SELECT Sname SNAME 
FROM Student x 
WHERE NOT EXISTS(
						SELECT* 
						FROM SC y
						WHERE Sno = '2012001001' AND 
						NOT EXISTS(
								SELECT * 
								FROM SC z 
								WHERE z.Sno = x.Sno AND 
								z.Cno = y.Cno ));



实验三

写出创建Student、Course和SC表的SQL语句,并建立数据库

1、Student:

Sno 学号 字符串 长度7 主键

Sname 姓名 字符串 长度10 非空

Ssex 性别 字符串 长度2

Sage 年龄 整数

Sdept 系 字符串 长度20

2、Course:

Cno 课程号 字符串 长度6

Cname 课程名 字符串 长度20 主键

Credit 学分 整数

Semster 学期 整数

3、SC:

Sno 学号 字符串 长度7 主键,引用student外码

Cno 课程号 字符串 长度6 主键,引用Course外码

Grade 成绩 实数

CREATE TABLE Student(
	Sno CHAR(7) PRIMARY KEY,
	Sname CHAR(10) NOT NULL,
	Ssex CHAR(2),
	Sage INTEGER,
	Sdept CHAR(20)
);

CREATE TABLE Course(
	Cno CHAR(6),
	Cname CHAR(20),	
	Credit INTEGER,
	Semster INTEGER,
	PRIMARY KEY(Cname)
);

CREATE TABLE SC(
	Sno CHAR(7),
	Cno CHAR(6),
	Grade DOUBLE,
	PRIMARY KEY(Sno,Cno),
	FOREIGN KEY(Sno) REFERENCES TEST.Student(Sno),
	FOREIGN KEY(Cno) REFERENCES TEST.Course(Cno)
);

截屏2020-04-24 下午4.45.39

截屏2020-04-24 下午4.46.35

截屏2020-04-24 下午4.49.59



实验四

上文的Student、Course和SC表结构实现如下操作。

1、 查询学生选课表中的全部数据。

SELECT * FROM SC;

截屏2020-04-24 下午4.53.06

2、 查询计算机系的学生的姓名、年龄。

SELECT Sname,Sage
FROM Student
WHERE Sdept='CS';

截屏2020-04-24 下午5.01.13

3、 查询成绩在70-80分之间的学生的学号、课程名和成绩。

SELECT Sno,Cname,Grade
FROM Course C,SC S
WHERE C.Cno=S.Cno AND Grade BETWEEN 70 AND 80;

截屏2020-04-24 下午5.09.56

4、 查询计算机系年龄在18-20,包括18、20,之间且性别为“男”的学生的姓名、年龄。

SELECT Sname,Sage
FROM	Student
WHERE Sdept='CS' AND Ssex='男' AND Sage BETWEEN 18 AND 20;

5、 查询课程号为“c01”的课程的最高的分数。

SELECT MAX(Grade)
FROM SC
WHERE Cno='c01';

截屏2020-04-24 下午5.21.06

6、 查询计算机系学生的最大年龄和最小年龄。

SELECT MAX(Sage),MIN(Sage)
FROM Student
WHERE Sdept='CS';

截屏2020-04-24 下午5.31.04

7、 统计每个系的学生人数。

SELECT Sdept,COUNT(Sno)
FROM Student
GROUP BY Sdept;

截屏2020-04-24 下午5.36.12

8、 统计每门课程的选课人数和考试最高分。

SELECT Cno,COUNT(Sno),MAX(Grade)
FROM SC
GROUP BY Cno;

截屏2020-04-24 下午5.40.51

9、 统计每个学生的选课门数和考试总成绩,并按选课门数升序显示结果。

SELECT Sno,COUNT(Cno) num,SUM(Grade)
FROM SC
GROUP BY Sno
ORDER BY num;

截屏2020-04-24 下午5.50.15

10、 查询总成绩超过100分的学生,要求列出学号、总成绩。

SELECT Sno,SUM(Grade) SumGrade
FROM SC
GROUP BY Sno
HAVING SumGrade>100;

截屏2020-04-24 下午6.21.20

WHERE子句与HAVING 短语的区别在于作用对象不同。WHERE子句作用于基本表或视图,从中选择满足条件的元组。HAVING短语作用于组,从中选择满足条件的组。

[例3.48] 查询平均成绩大于等于90 分的学生学号和平均成绩。

下面的语句是不对的:

SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90
GROUP BY Sno;

因WHERE子句中是不能用聚集函数作为条件表达式的,正确的查询语句应该是:

SELECT Sno,AVG(Grade)
FROM SC
GROUPBYSno
HAVING AVG(Grade)>=90;

11、 查询选修了“c02”号课程的学生的姓名和所在系。

SELECT Sname,Sdept
FROM Student S JOIN SC ON SC.Sno=S.Sno
WHERE Cno='c02';

截屏2020-04-24 下午6.27.26

12、 查询成绩80分以上的学生的姓名、课程名和成绩,并按成绩降序排列结果。

SELECT Sname,Cname,Grade
FROM Student,Course,SC
WHERE SC.Sno=Student.Sno AND SC.Cno=Course.Cno AND Grade>80
ORDER BY Grade DESC;

截屏2020-04-24 下午6.51.27

13、 查询哪些学生没有选课,要求列出学号、姓名和所在系。

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sno NOT IN(SELECT Sno FROM SC);

截屏2020-04-24 下午8.23.45

14、 查询与VB在同一学期开设的课程的课程名和开课学期。

SELECT Cname,Semster
FROM Course
WHERE Semster IN(SELECT Semster FROM Course WHERE Cname='VB');

截屏2020-04-24 下午8.36.05

SELECT C1.Cname,C1.Semster
FROM Course C1,Course C2
WHERE C1.Semster=C2.Semster AND C2.Cname='VB';
#注意Cname和Semster全都要加C1、C2

15、 查询查询与李勇年龄相同的学生的姓名、所在系和年龄。

SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage IN(SELECT Sage FROM Student WHERE Sname='李勇');

截屏2020-04-24 下午8.50.32

16、 用子查询实现如下查询:

(1) 查询选修了“c01”号课程的学生的姓名和所在系。

SELECT Sname,Sdept
FROM Student
WHERE Sno IN(SELECT Sno FROM SC WHERE Cno='c01');

截屏2020-04-24 下午9.01.53

(2) 查询数学系MA成绩80分以上的学生的学号、姓名、课程号、成绩。

SELECT Student.Sno,Sname,Cno,Grade
FROM Student JOIN SC ON Student.Sno=SC.Sno
WHERE Sdept='MA' AND Student.Sno IN(SELECT Sno FROM SC WHERE Grade>80);

截屏2020-04-25 上午10.21.12

A JION B ON 条件 结果A在前,B在后加入

SELECT *
FROM SC JOIN  Student ON Student.Sno=SC.Sno;

截屏2020-04-25 上午10.21.59

(3) 查询计算机系考试成绩最高的学生的姓名。

SELECT Sname
FROM SC JOIN Student S ON SC.Sno=S.Sno
WHERE Sdept='CS' AND Grade>=ALL(SELECT Grade
																FROM SC JOIN Student S ON SC.Sno=S.Sno
																WHERE Sdept='CS');

截屏2020-04-25 上午10.47.46

(4) 查询VB考试成绩最高的学生的姓名、所在系、性别和成绩。

SELECT Sname,Sdept,Ssex,Grade
FROM SC JOIN Student S ON Sc.Sno=S.Sno JOIN Course C ON SC.Cno=C.Cno
WHERE Cname='VB' AND Grade>=ALL(SELECT Grade
																FROM SC JOIN Student S ON Sc.Sno=S.Sno JOIN Course C ON SC.Cno=C.Cno
																WHERE Cname='VB');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FUP8YP9J-1587826816289)(/Users/xcsxchen/Library/Application Support/typora-user-images/截屏2020-04-25 上午10.55.00.png)]

17、 查询没有选修VB课程的学生的姓名和所在系。

SELECT Sname,Sdept
FROM SC JOIN Student S ON SC.Sno=S.Sno JOIN Course C ON SC.Cno=C.Cno
WHERE Cname<>'VB';

截屏2020-04-25 上午11.02.53

18、 查询计算机系没有选课的学生的姓名和性别。

SELECT Sname,Ssex
FROM Student
WHERE Sdept='CS' AND Sno NOT IN(SELECT Sno FROM SC);

截屏2020-04-25 上午11.11.00

19、 创建一个新表,表名为test_t,其结构为:(COL1,COL2,COL3),其中:

​ COL1:整型,允许空值。

​ COL2:字符型,长度为10,不允许空值。

​ COL3:字符型,长度为10,允许空值。

​ 试写出按行插入如下数据的语句(空白处表示空值)。

COL1 COL2 COL2
B1
1 B2 C2
2 B3
CREATE TABLE test_t(COL1 INTEGER,COL2 char(10) NOT NULL,COL3 char(10));
INSERT INTO test_t(COL2) VALUES('B1');
INSERT INTO test_t VALUES(1,'B1','C2');
INSERT INTO test_t VALUES(2,'B3',NULL);

截屏2020-04-25 上午11.22.44

20、 删除考试成绩低于50分的学生的选课记录。

DELETE
FROM SC
WHERE Grade<50;

截屏2020-04-25 上午11.26.58

21、 删除没有人选的课程记录。

DELETE
FROM Course
WHERE Cno NOT IN(SELECT Cno FROM SC);

22、 删除计算机系VB成绩不及格学生的VB选课记录。

DELETE
FROM SC
WHERE Sno IN(SELECT Sno FROM Student WHERE Sdept='CS') AND 
			Cno IN(SELECT Cno FROM Course WHERE Cname='VB') AND 
			Grade<60;

23、 将第2学期开设的所有课程的学分增加2分。

UPDATE Course SET Credit=Credit+2
WHERE Semster=2;

24、 将计算机系学生的年龄增加1岁。

UPDATE Student SET Sage=Sage+1
WHERE Sdept='CS';

25、 将计算机系学生的“计算机文化学”课程的考试成绩加5分。

UPDATE SC SET Grade=Grade+5
WHERE Sno IN(SELECT Sno FROM Student WHERE Sdept='CS') AND
			Cno IN(SELECT Cno FROM Course WHERE Cname='计算机文化学');



版权声明:本文为xcsxchen原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。