sql2005常用sql语句（一）

1、查询“”课程比“”课程成绩高的所有学生的学号； 

 SELECT a.S# FROM (SELECT s#,score FROM SC WHERE C#='001') a,

 (SELECT s#,score  FROM SC WHERE C#='002') b 

 

  WHEREa.score>b.score AND a.s#=b.s#; 

 2、查询平均成绩大于分的同学的学号和平均成绩；

 

   SELECT S#,avg(score) 

 

   FROM sc 

 

   GROUP BY S# having avg(score) >60; 

 

3、查询所有同学的学号、姓名、选课数、总成绩；

 

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

 

  FROMStudent left Outer JOIN SC on Student.S#=SC.S# 

 

  GROUPBY Student.S#,Sname 

 

4、查询姓“李”的老师的个数；

 

 SELECT count(distinct(Tname)) 

 

  FROMTeacher 

 

  WHERETname 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、查询学过“”并且也学过编号“”课程的同学的学号、姓名；

 

 SELECT Student.S#,Student.Sname FROM Student,SC WHERE Student.S#=SC.S# ANDSC.C#='001'and exists( SELECT * FROM SC as SC_2 WHERE SC_2.S#=SC.S# ANDSC_2.C#='002'); 

 

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

 

 SELECT S#,Sname 

 

  FROMStudent 

 

  WHERES# in (SELECT S# FROM SC ,Course ,Teacher WHERE SC.C#=Course.C# ANDTeacher.T#=Course.T# AND Teacher.Tname='叶平' GROUP BY S# having count(SC.C#)=(SELECTcount(C#) FROM Course,Teacher  WHERE Teacher.T#=Course.T# AND Tname='叶平')); 

 

8、查询课程编号“”的成绩比课程编号“”课程低的所有同学的学号、姓名；

 

 SELECT S#,Sname FROM (SELECT Student.S#,Student.Sname,score ,(SELECT score FROMSC SC_2 WHERE SC_2.S#=Student.S# AND SC_2.C#='002') score2 

 

  FROMStudent,SC WHERE Student.S#=SC.S# AND C#='001') S_2 WHERE score2<score; 

 

9、查询所有课程成绩小于分的同学的学号、姓名；

 

 SELECT S#,Sname 

 

  FROMStudent 

 

  WHERES# not in (SELECT Student.S# FROM Student,SC WHERE S.S#=SC.S# ANDscore>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、查询至少有一门课与学号为“”的同学所学相同的同学的学号和姓名；

 

   SELECT S#,Sname FROM Student,SC WHERE Student.S#=SC.S# AND C# in SELECT C# FROMSC WHERE S#='1001'; 

 

12、查询至少学过学号为“”同学所有一门课的其他同学学号和姓名；

 

   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# ANDCourse.T#=Teacher.T# AND Teacher.Tname='叶平'); 

 

14、查询和“”号的同学学习的课程完全相同的其他同学学号和姓名；

 

   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表中插入一些记录，这些记录要求符合以下条件：没有上过编号“”课程的同学学号、、

   号课的平均成绩；

   Insert SC SELECT S#,'002',(SELECT avg(score) 

   FROM SC WHERE C#='002') FROM Student WHERE S# not in (SELECT S# FROM SC WHEREC#='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.scoreAS 最高分,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 0END)/COUNT(*) DESC 

20、查询如下课程平均成绩和及格率的百分数(用"1行"显示):企业管理（），马克思（），OO&UML（），数据库（）

    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(CASEWHEN C# = '001' THEN 1 ELSE 0 END) AS企业管理及格百分数  

       ,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1ELSE 0 END) AS马克思平均分

       ,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASEWHEN C# = '002' THEN 1 ELSE 0 END) AS马克思及格百分数

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

       ,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASEWHEN 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 1ELSE 0 END) AS数据库平均分

       ,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASEWHEN C# = '004' THEN 1 ELSE 0 END) AS数据库及格百分数

  FROMSC 

 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# 

  ORDERBY AVG(Score) DESC 

22、查询如下课程成绩第3名到第6 名的学生成绩单：企业管理（），马克思（），UML（），数据库（）

    [学生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); 

 3、统计列印各科成绩,各分数段人数:课程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;