数据库 实验二: 多表查询

实验目的：

１.掌握连接查询的一般格式。

２.熟练掌握连接查询的各种方法。

实验内容：

一、用数据导入的方式生成jxgl数据库中三张表：

● 学生表Student，由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，

记作：Student(Sno,Sname,Ssex,Sage,Sdept)

Student:

Sno	Sname	Sage	Ssex	Sdept
98001	钱横	18	男	CS
98002	王林	19	女	CS
98003	李民	20	男	IS
98004	赵三	16	女	MA
98005	欧阳勇	19	男	MA
98019	李四	18	男	IS

● 课程表Course，由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Ccredit)四个属性组成，记作：Course(Cno,Cname,Cpno,Ccredit)

Course:

Cno	Cname	Cpno	Ccredit
1	数据库系统	5	4
2	数学分析	NULL	2
3	信息系统导论	1	3
4	操作系统原理	6	3
5	数据结构	7	4
6	数据处理基础	NULL	4
7	C语言	6	3

● 学生SC，由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，记作：Sc(Sno,Cno,Grade)

Sc:

Sno	Cno	Grade
98001	1	87
98001	2	67
98001	3	90
98002	2	95
98002	3	88
98004	2	Null

---

实验内容：

对JXGL数据库进行查询。

１.查询钱横同学选修的课程号和分数。

SELECT cno,grade
FROM student,sc
WHERE student.sno=sc.sno 
AND sname='钱横'

2.查询选修的课程成绩为90分以上的学生姓名与课程名和成绩。

SELECT sname,cname,grade
FROM sc,student,course
WHERE student.sno=sc.sno 
AND course.cno=sc.cno
AND grade>90

3.查询选修2号课程且成绩在90分以上的所有学生的学号、姓名。

SELECT sname,student.sno
FROM student,sc
WHERE student.sno=sc.sno
AND cno=2
AND grade>90

4.查询每个学生的学号、姓名、选修的课程名及成绩。

SELECT student.sno,sname,sc.cno,cname,grade
FROM student,sc,course
WHERE student.sno=sc.sno
AND sc.cno=course.cno
ORDER BY sno asc

5.查询性别为男、课程成绩及格的学生学号,姓名,课程号,成绩。

SELECT sc.sno,sname,cno,grade
FROM student,sc
WHERE student.sno=sc.sno
AND ssex='男'
AND grade>=60

6.查询平均成绩大于85分的学号、姓名、平均成绩。

SELECT student.sno,sname,avg(grade)
FROM student,sc
WHERE student.sno=sc.sno
GROUP BY student.sno,sname
HAVING AVG(grade)>85

7.查询选修了数据库系统的学生的姓名及成绩。

SELECT sname,grade
FROM student,sc,course
WHERE student.sno=sc.sno
AND course.cno=sc.cno
AND cname='数据库系统'

8.查询每个系的学生选修的1号课程的平均成绩，最高分，最低分，选课人数。

SELECT sdept,AVG(grade) 平均成绩,MAX(grade) 最高分,
MIN(grade) 最低分,COUNT(sc.sno) 选课人数
FROM sc,student
WHERE student.sno=sc.sno
AND cno=2
GROUP BY sdept 

10.查询同时选修了“1”课程和“2”课程的学生的姓名。（自身连接查询）

SELECT DISTINCT student.sname
FROM sc JOIN student
ON student.sno=sc.sno
WHERE sc.cno=1 
AND student.sno in
(SELECT sc.sno
FROM sc
WHERE sc.cno=2)

11.查询未被选修的课程的课程名和学分。

SELECT cname,ccredit
FROM course
WHERE cno NOT IN
    (SELECT DISTINCT cno
    FROM sc)

12.查询“CS”系学生的选课信息。

SELECT *
FROM sc
WHERE sno IN
    (SELECT sno
    FROM student
    WHERE sdept='cs')

13.查询不选1号课程的学生名字。

SELECT sname 
FROM student 
WHERE Sno NOT IN 
    (SELECT Sno 
    from sc 
    where Cno=1)

14.查询至少被2个学生选修的课程的课程名。

SELECT cname 
FROM course 
WHERE cno IN
(SELECT cno 
FROM sc 
GROUP BY cno 
HAVING COUNT(cno)>=2)

15.查询计算机系（CS）选修了2门及以上课程的学生的学号。

SELECT sno
FROM student
WHERE sno IN
    (SELECT sno
    FROM sc
    GROUP BY sno
    HAVING COUNT(cno)>=2)
AND sdept ='cs'

16.查询与“钱横”在同一个系学习的学生信息。

SELECT *
FROM student
WHERE sdept IN 
    (SELECT sdept
    FROM student
    WHERE sname='钱横')

17.查询选修了课程名为“数学分析”的学生学号、姓名和所在系。

SELECT sno,sname,sdept
FROM student
WHERE sno IN
    (SELECT sno
    FROM sc
    WHERE cno IN
        (SELECT cno
        FROM course
        WHERE cname='数学分析')
        )

18.查询同时选修了“1”课程和“2”课程的学生的姓名。

SELECT DISTINCT student.sname
FROM sc JOIN student
ON student.sno=sc.sno
WHERE sc.cno=1 
AND student.sno IN
    (SELECT sc.sno
    FROM sc
    WHERE sc.cno=2)

19.查询至少选修了3门课程的学生的姓名。

SELECT sno
FROM student
WHERE sno IN
    (SELECT sno
    FROM sc
    GROUP BY sno
    HAVING COUNT(cno)>=3)

20.查询平均成绩在80分以上的学生的姓名。

SELECT sname 
FROM student
WHERE sno IN
    (SELECT sno
    FROM sc
    GROUP BY sno
    HAVING AVG(grade)>=80)     

---

技术小白记录学习过程，有错误或不解的地方请指出，如果这篇文章对你有所帮助，请点点赞收藏+关注 谢谢支持 !!!