SQL 面试题练习

学生表 student

s_id s_name s_birth        s_sex

01    赵雷    1990-01-01    男
02    钱电    1990-12-21    男
03    孙风    1990-05-20    男
04    李云    1990-08-06    男
05    周梅    1991-12-01    女
06    吴兰    1992-03-01    女
07    郑竹    1989-07-01    女
08    王菊    1990-01-20    女

学生课程成绩表 score

s_id c_id s_score

01    01    80
01    02    90
01    03    99
02    01    70
02    02    60
02    03    80
03    01    80
03    02    80
03    03    80
04    01    50
04    02    30
04    03    20
05    01    76
05    02    87
06    01    31
06    03    34
07    02    89
07    03    98

课程表 course

c_id c_name t_id

01    语文    02
02    数学    01
03    英语    03 

教师表 teacher

t_id t_name

01    张三
02    李四
03    王五 

练习题

查询课程编号为01的课程比02的课程成绩高的所有学生的学号

-- 方式一
SELECT t1.s_id as '学号',t1.s_score as '课程01成绩',t2.s_score as '课程02成绩'
from
	(SELECT s_id,s_score -- 课程01的成绩
	FROM score 
	WHERE c_id='01')as T1, -- 笛卡尔集
	(SELECT s_id,s_score -- 课程02的成绩
	FROM score 
	WHERE c_id='02') AS T2 
where t1.s_id = t2.s_id --条件
and t1.s_score > t2.s_score


-- 方式二
SELECT st.s_id,st.s_name,a.s_score as '课程01成绩',b.s_score as '课程02成绩'
FROM student st
LEFT JOIN (SELECT s_id,s_score FROM score WHERE c_id='01') a ON st.s_id=a.s_id
LEFT JOIN (SELECT s_id,s_score FROM score WHERE c_id='02') b ON st.s_id=b.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 student.s_id as '学号',student.s_name as '姓名',t1.ct as '所选课程数',t1.ss as '总分'
from student 
left join
(SELECT s_id,count(c_id) as ct ,sum(s_score) as ss
from score
GROUP BY s_id) as t1 on student.s_id = t1.s_id;


-- 方式二
SELECT st.s_id,st.s_name,sc.s_id,COUNT(sc.c_id),SUM(sc.s_score)
FROM student st
LEFT JOIN score sc
ON st.s_id=sc.s_id
GROUP BY st.s_id;

查询没学过“张三”老师课程的学生的学号、姓名 

SELECT student.s_id,student.s_name
from student
where student.s_id not in(
SELECT DISTINCT score.s_id from score
where score.c_id = (
	SELECT course.c_id
	from teacher,course 
	where teacher.t_id = course.t_id
	and teacher.t_name = '张三'
	)
)

查询学过“张三”老师课程的学生的学号、姓名（重点）

查询学过编号为’01’课程并且也学过’02’课程的学生的学号、姓名

-- 方式一 学过课程01 与学过课程02做一个内连接 即交集
SELECT sid,sna
from 
    (SELECT st.s_id as sid,st.s_name as sna
    from student st
    left join score on st.s_id = score.s_id
    where score.c_id = '01') as a
inner join
    (SELECT st.s_id as sid2,st.s_name as sna2
    from student st
    left join score on st.s_id = score.s_id
    where score.c_id = '02') as b
on a.sid = b.sid2

-- 方式二 学过01课程的学生id 与 学过02课程的学生id 重复即两门都学过
SELECT sid,sna
from (
	SELECT st.s_id as sid,st.s_name as sna
	from student st
	left join score on st.s_id = score.s_id
where score.c_id = '01') as a
where a.sid in (
	SELECT st.s_id as sid2
	from student st
	left join score on st.s_id = score.s_id
	where score.c_id = '02' 
)


-- 方式三 先找出两门课程中的交叉学生id，再用这个id和学生表联查出学生姓名等详细信息
SELECT *
from (
	SELECT *
	from (
		SELECT sc.s_id as scid1
		from score sc
		where sc.c_id = '01')as a
	where a.scid1 in (
		SELECT sc.s_id as scid2
		from score sc
		where sc.c_id = '02'
	)
) as b left join student
on student.s_id = b.scid1

查询学过编号为’01’课程但没学过’02’课程的学生的学号、姓名

-- 思路 找出学01课程的sid 去 not in 学02课程的sid
select *
from(
	SELECT st.s_id as sid,st.s_name as sna
	from student st
	left join score on st.s_id = score.s_id
	where score.c_id = '01') as a
where a.sid not in (
	SELECT st.s_id as sid2
	from student st
	left join score on st.s_id = score.s_id
	where score.c_id = '02' 
)