数据库实验5

实验五：数据库单表查询

实验内容

注：以下所有表都基于XSKC模式
以SQL实验4数据库中数据为基础，请使用T-SQL 语句实现以下操作：
1.列出所有不姓“张”的所有学生的基本信息；

select *  
from XSKC.student  
where sname not like '张%'  

2.列出姓“王”且全名为3个汉字的学生的基本信息；

select *  
from XSKC.student  
where sname like '王__'  

3. 显示在1986年以后出生的学生的学号和姓名；

select sno,sname  
from XSKC.student  
where year(getdate())-sage>1986 

4. 按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，并重新命名各列；

select ssex 性别,sno 学号,sname 姓名,sage 年龄,sdept 院系  
from XSKC.student 

5. 查询没有分配院系的学生的姓名和学号；

select sname,sno  
from XSKC.student  
where sdept is NULL 

6. 显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系；

select sno,sname,ssex,sage,sdept  
from XSKC.student  
where sno like '_______[1,2,3,4,9]%' or sno like '________[1,2,3,4,9]% 

7. 列出选修了01号课程的学生的学号和成绩，按成绩的降序排列；

select sno,grade  
from XSKC.sc  
where cno='01'  
order by grade desc 

8. 按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩；

select sno,cno,grade  
from XSKC.sc  
where grade between 70 and 80  
order by cno desc,grade desc 

9. 按照院系降序显示所有学生的 “院系，学号、姓名、性别、年龄”等信息，其中院系按照以下规定显示：院系为CS显示为计算机系，院系为IS显示为信息系，院系为MA显示为数学系，院系为EN显示为外语系，院系为CM显示为中医系，院系为WM显示为西医系，其他显示为院系不明；

select sno,sname,ssex,sage,sdept=  
case  
when sdept='CS' then '计算机系'  
when sdept='IS' then '信息系'  
when sdept='MA' then '数学系'  
when sdept='EN' then '外语系'  
when sdept='CM' then '中医系'  
when sdept='WM' then '西医系'  
when sdept not in ('CS','IS','MA','EN','CM','WM') then '院系不明'  
end  
from XSKC.student  

10. 显示选修02号课程的成绩前两名的学生学号。

select top 2 sno  
from XSKC.sc  
where cno='02'  
order by grade desc 

11. 列出同时选修“1”号课程和“2”号课程的所有学生的学号；

select sno  
from XSKC.sc  
where cno='01' and sno in  
(select sno  
from XSKC.sc  
where cno='02')  

12. 显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模”，其中若该院系人数>=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”， 若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”；

select DISTINCT sdept,院系规模=  
case   
when count(*)>=5 then '规模很大'  
when COUNT(*)>=4 and COUNT(*)<5 then '规模一般'  
when COUNT(*)>=2 and COUNT(*)<4 then '规模较小'  
else'规模很小'  
end  
from XSKC.student  
where sdept is not null  
group by sdept  

13. 显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；

select count(*) 学生总人数,avg(sage) 平均年龄  
from XSKC.student  

14. 显示选修的课程数大于3的各个学生的选修课程数；

select sno,count(cno) 选修课程数  
from XSKC.sc  
group by sno  
having count(cno)>3  

15. 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；

select cno,count(cno) 课程总人数,max(grade) 最高成绩,min(grade) 最低成绩,avg(grade) 平均成绩  
from XSKC.sc  
group by cno  
order by cno desc  

16. 显示平均成绩大于“赵菁菁”平均成绩的各个学生的学号、平均成绩；

select sno 学号,avg(grade) 平均成绩  
from XSKC.sc  
group by sno  
having avg(grade)>(  
select avg(grade)  
from XSKC.sc  
where sno in(  
select sno  
from XSKC.student  
where sname='赵菁菁')) 

17. 显示IS系学生的学号、姓名、性别、年龄，并统计出IS系的学生个数；

select sno,sname,ssex,sage  
from XSKC.student  
where sdept='IS'  
select count(*) 学生个数  
from XSKC.student  
where sdept='IS'  

18. 显示选修课程数最多的学号及选修课程数最少的学号；

select sno 选修课程最多  
from XSKC.sc  
group by sno  
having count(sno)>=all(  
select count(sno)  
from XSKC.sc  
group by sno)  
select sno 选修课程最少  
from XSKC.sc  
group by sno  
having count(sno)<=all(  
select count(sno)  
from XSKC.sc  
group by sno)  

19. 显示每个院系的学生前两条记录，并组成新表ceshi；

select student2.*   
into ceshi  
from XSKC.student student2  
where sno in (  
select top 2 sno   
from XSKC.student  
where sdept=student2.sdept  
order by sno)  

20. *显示选修各个课程的及格的人数；

select cno 课程号,  
sum(case when grade>=60 then 1 else 0 end) 及格人数  
from XSKC.sc  
group by cno  

21. *显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；

select sdept 院系名称,  
sum(case ssex when '男' then 1 else 0 end) 男生人数,  
sum(case ssex when '女' then 1 else 0 end) 女生人数  
from XSKC.student  
group by sdept  

22. *列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；

select sno,avg(grade) 平均成绩  
from XSKC.sc  
where grade<60  
group by sno  
having count(cno)>=2