交叉科技-量化数据分析-SQL笔试题

某学校教学管理数据库有下述数据表：
Student(stuId, stuName, stuAge, stuSex) 学生表
stuId: 学号；
stuName: 学生姓名；
stuAge: 学生年龄；
stuSex: 学生性别

Course(courseId, courseName, teacherId) 课程表
courseId: 课程编号；
courseName: 课程名字；
teacherId: 教师编号

Scores(stuId, courseId, score) 成绩表
stuId: 学号；
courseId: 课程编号；
score: 成绩

Teacher(teacherId, teacherName) 教师表
teacherId: 教师编号；
teacherName: 教师名字

Q1: 查询平均成绩大于60分的同学的学号和平均成绩；

SELECT stuId, AVG(score) AS Avgscore
FROM Student
INNER JOIN Scores
BY students.stuId = Scores.studId
GROUP BY stuId
HAVING AVG(score) > 60

Q2: 查询没学过“张三”老师课的同学的学号、姓名；

SELECT studId, studName FROM
(SELECT stuId, stuName, courseId
From Student
INNER JOIN Scores
BY Students.stuId = Scores.stuID) AS stuscore
INNER JOIN(
SELECT courseId, teacherId, teacherName
FROM Teacher 
INNER JOIN Course 
BY Teacher. teacherId = Course. teacherId
WHERE TeacherName IS NOT ‘张三’) AS teachercourse
BY stuscore.courseId = teachercourse.courseId