数据库操作-4(多表查询)

1、docker启动mysql:

docker run --privileged=true --name mysql -p 3306:3306 -v /Users/zhangfuli/Desktop/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -d mysql:latest

2、查询学生的学号与平均成绩, 并按照成绩降序排序

SELECT sno, AVG(grade) FROM SC2 
GROUP BY sno
ORDER BY AVG(grade) DESC

3、查询至少选修4门课程的学生学号

SELECT sno FROM SC2
GROUP BY sno
HAVING COUNT(*) >= 4

4、查询选修了数学课程的学号与成绩

SELECT sno, grade FROM SC2
WHERE cno in (
	SELECT cno FROM Course
	WHERE cname = "math"
)
-- 查询CS专业学生的选课信息(学号，课程号，成绩)

5、找出年龄超过平均年龄的学生所有信息

SELECT * FROM Student
WHERE age > (
	SELECT AVG(age) FROM Student
)

6、查询其他系比CS专业最小年龄还大的学生的信息

SELECT * FROM Student
WHERE age > (
	SELECT MIN(age) FROM Student
	WHERE dept = 'CS'
) and dept <> 'CS'

7、查询平均成绩最高的学生的学号(平均成绩大于所有人)

SELECT sno FROM SC2
GROUP BY sno
HAVING AVG(grade) >= ALL(
	SELECT AVG(grade) FROM SC2
	GROUP BY sno
)

8、查询CS专业中年龄大约IoT专业任意一个的同学

SELECT * FROM Student
WHERE dept = 'CS' and age > any(
	SELECT age FROM Student
	WHERE dept = 'IoT'
)

SELECT * FROM Student
WHERE dept = 'CS' and age > some(
	SELECT age FROM Student
	WHERE dept = 'IoT'
)

9、找出有一门选课成绩在80分以上的学生姓名

SELECT * FROM Student
WHERE sno = any(
	SELECT DISTINCT sno FROM SC2
	WHERE grade >= 80
)

10、找出专业为’CS’或’AI’且选课成绩为80分以上的学生的学号、姓名、年龄、课程号、成绩

-- 连接查询
SELECT * FROM Student, SC2
WHERE Student.sno = SC2.sno 

SELECT Student.sno, sname, age, cno, grade FROM Student, SC2
WHERE Student.sno = SC2.sno 
	and dept in ('CS', 'AI')
	and grade >= 70

11、查询选修了数据库的学生号、成绩、学分

SELECT sno, grade, credit FROM SC2, Course
WHERE SC2.sno = Course.cno and cname = 'math'