1)查询平均成绩大于60分的同学的学号和平均成绩
SELECTsc.s_id,ROUND( AVG( sc.s_score ), 2 ) FROMscore sc GROUP BYsc.s_id HAVINGAVG( sc.s_score )> 60;
2）查询所有同学的学号、姓名、选课数、总成绩
SELECTst.s_id,st.s_name,count( c_id ),sum( CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END ) FROMstudent stLEFT JOIN score sc ON st.s_id = sc.s_id GROUP BYst.s_id
3)查询姓“肖”的老师的个数
SELECTcount( 1 ) FROMteacher WHEREt_name LIKE '肖%'
4)查询没有学过“林一”老师的同学的学号、姓名
方法一：使用exist
SELECTst.s_id,st.s_name FROMstudent st WHERENOT EXISTS (SELECTsc.s_id FROMscore sc WHEREEXISTS (SELECTc_id FROMteacher tcLEFT JOIN course cr ON tc.t_id = cr.t_id WHEREt_name = '林一' AND cr.c_id = sc.c_id ) AND sc.s_id = st.s_id )
方法二使用：in
SELECTst.s_id,st.s_name FROMstudent st WHEREst.s_id NOT IN (SELECTsc.s_id FROMscore sc WHEREsc.c_id IN ( SELECT c_id FROM teacher tc LEFT JOIN course cr ON tc.t_id = cr.t_id WHERE t_name = '林一' ))
5）查询各科成绩前三名的记录
SELECTsc.c_id,sc.s_score FROMscore sc WHERE( SELECT COUNT( 1 ) FROM score WHERE sc.c_id = score.c_id AND sc.s_score < score.s_score )< 3 ORDER BYsc.c_id ASC,sc.s_score DESC;
6）删除“002”同学的“001”课程的成绩
DELETE FROMscore WHEREs_id IN ( SELECT s_id FROM student st WHERE st.s_name = '002' ) AND c_id IN (SELECTc_id FROMcourse cr WHEREcr.c_name = '001')
如果觉得《SQL 查询没有学过“林一”老师的同学的学号 姓名》对你有帮助，请点赞、收藏，并留下你的观点哦！
