SQL Server较经典练习题及详解

1.对于教学数据库的三个基本表
S(S#,SNAME,AGE,SEX)
SC(S#,C#,GRADE)
C(C#,CNAME,TEACHER)
试用SQL的查询语句表达下列查询：
（1）检索WANG同学不学的课程的课程号。
SELECT C#
FROM C
WHERE NOT EXISTS
(SELECT *
FROM S,SC
WHERE S.S#=SC.S# AND SC.C#=C.C# AND SNAME=‘WANG’);
（2）检索至少选修两门课程的学生学号。
SELECT DISTINCT X.S#
FROM SC AS X, SC AS Y
WHERE X.S#=Y.S# AND X.C#!=Y.C#;
（3）检索全部学生都选修的课程的课程号与课程名。
SELECT C#,CNAME
FROM C
WHERE NOT EXISTS
(SELECT *
FROM S
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE S#=S.S# AND C#=C.C#));
（4）检索选修课程包含LIU老师所授课程的学生学号。
SELECT DISTINCT S#
FROM SC AS X
WHERE NOT EXISTS
(SELECT *
FROM C
WHERE TNAME=‘LIU’ AND NOT EXISTS
(SELECT *
FROM SC AS Y
WHERE Y.S#=X.S# AND Y.C#=C.C#));
2.试用SQL查询语句表达下列查询
对于教学数据库的三个基本表
S(S#,SNAME,AGE,SEX)
SC(S#,C#,GRADE)
C(C#,CNAME,TEACHER)
（1）在表C中统计开设课程的教师人数。
SELECT COUNT(DISTINCT TEACHER)
FROM C;
（2）求选修C4课程的女学生的平均年龄。
SELECT AVG(AGE)
FROM S,SC
WHERE S.S#=SC.S# AND C#=‘C4’ AND SEX=‘F’;
（3）求LIU老师所授课程的每门课程的平均成绩。
SELECT C.C#,AVG(GRADE)
FROM SC,C
WHERE SC.C#=C.C# AND TEACHER=‘LIU’
GROUP BY C.C#;
（4）统计每个学生选修课程的门数（超过5门的学生才统计）。要求输出学生学号和选修门数，查询结果按门数降序排列，若门数相同，按学号升序排列。
SELECT S#,COUNT(C#)
FROM SC
GROUP BY S#
HAVING COUNT()>5
ORDER BY 2 DESC,1;
（5）检索学号比WANG同学大，而年龄比他小的学生姓名。
SELECT SNAME
FROM S
WHERE S#>ALL(SELECT S#
FROM S
WHERE SNAME=‘WANG’)
AND AGE<ALL(SELECT S#
FROM S
WHERE SNAME=‘WANG’);
（6）在表SC中检索成绩为空值的学生学号和课程号。
SELECT S#,C#
FROM SC
WHERE GRADE IS NULL;
（7）检索姓名以L打头的所有学生的姓名和年龄。
SELECT SNAME,AGE
FROM S
WHERE SNAME LIKE ‘L%’;
（8）求年龄大于女同学平均年龄的男学生姓名和年龄。
SELECT SNAME,AGE
FROM S
WHERE SEX=‘M’
AND AGE>(SELECT AVG(AGE)
FROM S
WHERE SEX=‘F’);
（9）求年龄大于所有女同学年龄的男学生姓名和年龄。
SELECT SNAME,AGE
FROM S
WHERE SEXX=‘M’
AND AGE>ALL(SELECT AGE
FROM S
WHERE SEX=‘F’);
3.对于教学数据库的三个基本表
S（S#,SNAME,AGE,SEX）
SC（S#,C#,GRADE）
C（C#,CNAME,TEACHER）
（1）往关系C中插一个课程元组（‘C8’,‘VC++’,‘BAO’）。
INSERT
INTO C
VALUES(‘C8’,‘VC++’,‘BAO’);
（2）在SC中删除尚无成绩的选课元组。
DELETE
FROM SC
WHERE GRADE IS NULL;
（3）把选修LIU老师课程的女同学选课元组全部删去。
DELETE
FROM SC
WHERE S# IN(SELECT S#
FROM S
WHERE SEX=‘F’)
AND C# IN(SELECT C#
FROM C
WHERE TEACHER=‘LIU’);
（4）把MATHS课不及格的成绩全改为60分。
UPDATE SC
SET GRADE=60
WHERE GRADE<60
AND C# IN(SELECT C#
FROM C
WHERE CNAME=‘MATHS’);
（5）把低于所有课程总平均成绩的女同学成绩提高5%。
UPDATE SC
SET GRADE=GRADE1.05
WHERE S# IN(SELECT S#
FROM S
WHERE SEX=‘F’)
AND GRADE<(SELECT AVG(GRADE)
FROM SC)
（6）在表SC中修改C4课程的成绩，若成绩小于等于70分时提高5%，若成绩大于70分时提高4%（用两种方法实现，一种方法是用两个UPDATE语句实现，另一种方法是用带CASE操作的一个UPDATE语句实现）。
UPDATE SC
SET GRADE=GRADE1.04
WHERE C#=‘C4’ AND GRADE>70;
UPDATE SC
SET GRADE=GRADE1.05
WHERE C#=‘C4’ AND GRADE<=70;
（这两个UPDATE语句顺序不能颠倒）
用一个UPDATE语句实现：
UPDATE SC
SET GRADE=GRADECASE
WHEN GRADE>70 THEN 1.04
ELSE 1.05
END
WHERE C#=‘C4’;
（7）在表SC中，当某个成绩低于全部课程的平均成绩时，提高5%。
UPDATE SC
SET GRADE=GRADE1.05
WHERE GRADE<(SELECT AVG(GRADE)
FROM SC);