跟着大佬练习SQL

本文参考原博《走向面试之数据库基础：一、你必知必会的SQL语句练习-Part 1》

1. 建表写入数据

。。。已经打包好了看这里：等更新

2. 开始练习

• 查询“001”课程比“002”课程成绩高的所有学生的学号；

SELECT
    a.Sno 
FROM
    ( SELECT Sno, score FROM score WHERE Cno = '001' ) AS a,
    ( SELECT Sno, score FROM score WHERE Cno = '002' ) AS b 
WHERE
    a.Sno = b.Sno 
    AND a.score > b.score

结果：

• 查询平均成绩大于60分的同学的学号和平均成绩

SELECT
    Sno,
    avg( score ) 
FROM
    score 
GROUP BY
    Sno 
HAVING
    avg( score ) > 60

结果：

• 查询所有同学的学号、姓名、选课数、总成绩；
  （这道题有点难...）

SELECT
    s.Sno,
    s.Sname,
    count( sc.Cno ) AS coursenum,
    sum( sc.score ) 
FROM
    student AS s
    LEFT OUTER JOIN score AS sc ON s.Sno = sc.Sno 
GROUP BY
    s.Sno,
    s.Sname 
ORDER BY
    s.Sno

结果：

• 查询姓“叶”的老师的个数

SELECT
    count( DISTINCT Tname ) 
FROM
    teacher 
WHERE
    Tname LIKE '叶%'

结果：

• 查询没学过“叶平”老师课的同学的学号、姓名
  这题也有点难

SELECT
    s.Sno,
    s.Sname 
FROM
    student AS s 
WHERE
    s.Sno NOT IN (
    SELECT DISTINCT
        ( sc.Sno ) 
    FROM
        score AS sc,
        course AS c,
        teacher AS t 
    WHERE
        sc.Cno = c.Cno 
        AND c.Tno = t.Tno 
    AND t.Tname = '叶平' 
    )

结果：

• 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
  mysql目前不支持intersect,取交集可以用inner join... on

SELECT
    u1.* 
FROM
    (
    SELECT
        s.Sno,
        s.Sname 
    FROM
        student AS s,
        score AS sc 
    WHERE
        ( s.Sno = sc.Sno AND sc.Cno = '001' ) 
    ) AS u1
    INNER JOIN (
    SELECT
        s.Sno,
        s.Sname 
    FROM
        student AS s,
        score AS sc 
    WHERE
        ( s.Sno = sc.Sno AND sc.Cno = '002' ) 
    ) AS u2 ON u1.Sno = u2.Sno

结果：

或者：

SELECT
    sc.Sno,
    s.Sname 
FROM
    score sc,
    student s 
WHERE
    sc.Sno = s.Sno 
    AND sc.Cno IN ( 1, 2 ) 
GROUP BY
    sc.Sno 
HAVING
    COUNT( * ) = 2

结果一样

但不能这样（这样的结果是错的）

SELECT
    s.Sno,
    s.Sname 
FROM
    student AS s,
    score AS sc 
WHERE
    s.Sno = sc.Sno 
    AND sc.Cno =1 
    AND EXISTS ( SELECT * FROM student AS s, score AS sc WHERE s.Sno = sc.Sno AND sc.Cno = 2 )

• 查询学过“叶平”老师所教的所有课的同学的学号、姓名

SELECT
    s.Sno,
    s.Sname 
FROM
    student s,
    score sc,
    teacher t 
WHERE
    s.Sno = sc.Sno 
    AND sc.Cno = t.Tno 
    AND t.Tname = '叶平' 
GROUP BY
    s.Sno 
HAVING
    count( * ) = ( SELECT count( * ) FROM teacher t WHERE t.Tname = '叶平' )

结果：

有这么多人都修完了

• 查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名

SELECT
    s.Sno,
    s.Sname 
FROM
    student s,
    ( SELECT sc1.Sno, sc1.score FROM score sc1 WHERE sc1.Cno = 1 ) a,
    ( SELECT sc2.Sno, sc2.score FROM score sc2 WHERE sc2.Cno = 2 ) b 
WHERE
    s.Sno = a.Sno 
    AND s.Sno = b.Sno 
    AND a.score > b.score

结果：

只有张三....

• 查询有课程成绩小于60分的同学的学号、姓名

SELECT
    s.Sno,
    s.Sname 
FROM
    student s,
    score sc 
WHERE
    s.Sno = sc.Sno 
    AND sc.score < 60 
GROUP BY
    s.Sno

结果：

• 查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名

SELECT DISTINCT
    ( s.Sno ),
    s.Sname 
FROM
    student s,
    score sc,
    ( SELECT Cno FROM score sc WHERE sc.Sno = 1 ) a 
WHERE
    sc.Cno = a.Cno 
    AND sc.Sno = s.Sno
 /* GROUP BY s.Sno */
--用 DISTINCT 就不用 GROUP BY 了

或者

SELECT DISTINCT
    ( s.Sno ),
    s.Sname 
FROM
    Student s,
    score sc 
WHERE
    s.Sno = sc.Sno 
    AND sc.Cno IN ( SELECT DISTINCT ( sc2.Cno ) FROM score sc2 WHERE sc2.Sno = 1 ) 
ORDER BY
    s.Sno ASC

结果：

• 查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名
  这题难啊

SELECT
    s.Sno,
    s.Sname 
FROM
    Student s 
WHERE
    s.Sno != 2 
    AND s.Sno IN (
    SELECT DISTINCT
        ( Sno ) 
    FROM
        score 
    WHERE
        Cno IN ( SELECT Cno FROM score WHERE Sno = 2 ) 
    GROUP BY
        Sno 
    HAVING
    COUNT( DISTINCT Cno ) = ( SELECT COUNT( DISTINCT Cno ) FROM score WHERE Sno = 2 ) 
    )

结果：

• 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT
    sc.Cno AS '课程号',
    max( sc.score ) AS '最高分',
    min( sc.score ) AS '最低分' 
FROM
    score sc 
GROUP BY
    sc.Cno

结果：

• 查询各科成绩前三名的记录:(不考虑成绩并列情况)
  这题不会...使用原帖的方法时报错了..
  百度了一下，找到了思路：

-- 如果比这个大的个数小于三个，说明这个数肯定排前三，同时拿出数值。
SELECT
    sc.Cno,
    c.Cname,
    sc.Sno,
    s.Sname,
    sc.score 
FROM
    Student s,
    score sc,
    Course c 
WHERE
    s.Sno = sc.Sno 
    AND sc.Cno = c.Cno 
    AND ( SELECT count( * ) FROM score sc2 WHERE sc2.Cno = sc.Cno AND sc2.score >= sc.score ) <= 3 
ORDER BY
    sc.Cno,
    sc.score DESC

注：这是不考虑成绩并列的情况，假如有成绩并列的....百度吧

结果:

• 查询平均成绩大于70的所有学生的学号、姓名和平均成绩

SELECT
    s.Sno,
    s.Sname,
    s1.stuavg 
FROM
    student s,
    ( SELECT Sno, avg( score ) AS stuavg FROM score GROUP BY score.Sno ) AS s1 
WHERE
    s.Sno = s1.Sno 
    AND s1.stuavg > 70

结果：

结束

sql语句就练到这里了，其实还有许多我都没有练，大家可以去看原贴....