目录
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数:
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数:
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩:
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的):
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩:
6、查询"李"姓老师的数量:
7、查询学过"张三"老师授课的同学的信息:
8、查询没学过"张三"老师授课的同学的信息:文章来源:https://www.toymoban.com/news/detail-419545.html
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息:文章来源地址https://www.toymoban.com/news/detail-419545.html
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';
到了这里,关于MySql经典语句练习50题 ---- 1 ~ 9题的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!