试验七 Sql 查询语句

目的: 掌握 Select 查询语句。

一 单表
1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。
select sno,sname,sage from student
where sage between 19 and 21  and ssex=’女’ order by sage desc
2查询姓名中第戎2个字为“明”字的学生学号、性别。
select sname ,ssex from student where sname like ‘_明%’
3查询 1001课程没有成绩的学生学号、课程号
select sno,cno  from sc  where grade is null and cno=’1001’
4查询JSJ 、SX、WL 系的学生学号,姓名，结果按系及学号排列
select sno,sname from student where sdept in (‘JSJ’,’SX’,’WL’)
order by sdept,sno

5按10分制查询学生的sno,cno,10分制成绩
   （1-10分 为1 ，11-20分为2 ，30-39分为3，。。。90-100为10）
select sno , cno , grade/10.0+1 as level from sc

6查询 student 表中的学生共分布在那几个系中。（distinct）
select distinct sdept from student

7查询0001号学生1001，1002课程的成绩。
  Select cno from sc where sno=’0001’ and (cno=’1001’ or cno=’1002’)

二 统计
1查询姓名中有“明”字的学生人数。
select count(*) from student where sname like ‘%明%’
2计算‘JSJ’系的平均年龄及最大年龄。
  Select avg(sage) , max(sage)  from student Where sdept=’JSJ’
3计算每一门课的总分、平均分，最高分、最低分，按平均分由高到低排列
  select cno,sum(grade),avg(grade),max(grade),min(grade) from sc
  group by cno
  order by avg(grade) desc
4 计算 1001,1002 课程的平均分。
Select cno , avg(grade) from sc  where cno in (‘1001’,’1002’)
Group by cno
5 查询平均分大于80分的学生学号及平均分
select sc.sno , avg(grade) from sc
group by sc.sno
having  avg(grade)>80
6 统计选修课程超过 2 门的学生学号
  select sno from sc group by sno having count(*)>2
7 统计有10位成绩大于85分以上的课程号。
Select  cno from sc
where grade>85
group by cno having count(*) =10
8 统计平均分不及格的学生学号
select sno from sc    group by sno having avg(grade)<60

9 统计有大于两门课不及格的学生学号
select sno from sc  where  grade<60
group by sno  having count(*) >2

三 连接
1查询 JSJ 系的学生选修的课程号
  select cno from student,sc where student.sno=sc.sno and sdept=’JSJ’
2查询选修1002 课程的学生的学生姓名 (不用嵌套及嵌套2种方法）
a: select sname from  student,sc where student.sno = sc.sno and cno=’1002’
b: select sname from student where sno in (select sno from sc where cno=’1002’)
3查询数据库原理不及格的学生学号及成绩
select sno,grade from sc ,course
where sc.cno=course.cno and cname=’数据库原理’
4查询选修“数据库原理”课且成绩 80 以上的学生姓名(不用嵌套及嵌套2种方法）
a: select sname from student , sc , course
  where student.sno=sc.sno  and  sc.cno = course.cno and 
grade>80 and cname=’数据库原理’
 b: select sname from student  where sno in ( select sno from sc where grade>80 and cno in ( select cno from course where cname=’数据库原理’) )
5查询平均分不及格的学生的学号，姓名,平均分。
select sno, max(sname) , avg(grade) as avggrade from sc , student
where student.sno=sc.sno
group by  student.sno
having avg(grade) <60
6查询女学生平均分高于75分的学生姓名。
A: Select sname from student where ssex=’女’ and sno in (
   Select sno from sc group by sno having avg(grade)>75)

B: Select max(sname ) from sc,student where student.sno=sc.sno and Ssex=’女’
  Group by student.sno having avg(grade)>75

7查询男学生学号、姓名、课程号、成绩。(一门课程也没有选修的男学生也要列出，不能遗漏)
select student.sno,sname,cno,grade from student left join sc ON student.sno=sc.sno
  and ssex=’男’

四 嵌套、相关及其他
1 查询平均分不及格的学生人数
select count(*) from student where sno in (
 select sno from sc group by sno having avg(grade)<60 )
2 查询没有选修1002 课程的学生的学生姓名 
select sname from student where sno not in(
   select sno from sc where cno=’1002’)
student
0001 aa   X
0002 bb
0003 cc  X
sc
0001   1001
0001   1002
0002   1001
0003   1002  
  select sname from student where  not exists (
select * from sc where cno=’1002’ and sc.sno=student.sno)

3 查询平均分最高的学生学号及平均分 （2种方法 TOP , any , all）
a: select top 1 sno,avg(grade) from sc  group by sno order by  avg(grade) desc

B: select sno,avg(grade) from sc  group by sno
  having  avg(grade) = (select top 1 avg(grade) from sc
group by sno order by avg(grade) desc )

c: select sno,avg(grade) from sc group by sno
  having  avg(grade) >=all (  select avg(grade) from sc group by sno )

*4 查询没有选修1001，1002课程的学生姓名。
Select sname from student where not exists (
    Select  * from course where cno in (‘1001’,’1002’) and
       Not exists ( select * from sc where sno=student.sno and cno=course.cno )
   )
5 查询1002课程第一名的学生学号（2种方法）
a: select top 1 sno from sc cno=’1002’ order by grade desc

b: select sno from sc where cno=’1002’ and
grade >=all (select  grade from sc where cno=’1002’)
6 查询平均分前三名的学生学号
select top 3 sno from sc  group by sno  order by avg(grade) desc
7 查询 JSJ 系的学生与年龄不大于19岁的学生的差集
a: select * from student where sdept=’JSJ’ and  sage>19
b: select * from student where sdept=’JSJ’
   except  select * from student where sage<19
8 查询1001号课程大于90分的学生学号、姓名及平均分大于85分的学生学号、姓名
select student.sno,sname from student,sc where cno=’1001’ and grade>90
union
select sno,sname from student where sno in (
   select sno from sc group by sno having avg(grade)>85 )
9  查询每门课程成绩都高于该门课程平均分的学生学号
 select sno from student where sno not in (
select sno from sc X  where grade < (
select  avg(grade)  from sc where cno=X.cno )
)
10 查询大于本系科平均年龄的学生姓名
  select sname from student  X where sage > (
select avg(sage) from student y where sdept=x.sdept)