sql四十题

所以大于六十分的
SELECT * from score where num > 60;

 

所有老师以及所教课程

select teacher.tname, course.cname from course
left join teacher on course.teacher_id = teacher.tid;

 

男生个数以及女生个数

select gender,count(gender) from student group by gender;

 

临时表-在内存中临时存在

select sid from (select * from score where num > 60)as B;

 

查询平均成绩大于60的同学的学号和平均成绩

select student_id,avg(num) from score group by student_id having avg(num) > 60;

 

查询平均成绩大于60的同学的学号和平均成绩和姓名

select student.sname,b.student_id,b.ccc from (select student_id,avg(num) as ccc from score group by student_id having avg(num) > 60) as b
left join student on student.sid = b.student_id; 注意聚合函数

 

查询所有同学的学号，姓名，选课数，总成绩；

select student_id,student.sname,count(course_id),sum(num) from score left join student on score.student_id = student.sid
group by student_id;

 

查询没学过叶平老师课的学号和姓名

SELECT
student.sid,
student.sname
FROM
student
WHERE
sid NOT IN (
SELECT
student_id
FROM
score
WHERE
course_id IN ( SELECT course.cid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = "李平老师" )
GROUP BY
student_id
)

查询生物比物理课成绩高的所有学生学号

select B.student_id from
(select score.sid,score.student_id,course.cname,score.num from score left join course on course.cid = score.course_id where course.cname ="生物") as A
inner join
(select score.sid,score.student_id,course.cname,score.num from score left join course on course.cid = score.course_id where course.cname ="物理")as B
on A.student_id = B.student_id