数据库 实验三 数据库的复杂查询（一）

实验内容

（一）连接查询

1．查询比“王敏”年纪大的男学生信息。

连接查询SQL语句：

SELECT S.* FROM Student S

JOIN SC ON S.Sno = SC.Sno

WHERE S.Sage > (SELECT Sage FROM Student WHERE Sname = '王敏') AND S.Ssex = '男';

2．检索所有学生的选课信息。（提示：使用外连接）

连接查询SQL语句：

SELECT student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM student LEFT OUTER JOIN sc ON(student.Sno = sc.Sno);

3．查询已选课学生的学号、姓名、课程名、成绩。

连接查询SQL语句：

SELECT sc.Sno,Sname,Cno,Grade FROM sc LEFT OUTER JOIN student ON(student.Sno = sc.Sno);

4．查询选修了“信息系统”的学生的学号和姓名。

连接查询SQL语句：

SELECT student.Sno,Sname FROM Course,SC,Student WHERE student.Sno = sc.Sno AND sc.Cno = course.Cno AND course.Cname = '信息系统';

5．查询与“刘晨”在同一个系的学生学号、姓名、性别。

连接查询SQL语句：

SELECT Sno,Sname,Ssex FROM student WHERE Sdept IN(SELECT Sdept FROM student WHERE Sname = '刘晨');

6．检索学生的学号、姓名、学习课程名及课程成绩。

连接查询SQL语句：

SELECT student.Sno, Sname, Cname, Grade

FROM student,sc,course

WHERE student.Sno=sc.Sno AND sc.Cno=course.Cno;

7．检索选修3门以上课程的学生的学号、总成绩。

SQL语句：

SELECT Sno,sum(Grade) FROM SC GROUP BY Sno HAVING COUNT(Sno) > 3;

8．检索多于2名学生选修的课程号及平均成绩。

SQL语句：

SELECT Cno,AVG(Grade) FROM SC GROUP BY Cno HAVING COUNT(Cno) > 2;

9.检索选修表(SC表)中课程成绩为空的学生的姓名、课程名称及学分。

SQL语句：

SELECT student.Sname, course.Cname, course.Ccredit FROM Student

JOIN SC ON student.Sno = SC.Sno

JOIN Course ON SC.Cno = course.Cno

WHERE SC.Grade IS NULL;

（二）流程控制语句

1．如果Student表中有20岁的学生，把该学生的学号，姓名和性别查询出来，否则输出“没有20岁的学生”。写出SQL语句：（使用if...else语句）

DECLARE @Sno INT, @Sname VARCHAR(50), @Ssex VARCHAR(10)

IF EXISTS(SELECT * FROM Student WHERE Sage = 20)

BEGIN

    SELECT @Sno = Sno, @Sname = Sname, @Ssex = Ssex FROM Student WHERE Sage = 20

    SELECT @Sno AS 学号, @Sname AS 姓名, @Ssex AS 性别

END

ELSE

BEGIN

    SELECT '没有20岁的学生' AS 提示信息

END

2．使用While语句求1到100之间的累加和，输出结果。写出SQL语句：

DECLARE @Sum INT = 0, @Number INT = 1

WHILE @Number <= 100

BEGIN

    SET @Sum = @Sum + @Number

    SET @Number = @Number + 1

END

SELECT @Sum AS 累加和

（三）用户自定义函数的应用

定义一个用户自定义函数Score_Rechange,将成绩从百分制转化为五级记分制。将该用户定义函数用在查询每个学生的成绩中，给出五级记分制的成绩。写出SQL语句：

CREATE FUNCTION Score_Rechange(@Score INT)

RETURNS VARCHAR(10)

AS

BEGIN

    DECLARE @Grade VARCHAR(10)

    IF @Score >= 90

        SET @Grade = 'A'

    ELSE IF @Score >= 80

        SET @Grade = 'B'

    ELSE IF @Score >= 70

        SET @Grade = 'C'

    ELSE IF @Score >= 60

        SET @Grade = 'D'

    ELSE

        SET @Grade = 'E'

    

    RETURN @Grade

END