文件 No.2 SQL习题集

查询选修了“02”号课程学生的学号

sno from sc where cno = ‘02’;查询没选“02”号课程，学生的学号

s.sno from s where not ( * from sc where cno = ‘02’ and s.sno = sc.sno);查询选修了“02”号课程学生的姓名、性别、年龄

sname, sex, age from s where ( * from sc where cno = ‘02’ and s.sno = sc.sno);查询没选修“02”号课程学生的姓名、性别、年龄

sname, sex, age from s where not ( * from sc where cno = ‘02’ and s.sno = sc.sno);查询女生的名字，她的年龄与某一男生的年龄相等

sname from s where age = some( age from s where sex = ‘男’) and sex = ‘女’;查询至少有一门课程不及格的学生的学号

sno from s where ( * from sc where sc.grade < 60 and sc.sno = s.sno);查询所有课程都及格的学生的姓名、性别、年龄

sno from s where not ( * from sc where sc.grade < 60 and sc.sno = s.sno);查询选修“C++”学生的学号和姓名

s.sno, s.sname from s, sc, c where ame = ‘C++’ and o = o and sc.sno = s.sno;查询“刘德华”选学“数据库”的成绩。

sc.grade from s, sc, c where ame = ‘数据库’ and o = o and sc.sno = s.sno and s.sname = ‘刘德华’;查询至少选修了“数据库”和“操作系统”课程的学生的学号和姓名。

s.sno, s.sname from s, ( s.sno as sn,count(*) as num from s, sc, c where ame in(‘数据库’,’操作系统’) and o = o and sc.sno = s.sno group by s.sno) where num >= 2 and s.sno = sn; 统计每个年龄所对应的学生人数。

count(*), age from s group by age;查询每个系的人数分别是多少

count(*), dept from s group by dept;查询每门课选课的人数，给出课程号和人数。

cno, count(*) from sc group by cno;查询每个学生选课的门数，给出学号和课程门数。

sno, count(*) from sc group by sno;查询选课门数超过15的学生，给出学号，姓名和性别。

s.sno, s.sname, s.sex from s, ( sno, count(*) as cou from sc group by sno) t where t.cou > 15 and s.sno = t.sno;查询课程的名称和学分，该课程的选修人数在100至150之间。

ame, c. from c, ( cno, count(*) as cou from sc group by cno) t where t.cou 100 and 150 and o = o;查询男女生的平均成绩各是多少。

count(*), avg(grade) from sc, s where sc.sno = s.sno group by sex;查询平均成绩比每个女生平均成绩都高的男生的姓名和平均成绩。

s.sname, t.av from s,

( sno, avg(grade) as av from sc group by sno) t,

( 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 = ‘男’;查询平均成绩不及格的学生的学号、姓名和性别。

s.sno, s.sname, s.sex from s, ( sno, avg(grade) as av from sc group by sno) t where t.av < 60 and t.sno = s.sno;查询总成绩最高的学生的学号、姓名和性别。

s.sno, s.sname, s.sex from s,

( max(t.su) as ma from ( sno, sum(grade) as su from sc group by sno) t) t,

( sno, sum(grade) as su from sc group by sno) t2

where s.sno = t2.sno and t2.su = t.ma;查询所有的学生都选修的课程。

ame from c, ( cno, count() as cou from sc group by cno) t, ( count() as co from s) t2 where t.cou = t2.co and o = o;学生成绩排榜（平均成绩从大到小顺序,要求给出学号、姓名和总成绩）。

s.sno, s.sname, t.su from s,

( 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;查询平均成绩不算更低的学生姓名和学号。

s.sno, s.sname from s,

( min(t.av) as mi from ( sno, avg(grade) as av from sc group by sno) t) t,

( sno, avg(grade) as av from sc group by sno) t2

where s.sno = t2.sno and t2.av != t.mi;求至少选修了“数据库概论”和“OS”两门课程的学生信息（学号、姓名、性别、年龄）

s.sno, s.sname from s, ( s.sno as sn,count(*) as num from s,

sc,

c where ame in(‘数据库概论’,’OS’) and o = o and sc.sno = s.sno group by s.sno)

where num >= 2 and s.sno = sn;查询这样的男学生：他的平均成绩高于所有女生的平均成绩。要求给出该男生的学号、姓名、性别和年龄

s.sno, s.sname, s.sex, s.age from s,

( sno, avg(grade) as av from sc group by sno) t,

( 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 = ‘男’;查询这样的男生信息（学号、姓名、年龄）：他所选的课程中成绩都是及格的（做法挺多，但尝试下not 操作符）

sno, sname, age from s where not ( sno from sc where grade < 60 and sno = s.sno);删除男生选课信息

from sc where ( * from s where sex = ‘男’ and s.sno = sc.sno);将男生的各科的考试成绩提高10%

sc set grade = grade * 1.1 where ( * from s where sex = ‘男’ and s.sno = sc.sno);