您的位置:新葡亰496net > 网络数据库 > 新葡亰496net数据库知识必杀,50个常用的sql语句

新葡亰496net数据库知识必杀,50个常用的sql语句

发布时间:2019-08-17 02:17编辑:网络数据库浏览(79)

    Student(S#,Sname,Sage,Ssex) 学生表
    Course(C#,Cname,T#) 课程表
    SC(S#,C#,score) 成绩表
    Teacher(T#,Tname) 教师表

    Student(S#,Sname,Sage,Ssex) 学生表
    Course(C#,Cname,T#) 课程表
    SC(S#,C#,score) 成绩表
    Teacher(T#,Tname) 教师表

    四十多个常用的sql语句

    肆20个常用的sql语句

    Student(S#,Sname,Sage,Ssex) 学生表
    Course(C#,Cname,T#) 课程表
    SC(S#,C#,score) 成绩表
    Teacher(T#,Tname) 教师表

    问题:
    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 Student.S# from Student,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 S#,Sname from Student,SC where Student.S#=SC.S# and 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#
    );
    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),Marx(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 课程ID,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],[学员姓名],集团处理,Marx,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);

    新葡亰496net数据库知识必杀,50个常用的sql语句。问题:
    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 Student.S# from Student,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 S#,Sname from Student,SC where Student.S#=SC.S# and 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#
    );
    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 课程ID,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],[学员姓名],公司管理,Marx,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);

    Student(S#,Sname,Sage,Ssex) 学生表

    Student(S#,Sname,Sage,Ssex) 学生表

    问题:
    1、查询“001”课程比“002”课程成绩高的具有学生的学号;
      select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
    新葡亰496net,  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 Student.S# from Student,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 S#,Sname from Student,SC where Student.S#=SC.S# and 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#
                        );
    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 课程ID,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)

    23、总括列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70]新葡亰496net数据库知识必杀,50个常用的sql语句。,[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;

    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;

    Course(C#,Cname,T#) 课程表

    Course(C#,Cname,T#) 课程表

    • 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);

    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
    O福睿斯DE路虎极光 BY 平均成绩 desc;

    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
    O路虎极光DE陆风X8 BY 平均成绩 desc;

    SC(S#,C#,score) 成绩表

    SC(S#,C#,score) 成绩表

    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;

    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、一九八三年出生的学生名单(注: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';

    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、1982年降生的学生名单(注: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';

    Teacher(T#,Tname) 教师表

    Teacher(T#,Tname) 教师表

    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
        O本田UR-VDE福特Explorer 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、1983年降生的学生名单(注: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';

     

    问题:

    问题:

    (转 )

    1、查询“001”课程比“002”课程战绩高的有着学员的学号;

    1、查询“001”课程比“002”课程成绩高的兼具学员的学号;

     select a.S# from (select s#,score from SC where C#='001') a,(select s#,score

     select a.S# from (select s#,score from SC where C#='001') a,(select s#,score

     from SC where C#='002') b

     from SC where C#='002') b

     where a.score>b.score and a.s#=b.s#;

     where a.score>b.score and a.s#=b.s#;

    2、查询平均战表超乎60分的同室的学号和平均成绩;

    2、查询平均战绩超乎60分的校友的学号和平均成绩;

        select S#,avg(score)

        select S#,avg(score)

        from sc

        from sc

        group by S# having avg(score) >60;

        group by S# having avg(score) >60;

    3、查询全部同学的学号、姓名、选课数、总成绩;

    3、查询全体同学的学号、姓名、选课数、总战表;

     select Student.S#,Student.Sname,count(SC.C#),sum(score)

     select Student.S#,Student.Sname,count(SC.C#),sum(score)

     from Student left Outer join SC on Student.S#=SC.S#

     from Student left Outer join SC on Student.S#=SC.S#

     group by Student.S#,Sname

     group by Student.S#,Sname

    4、查询姓“李”的民间兴办教授的个数;

    4、查询姓“李”的教授的个数;

     select count(distinct(Tname))

     select count(distinct(Tname))

     from Teacher

     from Teacher

     where Tname like '李%';

     where Tname like '李%';

    5、查询没学过“叶平”老师课的同桌的学号、姓名;

    5、查询没学过“叶平”老师课的同室的学号、姓名;

        select Student.S#,Student.Sname

        select Student.S#,Student.Sname

        from Student 

        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='叶平');

        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”课程的同校的学号、姓名;

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

     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、查询学过“叶平”老师所教的全数课的同学的学号、姓名;

    7、查询学过“叶平”老师所教的全体课的校友的学号、姓名;

     select S#,Sname

     select S#,Sname

     from Student

     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='叶平'));

     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”课程低的具有同学的学号、姓名;

    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

     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;

     from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 <score;

    9、查询全体课程成绩小于60分的同室的学号、姓名;

    9、查询全数课程战绩小于60分的同班的学号、姓名;

     select S#,Sname

     select S#,Sname

     from Student

     from Student

     where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

     where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

    10、查询未有学全全数课的同校的学号、姓名;

    10、查询未有学全全部课的同窗的学号、姓名;

        select Student.S#,Student.Sname

        select Student.S#,Student.Sname

        from Student,SC

        from Student,SC

        where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);

        where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);

    11、查询至少有一门课与学号为“1001”的同室所学同样的同室的学号和人名;

    11、查询至少有一门课与学号为“1001”的同桌所学同样的同桌的学号和姓名;

        select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';

        select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';

    12、查询至少学过学号为“001”同学全体一门课的其余同学学号和姓名;

    12、查询至少学过学号为“001”同学全部一门课的其余同学学号和人名;

        select distinct SC.S#,Sname

        select distinct SC.S#,Sname

        from Student,SC

        from Student,SC

        where Student.S#=SC.S# and C# in (select C# from SC where S#='001');

        where Student.S#=SC.S# and C# in (select C# from SC where S#='001');

    13、把“SC”表中“叶平”老师教的课的战表都改成为此课程的平均战绩;

    13、把“SC”表中“叶平”老师教的课的实际业绩都更改为此课程的平均战绩;

        update SC set score=(select avg(SC_2.score)

        update SC set score=(select avg(SC_2.score)

        from SC SC_2

        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='叶平');

        where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');

    14、查询和“1002”号的同桌学习的教程一模二样的其余同学学号和人名;

    14、查询和“1002”号的同学学习的课程千篇一律的别的同学学号和姓名;

        select S# from SC where C# in (select C# from SC where S#='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');

        group by S# having count(*)=(select count(*) from SC where S#='1002');

    15、删除学习“叶平”老师课的SC表记录;

    15、删除学习“叶平”老师课的SC表记录;

        Delect SC

        Delect SC

        from course ,Teacher 

        from course ,Teacher 

        where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';

        where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';

    16、向SC表中插入一些记下,这一个记录要求符合以下原则:未有上过编号“003”课程的同室学号、2

    16、向SC表中插入一些记下,这么些记录要求符合以下法则:未有上过编号“003”课程的同校学号、2

        号课的平分战绩;

        号课的平分成绩;

        Insert SC select S#,'002',(Select avg(score)

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

        from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');

    17、按平均战绩从高到低展现全数学员的“数据库”、“公司管理”、“韩文”三门的教程成绩,按如下情势显得: 学生ID,,数据库,集团管理,英语,有效课程数,有效平均分

    17、按平均战表从高到低呈现全数学员的“数据库”、“公司管理”、“德语”三门的科目战绩,按如下格局显得: 学生ID,,数据库,公司管理,塞尔维亚(Република Србија)语,有效课程数,有效平均分

        SELECT S# as 学生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#='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#='001') AS 公司管理

            ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语

            ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语

            ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩

            ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩

        FROM SC AS t

        FROM SC AS t

        GROUP BY S#

        GROUP BY S#

        ORDER BY avg(t.score) 

        ORDER BY avg(t.score) 

    18、查询各科成绩最高和压低的分:以如下情势展示:课程ID,最高分,最低分

    18、查询各科成绩最高和最低的分:以如下格局体现:课程ID,最高分,最低分

        SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分

        SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分

        FROM SC L ,SC AS R

        FROM SC L ,SC AS R

        WHERE L.C# = R.C# and

        WHERE L.C# = R.C# and

            L.score = (SELECT MAX(IL.score)

            L.score = (SELECT MAX(IL.score)

                          FROM SC AS IL,Student AS IM

                          FROM SC AS IL,Student AS IM

                          WHERE L.C# = IL.C# and IM.S#=IL.S#

                          WHERE L.C# = IL.C# and IM.S#=IL.S#

                          GROUP BY IL.C#)

                          GROUP BY IL.C#)

            AND

            AND

            R.Score = (SELECT MIN(IR.score)

            R.Score = (SELECT MIN(IR.score)

                          FROM SC AS IR

                          FROM SC AS IR

                          WHERE R.C# = IR.C#

                          WHERE R.C# = IR.C#

                      GROUP BY IR.C#

                      GROUP BY IR.C#

                        );

                        );

    19、按各科平均战绩从低到高和及格率的比重从高到低依次

    19、按各科平均成绩从低到高和及格率的比例从高到低依次

        SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩

        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 及格百分数

            ,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数

        FROM SC T,Course

        FROM SC T,Course

        where t.C#=course.C#

        where t.C#=course.C#

        GROUP BY t.C#

        GROUP BY t.C#

        ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC

        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

    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 集团管理平均分

        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 公司管理及格百分数

            ,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 马克思平均分

            ,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 马克思及格百分数

            ,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 Marx及格百分数

            ,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分

            ,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及格百分数

            ,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 数据库平均分

            ,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 数据库及格百分数

            ,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

     FROM SC

    21、查询区别老师所教不相同学科平均分从高到低彰显

    21、查询分歧老师所教差别科目平均分从高到低彰显

     SELECT max(Z.T#) AS 教授ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩

     SELECT max(Z.T#) AS 教授ID,MAX(Z.Tname) AS 教授姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩

        FROM SC AS T,Course AS C ,Teacher AS Z

        FROM SC AS T,Course AS C ,Teacher AS Z

        where T.C#=C.C# and C.T#=Z.T#

        where T.C#=C.C# and C.T#=Z.T#

     GROUP BY C.C#

     GROUP BY C.C#

     ORDER BY AVG(Score) DESC

     ORDER BY AVG(Score) DESC

    22、查询如下课程成绩第 3 名到第 6 名的学员战绩单:集团管理(001),马克思(002),UML (003),数据库(004

    22、查询如下课程成绩第 3 名到第 6 名的学员成绩单:集团管理(001),马克思(002),UML (003),数据库(004

        [学生ID],[学员姓名],公司管理,马克思,UML,数据库,平均战表

        [学生ID],[学生姓名],公司管理,Marx,UML,数据库,平均战绩

        SELECT DISTINCT top 3

        SELECT DISTINCT top 3

          SC.S# As 学生学号,

          SC.S# As 学生学号,

            Student.Sname AS 学生姓名 ,

            Student.Sname AS 学生姓名 ,

          T1.score AS 集团管理,

          T1.score AS 集团管理,

          T2.score AS 马克思,

          T2.score AS 马克思,

          T3.score AS UML,

          T3.score AS UML,

          T4.score AS 数据库,

          T4.score AS 数据库,

          ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0) 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

          FROM Student,SC LEFT JOIN SC AS T1

                          ON SC.S# = T1.S# AND T1.C# = '001'

                          ON SC.S# = T1.S# AND T1.C# = '001'

                LEFT JOIN SC AS T2

                LEFT JOIN SC AS T2

                          ON SC.S# = T2.S# AND T2.C# = '002'

                          ON SC.S# = T2.S# AND T2.C# = '002'

                LEFT JOIN SC AS T3

                LEFT JOIN SC AS T3

                          ON SC.S# = T3.S# AND T3.C# = '003'

                          ON SC.S# = T3.S# AND T3.C# = '003'

                LEFT JOIN SC AS T4

                LEFT JOIN SC AS T4

                          ON SC.S# = T4.S# AND T4.C# = '004'

                          ON SC.S# = T4.S# AND T4.C# = '004'

          WHERE student.S#=SC.S# and

          WHERE student.S#=SC.S# and

          ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0)

          ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0)

          NOT IN

          NOT IN

          (SELECT

          (SELECT

                DISTINCT

                DISTINCT

                TOP 15 WITH TIES

                TOP 15 WITH TIES

                ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0)

                ISNULL(T1.score,0) ISNULL(T2.score,0) ISNULL(T3.score,0) ISNULL(T4.score,0)

          FROM sc

          FROM sc

                LEFT JOIN sc AS T1

                LEFT JOIN sc AS T1

                          ON sc.S# = T1.S# AND T1.C# = 'k1'

                          ON sc.S# = T1.S# AND T1.C# = 'k1'

               LEFT JOIN sc AS T2

               LEFT JOIN sc AS T2

                          ON sc.S# = T2.S# AND T2.C# = 'k2'

                          ON sc.S# = T2.S# AND T2.C# = 'k2'

                LEFT JOIN sc AS T3

                LEFT JOIN sc AS T3

                          ON sc.S# = T3.S# AND T3.C# = 'k3'

                          ON sc.S# = T3.S# AND T3.C# = 'k3'

                LEFT JOIN sc AS T4

                LEFT JOIN sc AS T4

                          ON sc.S# = T4.S# AND T4.C# = 'k4'

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

          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]

    23、总结列印各科战绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

        SELECT SC.C# as 课程ID, Cname as 课程名称

        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 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 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 BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]

            ,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]

            ,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]

        FROM SC,Course

        FROM SC,Course

        where SC.C#=Course.C#

        where SC.C#=Course.C#

        GROUP BY SC.C#,Cname;

        GROUP BY SC.C#,Cname;

    24、查询学生平均成绩及其排名

    24、查询学平生均战绩及其排名

          SELECT 1 (SELECT COUNT( distinct 平均战表)

          SELECT 1 (SELECT COUNT( distinct 平均成绩)

                  FROM (SELECT S#,AVG(score) AS 平均战表

                  FROM (SELECT S#,AVG(score) AS 平均成绩

                          FROM SC

                          FROM SC

                      GROUP BY S#

                      GROUP BY S#

                      ) AS T1

                      ) AS T1

                WHERE 平均战表 > T2.平均成绩) as 排行,

                WHERE 平均成绩 > T2.等分战绩) as 排行,

          S# as 学生学号,平均战表

          S# as 学生学号,平均成绩

        FROM (SELECT S#,AVG(score) 平均战绩

        FROM (SELECT S#,AVG(score) 平均成绩

                FROM SC

                FROM SC

            GROUP BY S#

            GROUP BY S#

            ) AS T2

            ) AS T2

        OMuranoDEEvoque BY 平均成绩 desc;

        OOdysseyDELacrosse BY 平均成绩 desc;

    25、查询各科战绩前三名的笔录:(不思虑成绩并列景况)

    25、查询各科成绩前三名的记录:(不驰念成绩并列情状)

          SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数

          SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数

          FROM SC t1

          FROM SC t1

          WHERE score IN (SELECT TOP 3 score

          WHERE score IN (SELECT TOP 3 score

                  FROM SC

                  FROM SC

                  WHERE t1.C#= C#

                  WHERE t1.C#= C#

                ORDER BY score DESC

                ORDER BY score DESC

                  )

                  )

          ORDER BY t1.C#;

          ORDER BY t1.C#;

    26、查询每门学科被选修的学生数

    26、查询每门学科被选修的学童数

     select c#,count(S#) from sc group by C#;

     select c#,count(S#) from sc group by C#;

    27、查询出只选修了一门学科的万事学生的学号和人名

    27、查询出只选修了一门学科的漫天学员的学号和人名

     select SC.S#,Student.Sname,count(C#) AS 选课数

     select SC.S#,Student.Sname,count(C#) AS 选课数

     from SC ,Student

     from SC ,Student

     where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;

     where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;

    28、查询男子、女孩子人数

    28、查询男人、女人人数

        Select count(Ssex) as 男人人数 from Student group by Ssex having Ssex='男';

        Select count(Ssex) as 男士人数 from Student group by Ssex having Ssex='男';

        Select count(Ssex) as 女孩子人数 from Student group by Ssex having Ssex='女';

        Select count(Ssex) as 女孩子人数 from Student group by Ssex having Ssex='女';

    29、查询姓“张”的学生名单

    29、查询姓“张”的学生名单

        SELECT Sname FROM Student WHERE Sname like '张%';

        SELECT Sname FROM Student WHERE Sname like '张%';

    30、查询同名同性学生名单,并总计同名家数

    30、查询同名同性学生名单,并总计同名人数

     select Sname,count(*) from Student group by Sname having count(*)>1;;

     select Sname,count(*) from Student group by Sname having count(*)>1;;

    31、1983年诞生的学生名单(注:Student表中Sage列的项目是datetime)

    31、1981年落地的学生名单(注:Student表中Sage列的档期的顺序是datetime)

        select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age

        select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age

        from student

        from student

        where CONVERT(char(11),DATEPART(year,Sage))='1981';

        where CONVERT(char(11),DATEPART(year,Sage))='1981';

    32、查询每门课程的平均战表,结果按平均战表升序排列,平均成绩同样时,按学科号降序排列

    32、查询每门科目标平分成绩,结果按平均战表升序排列,平均成绩同样时,按学科号降序排列

        Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;

        Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;

    33、查询平均战表抢先85的装有学生的学号、姓名和平均成绩

    33、查询平均战表超过85的保有学员的学号、姓名和平均战绩

        select Sname,SC.S# ,avg(score)

        select Sname,SC.S# ,avg(score)

        from Student,SC

        from Student,SC

        where Student.S#=SC.S# group by SC.S#,Sname having    avg(score)>85;

        where Student.S#=SC.S# group by SC.S#,Sname having    avg(score)>85;

    34、查询课程名叫“数据库”,且分数低于60的学员姓名和分数

    34、查询课程名称叫“数据库”,且分数低于60的学员姓名和分数

        Select Sname,isnull(score,0)

        Select Sname,isnull(score,0)

        from Student,SC,Course

        from Student,SC,Course

        where SC.S#=Student.S# and SC.C#=Course.C# and Course.Cname='数据库'and score <60;

        where SC.S#=Student.S# and SC.C#=Course.C# and Course.Cname='数据库'and score <60;

    35、查询全数学员的选课景况;

    35、查询全部学员的选课意况;

        SELECT SC.S#,SC.C#,Sname,Cname

        SELECT SC.S#,SC.C#,Sname,Cname

        FROM SC,Student,Course

        FROM SC,Student,Course

        where SC.S#=Student.S# and SC.C#=Course.C# ;

        where SC.S#=Student.S# and SC.C#=Course.C# ;

    36、查询别的一门科目成绩在70分以上的全名、课程名称和分数;

    36、查询别的一门学科成绩在70分以上的全名、课程名称和分数;

        SELECT distinct student.S#,student.Sname,SC.C#,SC.score

        SELECT distinct student.S#,student.Sname,SC.C#,SC.score

        FROM student,Sc

        FROM student,Sc

        WHERE SC.score>=70 AND SC.S#=student.S#;

        WHERE SC.score>=70 AND SC.S#=student.S#;

    37、查询不如格的学科,并按学科号从大到小排列

    37、查询不如格的学科,并按学科号从大到小排列

        select c# from sc where scor e <60 order by C# ;

        select c# from sc where scor e <60 order by C# ;

    38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;

    38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;

        select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#='003';

        select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#='003';

    39、求选了课程的学习者人数

    39、求选了课程的学员人数

        select count(*) from sc;

        select count(*) from sc;

    40、查询选修“叶平”老师所授课程的学生中,成绩最高的学员姓名及其战绩

    40、查询选修“叶平”老师所授课程的学员中,成绩最高的学员姓名及其战表

        select Student.Sname,score

        select Student.Sname,score

        from Student,SC,Course C,Teacher

        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# );

        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、查询各类学科及相应的选修人数

    41、查询各样科目及相应的选修人数

        select count(*) from sc group by C#;

        select count(*) from sc group by C#;

    42、查询分歧学科成绩同样的上学的小孩子的学号、课程号、学生成绩

    42、查询差别学科战表一样的学生的学号、课程号、学生战绩

     select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and A.C# <>B.C# ;

     select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and A.C# <>B.C# ;

    43、查询每门功成绩最佳的前两名

    43、查询每门功战表最佳的前两名

        SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数

        SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数

          FROM SC t1

          FROM SC t1

          WHERE score IN (SELECT TOP 2 score

          WHERE score IN (SELECT TOP 2 score

                  FROM SC

                  FROM SC

                  WHERE t1.C#= C#

                  WHERE t1.C#= C#

                ORDER BY score DESC

                ORDER BY score DESC

                  )

                  )

          ORDER BY t1.C#;

          ORDER BY t1.C#;

    44、计算每门课程的学员选修人数(当先10人的课程才计算)。须求输出课程号和选修人数,查询结果按人头降序排列,查询结果按人口降序排列,若人数一样,按学科号升序排列 

    44、总计每门课程的学童选修人数(超过10人的教程才总结)。供给输出课程号和选修人数,查询结果按人口降序排列,查询结果按人头降序排列,若人数同样,按学科号升序排列 

        select C# as 课程号,count(*) as 人数

        select C# as 课程号,count(*) as 人数

        from sc 

        from sc 

        group by C#

        group by C#

        order by count(*) desc,c# 

        order by count(*) desc,c# 

    45、检索至少选修两门课程的学生学号

    45、检索至少选修两门学科的学员学号

        select S# 

        select S# 

        from sc 

        from sc 

        group by s#

        group by s#

        having count(*) > = 2

        having count(*) > = 2

    46、查询全数上学的小孩子都选修的科目标课程号和学科名

    46、查询任何学员都选修的课程的课程号和课程名

        select C#,Cname 

        select C#,Cname 

        from Course 

        from Course 

        where C# in (select c# from sc group by c#) 

        where C# in (select c# from sc group by c#) 

    47、查询没学过“叶平”老师教师的任一门科目标学生姓名

    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='叶平');

        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、查询两门以上不比格课程的同桌的学号及其平均战表

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

        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,按分数降序排列的同桌学号

    49、检索“004”课程分数小于60,按分数降序排列的同学学号

        select S# from SC where C#='004'and score <60 order by score desc;

        select S# from SC where C#='004'and score <60 order by score desc;

    50、删除“002”同学的“001”课程的成就

    50、删除“002”同学的“001”课程的成绩

    delete from Sc where S#='001'and C#='001';

    delete from Sc where S#='001'and C#='001';

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net数据库知识必杀,50个常用的sql语句

    关键词: