數(shù)據(jù)庫SQL基本功練習(xí)

表架構(gòu)

成都創(chuàng)新互聯(lián)專注骨干網(wǎng)絡(luò)服務(wù)器租用10年，服務(wù)更有保障！服務(wù)器租用，達(dá)州電信機(jī)房 成都服務(wù)器租用，成都服務(wù)器托管，骨干網(wǎng)絡(luò)帶寬，享受低延遲，高速訪問。靈活、實(shí)現(xiàn)低成本的共享或公網(wǎng)數(shù)據(jù)中心高速帶寬的專屬高性能服務(wù)器。

Student(S#,Sname,Sage,Ssex) 學(xué)生表 
Course(C#,Cname,T#) 課程表 
SC(S#,C#,score) 成績(jī)表 
Teacher(T#,Tname) 教師表

建表語句 

CREATE TABLE student 
  ( 
     s#    INT, 
     sname nvarchar(32), 
     sage  INT, 
     ssex  nvarchar(8) 
  ) 
CREATE TABLE course 
  ( 
     c#    INT, 
     cname nvarchar(32), 
     t#    INT 
  ) 
CREATE TABLE sc 
  ( 
     s#    INT, 
     c#    INT, 
     score INT 
  ) 
CREATE TABLE teacher 
  ( 
     t#    INT, 
     tname nvarchar(16) 
  )

測(cè)試數(shù)據(jù)

insert into Student select 1,N'劉一',18,N'男' union all
 select 2,N'錢二',19,N'女' union all
 select 3,N'張三',17,N'男' union all
 select 4,N'李四',18,N'女' union all
 select 5,N'王五',17,N'男' union all
 select 6,N'趙六',19,N'女' 
 
 insert into Teacher select 1,N'葉平' union all
 select 2,N'賀高' union all
 select 3,N'楊艷' union all
 select 4,N'周磊'
 
 insert into Course select 1,N'語文',1 union all
 select 2,N'數(shù)學(xué)',2 union all
 select 3,N'英語',3 union all
 select 4,N'物理',4
 
 insert into SC 
 select 1,1,56 union all 
 select 1,2,78 union all 
 select 1,3,67 union all 
 select 1,4,58 union all 
 select 2,1,79 union all 
 select 2,2,81 union all 
 select 2,3,92 union all 
 select 2,4,68 union all 
 select 3,1,91 union all 
 select 3,2,47 union all 
 select 3,3,88 union all 
 select 3,4,56 union all 
 select 4,2,88 union all 
 select 4,3,90 union all 
 select 4,4,93 union all 
 select 5,1,46 union all 
 select 5,3,78 union all 
 select 5,4,53 union all 
 select 6,1,35 union all 
 select 6,2,68 union all 
 select 6,4,71

問題

1、查詢“001”課程比“002”課程成績(jī)高的所有學(xué)生的學(xué)號(hào)； 
  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、查詢平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)； 
    select S#,avg(score) 
    from sc 
    group by S# having avg(score) >60;3、查詢所有同學(xué)的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)； 
  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#,Sname4、查詢姓“李”的老師的個(gè)數(shù)； 
  select count(distinct(Tname)) 
  from Teacher 
  where Tname like '李%';5、查詢沒學(xué)過“葉平”老師課的同學(xué)的學(xué)號(hào)、姓名； 
    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、查詢學(xué)過“001”并且也學(xué)過編號(hào)“002”課程的同學(xué)的學(xué)號(hào)、姓名； 
  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、查詢學(xué)過“葉平”老師所教的所有課的同學(xué)的學(xué)號(hào)、姓名； 
  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、查詢課程編號(hào)“002”的成績(jī)比課程編號(hào)“001”課程低的所有同學(xué)的學(xué)號(hào)、姓名； 
  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、查詢所有課程成績(jī)小于60分的同學(xué)的學(xué)號(hào)、姓名； 
  select S#,Sname 
  from Student 
  where S# not in (select S.S# from Student AS S,SC where S.S#=SC.S# and score>60);10、查詢沒有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名； 
    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、查詢至少有一門課與學(xué)號(hào)為“1001”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號(hào)和姓名； 
    select distinct S#,Sname from Student,SC where Student.S#=SC.S# and SC.C# in (select C# from SC where S#='1001');12、查詢至少學(xué)過學(xué)號(hào)為“001”同學(xué)所有一門課的其他同學(xué)學(xué)號(hào)和姓名； 
    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”表中“葉平”老師教的課的成績(jī)都更改為此課程的平均成績(jī)； 
    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”號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)學(xué)號(hào)和姓名； 
    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、刪除學(xué)習(xí)“葉平”老師課的SC表記錄； 
    Delect SC 
    from course ,Teacher  
    where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='葉平';16、向SC表中插入一些記錄，這些記錄要求符合以下條件：沒有上過編號(hào)“003”課程的同學(xué)學(xué)號(hào)、2、 
    號(hào)課的平均成績(jī)； 
    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、按平均成績(jī)從高到低顯示所有學(xué)生的“數(shù)據(jù)庫”、“企業(yè)管理”、“英語”三門的課程成績(jī)，按如下形式顯示： 學(xué)生ID,,數(shù)據(jù)庫,企業(yè)管理,英語,有效課程數(shù),有效平均分 
    SELECT S# as 學(xué)生ID 
        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 數(shù)據(jù)庫 
        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企業(yè)管理 
        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英語 
        ,COUNT(*) AS 有效課程數(shù), AVG(t.score) AS 平均成績(jī) 
    FROM SC AS t 
    GROUP BY S# 
    ORDER BY avg(t.score)  

18、查詢各科成績(jī)最高和最低的分：以如下形式顯示：課程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# 
                    ); 
自己寫的:select c# ,max(score)as 最高分 ,min(score) as 最低分 from dbo.sc  group by c#

19、按各科平均成績(jī)從低到高和及格率的百分?jǐn)?shù)從高到低順序 
    SELECT t.C# AS 課程號(hào),max(course.Cname)AS 課程名,isnull(AVG(score),0) AS 平均成績(jī) 
        ,100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分?jǐn)?shù) 
    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、查詢?nèi)缦抡n程平均成績(jī)和及格率的百分?jǐn)?shù)(用"1行"顯示): 企業(yè)管理（001），馬克思（002），OO&UML （003），數(shù)據(jù)庫（004） 
    SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企業(yè)管理平均分 
        ,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 企業(yè)管理及格百分?jǐn)?shù) 
        ,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 馬克思及格百分?jǐn)?shù) 
        ,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及格百分?jǐn)?shù) 
        ,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 數(shù)據(jù)庫平均分 
        ,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 數(shù)據(jù)庫及格百分?jǐn)?shù) 
  FROM SC

21、查詢不同老師所教不同課程平均分從高到低顯示 
 SELECT max(Z.T#) AS 教師ID,MAX(Z.Tname) AS 教師姓名,C.C# AS 課程ID,MAX(C.Cname) AS 課程名稱,AVG(Score) AS 平均成績(jī) 
   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、查詢?nèi)缦抡n程成績(jī)第 3 名到第 6 名的學(xué)生成績(jī)單：企業(yè)管理（001），馬克思（002），UML （003），數(shù)據(jù)庫（004） 
   [學(xué)生ID],[學(xué)生姓名],企業(yè)管理,馬克思,UML,數(shù)據(jù)庫,平均成績(jī) 
   SELECT  DISTINCT top 3 
     SC.S# As 學(xué)生學(xué)號(hào), 
       Student.Sname AS 學(xué)生姓名 , 
     T1.score AS 企業(yè)管理, 
     T2.score AS 馬克思, 
     T3.score AS UML, 
     T4.score AS 數(shù)據(jù)庫, 
     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、統(tǒng)計(jì)列印各科成績(jī),各分?jǐn)?shù)段人數(shù):課程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、查詢學(xué)生平均成績(jī)及其名次 
     SELECT 1+(SELECT COUNT( distinct 平均成績(jī)) 
             FROM (SELECT S#,AVG(score) AS 平均成績(jī) 
                     FROM SC 
                 GROUP BY S# 
                 ) AS T1 
           WHERE 平均成績(jī) > T2.平均成績(jī)) as 名次, 
     S# as 學(xué)生學(xué)號(hào),平均成績(jī) 
   FROM (SELECT S#,AVG(score) 平均成績(jī) 
           FROM SC 
       GROUP BY S# 
       ) AS T2 
   ORDER BY 平均成績(jī) desc; 
 
25、查詢各科成績(jī)前三名的記錄:(不考慮成績(jī)并列情況) 
     SELECT t1.S# as 學(xué)生ID,t1.C# as 課程ID,Score as 分?jǐn)?shù) 
     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、查詢每門課程被選修的學(xué)生數(shù) 
 select c#,count(S#) from sc group by C#; 
27、查詢出只選修了一門課程的全部學(xué)生的學(xué)號(hào)和姓名 
 select SC.S#,Student.Sname,count(C#) AS 選課數(shù) 
 from SC ,Student 
 where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1; 
28、查詢男生、女生人數(shù) 
   Select count(Ssex) as 男生人數(shù) from Student group by Ssex having Ssex='男'; 
   Select count(Ssex) as 女生人數(shù) from Student group by Ssex having Ssex='女'； 
29、查詢姓“張”的學(xué)生名單 
   SELECT Sname FROM Student WHERE Sname like '張%'; 
30、查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù) 
 select Sname,count(*) from Student group by Sname having  count(*)>1;; 
31、1981年出生的學(xué)生名單(注：Student表中Sage列的類型是datetime) 
   select Sname,  CONVERT(char (11),DATEPART(year,Sage)) as age 
   from student 
   where  CONVERT(char(11),DATEPART(year,Sage))='1981'; 
32、查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)升序排列，平均成績(jī)相同時(shí)，按課程號(hào)降序排列 
   Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ; 
33、查詢平均成績(jī)大于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī) 
   select Sname,SC.S# ,avg(score) 
   from Student,SC 
   where Student.S#=SC.S# group by SC.S#,Sname having    avg(score)>85; 
34、查詢課程名稱為“數(shù)據(jù)庫”，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù) 
   Select Sname,isnull(score,0) 
   from Student,SC,Course 
   where SC.S#=Student.S# and SC.C#=Course.C# and  Course.Cname='數(shù)據(jù)庫'and score <60; 
35、查詢所有學(xué)生的選課情況； 
   SELECT SC.S#,SC.C#,Sname,Cname 
   FROM SC,Student,Course 
   where SC.S#=Student.S# and SC.C#=Course.C# ; 
36、查詢?nèi)魏我婚T課程成績(jī)?cè)?0分以上的姓名、課程名稱和分?jǐn)?shù)； 
   SELECT  distinct student.S#,student.Sname,SC.C#,SC.score 
   FROM student,Sc 
   WHERE SC.score>=70 AND SC.S#=student.S#; 
37、查詢不及格的課程，并按課程號(hào)從大到小排列 
   select c# from sc where scor e <60 order by C# ; 
38、查詢課程編號(hào)為003且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名； 
   select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#='003'; 
39、求選了課程的學(xué)生人數(shù) 
   select count(*) from sc; 
40、查詢選修“葉平”老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生姓名及其成績(jī) 
   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、查詢各個(gè)課程及相應(yīng)的選修人數(shù) 
   select count(*) from sc group by C#; 
42、查詢不同課程成績(jī)相同的學(xué)生的學(xué)號(hào)、課程號(hào)、學(xué)生成績(jī) 
 select distinct  A.S#,B.score from SC A  ,SC B where A.Score=B.Score and A.C# <>B.C# ; 
43、查詢每門功成績(jī)最好的前兩名 
   SELECT t1.S# as 學(xué)生ID,t1.C# as 課程ID,Score as 分?jǐn)?shù) 
     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、統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)（超過10人的課程才統(tǒng)計(jì)）。要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列  
   select  C# as 課程號(hào),count(*) as 人數(shù) 
   from  sc  
   group  by  C# 
   order  by  count(*) desc,c#  
45、檢索至少選修兩門課程的學(xué)生學(xué)號(hào) 
   select  S#  
   from  sc  
   group  by  s# 
   having  count(*)  >  =  2 46、查詢?nèi)繉W(xué)生都選修的課程的課程號(hào)和課程名 
   select  C#,Cname  
   from  Course  
   where  C#  in  (select  c#  from  sc group  by  c#)  
47、查詢沒學(xué)過“葉平”老師講授的任一門課程的學(xué)生姓名 
   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、查詢兩門以上不及格課程的同學(xué)的學(xué)號(hào)及其平均成績(jī) 
   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”課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的同學(xué)學(xué)號(hào) 
   select S# from SC where C#='004'and score <60 order by score desc; 
50、刪除“002”同學(xué)的“001”課程的成績(jī) 
delete from Sc where S#='001'and C#='001'; 

分享文章：數(shù)據(jù)庫SQL基本功練習(xí)
文章來源：http://bm7419.com/article10/jcicgo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供手機(jī)網(wǎng)站建設(shè)、網(wǎng)站設(shè)計(jì)公司、云服務(wù)器、網(wǎng)站維護(hù)、品牌網(wǎng)站設(shè)計(jì)、網(wǎng)站制作

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)