SQL 入门必练50题

最近刚入门SQL的学习，特意找来了一套入门级别的sql50题，自己写的答案，还望大佬们批评指正哈！·~

相关表如下：

course:

score:

student:

teacher:

total:

-- 创建total表
create table total(
select a.s_id as s_id,a.s_name as s_name,a.s_age as s_age,a.s_sex as s_sex,
b.c_id as c_id,b.score as score,c.t_id as t_id,d.t_name as t_name
from student a
left join
score  b on a.s_id=b.s_id
left join
course c on b.c_id=c.c_id
left join
teacher d on c.t_id=d.t_id
);
select * from total;

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT a.s_id as s_id,score1,score2 FROM
(SELECT s_id,score as score1 from score where c_id='01') a
	INNER JOIN
(select s_id,score as score2 from score WHERE c_id='02') b
	on a.s_id=b.s_id
	where score1 > score2

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT a.s_id as s_id , score1, score2 from
(SELECT s_id ,score as score1 from score WHERE c_id='01') a
	INNER JOIN
(SELECT s_id ,score as score2 from score WHERE c_id='02') b
	on a.s_id=b.s_id
	WHERE score1 < score2

-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT s_name,score.s_id,AVG(score) as avg_score from score
	left JOIN student
	on student.s_id = score.s_id
	GROUP BY s_id
	HAVING AVG(score) >=60

-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
SELECT s_name,score.s_id,AVG(score) as avg_score from score
	left JOIN student
	on student.s_id = score.s_id
	GROUP BY s_id
	HAVING AVG(score) < 60

-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT s_id,s_name,COUNT(c_id) as c_id,SUM(score) from total
	GROUP BY s_id

-- 6、查询"李"姓老师的数量
SELECT count(*) from teacher
	WHERE t_name LIKE '李%'

-- 7、查询学过"张三"老师授课的同学的信息
select s_id,s_name,s_age from total
	WHERE t_name='张三'

-- 8、查询没学过"张三"老师授课的同学的信息
SELECT s_id from student WHERE s_id
	not in 
(
	select s_id fro