mysql复杂查询,优质题目
表结构
1.自行添加测试数据
2.查询平均成绩大于60分的同学的学号和平均成绩;
SELECT t1.sid,AVG(t2.number) FROM student t1 LEFT JOIN score t2 ON t1.sid=t2.student_id GROUP BY t1.sid HAVING AVG(t2.number)>60;
3.查询所有同学的学号、姓名、选课数、总成绩;
SELECT t1.sid,t1.sname,t2.temp1,t3.temp2 FROM student t1,(SELECT student_id,COUNT(sid) temp1 FROM score GROUP BY student_id) t2, (SELECT student_id,SUM(number) temp2 FROM score GROUP BY student_id) t3 WHERE t1.sid=t2.student_id AND t1.sid=t3.student_id; #较复杂,有难度
4.查询姓“李”的老师的个数;
SELECT COUNT(tid) FROM teacher WHERE tname LIKE '李%';
5.查询没学过“叶平”老师课的同学的学号、姓名;
SELECT t1.`sid`,t1.`sname` FROM student t1 WHERE t1.`sid` NOT IN(SELECT student_id FROM score WHERE course_id IN(SELECT t2.cid FROM teacher t1 LEFT JOIN course t2 ON t1.`tid`=t2.`teacher_id` WHERE t1.tname='叶平' ORDER BY t2.`cid`)); #子句 SELECT student_id FROM score WHERE course_id IN(SELECT t2.cid FROM teacher t1 LEFT JOIN course t2 ON t1.`tid`=t2.`teacher_id` WHERE t1.tname='叶平' ORDER BY t2.`cid`);
#较复杂
6.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
#方法1 SELECT t1.`student_id`,t2.`sname` FROM score t1 LEFT JOIN student t2 ON t1.`student_id`=t2.`sid` WHERE course_id=1 UNION ALL SELECT t1.`student_id`,t2.`sname` FROM score t1 LEFT JOIN student t2 ON t1.`student_id`=t2.`sid` WHERE course_id=2; #方法2 SELECT t1.sid,t1.sname FROM (SELECT t1.`student_id` sid,t2.`sname` sname FROM score t1 LEFT JOIN student t2 ON t1.`student_id`=t2.`sid` WHERE course_id=1 UNION ALL SELECT t1.`student_id`,t2.`sname` FROM score t1 LEFT JOIN student t2 ON t1.`student_id`=t2.`sid` WHERE course_id=2) t1 GROUP BY t1.sid HAVING COUNT(*)>1;
7.查询学过“叶平”老师所教的所有课的同学的学号、姓名;
SELECT t1.`student_id`,t2.`sname` FROM score t1 LEFT JOIN student t2 ON t1.`student_id`=t2.`sid` WHERE t1.`course_id` IN(SELECT t2.cid FROM teacher t1 LEFT JOIN course t2 ON t1.`tid`=t2.`teacher_id` WHERE t1.tname='叶平' ORDER BY t2.`cid`); #子句 SELECT t2.cid FROM teacher t1 LEFT JOIN course t2 ON t1.`tid`=t2.`teacher_id` WHERE t1.tname='叶平' ORDER BY t2.`cid`;
8.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
#子句1 SELECT number FROM score WHERE course_id=2; #子句2 SELECT number FROM score t1 WHERE t1.`course_id`=1; #总语句 SELECT t3.`sid`,t3.`sname` FROM (SELECT student_id,number FROM score WHERE course_id=2) t1,(SELECT student_id,number FROM score WHERE course_id=1) t2,student t3 WHERE t1.student_id=t3.sid AND t2.student_id=t3.sid AND t1.number<t2.number;
9.查询有课程成绩小于60分的同学的学号、姓名;
#方法1 SELECT DISTINCT t1.student_id,t2.`sname` FROM score t1 LEFT JOIN student t2 ON t1.`student_id`=t2.`sid` WHERE t1.number<60; #方法2 SELECT t1.sid,t1.sname FROM student t1,(SELECT student_id FROM score WHERE number<60 GROUP BY student_id) t2 WHERE t1.sid=t2.student_id;
10.查询没有学全所有课的同学的学号、姓名;
#总语句 SELECT sid,sname FROM student WHERE sid NOT IN(SELECT student_id FROM score GROUP BY student_id HAVING COUNT(course_id)=(SELECT COUNT(cid) FROM course)); #子句 SELECT sid,sname FROM student WHERE sid NOT IN(); #子句:学全了的学生编号 SELECT student_id FROM score GROUP BY student_id HAVING COUNT(course_id)=(SELECT COUNT(cid) FROM course); #子句:课程的总数 SELECT COUNT(cid) FROM course;
11.查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名;
#总语句 SELECT DISTINCT t1.student_id,t2.`sname` FROM score t1 LEFT JOIN student t2 ON t1.`student_id`=t2.`sid` WHERE course_id IN(SELECT course_id FROM score WHERE student_id=1); #子句:学号1所选所有课程 SELECT course_id FROM score WHERE student_id=1
oraclepeixun