Mysql查询语句练习题

表结构示意图：

 

题目：

 

7、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名;

select student.sid,student.sname from student right join (select New.student_id as ID,count(New.course_id) as Num from (select * from score where course_id=1 or course_id=2) as New group by New.student_id having Num=2) as A on student.sid=A.ID;

 

8、查询学过“小多”老师所教的课的同学的学号、姓名；

 

（1）查询出小多老师的课程数量
select count(course.cid) as Num from course left join teacher on course.teacher_id=teacher.tid where teacher.tname='小多' group by teacher.tid;

（2）查询出上过小多老师课的学生ID和该学生上过小多老师的课程数量
select score.student_id,count(score.course_id) from score where score.course_id in (select cid from teacher left join course on teacher.tid=course.teacher_id where teacher.tname='小多') group by score.student_id

（3）连表
select student.sid,student.sname from student right join (select B.ID_B as ID_A from (select score.student_id as ID_B,count(score.course_id) as NumB from score where score.course_id in (select cid from teacher left join course on teacher.tid=course.teacher_id where teacher.tname='小多') group by score.student_id) as B left join (select count(course.cid) as NumC from course left join teacher on course.teacher_id=teacher.tid where teacher.tname='小多' group by teacher.tid) as C on B.NumB=C.NumC) as A on student.sid=A.ID_A;

 

9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

select student.sid,student.sname from student right join (select B.student_id as ID_A from (select student_id,number from score where course_id=1) as B left join (select student_id,number from score where course_id=2) as C on B.student_id=C.student_id where B.number > C.number) as A on student.sid=A.ID_A;

 

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

select sid,sname from student where sid in (select student_id from score where number<60);

 

11、查询没有学全所有课的同学的学号、姓名；

select sid,sname from student where sid not in (select student_id from score group by student_id having count(course_id)=3);

 

12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；

select student.sid,student.sname from student right join (select student_id from score where student_id !=1 and course_id in (select course_id from score where student_id=1) group by student_id) as A on student.sid=A.student_id;

 

14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；

select student.sid,student.sname from student right join (select student_id,count(course_id) from score where student_id !=2 and course_id in (select course_id from score where student_id=2) group by student_id having count(course_id)=(select count(course_id) from score where student_id=2 group by student_id)) as A on student.sid=A.student_id;

 

15、删除学习“叶平”老师课的SC表记录；

delete from score where course_id = (select cid from course left join teacher on course.teacher_id=teacher.tid where teacher.tname='叶平');