<rt id="bn8ez"></rt>
<label id="bn8ez"></label>

  • <span id="bn8ez"></span>

    <label id="bn8ez"><meter id="bn8ez"></meter></label>

    少年阿賓

    那些青春的歲月

      BlogJava :: 首頁 :: 聯系 :: 聚合  :: 管理
      500 Posts :: 0 Stories :: 135 Comments :: 0 Trackbacks

    Student(S#,Sname,Sage,Ssex) 學生表
    Course(C#,Cname,T#) 課程表
    SC(S#,C#,score) 成績表
    Teacher(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、查詢所有課程成績小于60分的同學的學號、姓名;
    select S#,Sname
    from Student
    where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

    9、查詢沒有學全所有課的同學的學號、姓名;
    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);

    10、查詢至少有一門課與學號為“1001”的同學所學相同的同學的學號和姓名;
    select S#,Sname
    from Student,SC
    where Student.S#=SC.S# and C# in (select C# from SC where S#='1001');

    11、刪除學習“葉平”老師課的SC表記錄;
    Delect SC
    from course ,Teacher
    where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='葉平';

    12、查詢各科成績最高和最低的分:以如下形式顯示:課程ID,最高分,最低分
    SELECT L.C# 課程ID,L.score 最高分,R.score 最低分
    FROM SC L ,SC R
    WHERE L.C# = R.C#
    and
    L.score = (SELECT MAX(IL.score)
    FROM SC IL,Student IM
    WHERE IL.C# = L.C# and IM.S#=IL.S#
    GROUP BY IL.C#)
    and
    R.Score = (SELECT MIN(IR.score)
    FROM SC IR
    WHERE IR.C# = R.C#
    GROUP BY IR.C# );

    13、查詢學生平均成績及其名次
    SELECT 1+(SELECT COUNT( distinct 平均成績)
    FROM (SELECT S#,AVG(score) 平均成績
    FROM SC
    GROUP BY S# ) T1
    WHERE 平均成績 > T2.平均成績) 名次, S# 學生學號,平均成績
    FROM (SELECT S#,AVG(score) 平均成績 FROM SC GROUP BY S# ) T2
    ORDER BY 平均成績 desc;

    14、查詢各科成績前三名的記錄:(不考慮成績并列情況)
    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#;

    15、查詢每門功成績最好的前兩名
    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#;

    補充:
    已經知道原表
    year salary
    ——————
    2000 1000
    2001 2000
    2002 3000
    2003 4000

    解:
    select b.year,sum(a.salary)
    from salary a,salary b
    where a.year<=b.year
    group by b.year
    order by b.year;

    在面試過程中多次碰到一道SQL查詢的題目,查詢A(ID,Name)表中第31至40條記錄,ID作為主鍵可能是不是連續增長的列,完整的查詢語句如下:
    方法一:
    select top 10 *
    from A
    where ID >(select max(ID) from (select top 30 ID from A order by ID ) T) order by ID
    方法二:
    select top 10 *
    from A
    where ID not In (select top 30 ID from A order by ID)
    order by ID

    posted on 2012-07-08 10:26 abin 閱讀(581) 評論(0)  編輯  收藏 所屬分類: Database
    主站蜘蛛池模板: 91精品免费不卡在线观看| 免费高清av一区二区三区| 亚洲成年人免费网站| 亚洲国产成人九九综合| 国产老女人精品免费视频| 一出一进一爽一粗一大视频免费的| 亚洲av日韩av永久无码电影| 国产在线精品免费aaa片| 亚洲中文字幕日本无线码| 亚洲精品电影在线| 久久最新免费视频| 国产啪亚洲国产精品无码| 亚洲成a人片在线观看老师| 国产又黄又爽又猛的免费视频播放 | 亚洲制服丝袜中文字幕| 亚洲一区免费在线观看| 亚洲一级在线观看| 9久9久女女免费精品视频在线观看| 久久国产免费观看精品3| 巨波霸乳在线永久免费视频| 7m凹凸精品分类大全免费| 99视频全部免费精品全部四虎 | 亚洲欧洲精品无码AV| 精品亚洲一区二区| 无码专区AAAAAA免费视频| 精品福利一区二区三区免费视频 | 一级中文字幕免费乱码专区 | 色婷婷精品免费视频| 曰批免费视频播放在线看片二| 久久亚洲精品成人AV| 亚洲AV成人一区二区三区AV| 亚洲国产天堂久久综合| 日韩一级在线播放免费观看| 无码专区永久免费AV网站| 国产成人免费高清激情明星| 无码成A毛片免费| 亚洲视频在线观看不卡| 亚洲最大在线观看| av无码免费一区二区三区| 免费在线观看h片| 亚洲AV日韩综合一区尤物|