SQL练习题

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';

转载于:https://www.cnblogs.com/lishisan/p/11219534.html