恒康安吉 發表於 2023-9-14 00:00:00

50条SQL查询技巧、查询语句示例

<p>
        Student(S#,Sname,Sage,Ssex) 学生表<br>
        Course(C#,Cname,T#) 课程表<br>
        SC(S#,C#,score) 成绩表<br>
        Teacher(T#,Tname) 教师表<br>
         <br>
        问题：<br><strong>1、查询“001”课程比“002”课程成绩高的所有学生的学号；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code16419">
        <br>
        select a.S# from (select s#,score from SC where C#='001') a,(select s#,score<br>
        from SC where C#='002') b<br>
        where a.score&gt;b.score and a.s#=b.s#;</div>
<p>
        <br><strong>2、查询平均成绩大于60分的同学的学号和平均成绩；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code8133">
        <br>
        select S#,avg(score)<br>
        from sc<br>
        group by S# having avg(score) &gt;60;</div>
<p>
        <br><strong>3、查询所有同学的学号、姓名、选课数、总成绩；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code86107">
        <br>
        select Student.S#,Student.Sname,count(SC.C#),sum(score)<br>
        from Student left Outer join SC on Student.S#=SC.S#<br>
        group by Student.S#,Sname</div>
<p>
        <br><strong>4、查询姓“李”的老师的个数；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code25628">
        <br>
        select count(distinct(Tname))<br>
        from Teacher<br>
        where Tname like '李%';</div>
<p>
        <br><strong>5、查询没学过“叶平”老师课的同学的学号、姓名；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code7065">
        <br>
        select Student.S#,Student.Sname<br>
        from Student<br>
        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='叶平');</div>
<p>
        <br><strong>6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code89311">
        <br>
        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');</div>
<p>
        <br><strong>7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code16015">
        <br>
        select S#,Sname<br>
        from Student<br>
        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='叶平'));</div>
<p>
        <br><strong>8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code35905">
        <br>
        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<br>
        from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 &lt;score;</div>
<p>
        <br><strong>9、查询所有课程成绩小于60分的同学的学号、姓名；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code2792">
        <br>
        select S#,Sname<br>
        from Student<br>
        where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score&gt;60);</div>
<p>
        <br><strong>10、查询没有学全所有课的同学的学号、姓名；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code75571">
        <br>
        select Student.S#,Student.Sname<br>
        from Student,SC<br>
        where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) &lt;(select count(C#) from Course);</div>
<p>
        <br><strong>11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code51004">
        <br>
        select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';</div>
<p>
        <br><strong>12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code24705">
        <br>
        select distinct SC.S#,Sname<br>
        from Student,SC<br>
        where Student.S#=SC.S# and C# in (select C# from SC where S#='001');</div>
<p>
        <br><strong>13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code77044">
        <br>
        update SC set score=(select avg(SC_2.score)<br>
        from SC SC_2<br>
        where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');</div>
<p>
        <br><strong>14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code20028">
        <br>
        select S# from SC where C# in (select C# from SC where S#='1002')<br>
        group by S# having count(*)=(select count(*) from SC where S#='1002');</div>
<p>
        <br><strong>15、删除学习“叶平”老师课的SC表记录；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code44191">
        <br>
        Delect SC<br>
        from course ,Teacher<br>
        where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';</div>
<p>
        <br><strong>16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、<br>
        号课的平均成绩；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code71703">
        <br>
        Insert SC select S#,'002',(Select avg(score)<br>
        from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');</div>
<p>
        <br><strong>17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code36050">
        <br>
        SELECT S# as 学生ID<br>
        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库<br>
        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理<br>
        ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语<br>
        ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩<br>
        FROM SC AS t<br>
        GROUP BY S#<br>
        ORDER BY avg(t.score)</div>
<p>
        <br><strong>18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code69567">
        <br>
        SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分<br>
        FROM SC L ,SC AS R<br>
        WHERE L.C# = R.C# and<br>
        L.score = (SELECT MAX(IL.score)<br>
        FROM SC AS IL,Student AS IM<br>
        WHERE L.C# = IL.C# and IM.S#=IL.S#<br>
        GROUP BY IL.C#)<br>
        AND<br>
        R.Score = (SELECT MIN(IR.score)<br>
        FROM SC AS IR<br>
        WHERE R.C# = IR.C#<br>
        GROUP BY IR.C#<br>
        );</div>
<p>
        <br><strong>19、按各科平均成绩从低到高和及格率的百分数从高到低顺序</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code42459">
        <br>
        SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩<br>
        ,100 * SUM(CASE WHEN isnull(score,0)&gt;=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数<br>
        FROM SC T,Course<br>
        where t.C#=course.C#<br>
        GROUP BY t.C#<br>
        ORDER BY 100 * SUM(CASE WHEN isnull(score,0)&gt;=60 THEN 1 ELSE 0 END)/COUNT(*) DESC</div>
<p>
        <br><strong>20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&amp;UML （003），数据库（004）</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code50340">
        <br>
        SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分<br>
        ,100 * SUM(CASE WHEN C# = '001' AND score &gt;= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数<br>
        ,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分<br>
        ,100 * SUM(CASE WHEN C# = '002' AND score &gt;= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数<br>
        ,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分<br>
        ,100 * SUM(CASE WHEN C# = '003' AND score &gt;= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数<br>
        ,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分<br>
        ,100 * SUM(CASE WHEN C# = '004' AND score &gt;= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数<br>
        FROM SC</div>
<p>
        <br><strong>21、查询不同老师所教不同课程平均分从高到低显示</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code138">
        <br>
        SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ＩＤ,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩<br>
        FROM SC AS T,Course AS C ,Teacher AS Z<br>
        where T.C#=C.C# and C.T#=Z.T#<br>
        GROUP BY C.C#<br>
        ORDER BY AVG(Score) DESC</div>
<p>
         </p>
<p>
        <strong>22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）<br>
        [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code77303">
        <br>
        SELECT DISTINCT top 3<br>
        SC.S# As 学生学号,<br>
        Student.Sname AS 学生姓名 ,<br>
        T1.score AS 企业管理,<br>
        T2.score AS 马克思,<br>
        T3.score AS UML,<br>
        T4.score AS 数据库,<br>
        ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分<br>
        FROM Student,SC LEFT JOIN SC AS T1<br>
        ON SC.S# = T1.S# AND T1.C# = '001'<br>
        LEFT JOIN SC AS T2<br>
        ON SC.S# = T2.S# AND T2.C# = '002'<br>
        LEFT JOIN SC AS T3<br>
        ON SC.S# = T3.S# AND T3.C# = '003'<br>
        LEFT JOIN SC AS T4<br>
        ON SC.S# = T4.S# AND T4.C# = '004'<br>
        WHERE student.S#=SC.S# and<br>
        ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)<br>
        NOT IN<br>
        (SELECT<br>
        DISTINCT<br>
        TOP 15 WITH TIES<br>
        ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)<br>
        FROM sc<br>
        LEFT JOIN sc AS T1<br>
        ON sc.S# = T1.S# AND T1.C# = 'k1'<br>
        LEFT JOIN sc AS T2<br>
        ON sc.S# = T2.S# AND T2.C# = 'k2'<br>
        LEFT JOIN sc AS T3<br>
        ON sc.S# = T3.S# AND T3.C# = 'k3'<br>
        LEFT JOIN sc AS T4<br>
        ON sc.S# = T4.S# AND T4.C# = 'k4'<br>
        ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);</div>
<p>
        <br><strong>23、统计列印各科成绩,各分数段人数:课程ID,课程名称,,,,[ &lt;60]</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code72367">
        <br>
        SELECT SC.C# as 课程ID, Cname as 课程名称<br>
        ,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS <br>
        ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS <br>
        ,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS <br>
        ,SUM(CASE WHEN score &lt; 60 THEN 1 ELSE 0 END) AS <br>
        FROM SC,Course<br>
        where SC.C#=Course.C#<br>
        GROUP BY SC.C#,Cname;</div>
<p>
        <br><strong>24、查询学生平均成绩及其名次</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code60617">
        <br>
        SELECT 1+(SELECT COUNT( distinct 平均成绩)<br>
        FROM (SELECT S#,AVG(score) AS 平均成绩<br>
        FROM SC<br>
        GROUP BY S#<br>
        ) AS T1<br>
        WHERE 平均成绩 &gt; T2.平均成绩) as 名次,<br>
        S# as 学生学号,平均成绩<br>
        FROM (SELECT S#,AVG(score) 平均成绩<br>
        FROM SC<br>
        GROUP BY S#<br>
        ) AS T2<br>
        ORDER BY 平均成绩 desc;</div>
<p>
         </p>
<p>
        <strong>25、查询各科成绩前三名的记录:(不考虑成绩并列情况)</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code95865">
        <br>
        SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数<br>
        FROM SC t1<br>
        WHERE score IN (SELECT TOP 3 score<br>
        FROM SC<br>
        WHERE t1.C#= C#<br>
        ORDER BY score DESC<br>
        )<br>
        ORDER BY t1.C#;</div>
<p>
        <br><strong>26、查询每门课程被选修的学生数</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code90120">
        <br>
        select c#,count(S#) from sc group by C#;</div>
<p>
        <br><strong>27、查询出只选修了一门课程的全部学生的学号和姓名</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code33914">
        <br>
        select SC.S#,Student.Sname,count(C#) AS 选课数<br>
        from SC ,Student<br>
        where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;</div>
<p>
        <br><strong>28、查询男生、女生人数</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code75975">
        <br>
        Select count(Ssex) as 男生人数 from Student group by Ssex having Ssex='男';<br>
        Select count(Ssex) as 女生人数 from Student group by Ssex having Ssex='女'；</div>
<p>
        <br><strong>29、查询姓“张”的学生名单</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code23232">
        <br>
        SELECT Sname FROM Student WHERE Sname like '张%';</div>
<p>
        <br><strong>30、查询同名同性学生名单，并统计同名人数</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code34577">
        <p>
                <br>
                select Sname,count(*) from Student group by Sname having count(*)&gt;1;</p>
        <p>
                 </p>
        <p>
                 </p>
</div>
<p>
        <br><strong>31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)</strong></p>
<p>
         </p>
<p>
         </p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code27100">
        <br>
        select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age<br>
        from student<br>
        where CONVERT(char(11),DATEPART(year,Sage))='1981';</div>
<p>
        <br><strong>32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code22973">
        <br>
        Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;</div>
<p>
        <br><strong>33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code29123">
        <br>
        select Sname,SC.S# ,avg(score)<br>
        from Student,SC<br>
        where Student.S#=SC.S# group by SC.S#,Sname having avg(score)&gt;85;</div>
<p>
        <br><strong>34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code57558">
        <br>
        Select Sname,isnull(score,0)<br>
        from Student,SC,Course<br>
        where SC.S#=Student.S# and SC.C#=Course.C# and Course.Cname='数据库'and score &lt;60;</div>
<p>
        <br><strong>35、查询所有学生的选课情况；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code98811">
        <br>
        SELECT SC.S#,SC.C#,Sname,Cname<br>
        FROM SC,Student,Course<br>
        where SC.S#=Student.S# and SC.C#=Course.C# ;</div>
<p>
        <br><strong>36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code1611">
        <br>
        SELECT distinct student.S#,student.Sname,SC.C#,SC.score<br>
        FROM student,Sc<br>
        WHERE SC.score&gt;=70 AND SC.S#=student.S#;</div>
<p>
        <br><strong>37、查询不及格的课程，并按课程号从大到小排列</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code19769">
        <br>
        select c# from sc where scor e &lt;60 order by C# ;</div>
<p>
        <br><strong>38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code38736">
        <br>
        select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score&gt;80 and C#='003';</div>
<p>
        <br><strong>39、求选了课程的学生人数</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code28719">
        <br>
        select count(*) from sc;</div>
<p>
        <br><strong>40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code85330">
        <br>
        select Student.Sname,score<br>
        from Student,SC,Course C,Teacher<br>
        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# );</div>
<p>
        <br><strong>41、查询各个课程及相应的选修人数</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code15496">
        <br>
        select count(*) from sc group by C#;</div>
<p>
        <br><strong>42、查询不同课程成绩相同的学生的学号、课程号、学生成绩</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code51554">
        <br>
        select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and A.C# &lt;&gt;B.C# ;</div>
<p>
        <br><strong>43、查询每门功成绩最好的前两名</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code90266">
        <br>
        SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数<br>
        FROM SC t1<br>
        WHERE score IN (SELECT TOP 2 score<br>
        FROM SC<br>
        WHERE t1.C#= C#<br>
        ORDER BY score DESC<br>
        )<br>
        ORDER BY t1.C#;</div>
<p>
        <br><strong>44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code27246">
        <br>
        select C# as 课程号,count(*) as 人数<br>
        from sc<br>
        group by C#<br>
        order by count(*) desc,c#</div>
<p>
        <br><strong>45、检索至少选修两门课程的学生学号</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code16305">
        <br>
        select S#<br>
        from sc<br>
        group by s#<br>
        having count(*) &gt; = 2</div>
<p>
        <br><strong>46、查询全部学生都选修的课程的课程号和课程名</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code96011">
        <br>
        select C#,Cname<br>
        from Course<br>
        where C# in (select c# from sc group by c#)</div>
<p>
        <br><strong>47、查询没学过“叶平”老师讲授的任一门课程的学生姓名</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code56894">
        <br>
        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='叶平');</div>
<p>
        <br><strong>48、查询两门以上不及格课程的同学的学号及其平均成绩</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code74244">
        <br>
        select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score &lt;60 group by S# having count(*)&gt;2)group by S#;</div>
<p>
        <br><strong>49、检索“004”课程分数小于60，按分数降序排列的同学学号</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code75312">
        <br>
        select S# from SC where C#='004'and score &lt;60 order by score desc;</div>
<p>
        <br><strong>50、删除“002”同学的“001”课程的成绩</strong></p>
<div class="codetitle">
        <span><u>复制代码</u></span> 代码如下:</div>
<div class="codebody" id="code72107">
        <br>
        delete from Sc where S#='001'and C#='001';</div>
頁: [1]
查看完整版本: 50条SQL查询技巧、查询语句示例