50条SQL查询技巧

学生表（S＃，Sname，Sage，Ssex）学生表  课程表（C＃，Cname，T＃）课程表  SC（S＃，C＃，score）成绩表  老师（T＃，Tname）教师表     问题：  1，查询“001”课程比“002”课程成绩高的所有学生的学号;  从（选择s＃，从SC的得分C＃='001'）a（（选择s＃，得分））选择aS＃  从SC，其中C＃='002'）b  其中a.score > b.score和as＃= bs＃;  2，查询平均成绩大于60分的同学的学号和平均成绩;  选择S＃，avg（score）  从sc  群组由S＃拥有avg（得分）  > 60;  3，查询所有同学的学号，姓名，选课数，总成绩;  选择Student.S＃，Student.Sname，count（SC.C＃），sum（score）  从学生离开外部加入SC在Student.S＃= SC.S＃  group by Student.S＃，Sname  4，查询姓“李”的老师的个数;  选择count（distinct（Tname））  从老师  其中Tname像“李％”;  5，查询没学过“叶平”老师课的同学的学号，姓名;  选择Student.S＃，Student.Sname  来自学生  其中S＃不在（选择不同（SC.S＃）从SC，课程，老师，SC.C＃= Course.C＃和Teacher.T＃= Course.T＃和  Teacher.Tname = '叶平' ）;  6，查询学过“001”并且也学过编号“002”课程的同学的学号，姓名;  选择Student.S＃，Student.Sname from Student，SC where Student.S＃= SC.S＃和SC.C＃='001'并存在（从SC选择*作为SC_2，其中SC_2.S＃= SC.S＃和SC_2.C＃='002'）;  7，查询学过“叶平”老师所教的所有课的同学的学号，姓名;  选择S＃，Sname  来自学生  其中S＃（从SC，课程，教师中选择S＃，SC.C＃= Course.C＃和Teacher.T＃= Course.T＃和  Teacher.Tname = '叶平'  组由S＃计数（SC。 C＃）=（从课程中选择计数（C＃），Teacher.T＃= Course.T＃和  Tname = '叶平' ））;  8，查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号，姓名;  选择S＃，Sname从（选择Student.S＃，Student.Sname，得分，（SC SC_2中的选择分数，其中SC_2.S＃= Student.S＃和SC_2.C＃='002'）得分2  来自Student，SC，其中Student.S＃= SC.S＃和C＃='001'）S_2其中score2  < score ;  9，查询所有课程成绩小于60分的同学的学号，姓名;  选择S＃，Sname  来自学生  其中S＃不在（选择Student.S＃从学生，SC，其中SS＃= SC.S＃和得分> 60）;  10，查询没有学全所有课的同学的学号，姓名;  选择Student.S＃，Student.Sname  来自学生，SC  其中Student.S＃= SC.S＃group by Student.S＃，Student.Sname具有计数（C＃）  < （从课程中选择计数（C＃））;     11，查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;  选择S＃，Sname从Student，SC，其中Student.S＃= SC.S＃和C＃在SC中选择C＃，其中S＃='1001';  12，查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;  选择不同的SC.S＃，Sname  来自学生，SC  其中Student.S＃= SC.S＃和C＃in（从SC中选择C＃，其中S＃='001'）;  13，把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;  更新SC set  score =（select avg（SC_2.score）  来自SC SC_2  其中SC_2.C＃= SC.C＃）来自课程，教师课程C＃= SC.C＃和Course.T＃= Teacher.T＃和  Teacher.Tname = '叶平' ）;  14，查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;  从SC中选择S＃，其中C＃in（从SC中选择C＃，其中S＃='1002'）  由S＃计数（*）=（从SC选择计数（*），S＃='1002'）的S＃  15，删除学习“叶平”老师课的SC表记录;  反思SC  从老师那里  其中Course.C＃= SC.C＃和Course.T＃= Teacher.T＃和  Tname = '叶平' ;  16，向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号，2，  号课的平均成绩;  插入SC选择S＃，'002'，（选择平均（得分）  来自SC的C＃='002'）来自Student，其中S＃不在（从SC中选择S＃，其中C＃='002'）;  17，按平均成绩从高到低显示所有学生的“数据库”，“企业管理”，“英语”三门的课程成绩，按如下形式显示：学生ID ,,数据库，企业管理，英语，有效课程数，有效平均分  SELECT S＃作为学生ID  ，（SELECT score FROM SC WHERE SC.S＃= tS＃AND C＃='004'）AS数据库  ，（SELECT score FROM SC WHERE SC.S＃= tS＃AND C＃='001'）AS企业管理  ，（SELECT score FROM SC WHERE SC.S＃= tS＃AND C＃='006'）AS英语  ，COUNT（*）AS有效课程数，AVG（t.score）AS平均成绩  从SC AS t  GROUP BY S＃  平均值（t.score）  18，查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分  SELECT LC＃作为课程ID，L.score AS最高分，R.score AS最低分  来自SC L，SC AS R  在哪里LC＃= RC＃和  L.score  =（SELECT MAX（IL.score）  从SC AS IL，学生AS IM  WHERE LC＃= IL.C＃和IM.S＃= IL.S＃  GROUP BY IL.C＃）  和  R.Score  =（SELECT MIN（IR.score）  从SC AS IR  WHERE RC＃= IR.C＃  GROUP BY IR.C＃  ）;  19，按各科平均成绩从低到高和及格率的百分数从高到低顺序  SELECT tC＃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及格百分数  从SC T，课程  其中tC＃= course.C＃  GROUP BY tC＃  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得分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数据库及格百分数  从SC      21，查询不同老师所教不同课程平均分从高到低显示  最大（ZT＃）AS教师ID，MAX（Z.Tname）AS教师姓名，CC＃AS课程ID，MAX（C.Cname）AS课程名称，AVG（Score）AS平均成绩  从SC AS T，课程AS C，教师AS Z  其中TC＃= CC＃和CT＃= ZT＃  GROUP BY CC＃  订单AVG（得分）DESC  22，查询如下课程成绩第3名到第6名学生成绩单：企业管理（001），马克思（002），UML（003），数据库（004）  [学生ID]，[学生姓名]，企业管理，马克思，UML，数据库，平均成绩  SELECT DISTINCT top 3  SC.S＃作为学生学号，  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）作为总分  来自学生，SC LEFT加入SC作为T1  ON SC.S＃= T1.S＃AND T1.C＃='001'  左键加入SC作为T2  ON SC.S＃= T2.S＃AND T2.C＃='002'  LEFT加入SC作为T3  ON SC.S＃= T3.S＃AND T3.C＃='003'  左键加入SC作为T4  ON SC.S＃= T4.S＃AND T4.C＃='004'  WHERE student.S＃= SC.S＃和  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）  从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＃和T4.C＃='k4'  ORDER BY ISNULL（T1.score，0）+ ISNULL（T2.score，0）+ ISNULL（T3.score，0）+ ISNULL（T4.score，0）DESC）;     23，统计列印各科成绩，各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[  < 60 ]  SELECT SC.C＃作为课程ID，Cname作为课程名称  ，SUM（CASE WHE得分在85和100之间，然后1 ELSE 0结束）AS [100  -  85]  ，SUM（CASE WHEN得分为70和85 THEN 1 ELSE 0 END）AS [85  -  70]  ，SUM（CASE WHE得分在60和70之间，1 ELSE 0 END）AS [70  -  60]  ，SUM（CASE WHEN得分  < 60  THEN 1 ELSE 0 END）AS [60  - ]   从SC，课程  其中SC.C＃= Course.C＃  GROUP BY SC.C＃，Cname;     24，查询学生平均成绩及其名次  SELECT 1+（SELECT COUNT（distinct平均成绩）  FROM（SELECT S＃，AVG（score）AS平均成绩  从SC  GROUP BY S＃  ）AS T1  WHERE平均成绩  >  T2。平均成绩）作为名次，  S＃as学生学号，平均成绩  FROM（SELECT S＃，AVG（score）平均成绩  从SC  GROUP BY S＃  ）AS T2  ORDER BY平均成绩     25，查询各科成绩前三名的记录:(不考虑成绩并列情况）  SELECT t1.S＃as学生ID，t1.C＃作为课程ID，Score as分数  从SC t1  WHERE得分IN（选择TOP 3分  从SC  WHERE t1.C＃= C＃  ORDER BY得分DESC  ）  ORDER BY t1.C＃;  26，查询每门课程被选修的学生数  从C＃中选择c＃，count（S＃）;  27，查询出只选修了一门课程的全部学生的学号和姓名  选择SC.S＃，Student.Sname，count（C＃）AS选课数  来自SC，学生  其中SC.S＃= Student.S＃group by SC.S＃，Student.Sname的count（C＃）= 1;  28，查询男生，女生人数  选择计数（Ssex）作为男生人数从Ssex的学生组由  Ssex = '男' ;  从Ssex选择count（Ssex）作为女生人数由  Ssex = “女” ;  29，查询姓“张”的学生名单  SELECT SNAME FROM Student WHERE Sname like'张％';  30，查询同名同性学生名单，并统计同名人数  从学生组中选择Sname，count（*）由Sname计数（*）> 1;  31，1981年出生的学生名单（注：学生表中圣人列的类型是日期时间）  选择Sname，CONVERT（char（11），DATEPART（year，Sage））作为年龄  来自学生  其中CONVERT（char（11），DATEPART（year，Sage））='1981';  32，查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列  选择C＃，平均（分数）从SC组按C＃顺序由平均（分数），C＃DESC;  33，查询平均成绩大于85的所有学生的学号，姓名和平均成绩  选择Sname，SC.S＃，avg（score）  来自学生，SC  其中Student.S＃= SC.S＃group by SC.S＃，Sname avg（score）> 85;  34，查询课程名称为“数据库”，且分数低于60的学生姓名和分数  选择Sname，isnull（score，0）  从学生，SC，课程  其中SC.S＃= Student.S＃和SC.C＃= Course.C＃和  Course.Cname ='数据库' ， 得分  < 60 ;  35，查询所有学生的选课情况;  SELECT SC.S＃，SC.C＃，Sname，Cname  从SC，学生，课程  其中SC.S＃= Student.S＃和SC.C＃= Course.C＃;  36，查询任何一门课程成绩在70分以上的姓名，课程名称和分数;  SELECT distinct student.S＃，student.Sname，SC.C＃，SC.score  从学生，Sc  WHERE SC.score > = 70 AND SC.S＃= student.S＃;  37，查询不及格的课程，并按课程号从大到小排列  从sc中选择c＃，其中scor e  < 60  order by C＃;  38，查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;  选择SC.S＃，Student.Sname从SC，Student其中SC.S＃= Student.S＃和Score > 80和C＃='003';  39，求选了课程的学生人数  从sc选择count（*）;  40，查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩  选择Student.Sname，得分  来自学生，SC，课程C，教师  其中Student.S＃= SC.S＃和SC.C＃= CC＃和CT＃= Teacher.T＃和  Teacher.Tname = '叶平'  和  SC.score =（从SC选择max（score），其中C＃= CC＃）;  41，查询各个课程及相应的选修人数  通过C＃从sc组中选择count（*）;  42，查询不同课程成绩相同的学生的学号，课程号，学生成绩  从SC A，SC B中选择不同的AS＃，B.score，其中  A.Score = B .Score和AC＃  < > BC＃;  43，查询每门功成绩最好的前两名  SELECT t1.S＃as学生ID，t1.C＃作为课程ID，Score as分数  从SC t1  WHERE得分IN（选择TOP 2得分  从SC  WHERE t1.C＃= C＃  ORDER BY得分DESC  ）  ORDER BY t1.C＃;  44，统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列  选择C＃作为课程号，count（*）作为人数  从sc  由C＃组  order by count（*）desc，c＃  45，检索至少选修两门课程的学生学号  选择S＃  从sc  组由s＃  计数（*）  >  = 2  46，查询全部学生都选修的课程的课程号和课程名  选择C＃，Cname  从课程  其中C＃in（select c＃from sc group by c＃）  47，查询没学过“叶平”老师讲授的任一门课程的学生姓名  选择Sname从学生S​​＃不在（选择S＃从课程，老师，SC其中Course.T＃= Teacher.T＃和SC.C＃= course.C＃和  Tname = '叶平' ）;  48，查询两门以上不及格课程的同学的学号及其平均成绩  从SC中选择S＃，avg（isnull（score，0）），其中S＃in（从S中选择S＃，其中得分  < 60  组由S＃具有count（*）> 2）组由S＃组成;  49，检索“004”课程分数小于60，按分数降序排列的同学学号  从SC中选择S＃，其中C＃='004'，得分  < 60  order by score desc;  50，删除“002”同学的“001”课程的成绩  从Sc中删除，其中S＃='001'和C＃='001';