实验三、数据库查询
一、实验目的
掌握数据表的查询操作。
二、实验类型：验证
三、实验内容
创建课程管理数据库及数据库表，输入数据，实现下列操作
实验要求：写出对应语句
1、简单查询
（1）查询学生表的所有信息
Select * from s
（2）查询学生表的sno,sn列
select sn,sn from s
（3）查询学生表的学生姓名和学生出生年份（计算列）
select sn,y=year(getdate())-age from s
（4）查询学生表中学生的院系名称，去掉重复项
select distinct dept from s
（5）查询sal高于3500的教师编号和姓名
select tno,tn from t where sal>=3500
（6）查询在18,19两个年龄的学生学号和姓名
select * from student where age=18 or age=19
（7）查询姓名中不含“李”字的学生姓名和性别
select * from s where sn not like '%李%'
（8）查询学号末位不是‘2’‘3’‘4’的学生信息
select * from s where sno like '__[^2-4]'
（9）查询学生信息，第一排序按年龄降序排序，第二排序按院系升序排序
select * from s order by age desc,dept asc
2、聚合函数及分组查询
（10）统计男生和女生的人数
select sex,count(sno) from s group by sex
（11）统计男生和女生的平均年龄
select sex,avg=avg(age) from s group by sex
（12）统计所有18岁以上的男生
select num=count(*) from s where age>18 and sex=’男’
（13）统计所有学生的选课门数、成绩总分和平均分
select sno,num=COUNT(*),avg=avg(score),sum=SUM(score) from sc group by sno
（14）统计各院系教师的平均实发工资
select dept,avg_sal=avg(sal+comm) from t group by dept
3、多表查询
（15）查询姓名为‘钱尔’的同学所选修的课程名和成绩
select sn,cn,score from sc,c,s
where sc.sno=s.sno and sc.cno=c.cno and sn='钱尔'
（16）统计’程序设计’课程的上课人数
select count(sno) from sc,c where sc.cno=c.cno and cn='程序设计'
（17）查询所有教师开课信息包括教师姓名、课程名称
select tn,cn from tc,c,t
where tc.cno=c.cno and tc.tno=t.tno
（18）查询信息学院的学生的姓名、选课课程名称、分数、任课教师姓名
select sn,s.dept,cn,tn,score from sc,s,tc,c,t
where sc.sno=s.sno and sc.cno=tc.cno and tc.cno=c.cno and tc.tno=t.tno and s.dept='信息'
（19）按院系分类统计课程的平均成绩
select cn,dept,AVG(score) from sc,c,s
where sc.cno=c.cno and sc.sno=s.sno
group by cn,dept order by cn
4、嵌套查询
简单子查询：要求使用子查询
（20）查询与“赵勇”同院系的学生姓名
select dept from s where sn='赵勇'
select sn,dept from s where dept=(select dept from s where
sn='赵勇')
（21）查询选修了“程序设计”课程的学生学号
select sno from sc where cno in(select c.cno from c where cn='程序设计')
（22）查询“钱尔”没有选修的课程名称
select cn from c where cno not in (select cno from sc,s where sc.sno=s.sno and sn='钱尔')
（23）查询其他学院比刘平年龄大的学生信息。
select age from s where sn='刘平'
select sno,sn,age,dept from s where age>(select age from s where sn='刘平')
（24）查询其他系年龄比“物电学院”所有学生都要大的学生信息。
select MAX(age) from s where dept='物电学院'
select sno,sn,age,dept from s where age>(select MAX(age) from s dept='物电学院')
相关子查询：要求使用相关子查询
（25）查询有考试成绩85以上的学生学号
select sno from s where exists
(select * from sc where s.sno=sc.sno and sc.score>=85)
（26）查询没有选修（或者选修了所有）课程的学生学号
select sno from s where not exists
( select * from sc where s.sno=sc.sno)
（27）查询选修了所有S001学生选修课程的学生信息
select cno from sc where sno='s01'
where
select cno from sc where sno='s01' and cno
not in (select cno from sc where sno='s04')
select distinct sno from sc as sc1 where not exists
(
select cno from sc where sno='s01' and cno not in
(
select cno from sc as sc2 where sc1.sno=sc2.sno)
)
5、其他语句中使用查询子句
（28）将年龄小于18岁的女生，考试成绩低于70分的成绩增加5分
update sc
set score=score+5
where score>70 and sno in
(select sno from s where sex='女' and age<18)
（29）将信息学院所有学生成绩减少5分
update sc
set score=score-5
where sno in
(select sno from s where dept='信息')
（30）将信息学院学生选修课成绩不低于平均分的成绩增加2分
update sc
set score=score+2
where sno in (select sno from s where dept='信息')
and score>=(select AVG(score) as avg from sc as sc1 group by sc1.cno having sc.cno=sc1.cno)
（31）删除“王平”老师授课的的选课记录
delete sc
where cno in
(select cno from tc,t where t.tn='王平' and tc.tno=t.tno)
因篇幅问题不能全部显示，请点此查看更多更全内容