子查询

（1）查询计算机系学生的姓名、年龄、性别；
mysql> select sname,sage,ssex

-> from student
-> where dno = (select dno
->              from dept
->              where dname = '计算机系');

（2）查询刘晨的平均成绩；
mysql> select avg(grade)

-> from sc
-> where sno = (select sno
->              from student
->              where sname = '刘晨');

（3）查询从未选修过课程的男学生的姓名；
mysql> select sname

-> from student
-> where ssex = '男' and sno not in(select sno from sc);

（4）查询选修了C01号课程或C02号课程的学生姓名；

mysql> select sname
-> from student
-> where sno in(select sno
->              from sc
->              where cno = 'c01' or cno = 'c02');

（5）查询所有选修了“数据库”课程的学生的学号和姓名；
mysql> select sno,sname

-> from student
-> where sno in(select sno
->               from sc
->               where cno = (select cno
->                           from course
->                           where cname = '数据库'));

（6）查询至少选修了3门课的学生的姓名；
mysql> select sname

-> from student
-> where sno in    (select sno
->               from sc
->               group by sno
->               having count(cno) >=3);

（7）查询选修了C03号课程且成绩在85分以上的所有学生学号和姓名；
mysql> select sno,sname

-> from student
-> where sno in(select sno
->              from sc
->              where cno = 'c03' and grade > 85);

（8）查询数据库课程中大于该课平均成绩的学生学号；
select sno
from sc
where cno = (select cno from course where cname = '数据库')

 and
 grade > (select avg(grade) from sc where cno = (select cno from course where cname = '数据库'));

（9）查询和刘晨在同一个系的学生姓名（不包括刘晨）；
mysql> select sname

-> from student
-> where sname <> '刘晨' and dno = (select dno
->                                      from student
->                                  where sname = '刘晨');

（10）将女同学的成绩提高5%；
mysql> update sc

-> set grade = grade * 1.05
-> where sno in (select sno
->               from student
->               where ssex = '女');

（11）删除刘晨的选课记录
mysql> delete from sc

-> where sno = (select sno
->             from student
->             where sname = '刘晨');