MySQL 多表查询习题

三个表的sql文件

链接：https://pan.baidu.com/s/17yCdmnEbRawGyOlaJpLUHw
提取码：wgm4

student表：

sc表：

course表：

习题及对应答案

1. 查询2号课程的间接先修课

    select cname from course
    where cno=(
    select cpno from course
    where cno=(
    select cpno from course
    where cno='2'))
   

2. 查找哪些学生没有选课

    select s.id,s.sname from student s left join sc on s.sno=sc.sno
    where grade is null
   

3. 查找选两门课以上同学的姓名、课程名和成绩

    select sname,cname,grade from student,course,sc
    where student.sno=(select sno from sc
    group by sno
    having count(grade)>2) and student.sno=sc.sno and sc.cno=course.cno
   

4. 查询王姓同学C++的成绩

    select grade from student s LEFT JOIN sc on s.sno=sc.sno
    where cno=(select cno from course where cname='c++') and sname like '王%'
   

5. 查询比李大奎c++成绩高的同学(没考c++，当c做了）

    select * from student
    where sno=(
    select sno from sc
    where cno=(select cno from course where cname='c') and grade>
    (
    select grade from sc
    where sno=(select sno from student
    where sname='李大奎') and cno=(select cno from course where cname='c')
    )
    )
   

6. c++最高分是谁

    select sname from student
    where sno=(
    select sno from sc
    where grade=(
    select max(grade) from sc
    where cno=(select cno from course where cname='c++')))
   

7. 总分最高是谁

    select sname from student 
    where sno=(
    select sno from sc
    GROUP BY sno
    ORDER BY sum(grade) DESC
    limit 1)
   

8. 创建一个学生成绩视图

    create view student_gradeA as
    select student.sno,student.sname,course.cname,course.cno,sc.grade FROM
    sc inner join student on sc.sno=student.sno
    inner join course on sc.cno=course.cno
   

9. 假设开除一名同学的操作是第一步把状态改为0，第二步把该学生所有的成绩设置为零，请设计一个存储过程完成开除李大奎。

    DELIMITER $$
    create  procedure kaichu(in s_name VARCHAR(12))
    COMMENT '开除'
    BEGIN
    	update student set status=0 where sname=s_name;
    	update sc set grade=0 where sno=(select sno from student where sname=s_name);
    END $$
    
    call  kaichu('李大奎');
   

10. 不用视图完成把李大奎的C语言成绩改成80

    UPDATE sc set grade=80
    where sno=(
    select sno from student
    where sname='李大奎') and cno=(
    select cno from course
    where cname='c')
    

11. 查询和李大奎选相同课程同学的信息。

    select * from student
    where id=any(
    
    select distinct id from sc
    where cno=any(
    select cno from sc
    where sno=
    (select sno from student where sname='李大奎')))