sql基礎編-50よく使うsql文

15608 ワード
java sql C++ c 同前
Student（S〓、Sname、Sage、Ssex）学生表
Course(C〓，Cname，T〓課程表
SC（S〓，C〓，score）成績表
Techer(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、平均成績が高いから低いまで、すべての学生の「データベース」、「企業管理」、「英語」の3科目の課程成績を表示します。以下の形式によって表示されます。学生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）
ps：MKSは河蟹によって、高さを変えられます。
    

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、各科目の成績上位3位の記録を調べる：
      
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、各仕事の成績が一番いいのは上位2名です。
    
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';
Bootstrapの写真は順番に放送します。
tomcatは自分のjdkと結合します。