SQL経典の50の面接試験問題

13210 ワード
データベース関連
これらの問題は著者本人が設計したのではなく、収集しただけです。
データベーステーブル
Student（S〓、Sname、Sage、Ssex）学生表S〓学号；Sname：学生の名前。Sage：学生年齢；Ssex：学生の性別
Course（C〓、Cname、T〓課程表C〓〓、課程番号；Cname：コース名；T〓〓：教師番号
SC(S葃，C29859;，score)成績表C〓〓、課程番号；スコア
Techer（T〓、Tname）教師表T夜：教師番号；先生の名前は？
問題:
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
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#)
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）
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# count(*)>10 
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#='002'and C#='001';
sqlトリガー使用例
いくつかのodps sql技術