学生成绩管理系统复杂数据查询

情境8 学生成绩管理系统复杂数据查询

一、工作目的

1．掌握在一个数据表中实现数据的简单查询

2．掌握同时在多个数据表中实现数据的复合查询

3．掌握实现分组查询

4．掌握对查询结果集排序

5．掌握利用库函数进行数据统计

二、工作任务

任务一、连接查询

1、内连接

查询成绩表（Grade）和课程表（Course）中所有学生的成绩及课程信息。

SELECT sNo,o,ame,grade FROM Grade

INNER JOIN Course ON o= o

2、外连接

从学生表(Student)、成绩表(Grade)和课程表（Course）中查询学生的学号(sNo)、姓名(sName)、课程名(cName)和成绩(grade)（包括没有选课的同学）。

SELECT xs.sNo, sName, ame, grade FROM Student xs

LEFT JOIN Grade cj ON cj.sNo = xs.sNo

LEFT JOIN Course kc ON o = o

从学生表(Student)、成绩表(Grade)和课程表（Course）中查询学生的学号(sNo)、姓名(sName)、课程名(cName)和成绩(grade)（包括还没有讲过的新课）。

SELECT xs.sNo, sName, ame, grade

FROM Grade cj INNER JOIN Student xs ON cj.sNo = xs.sNo

RIGHT JOIN Course kc ON o = o

从学生表(Student)、教师表(Teacher)中查询同姓的教师和学生的姓名。

SELECT LEFT(XS.SNAME,1) 姓氏,XS.SNAME 学生姓名,JS.TNAME 教师姓名

FROM TEACHER JS

FULL JOIN STUDENT XS ON LEFT(JS.TNAME,1) = LEFT(XS.SNAME,1)

3、自连接

查询成绩表(Grade)中与学号(sNo)为“082034101”的学生所学的课程相同的学生的学号(sNo)、课程号(cNo)、成绩(grade)。

SELECT cj1.sNo,cj2.sNo,o,cj1.grade FROM Grade cj1, Grade cj2

WHERE o=o AND cj1.sNo<>’082034101’ AND cj2.sNo=’082034101’

在学生表(Student)中查询年龄相差2岁的每一对学生的学号(sNo)、出生日期(sBirthday) 。SELECT xs1.sNo,xs1.sBirthday,xs2.sNo,xs2.sBirthday

FROM Student xs1,Student xs2

WHERE YEAR(xs1.sBirthday)=YEAR(xs2.sBirthday)+2

任务二、嵌套查询

查询哪些课程(cName)被学号(sNo)为“082034101”的学生所学习。

SELECT cName FROM Course WHERE cNo IN

（SELECT cNo FROM Grade WHERE sNo=’082034101’)

查询所有成绩(grade)都及格的学生信息。

SELECT * FROM Student

WHERE sNo NOT IN

(SELECT sNo FROM Grade WHERE grade<60)

AND sNo IN (SELECT sNo FROM Grade)

找出至少学习一门有前导课程的学生的学号(sNo)、姓名(sName)。

SELECT sNo, sName FROM Student WHERE sNo IN

(SELECT sNo FROM Grade WHERE cNo IN

(SELECT cNo FROM Course WHERE cPcno IS NOT NULL))

查询与课程号(cNo)“1203”的前导课程(cPcno)相同的其它课程的课程号(cNo)、课程名(cName)。SELECT cNo,cName FROM Course WHERE cPcno=

(SELECT cPcno FROM Course WHERE cNo=’1203’)

AND cNo<>’1203’

查询年龄比“杜鹃”大的学生的学号(sNo)、课程号(cNo)、成绩(grade)。

SELECT * FROM Grade WHERE sNo IN

(SELECT sNo FROM Student WHERE sBirthday<

(SELECT sBirth day FROM Student WHERE sName=’杜鹃’))

查询有学生的成绩(grade)大于或等于“0103”课程号(cNo)中的任何一位学生成绩的学生学号SELECT DISTINCT sNo FROM Grade WHERE grade>=ANY

(SELECT grade FROM Grade WHERE cNo=’0103’)

查询有学生的成绩(grade)大于“0103”课程号(cNo)中的所有学生成绩的学生学号(sNo)。SELECT DISTINCT sNo FROM Grade WHERE grade>ALL

(SELECT grade FROM Grade WHERE cNo=’0103’)

查询选修了“1209”课程的学生姓名。

SELECT sName FROM Student

WHERE EXISTS (SELECT * FROM Grade

WHERE Student.sNo=Grade.sNo AND cNo=’1209’)

查询没有选修“1209”课程的学生姓名。

SELECT sName FROM Student

WHERE NOT EXISTS (SELECT * FROM Grade

WHERE Student.sNo=Grade.sNo AND cNo=’1209’)

查询选修了全部课程的学生的姓名。

SELECT sName FROM Student

WHERE NOT EXISTS (SELECT * FROM Course

WHERE NOT EXISTS (SELECT * FROM Grade

WHERE Student.sNo=Grade.sNo

AND o=o)

任务三、集合查询

查询至少有一门成绩(grade)及格或性别(sSex)为“男”的学生信息。

（SELECT * FROM Student WHERE sSex=’男’）UNION

(SELECT * FROM Student WHERE EXISTS

(SELECT * FROM Grade WHERE

Student.sNo=Grade.sNo AND grade>60))

查询成绩(grade)及格并且性别(sSex)为“男”的学生信息。

(SELECT * FROM Student WHERE sSex=’男’) INTERS ECT