注：以下操作均建立在上篇文章sql Server操作1的数据基础之上
一、实验目的
熟悉SQL语句的基本使用方法，学习如何编写SQL语句来实现查询
二、实验内容和要求
使用SQL查询分析器查询数据，练习查询语句的使用，掌握SELECT 语句的完整结构，包括简单查询、嵌套查询、连接查询等基本实现方法。
三、实验主要仪器设备和材料
1．计算机及操作系统：PC机，Windows 2000/xp/win7；
2．数据库管理系统：SQL sever 2005/2008；
四、实验方法、步骤及结果测试
实验题目：
1、对实验一建立的数据库表进行查询
简单查询：
1 题目1、查询学生的系别，用小写字母表示。
2 SQL语句
3 select distinct LOWER(Sdept) from S;
1 题目2、查询叫“刘%”的学生学号、系别
2 SQL语句
3 select Sno Sdept from S where Sname like '刘%' escape'';
1 题目3、查询教师姓名第二个字是“一”的教师姓名，所教课程名。
2 SQL语句
3 select Tname,Cname from C where Tname like '_一_';
|
1
2
3
4
5
6
|
题目4、查询没有成绩的学生学号，学号降序排列。 SQL语句 select S.Sno from S where not exists( select * from Sc where S.Sno=Sc.Sno ) order by S.Sno desc; |
|
1
2
3
4
5
6
|
题目5、查询选修课程的学生人数 SQL语句 select count(distinct Sno) As '学生人数' from Sc --select count(*) --from S --where exists( select * from Sc where Sc.Sno = S.Sno ) |
1 题目6、各科选修课成绩及格的选修人数。
2 SQL语句
3 select count(distinct Sno)As'及格人数' from Sc where Grade>=60
4 group by Cno;
|
1
2
3
4
5
6
|
题目7、各学院男生人数,超过2人的输出学院名和人数，并按男生人数降序排列 SQL语句 select Ssp,count(*) from S where S.Ssex='男'group by Ssp having count(*)>2 order by count(*) desc; |
1 题目8、查询IS系男女学生人数各是多少SQL语句
2 select S.Ssex As'性别',count(*) As'IS系人数' from S where S.Sdept='IS'
3 group by Ssex;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
题目9、找出年龄>23岁，平均成绩不及格的学生学号和平均分 SQL语句 select sno,avg(grade) from SC where sno in( select sno from S where getdate()-sbirth>23 ) group by sno having avg(grade)<60 或者： --获得当前系统时间 --跨越表查询要用having，不能用在where Select S.Sno As'学生学号',Avg(Sc.Grade) As'平均成绩'from S,Sc where S.Sno=Sc.Sno and getdate()-Sbirth >23 group by S.Sno having AVG(Sc.Grade)<60; |
1 题目10、显示所有学生信息，如果该生选修过课程，显示其选修课号和考试成绩
2 SQL语句
3 select S.*,Sc.Cno,Sc.Grade from S left join Sc on(SC.Sno=S.Sno);
|
1
2
3
4
5
6
7
8
9
10
11
|
题目11、查询每门课都是80分以上的学生的学号与姓名。 SQL语句 --如果没有加"S.Sno=Sc.Sno and"会出现有未选修了课程的学生学号 --如果没有distinct则会出现重复 Select distinct S.Sno As'学生学号',S.Sname As'姓名'from S,Sc where S.Sno=Sc.Sno and S.Sno not in (select Sno from Sc where Grade<=80); 或者： select sno,sname from S where sno in(select sno from sc group by sno having min(grade)>80) |
1 题目12、查询学分比“计算机应用”多的课程号和课程名、学分
2 SQL语句
3 Select Cname As'课程名',cCredit As'学分' from C
4 where cCredit>(Select cCredit from C where Cname='计算机应用')
5 group by Cname,cCredit;
|
1
2
3
4
5
6
7
8
9
10
11
|
题目13、查询CS系中年龄比IS系所有人年龄都小的学生学号和姓名 SQL语句 use Student Select Sno As'学生学号',Sname As'姓名' from S where Sdept='CS' and Sbirth>(Select Max(sBirth) from S where Sdept='IS') group by Sno,Sname; 或者： use Student Select Sno As'学生学号',Sname As'姓名' from S where Sdept='CS' and Sbirth>all(Select sBirth from S where Sdept='IS') group by Sno,Sname; |
1 题目14、is系没有选修02号课程的学生学号和学生姓名
2 SQL语句
3 Select Sno As'学生学号',Sname As'学生姓名' from S
4 where S.Sdept='IS'and not exists (select * from Sc where Sc.Sno=S.Sno and Sc.Cno=02)
5 group by Sno,Sname;
1 题目15、被全部学生都选修的课程号、课程名
2 SQL语句
3 select Cno As'课程号',Cname As'课程名' from C
4 where not exists(select * from S where not exists (select * from Sc where Sc.Sno=S.Sno and Sc.Cno=C.Cno))
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
题目16、选修了01号课又选修了02号课的学生的学号和姓名 实现代码及查询结果截图： SQL语句 select S.Sno As'学生学号',S.Sname As'学生姓名' from S,Sc where Sc.Sno=S.Sno and Sc.Cno=01 intersectselect S.Sno As'学生学号',S.Sname As'学生姓名' from S,Sc where Sc.Sno=S.Sno and Sc.Cno=02; 或者： select Sname,Sno from S where Sno in( select Sno from SC where cno='01' and Sno in( select Sno from SC where Cno='02')) |
1 题目17、被全部IS系的学生都选修的课程号和课程名
2 SQL语句
3 select Cno As'课程号',Cname As'课程名' from C
4 where not exists(select * from S where S.Sdept='IS'and not exists (select * from Sc where Sc.Sno=S.Sno and Sc.Cno=C.Cno))
|
1
2
3
4
5
6
7
8
9
10
11
|
题目18、查询选修高等数学与选修数据库的学生差集 SQL语句 select * from S where exists (select *from Sc where Sc.Sno=S.Sno and exists(select * from C where Sc.Cno=C.Cno and C.Cname='高等数学')) exceptselect * from S where exists (select *from Sc where Sc.Sno=S.Sno and exists(select * from C where Sc.Cno=C.Cno and C.Cname='数据库')); 或者： select Sno from SC,C whereC.Cname='高等数学' and C.Cno=SC.Cno and SC.Sno not in( select Sno from SC,C where SC.Cno=C.Cno and C.Cname='数据库') |
|
1
2
3
4
5
6
|
题目19、没有选修“李一”老师开设的课程的学生学号、姓名、系别 SQL语句 select S.Sno As'学生学号',S.Sname As'学生姓名',S.Ssp As'系别' from S where not exists( select * from SC,C where SC.cno=C.cno and SC.sno=S.sno and tname='李一') |
1 题目20、查询各选修学生最高分最低分之差大于30分的学生学号
2 SQL语句
3 select Sc.Sno As'学生学号' from Sc
4 group by Sno
5 having max(Grade)-min(Grade)>30;