MYSQL连接查询和子查询练习

连接查询和子查询练习

1. 谁和刘晨同岁
   （子查询）
   select sname
   from student
   where sage in (select sage from student where sname = ‘刘晨’)
   and sname != ‘刘晨’;
2. 查询各个学生的姓名、所选修课程号及成绩。（连接查询）
   select sname,cno,grade
   from student join sc on student.sno = sc.sno;
3. 查询刘晨选修了几门课程。(两种方法)
   （连接查询）
   select count(cno)
   from sc
   join student on sc.sno = student.sno
   where sname = ‘刘晨’;
4. 查询选修了1号课或2号课的学生的姓名。(两种方法)
   （连接查询）
   select sname
   from sc
   join student on sc.sno = student.sno
   where cno = ‘c01’ or cno = ‘c02’;
   （子查询）
   select sname
   from student
   where sno in (select sno from sc where cno = ‘c01’ or cno = ‘c02’);
5. 查询李勇选修了几门课程。(两种方法)
   （子查询）
   select count(cno)
   from sc
   where sno in (select sno from student where sname = ‘李勇’);
   （连接查询）
   select count(cno)
   from sc
   join student on sc.sno = student.sno
   where sname = ‘李勇’;
6. 查询王名的平均成绩。(两种方法)
   （子查询）
   select avg(grade)
   from sc
   where sno = (select sno from student where sname = ‘王名’);
   （连接查询）
   select avg(grade)
   from student join sc on student.sno = sc.sno
   where sname = ‘王名’;
7. 将学生的学号、姓名、课程名和成绩查询出来。（连接查询）
   select student.sno,sname,cname,grade
   from student join sc on student.sno = sc.sno
   join course on course.cno = sc.cno;
8. 查询选修了3号课程且成绩在85分以上的所有学生的姓名和学号。(两种方法)
   select sc.sno,sname
   from student join sc on student.sno = sc.sno
   where cno = ‘c03’ and grade > 85;
9. 查询数据库课程的最高成绩。(两种方法)
   （子查询）
   select max(grade)
   from sc
   where cno = (select cno
   from course
   where cname = ‘数据库’);
   （连接查询）
   select max(grade)
   from sc join course on sc.cno = course.cno
   where cname = ‘数据库’;
10. 查询刘晨所选课程的名称和成绩。
    select cname,grade
    from course join sc on course.cno=sc.cno
    join student on student.sno=sc.sno
    where sname=‘刘晨’;
11. 查询刘晨所得总学分。
    select sum(ccredit)
    from course join sc on course.cno=sc.cno
    join student on student.sno=sc.sno
    where sname=‘刘晨’;
12. 查询有哪些学生（姓名）选修了数据库课程。(两种方法)
    select sname
    from course join sc on course.cno=sc.cno
    join student on student.sno=sc.sno
    where cname =‘数据库’;
13. 将王名的年龄改为和刘晨一样；
14. 删除选修了c04课程的学生信息；
    delete from student
    where sno in (select sno
    from sc
    where cno = ‘c04’);
15. 将大数据处理的学分改为和数据库一样；
16. 将0002同学c02的成绩改为和0003的c02成绩一样。
17. 删除没有人选修的课程信息；