·设教学数据库Education有三个关系:
学生关系S(SNO 学生学号,SNAME 学生姓名,SAGE 年龄,SEX 性别,SDEPT 系别);
学习关系SC(SNO 学生学号,CNO 课程号,GRADE 成绩);
课程关系C(CNO课程号,CNAME 课程名称,CDEPT 所属院系,TNAME 教师名称)
查询问题:


#1查所有年龄在20岁以下的学生姓名及年龄
#SELECT sname,sage from s where sage<20

#2查考试成绩有不及格的学生的学号
#SELECT sno from sc where grade < 90

#3查所年龄在20至23岁之间的学生姓名、系别及年龄。
#SELECT sname,sdept,sage from s where sage BETWEEN 20 and 23   //在...之间

#4查计算机系、数学系、信息系的学生姓名、性别。
#SELECT sname,ssex from s where sdept in (1409,1408)

#5查既不是计算机系、数学系、又不是信息系的学生姓名、性别
#SELECT sname,ssex from s where sdept not in (1409,1408)    //不在...之间

#6查所有姓“刘”的学生的姓名、学号和性别。
#SELECT sname,ssex,sno from s where sname like '刘%'

#7查姓“上官”且全名为3个汉字的学生姓名。
#SELECT sname from s where sname like '上官_ '      //代表任意一个字段

#8查所有不姓“张”的学生的姓名。
#SELECT sname,ssex,sno from s where sname not like '刘%'

#9:查DB_Design相关课程的课程号。
#SELECT cno,cname from c where cname like '%DBc_design%' ESCAPE 'c'     // 声明一个转译符号   例:(声明'c'为转译符号)

#10:查缺考的学生的学号和课程号。
#SELECT sno,cno from sc where grade is null   //是一个空

#11:查年龄为空值的学生的学号和姓名。
#SELECT sno,sname from s where sage is null

#12:查计算机系20岁以下的学生的学号和姓名。
#SELECT sno,sname from s where sage<20 and sdept = 1408

#14:查询选修了C3课程的学生的学号和成绩,其结果按分数的降序排列。

#SELECT sno,grade from sc,c where sc.cno=c.cno and cname='php' ORDER BY grade desc     //排序< /FONT>

#15:查询全体学生的情况,查询结果按所在系升序排列,对同一系中的学生按年龄降序排列。
#SELECT * from s ORDER BY sdept asc ,sage desc

#16:查询学生总人数。
#SELECT count(sno) as studentnum from s

#17:查询选修了课程的学生人数。
#SELECT COUNT(DISTINCT(sno)) from sc     // 去除重复字段

#18:计算选修了C1课程的学生平均成绩。
#SELECT avg(grade) from sc INNER JOIN c on sc.cno = c.cno where cname = 'php'    //取平均值 < /FONT >

#19:查询学习C3课程的学生最高分数。
#SELECT max(grade) from sc where cno = (SELECT cno from c where cname = 'php');    //取最大值 < /FONT >

#20:查询各个课程号与相应的选课人数。
#SELECT cno,count(sno) from sc GROUP BY cno

#21:查询计算机系选修了3门以上课程的学生的学号。
#SELECT sc.sno,count(cno) as num from s,sc where s.sno=sc.sno and sdept = '1505' GROUP BY sno< /FONT > HAVING num>3    //筛选< /FONT >

#22:求基本表S中男同学的每一年龄组(超过50人)有多少人?要求查询结果按人数升序排列,人数相同按年龄降序排列。
#SELECT sage,COUNT(sage) as num  from s where ssex = '男' GROUP BY sage HAVING num>50 ORDER BY num asc,sage desc;< /FONT >

#23:查询每个学生及其选修课程的情况.
#SELECT * from s,sc,c where s.sno = sc.sno and sc.cno = c.cno < /FONT >

#24:查询选修了C2课程且成绩在90分以上的所有学生。
#SELECT * from s,sc,c where s.sno = sc.sno and sc.cno = c.cno and grade>90 and cname= 'php'
#SELECT * from s INNER JOIN sc on s.sno = sc.sno INNER JOIN c on sc.cno = c.cno where grade >90 and cname = 'php' < /FONT >

#25:查询每个学生选修的课程名及其成绩。
#SELECT s.sno,sname,cname,grade from s,sc,c where s.sno = sc.sno and sc.cno = c.cno < /FONT >

#26:统计每一年龄选修课程的学生人数。
#SELECT sage,COUNT(sc.sno) from s,sc where s.sno=sc.sno GROUP BY sage ;< /FONT>

#27:查询选修了C2课程的学生姓名。
#SELECT sname from s,sc,c where s.sno = sc.sno and sc.cno = c.cno and cname = 'php' < /FONT>

#28:查询与“张三”在同一个系学习的学生学号、姓名和系别。
#SELECT sno,sname,sdept from s where sdept=(SELECT sdept from s where sname = '张三是')< /FONT >

#29:查询选修课程名为“数据库”的学生学号和姓名。
#SELECT sno,sname from s,sc,c where s.sno = sc.sno and sc.cno = c.cno and cname = '数据库' < /FONT >

#30:查询所有未选修C2课程的学生姓名。
#SELECT sname from s where sname not in (SELECT sname from s,sc,c where s.sno = sc.sno and sc.cno = c.cno and cname = 'php') < /FONT>

#31:查询选修了全部课程的学生姓名。
#SELECT sname FROM s WHERE NOT EXISTS (SELECT * FROM c WHERE NOT EXISTS (SELECT * FROM sc WHERE sc.sno = s.sno AND sc.cno = c.cno))         //不存在
#SELECT sname FROM s INNER JOIN sc ON s.sno = sc.sno INNER JOIN c ON sc.cno = c.cno GROUP BY sname having COUNT(DISTINCT (sc.cno)) = (select count(cno) from c)       //去除重复字段 < /FONT >

#32:查询所学课程包含学生S3所学课程的学生学号
#SELECT DISTINCT(sno) from sc,c where sc.cno = c.cno and cname in (SELECT cname from s,sc,c where s.sno = sc.sno and sc.cno = c.cno and sname = '梁斌斌')         //去除重复字段 < /FONT >

#
DISTINCT 去除重复的值 
max 取最大值 
avg 取平均值 
min 取最小值 
is null  是一个空的 
escape 声明一个转译