title: SQL练习题
time:2019年8月15日23:28:08
tags: SQL
SQL练习题
- 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号(重点)
SELECT
a.s_id "s_id",
c.s_name "name",
a.s_score "01",
b.s_score "02"
FROM
( SELECT s_id, s_score FROM score WHERE c_id = '01' ) AS a
INNER JOIN ( SELECT s_id, s_score FROM score WHERE c_id = '02' ) AS b ON a.s_id = b.s_id
INNER JOIN student AS c ON c.s_id = a.s_id
WHERE
a.s_score > b.s_score
- 查询平均成绩大于60分的学生的学号和平均成绩(简单,第二道重点)
SELECT
s_id,
avg( s_score )
FROM
score
GROUP BY
s_id
HAVING
avg( s_score ) > 60
- 查询所有学生的学号、姓名、选课数、总成绩(不重要)
SELECT
a.s_id "学号",
a.s_name "姓名",
count( b.c_id ) "选课数",
sum( CASE WHEN b.s_score IS NULL THEN 0 ELSE b.s_score END ) "总成绩"
FROM
student AS a
LEFT JOIN score AS b ON a.s_id = b.s_id
GROUP BY
a.s_id,
a.s_name
- 查询姓“张”的老师的个数(不重要)
SELECT
count( t_id )
FROM
teacher
WHERE
t_name LIKE '张%'
- 查询姓“张”的老师的个数(去重)(不重要)
SELECT
count( DISTINCT t_id )
FROM
teacher
WHERE
t_name LIKE '张%'
- 查询没学过“张三”老师课的学生的学号、姓名(重点)
-
不能直接select * from score where c_id = ‘02’ 这是相当于把表中有02的记录删掉了,不代表没有学过
SELECT
s_id,
s_name
FROM
student
WHERE
s_id NOT IN (
SELECT
s_id
FROM
score
WHERE
c_id = ( SELECT c_id FROM course WHERE t_id = ( SELECT t_id FROM teacher WHERE t_name = ‘张三’ ) )
)
-
SELECT
s_id,
s_name
FROM
student
WHERE
s_id NOT IN (
SELECT
s_id
FROM
score AS s
INNER JOIN course AS c ON s.c_id = c.c_id
INNER JOIN teacher AS t ON t.t_id = c.t_id
WHERE
t_name = '张三'
)
- 查询学过“张三”老师所教的所有课的同学的学号、姓名(重点)
SELECT
s_id,
s_name
FROM
student
WHERE
s_id IN (
SELECT
s_id
FROM
score AS s
INNER JOIN course AS c ON s.c_id = c.c_id
INNER JOIN teacher AS t ON t.t_id = c.t_id
WHERE
t_name = '张三'
)
- 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名(重点)
SELECT
s_id,
s_name
FROM
student
WHERE
s_id IN (
SELECT
a.s_id
FROM
( SELECT * FROM score WHERE c_id = '01' ) AS a
INNER JOIN ( SELECT * FROM score WHERE c_id = '02' ) AS b
WHERE
a.s_id = b.s_id
)
- 查询课程编号为“02”的总成绩(不重点)
SELECT
sum( s_score )
FROM
score
WHERE
c_id = '02'
- 查询所有课程成绩小于60分的学生的学号、姓名
SELECT
a.s_id
FROM
( SELECT s_id, count( s_id ) AS cnt FROM score WHERE s_score < 60 GROUP BY s_id ) AS a
INNER JOIN ( SELECT s_id, count( s_id ) AS cnt FROM score GROUP BY s_id ) AS b ON a.s_id = b.s_id
WHERE
a.cnt = b.cnt
- 查询没有学全所有课的学生的学号、姓名(重点)
– SELECT s_id from
– (select s_id , count(c_id) as cnt from score group by s_id ) as a
– inner join
– (select count(c_id) as cnt from course ) as b on a.cnt != b.cnt
SELECT
st.s_id
FROM
student AS st
LEFT JOIN score AS cs ON st.s_id = cs.s_id
GROUP BY
cs.s_id
HAVING
count( DISTINCT cs.c_id ) < ( SELECT count( DISTINCT c_id ) FROM course )