python学习之老男孩python全栈第九期_数据库day004 -- 作业

时间:2022-02-07 21:54:17

https://www.cnblogs.com/YD2018/p/9451809.html

 

11. 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

select student.sid,student.sname from score LEFT JOIN student on score.student_id=student.sid where (course_id=1 or course_id=2) GROUP BY score.student_id having count(course_id)>1

python学习之老男孩python全栈第九期_数据库day004 -- 作业


 

12. 查询学过“李平”老师所教的所有课的同学的学号、姓名

SELECT student.sid,student.sname from score LEFT JOIN student on score.student_id=student.sid where score.course_id in (SELECT course.cid from course where course.teacher_id=(select teacher.tid from teacher where teacher.tname='李平老师')) GROUP BY score.student_id having count(score.course_id) = (SELECT count(course.cid) from course where course.teacher_id=(select teacher.tid from teacher where teacher.tname='李平老师'));

python学习之老男孩python全栈第九期_数据库day004 -- 作业


 

13. 查询有课程成绩小于60分的同学的学号、姓名

 一. group by 做:

SELECT student.sid,student.sname from score LEFT JOIN student ON score.student_id=student.sid where score.num<60 group by score.student_id;

python学习之老男孩python全栈第九期_数据库day004 -- 作业

二. distinct  做:

去重:distinct -- 效率不高
SELECT distinct student.sid,student.sname from score LEFT JOIN student ON score.student_id=student.sid where score.num<60;

 

14. 查询没有学全所有课的同学的学号、姓名

SELECT student.sid,student.sname from score LEFT JOIN student ON score.student_id=student.sid where score.course_id in (SELECT course.cid from course) GROUP BY score.student_id having count(score.course_id) !=(SELECT count(course.cid) from course);

python学习之老男孩python全栈第九期_数据库day004 -- 作业