## 关联查询与聚合函数 1. 教语文的老师所带班级各多少学生? 2. 每门课的老师所带班级各多少学生? 3. 来自上海的学生的班主任都有谁? 4. 来自南京的学生共多少人 5. 来自武汉的男同学和女同学分别多少人? 6. 每个城市的学生各多少人 7. 高于平均工资的老师所带的班级分别多少学生? 8. 每个老师班里各多少来自郑州的学生? ## 多对多查询 1. 查看1年级1班所有同学的语文成绩是多少? 2. 统计1年级1班数学成绩的平均值? 3. 统计6年级的英语成绩的平均值? 4. 查看"刘苍松"所带班级的英语平均分? 5. 查看工资最高的老师所带班级的各科成绩的平均分,最高分和最低分分别是多少? 6. 查看每位大队长的5门成绩平均分是多少?
时间: 2024-03-19 19:42:20 浏览: 21
1. SELECT COUNT(*) FROM students WHERE class_id = (SELECT id FROM classes WHERE grade = '1' AND class_num = '1') AND subject = '语文' AND teacher_id IN (SELECT id FROM teachers WHERE subject = '语文');
2. SELECT teacher_id, COUNT(*) FROM students WHERE subject = '语文' GROUP BY teacher_id;
3. SELECT DISTINCT teacher_name FROM students INNER JOIN classes ON students.class_id = classes.id WHERE city = '上海';
4. SELECT COUNT(*) FROM students INNER JOIN classes ON students.class_id = classes.id WHERE city = '南京';
5. SELECT COUNT(*) FROM students INNER JOIN classes ON students.class_id = classes.id WHERE city = '武汉' AND gender = '男';
SELECT COUNT(*) FROM students INNER JOIN classes ON students.class_id = classes.id WHERE city = '武汉' AND gender = '女';
6. SELECT city, COUNT(*) FROM students INNER JOIN classes ON students.class_id = classes.id GROUP BY city;
7. SELECT class_id, COUNT(*) FROM students WHERE teacher_id IN (SELECT id FROM teachers WHERE salary > (SELECT AVG(salary) FROM teachers)) GROUP BY class_id;
8. SELECT teacher_id, COUNT(*) FROM students WHERE city = '郑州' GROUP BY teacher_id;
多对多查询:
1. SELECT chinese FROM student_course WHERE student_id IN (SELECT id FROM students WHERE class_id = (SELECT id FROM classes WHERE grade = '1' AND class_num = '1'));
2. SELECT AVG(math) FROM student_course WHERE student_id IN (SELECT id FROM students WHERE class_id = (SELECT id FROM classes WHERE grade = '1' AND class_num = '1'));
3. SELECT AVG(english) FROM student_course WHERE student_id IN (SELECT id FROM students WHERE class_id IN (SELECT id FROM classes WHERE grade = '6'));
4. SELECT AVG(english) FROM student_course WHERE student_id IN (SELECT id FROM students WHERE class_id IN (SELECT id FROM classes WHERE teacher_id IN (SELECT id FROM teachers WHERE name = '刘苍松'))) AND subject = '英语';
5. SELECT class_id, AVG(score), MAX(score), MIN(score) FROM student_course WHERE student_id IN (SELECT id FROM students WHERE class_id IN (SELECT id FROM classes WHERE teacher_id IN (SELECT id FROM teachers ORDER BY salary DESC LIMIT 1))) GROUP BY class_id, subject;
6. SELECT captain_id, AVG(chinese), AVG(math), AVG(english), AVG(history), AVG(physical) FROM students INNER JOIN classes ON students.class_id = classes.id GROUP BY captain_id;