sql语句
#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 ('1408','1409')
#查既不是计算机系、数学系、又不是信息系的学生姓名、性别
#SELECT sname,ssex from s where sdept not in ('1408','1409')
#查所有姓“刘”的学生的姓名、学号和性别。
#SELECT sname,sno,ssex from s where sname like '刘%'
#查姓“上官”且全名为3个汉字的学生姓名。
#SELECT sname from s where sname like '上官_'
#8:查所有不姓“张”的学生的姓名。
#SELECT sname from s where sname not like '张%'
#9:查DB_Design相关课程的课程号。
#SELECT cno,cname from c where cname like '%DB_Design%' ESCAPE 'B'
#10:查缺考的学生的学号和课程号。
#SELECT sno,cno from sc where grade is null
#12:查计算机系20岁以下的学生的学号姓名 and 多条件查询
#14:查询选修了C3课程的学生的学号和成绩,其结果按分数的降序排列。
#SELECT sno,grade from sc,c where sc.cno = c.cno and cname= 'php' ORDER BY grade desc
#23:查询每个学生及其选修课程的情况。
#select * from s,sc,c where s.sno = sc.sno and sc.cno = c.cno
#SELECT * from s INNER JOIN sc on s.sno = sc.sno INNER JOIN c on sc.cno = c.cno
#查询全体学生的情况,查询结果按所在系升序排列,对同一系中的学生按年龄降序排列。
#SELECT * from s ORDER BY sdept asc ,sage desc
#查询学生总人数。
#SELECT count(sname) as num from s
#SELECT count(DISTINCT(sno)) from sc
#18:计算选修了C1课程的学生平均成绩。
#SELECT avg(grade) from sc,c where c.cno = sc.cno and cname = 'php'
#SELECT avg(grade) from sc INNER JOIN c on sc.cno = c.cno where cname = 'php'
# count avg max min
#20:查询各个课程号与相应的选课人数。
#SELECT cno,count(sno) from sc GROUP BY cno
#21:查询计算机系选修了3门以上课程的学生的学号。 HAVING 在结果集中再次筛选
#SELECT s.sno from s,sc where s.sno = sc.sno and sdept = '1502' GROUP BY sno HAVING count(cno) >3
#22:求基本表S中男同学的每一年龄组(超过50人)有多少人?要求查询结果按人数升序排列,人数相同按年龄降序排列。
#SELECT sage,count(sno) as num from s where ssex = '男' GROUP BY sage HAVING num >50 ORDER BY num asc,sage desc
#24:查询选修了C2课程且成绩在90分以上的所有学生。
#SELECT * from s,c,sc where s.sno = sc.sno and sc.cno = c.cno and grade >90 and cname = 'oop'
#25:查询每个学生选修的课程名及其成绩。
#SELECT s.sno,sname,cname,grade from s,sc,c where s.sno = sc.sno and sc.cno = c.cno
#26:统计每一年龄选修课程的学生人数。
#SELECT sage,count(DISTINCT(s.sno)) from s,sc where s.sno = sc.sno GROUP BY sage
#28:查询与“张三”在同一个系学习的学生学号、姓名和系别。
# 子查询
#SELECT sno,sname,sdept from s where sdept = (SELECT sdept from s where sname = 'zhangsan')
#29:查询选修课程名为“数据库”的学生学号和姓名。
#SELECT s.sno,sname from s,sc,c where s.sno = sc.sno and sc.cno = c.cno and cname = 'DB_Design'
#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))
#32:查询所学课程包含学生S3所学课程的学生学号
#SELECT DISTINCT(sc.sno) from sc where sc.cno in (SELECT c.cno from s,sc,c where s.sno = sc.sno and sc.cno = c.cno and s.sno=3)