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