sql练习题
问题描述： 为管理岗位业务培训信息，建立3个表:
S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (C#,CN ) C#,CN 分别代表课程编号、课程名称
SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩
要求实现如下5个处理：
1.使用标准 SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名 --实现代码：
SELECT
SN,
SD
FROM
S
WHERE
[S#] IN ( SELECT [S#] FROM C, SC WHERE C.[C#] = SC.[C#] AND CN = N'税收基础' ) 2.使用标准 SQL嵌套语句查询选修课程编号为’ C2’的学员姓名和所属单位 --实现代码：
SELECT
S.SN,
S.SD
FROM
S,
SC
WHERE
S.[S#] = SC.[S#]
AND SC.[C#] = 'C2' 3.使用标准 SQL嵌套语句查询不选修课程编号为’ C5’的学员姓名和所属单位 --实现代码：
SELECT
SN,
SD
FROM
S
WHERE
[S#] NOT IN ( SELECT [S#] FROM SC WHERE [C#] = 'C5' ) 4.使用标准 SQL嵌套语句查询选修全部课程的学员姓名和所属单位 --实现代码：
SELECT
SN,
SD
FROM
S
WHERE
[S#] IN (
SELECT
[S#]
FROM
SC
RIGHT JOIN C ON SC.[C#] = C.[C#]
GROUP BY
[S#]
HAVING
COUNT ( * ) = COUNT ( DISTINCT [S#] )) 5.查询选修了课程的学员人数 --实现代码：
SELECT
学员人数= COUNT ( DISTINCT [S#] )
FROM
SC 6.查询选修课程超过 5门的学员学号和所属单位 --实现代码：
SELECT
SN,
SD
FROM
S
WHERE
[S#] IN (
SELECT
[S#]
FROM
SC
GROUP BY
[S#]
HAVING
COUNT ( DISTINCT [C#] ) > 5)
问题描述：
本题用到下面三个关系表：
CARD 借书卡。 CNO 卡号，NAME 姓名，CLASS 班级
BOOKS 图书。 BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数
BORROW 借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书日期
备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。
要求实现如下15个处理：
1.写出建立 BORROW表的 SQL语句，要求定义主码完整性约束和引用完整性约束 --实现代码：
CREATE TABLE BORROW (
CNO INT FOREIGN KEY REFERENCES CARD ( CNO ),
BNO INT FOREIGN KEY REFERENCES BOOKS ( BNO ),
RDATE datetime,
PRIMARY KEY ( CNO, BNO )) 2.找出借书超过 5本的读者,输出借书卡号及所借图书册数 --实现代码：
SELECT
CNO,借图书册数= COUNT ( * )
FROM
BORROW
GROUP BY
CNO
HAVING
COUNT ( * ) > 5 3.查询借阅了 "水浒"一书的读者，输出姓名及班级 --实现代码：
SELECT
*
FROM
CARD c
WHERE
EXISTS (
SELECT
*
FROM
BORROW a,
BOOKS b
WHERE
a.BNO= b.BNO
AND b.BNAME= N'水浒'
AND a.CNO= c.CNO
) 4.查询过期未还图书，输出借阅者（卡号）、书号及还书日期 --实现代码：
SELECT
*
FROM
BORROW
WHERE
RDATE < GETDATE() 5.查询书名包括 "网络"关键词的图书，输出书号、书名、作者 --实现代码：
SELECT
BNO,
BNAME,
AUTHOR
FROM
BOOKS
WHERE
BNAME LIKE N'%网络%' 6.查询现有图书中价格最高的图书，输出书名及作者 --实现代码：
SELECT
BNO,
BNAME,
AUTHOR
FROM
BOOKS
WHERE
PRICE = ( SELECT MAX ( PRICE ) FROM BOOKS ) 7.查询当前借了 "计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出 --实现代码：
SELECT
a.CNO
FROM
BORROW a,
BOOKS b
WHERE
a.BNO= b.BNO
AND b.BNAME= N'计算方法'
AND NOT EXISTS (
SELECT
*
FROM
BORROW aa,
BOOKS bb
WHERE
aa.BNO= bb.BNO
AND bb.BNAME= N'计算方法习题集'
AND aa.CNO= a.CNO
)
ORDER BY
a.CNO DESC 8.将 "C01"班同学所借图书的还期都延长一周 --实现代码：
UPDATE b
SET RDATE = DATEADD( DAY, 7, b.RDATE )
FROM
CARD a,
BORROW b
WHERE
a.CNO= b.CNO
AND a.CLASS= N'C01' 9.从 BOOKS表中删除当前无人借阅的图书记录 --实现代码：
DELETE A
FROM
BOOKS a
WHERE
NOT EXISTS ( SELECT * FROM BORROW WHERE BNO = a.BNO ) 10.如果经常按书名查询图书信息，请建立合适的索引 --实现代码：
CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS ( BNAME ) 11.在 BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是 "数据库技术及应用"，就将该读者的借阅记录保存在 BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表） --实现代码：
CREATE TRIGGER TR_SAVE ON BORROW FOR INSERT,
UPDATE AS
IF
@@ROWCOUNT > 0 INSERT BORROW_SAVE SELECT
i.*
FROM
INSERTED i,
BOOKS b
WHERE
i.BNO= b.BNO
AND b.BNAME= N'数据库技术及应用' 12.建立一个视图，显示 "力01"班学生的借书信息（只要求显示姓名和书名） --实现代码：
CREATE VIEW V_VIEW AS SELECT
a.NAME,
b.BNAME
FROM
BORROW ab,
CARD a,
BOOKS b
WHERE
ab.CNO= a.CNO
AND ab.BNO= b.BNO
AND a.CLASS= N'力01' 13.查询当前同时借有 "计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出 --实现代码：
SELECT
a.CNO
FROM
BORROW a,
BOOKS b
WHERE
a.BNO= b.BNO
AND b.BNAME IN ( N'计算方法', N'组合数学' )
GROUP BY
a.CNO
HAVING
COUNT ( * ) = 2
ORDER BY
a.CNO DESC 14.假定在建 BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句 --实现代码：
ALTER TABLE BOOKS ADD PRIMARY KEY ( BNO ) 15.1 将 NAME最大列宽增加到 10个字符（假定原为6个字符） --实现代码：
ALTER TABLE CARD ALTER COLUMN NAME VARCHAR ( 10 ) 15.2 为该表增加 1列 NAME（系名），可变长，最大 20个字符 --实现代码：
ALTER TABLE CARD ADD 系名 VARCHAR (
20)
问题： 1、查询“001”课程比“002”课程成绩高的所有学生的学号； SELECT
a.S#
FROM
( SELECT s #, score FROM SC WHERE C #= '001' ) a,
( SELECT s #, score FROM SC WHERE C #= '002' ) b
WHERE
a.score> b.score
AND a.s#= b.s#;
2、查询平均成绩大于60分的同学的学号和平均成绩； SELECT
S #,
AVG ( score )
FROM
sc
GROUP BY
S #
HAVING
AVG ( score ) > 60;
3、查询所有同学的学号、姓名、选课数、总成绩； SELECT
Student.S#,
Student.Sname,
COUNT ( SC.C# ),
SUM ( score )
FROM
Student
LEFT OUTER JOIN SC ON Student.S#= SC.S#
GROUP BY
Student.S#,
Sname 4、查询姓“李”的老师的个数； SELECT COUNT
(
DISTINCT ( Tname ))
FROM
Teacher
WHERE
Tname LIKE '李%';
5、查询没学过“叶平”老师课的同学的学号、姓名； SELECT
Student.S#,
Student.Sname
FROM
Student
WHERE
S # NOT IN (
SELECT DISTINCT
( SC.S# )
FROM
SC,
Course,
Teacher
WHERE
SC.C#= Course.C#
AND Teacher.T#= Course.T#
AND Teacher.Tname= '叶平'
);
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； SELECT
Student.S#,
Student.Sname
FROM
Student,
SC
WHERE
Student.S#= SC.S#
AND SC.C#= '001'
AND EXISTS ( SELECT * FROM SC AS SC_2 WHERE SC_2.S#= SC.S# AND SC_2.C#= '002' );
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； SELECT
S #,
Sname
FROM
Student
WHERE
S # IN (
SELECT
S #
FROM
SC,
Course,
Teacher
WHERE
SC.C#= Course.C#
AND Teacher.T#= Course.T#
AND Teacher.Tname= '叶平'
GROUP BY
S #
HAVING
COUNT ( SC.C# ) = ( SELECT COUNT ( C # ) FROM Course, Teacher WHERE Teacher.T#= Course.T# AND Tname = '叶平' ));
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名； SELECT
S #,
Sname
FROM
(
SELECT
Student.S#,
Student.Sname,
score,
( SELECT score FROM SC SC_2 WHERE SC_2.S#= Student.S# AND SC_2.C#= '002' ) score2
FROM
Student,
SC
WHERE
Student.S#= SC.S#
AND C #= '001'
) S_2
WHERE
score2 < score;
9、查询所有课程成绩小于60分的同学的学号、姓名； SELECT
S #,
Sname
FROM
Student
WHERE
S # NOT IN ( SELECT S.S# FROM Student AS S, SC WHERE S.S#= SC.S# AND score > 60 );
10、查询没有学全所有课的同学的学号、姓名； SELECT
Student.S#,
Student.Sname
FROM
Student,
SC
WHERE
Student.S#= SC.S#
GROUP BY
Student.S#,
Student.Sname
HAVING
COUNT ( C # ) < ( SELECT COUNT ( C # ) FROM Course );
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名； SELECT DISTINCT
S #,
Sname
FROM
Student,
SC
WHERE
Student.S#= SC.S#
AND SC.C# IN ( SELECT C # FROM SC WHERE S #= '1001' );
12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名； SELECT DISTINCT
SC.S#,
Sname
FROM
Student,
SC
WHERE
Student.S#= SC.S#
AND C # IN ( SELECT C # FROM SC WHERE S #= '001' );
13、把“ SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩； UPDATE SC
SET score = ( SELECT AVG ( SC_2.score ) FROM SC SC_2 WHERE SC_2.C#= SC.C# )
FROM
Course,
Teacher
WHERE
Course.C#= SC.C#
AND Course.T#= Teacher.T#
AND Teacher.Tname= '叶平'
);
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名； SELECT
S #
FROM
SC
WHERE
C # IN ( SELECT C # FROM SC WHERE S #= '1002' )
GROUP BY
S #
HAVING
COUNT ( * ) = ( SELECT COUNT ( * ) FROM SC WHERE S #= '1002' );
15、删除学习“叶平”老师课的 SC表记录； Delect SC
FROM
course,
Teacher
WHERE
Course.C#= SC.C#
AND Course.T#= Teacher.T#
AND Tname = '叶平';
16、向 SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“ 003”课程的同学学号、2、 号课的平均成绩； INSERT SC SELECT
S #,
'002',
( SELECT AVG ( score ) FROM SC WHERE C #= '002' )
FROM
Student
WHERE
S # NOT IN ( SELECT S # FROM SC WHERE C #= '002' );
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生 ID,,数据库,企业管理,英语,有效课程数,有效平均分 SELECT
S # AS 学生 ID,
( SELECT score FROM SC WHERE SC.S#= t.S# AND C #= '004' ) AS 数据库
,
( SELECT score FROM SC WHERE SC.S#= t.S# AND C #= '001' ) AS 企业管理
,
( SELECT score FROM SC WHERE SC.S#= t.S# AND C #= '006' ) AS 英语
,
COUNT ( * ) AS 有效课程数,
AVG ( t.score ) AS 平均成绩
FROM
SC AS t
GROUP BY
S #
ORDER BY
AVG ( t.score ) 18、查询各科成绩最高和最低的分：以如下形式显示：课程 ID，最高分，最低分 SELECT
L.C# AS 课程 ID,
L.score AS 最高分,
R.score AS 最低分
FROM
SC L,
SC AS R
WHERE
L.C# = R.C#
AND L.score = (
SELECT MAX
( IL.score )
FROM
SC AS IL,
Student AS IM
WHERE
L.C# = IL.C#
AND IM.S#= IL.S#
GROUP BY
IL.C#
)
AND R.Score = ( SELECT MIN ( IR.score ) FROM SC AS IR WHERE R.C# = IR.C# GROUP BY IR.C# );
自己写的: SELECT
c # ,
MAX ( score ) AS 最高分 ,
MIN ( score ) AS 最低分
FROM
dbo.sc
GROUP BY
c # 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 SELECT
t.C# AS 课程号,
MAX ( course.Cname ) AS 课程名,
isnull( AVG ( score ), 0 ) AS 平均成绩
,
100 * SUM ( CASE WHEN isnull( score, 0 ) >= 60 THEN 1 ELSE 0 END ) / COUNT ( * ) AS 及格百分数
FROM
SC T,
Course
WHERE
t.C#= course.C#
GROUP BY
t.C#
ORDER BY
100 * SUM ( CASE WHEN isnull( score, 0 ) >= 60 THEN 1 ELSE 0 END ) / COUNT ( * ) DESC 20、查询如下课程平均成绩和及格率的百分数 (用 "1行"显示 ) : 企业管理（ 001），马克思（002）， OO & UML （ 003），数据库（004） SELECT SUM
( CASE WHEN C # = '001' THEN score ELSE 0 END ) / SUM ( CASE C # WHEN '001' THEN 1 ELSE 0 END ) AS 企业管理平均分
,
100 * SUM ( CASE WHEN C # = '001' AND score >= 60 THEN 1 ELSE 0 END ) / SUM ( CASE WHEN C # = '001' THEN 1 ELSE 0 END ) AS 企业管理及格百分数
,
SUM ( CASE WHEN C # = '002' THEN score ELSE 0 END ) / SUM ( CASE C # WHEN '002' THEN 1 ELSE 0 END ) AS 马克思平均分
,
100 * SUM ( CASE WHEN C # = '002' AND score >= 60 THEN 1 ELSE 0 END ) / SUM ( CASE WHEN C # = '002' THEN 1 ELSE 0 END ) AS 马克思及格百分数
,
SUM ( CASE WHEN C # = '003' THEN score ELSE 0 END ) / SUM ( CASE C # WHEN '003' THEN 1 ELSE 0 END ) AS UML平均分,
100 * SUM ( CASE WHEN C # = '003' AND score >= 60 THEN 1 ELSE 0 END ) / SUM ( CASE WHEN C # = '003' THEN 1 ELSE 0 END ) AS UML及格百分数,
SUM ( CASE WHEN C # = '004' THEN score ELSE 0 END ) / SUM ( CASE C # WHEN '004' THEN 1 ELSE 0 END ) AS 数据库平均分
,
100 * SUM ( CASE WHEN C # = '004' AND score >= 60 THEN 1 ELSE 0 END ) / SUM ( CASE WHEN C # = '004' THEN 1 ELSE 0 END ) AS 数据库及格百分数
FROM
SC
21、查询不同老师所教不同课程平均分从高到低显示 SELECT MAX
( Z.T# ) AS 教师 ID,
MAX ( Z.Tname ) AS 教师姓名,
C.C# AS 课程ＩＤ,
MAX ( C.Cname ) AS 课程名称,
AVG ( Score ) AS 平均成绩
FROM
SC AS T,
Course AS C,
Teacher AS Z
WHERE
T.C#= C.C#
AND C.T#= Z.T#
GROUP BY
C.C#
ORDER BY
AVG ( Score ) DESC 22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（ 001），马克思（002）， UML （ 003），数据库（004） [学生ID],
[学生姓名],企业管理,马克思,
UML,数据库,平均成绩 SELECT DISTINCT TOP
3 SC.S# AS 学生学号,
Student.Sname AS 学生姓名 ,
T1.score AS 企业管理,
T2.score AS 马克思,
T3.score AS UML,
T4.score AS 数据库,
ISNULL( T1.score, 0 ) + ISNULL( T2.score, 0 ) + ISNULL( T3.score, 0 ) + ISNULL( T4.score, 0 ) AS 总分
FROM
Student,
SC
LEFT JOIN SC AS T1 ON SC.S# = T1.S#
AND T1.C# = '001'
LEFT JOIN SC AS T2 ON SC.S# = T2.S#
AND T2.C# = '002'
LEFT JOIN SC AS T3 ON SC.S# = T3.S#
AND T3.C# = '003'
LEFT JOIN SC AS T4 ON SC.S# = T4.S#
AND T4.C# = '004'
WHERE
student.S#= SC.S#
AND ISNULL( T1.score, 0 ) + ISNULL( T2.score, 0 ) + ISNULL( T3.score, 0 ) + ISNULL( T4.score, 0 ) NOT IN (
SELECT DISTINCT TOP
15 WITH TIES ISNULL( T1.score, 0 ) + ISNULL( T2.score, 0 ) + ISNULL( T3.score, 0 ) + ISNULL( T4.score, 0 )
FROM
sc
LEFT JOIN sc AS T1 ON sc.S# = T1.S#
AND T1.C# = 'k1'
LEFT JOIN sc AS T2 ON sc.S# = T2.S#
AND T2.C# = 'k2'
LEFT JOIN sc AS T3 ON sc.S# = T3.S#
AND T3.C# = 'k3'
LEFT JOIN sc AS T4 ON sc.S# = T4.S#
AND T4.C# = 'k4'
ORDER BY
ISNULL( T1.score, 0 ) + ISNULL( T2.score, 0 ) + ISNULL( T3.score, 0 ) + ISNULL( T4.score, 0 ) DESC
);
23、统计列印各科成绩,各分数段人数:课程 ID,课程名称,
[100-85],
[85-70],
[70-60],
[ <60] SELECT
SC.C# AS 课程 ID,
Cname AS 课程名称
,
SUM ( CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END ) AS [100 - 85],
SUM ( CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END ) AS [85 - 70],
SUM ( CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END ) AS [70 - 60],
SUM ( CASE WHEN score < 60 THEN 1 ELSE 0 END ) AS [60 -]
FROM
SC,
Course
WHERE
SC.C#= Course.C#
GROUP BY
SC.C#,
Cname;
24、查询学生平均成绩及其名次 SELECT
1+ (
SELECT COUNT
( DISTINCT 平均成绩 )
FROM
( SELECT S #, AVG ( score ) AS 平均成绩 FROM SC GROUP BY S # ) AS T1
WHERE
平均成绩 > T2.平均成绩
) AS 名次,
S # AS 学生学号,平均成绩
FROM
( SELECT S #, AVG ( score ) 平均成绩 FROM SC GROUP BY S # ) AS T2
ORDER BY
平均成绩 DESC;
25、查询各科成绩前三名的记录 : (不考虑成绩并列情况) SELECT
t1.S# AS 学生 ID,
t1.C# AS 课程 ID,
Score AS 分数
FROM
SC t1
WHERE
score IN ( SELECT TOP 3 score FROM SC WHERE t1.C#= C # ORDER BY score DESC )
ORDER BY
t1.C#;
26、查询每门课程被选修的学生数 SELECT
c #,
COUNT ( S # )
FROM
sc
GROUP BY
C #;
27、查询出只选修了一门课程的全部学生的学号和姓名 SELECT
SC.S#,
Student.Sname,
COUNT ( C # ) AS 选课数
FROM
SC,
Student
WHERE
SC.S#= Student.S#
GROUP BY
SC.S# ,
Student.Sname
HAVING
COUNT ( C # ) = 1;
28、查询男生、女生人数 SELECT COUNT
( Ssex ) AS 男生人数
FROM
Student
GROUP BY
Ssex
HAVING
Ssex = '男';
SELECT COUNT
( Ssex ) AS 女生人数
FROM
Student
GROUP BY
Ssex
HAVING
Ssex = '女'； 29、查询姓“张”的学生名单 SELECT
Sname
FROM
Student
WHERE
Sname LIKE '张%';
30、查询同名同性学生名单，并统计同名人数 SELECT
Sname,
COUNT ( * )
FROM
Student
GROUP BY
Sname
HAVING
COUNT ( * ) > 1;;
31、1981年出生的学生名单 (注： Student表中Sage列的类型是datetime ) SELECT
Sname,
CONVERT (
CHAR ( 11 ),
DATEPART( YEAR, Sage )) AS age
FROM
student
WHERE
CONVERT (
CHAR ( 11 ),
DATEPART( YEAR, Sage )) = '1981';
32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列 SELECT
C #,
AVG ( score )
FROM
SC
GROUP BY
C #
ORDER BY
AVG ( score ),
C # DESC;
33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩 SELECT
Sname,
SC.S# ,
AVG ( score )
FROM
Student,
SC
WHERE
Student.S#= SC.S#
GROUP BY
SC.S#,
Sname
HAVING
AVG ( score ) > 85;
34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数 SELECT
Sname,
isnull( score, 0 )
FROM
Student,
SC,
Course
WHERE
SC.S#= Student.S#
AND SC.C#= Course.C#
AND Course.Cname= '数据库'
AND score < 60;
35、查询所有学生的选课情况； SELECT
SC.S#,
SC.C#,
Sname,
Cname
FROM
SC,
Student,
Course
WHERE
SC.S#= Student.S#
AND SC.C#= Course.C# ;
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数； SELECT DISTINCT
student.S#,
student.Sname,
SC.C#,
SC.score
FROM
student,
Sc
WHERE
SC.score>= 70
AND SC.S#= student.S#;
37、查询不及格的课程，并按课程号从大到小排列 SELECT
c #
FROM
sc
WHERE
scor e < 60
ORDER BY
C # ;
38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名； SELECT
SC.S#,
Student.Sname
FROM
SC,
Student
WHERE
SC.S#= Student.S#
AND Score > 80
AND C #= '003';
39、求选了课程的学生人数 SELECT COUNT
( * )
FROM
sc;
40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩 SELECT
Student.Sname,
score
FROM
Student,
SC,
Course C,
Teacher
WHERE
Student.S#= SC.S#
AND SC.C#= C.C#
AND C.T#= Teacher.T#
AND Teacher.Tname= '叶平'
AND SC.score= ( SELECT MAX ( score ) FROM SC WHERE C #= C.C# );
41、查询各个课程及相应的选修人数 SELECT COUNT
( * )
FROM
sc
GROUP BY
C #;
42、查询不同课程成绩相同的学生的学号、课程号、学生成绩 SELECT DISTINCT
A.S#,
B.score
FROM
SC A,
SC B
WHERE
A.Score= B.Score
AND A.C# <> B.C# ;
43、查询每门功成绩最好的前两名 SELECT
t1.S# AS 学生 ID,
t1.C# AS 课程 ID,
Score AS 分数
FROM
SC t1
WHERE
score IN ( SELECT TOP 2 score FROM SC WHERE t1.C#= C # ORDER BY score DESC )
ORDER BY
t1.C#;
44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列 SELECT
C # AS 课程号,
COUNT ( * ) AS 人数
FROM
sc
GROUP BY
C #
ORDER BY
COUNT ( * ) DESC,
c # 45、检索至少选修两门课程的学生学号 SELECT
S #
FROM
sc
GROUP BY
s #
HAVING
COUNT ( * ) > = 2 46、查询全部学生都选修的课程的课程号和课程名 SELECT
C #,
Cname
FROM
Course
WHERE
C # IN ( SELECT c # FROM sc GROUP BY c # ) 47、查询没学过“叶平”老师讲授的任一门课程的学生姓名 SELECT
Sname
FROM
Student
WHERE
S # NOT IN (
SELECT
S #
FROM
Course,
Teacher,
SC
WHERE
Course.T#= Teacher.T#
AND SC.C#= course.C#
AND Tname = '叶平'
);
48、查询两门以上不及格课程的同学的学号及其平均成绩 SELECT
S #,
AVG (
isnull( score, 0 ))
FROM
SC
WHERE
S # IN ( SELECT S # FROM SC WHERE score < 60 GROUP BY S # HAVING COUNT ( * ) > 2 )
GROUP BY
S #;
49、检索“004”课程分数小于60，按分数降序排列的同学学号 SELECT
S #
FROM
SC
WHERE
C #= '004'
AND score < 60
ORDER BY
score DESC;
50、删除“002”同学的“001”课程的成绩 DELETE
FROM
Sc
WHERE
S #= '001'
AND C #= '001';