MySql经典语句练习50题 ---- 1 ~ 9题

目录

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数： 

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数：

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩：

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)：

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩：

6、查询"李"姓老师的数量：

7、查询学过"张三"老师授课的同学的信息：

8、查询没学过"张三"老师授课的同学的信息：

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息：

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数： 

SELECT student.*, sc1.s_score score1, sc2.s_score score2
FROM student, score sc1, score sc2
WHERE student.s_id = sc1.s_id AND sc1.s_id = sc2.s_id AND sc1.c_id = '01' AND sc2.c_id = '02' AND sc1.s_score > sc2.s_score;

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数：

SELECT student.*, sc1.s_score score1, sc2.s_score score2
FROM student, score sc1, score sc2
WHERE student.s_id = sc1.s_id AND sc1.s_id = sc2.s_id AND sc1.c_id = '01' AND sc2.c_id = '02' AND sc1.s_score < sc2.s_score;

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩：

SELECT student.s_id, s_name, avg(s_score) avg
FROM student, score
WHERE student.s_id = score.s_id
GROUP BY student.s_id
HAVING AVG(s_score) >= 60;

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)：

SELECT student.s_id, s_name, AVG(s_score) avg
FROM student LEFT JOIN score ON (student.s_id = score.s_id)
GROUP BY student.s_id
HAVING AVG(s_score) < 60 OR AVG(s_score) IS NULL;

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩：

SELECT student.s_id, s_name, count(c_id) number, sum(s_score) sum
FROM student, score
WHERE student.s_id = score.s_id
GROUP BY student.s_id;

6、查询"李"姓老师的数量：

SELECT count(t_name) number
FROM teacher
WHERE t_name LIKE '李%';

7、查询学过"张三"老师授课的同学的信息：

SELECT *
FROM student
WHERE EXISTS (SELECT *
              FROM score, teacher
			  WHERE student.s_id = score.s_id AND c_id = t_id and t_name = '张三');

8、查询没学过"张三"老师授课的同学的信息：

SELECT *
FROM student
WHERE NOT EXISTS (SELECT *
				  FROM score, teacher
				  WHERE student.s_id = score.s_id AND c_id = t_id and t_name = '张三');

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息：

SELECT student.*
FROM student, score sc1, score sc2
WHERE student.s_id = sc1.s_id and student.s_id = sc2.s_id and sc1.c_id = '01' and sc2.c_id = '02';