文件 No.2 SQL习题集

1. 查询选修了“02”号课程学生的学号
   select sno from sc where cno = ‘02’;
2. 查询没选“02”号课程，学生的学号
   select s.sno from s where not exists(select * from sc where cno = ‘02’ and s.sno = sc.sno);
3. 查询选修了“02”号课程学生的姓名、性别、年龄
   select sname, sex, age from s where exists(select * from sc where cno = ‘02’ and s.sno = sc.sno);
4. 查询没选修“02”号课程学生的姓名、性别、年龄
   select sname, sex, age from s where not exists(select * from sc where cno = ‘02’ and s.sno = sc.sno);
5. 查询女生的名字，她的年龄与某一男生的年龄相等
   select sname from s where age = some(select age from s where sex = ‘男’) and sex = ‘女’;
6. 查询至少有一门课程不及格的学生的学号
   select sno from s where exists(select * from sc where sc.grade < 60 and sc.sno = s.sno);
7. 查询所有课程都及格的学生的姓名、性别、年龄
   select sno from s where not exists(select * from sc where sc.grade < 60 and sc.sno = s.sno);
8. 查询选修“C++”学生的学号和姓名
   select s.sno, s.sname from s, sc, c where c.cname = ‘C++’ and c.cno = sc.cno and sc.sno = s.sno;
9. 查询“刘德华”选学“数据库”的成绩。
   select sc.grade from s, sc, c where c.cname = ‘数据库’ and c.cno = sc.cno and sc.sno = s.sno and s.sname = ‘刘德华’;
10. 查询至少选修了“数据库”和“操作系统”课程的学生的学号和姓名。
    select s.sno, s.sname from s, (select s.sno as sn,count(*) as num from s, sc, c where c.cname in(‘数据库’,’操作系统’) and c.cno = sc.cno and sc.sno = s.sno group by s.sno) where num >= 2 and s.sno = sn;

---

1. 统计每个年龄所对应的学生人数。
   select count(*), age from s group by age;
2. 查询每个系的人数分别是多少
   select count(*), dept from s group by dept;
3. 查询每门课选课的人数，给出课程号和人数。
   select cno, count(*) from sc group by cno;
4. 查询每个学生选课的门数，给出学号和课程门数。
   select sno, count(*) from sc group by sno;
5. 查询选课门数超过15的学生，给出学号，姓名和性别。
   select s.sno, s.sname, s.sex from s, (select sno, count(*) as cou from sc group by sno) t where t.cou > 15 and s.sno = t.sno;
6. 查询课程的名称和学分，该课程的选修人数在100至150之间。
   select c.cname, c.credit from c, (select cno, count(*) as cou from sc group by cno) t where t.cou between 100 and 150 and t.cno = c.cno;
7. 查询男女生的平均成绩各是多少。
   select count(*), avg(grade) from sc, s where sc.sno = s.sno group by sex;
8. 查询平均成绩比每个女生平均成绩都高的男生的姓名和平均成绩。
   select s.sname, t.av from s,
   (select sno, avg(grade) as av from sc group by sno) t,
   (select avg(grade) as av from sc, s where s.sno = sc.sno and s.sex = ‘女’) t2
   where s.sno = t.sno and t.av > t2.av and s.sex = ‘男’;
9. 查询平均成绩不及格的学生的学号、姓名和性别。
   select s.sno, s.sname, s.sex from s, (select sno, avg(grade) as av from sc group by sno) t where t.av < 60 and t.sno = s.sno;
10. 查询总成绩最高的学生的学号、姓名和性别。
    select s.sno, s.sname, s.sex from s,
    (select max(t.su) as ma from (select sno, sum(grade) as su from sc group by sno) t) t,
    (select sno, sum(grade) as su from sc group by sno) t2
    where s.sno = t2.sno and t2.su = t.ma;
11. 查询所有的学生都选修的课程。
    select c.cname from c, (select cno, count() as cou from sc group by cno) t, (select count() as co from s) t2 where t.cou = t2.co and t.cno = c.cno;
12. 学生成绩排榜（平均成绩从大到小顺序,要求给出学号、姓名和总成绩）。
    select s.sno, s.sname, t.su from s,
    (select sno, avg(grade) as av, sum(grade) as su from sc group by sno) t
    where s.sno = t.sno
    order by t.av desc;
13. 查询平均成绩不算最低的学生姓名和学号。
    select s.sno, s.sname from s,
    (select min(t.av) as mi from (select sno, avg(grade) as av from sc group by sno) t) t,
    (select sno, avg(grade) as av from sc group by sno) t2
    where s.sno = t2.sno and t2.av != t.mi;
14. 求至少选修了“数据库概论”和“OS”两门课程的学生信息（学号、姓名、性别、年龄）
    select s.sno, s.sname from s, (select s.sno as sn,count(*) as num from s,
    sc,
    c where c.cname in(‘数据库概论’,’OS’) and c.cno = sc.cno and sc.sno = s.sno group by s.sno)
    where num >= 2 and s.sno = sn;
15. 查询这样的男学生：他的平均成绩高于所有女生的平均成绩。要求给出该男生的学号、姓名、性别和年龄
    select s.sno, s.sname, s.sex, s.age from s,
    (select sno, avg(grade) as av from sc group by sno) t,
    (select avg(grade) as av from sc, s where s.sno = sc.sno and s.sex = ‘女’) t2
    where s.sno = t.sno and t.av > t2.av and s.sex = ‘男’;
16. 查询这样的男生信息（学号、姓名、年龄）：他所选的课程中成绩都是及格的（做法挺多，但尝试下not exists操作符）
    select sno, sname, age from s where not exists(select sno from sc where grade < 60 and sno = s.sno);
17. 删除男生选课信息
    delete from sc where exists(select * from s where sex = ‘男’ and s.sno = sc.sno);
18. 将男生的各科的考试成绩提高10%
    update sc set grade = grade * 1.1 where exists(select * from s where sex = ‘男’ and s.sno = sc.sno);