数据库实验5

实验五:数据库单表查询

实验内容

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

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

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

select *  
from XSKC.student  
where sname like '王__'  
  1. 显示在1986年以后出生的学生的学号和姓名;
select sno,sname  
from XSKC.student  
where year(getdate())-sage>1986 
  1. 按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息,并重新命名各列;
select ssex 性别,sno 学号,sname 姓名,sage 年龄,sdept 院系  
from XSKC.student 
  1. 查询没有分配院系的学生的姓名和学号;
select sname,sno  
from XSKC.student  
where sdept is NULL 
  1. 显示学号第八位或者第九位是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]% 
  1. 列出选修了01号课程的学生的学号和成绩,按成绩的降序排列;
select sno,grade  
from XSKC.sc  
where cno='01'  
order by grade desc 
  1. 按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩;
select sno,cno,grade  
from XSKC.sc  
where grade between 70 and 80  
order by cno desc,grade desc 
  1. 按照院系降序显示所有学生的 “院系,学号、姓名、性别、年龄”等信息,其中院系按照以下规定显示:院系为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  
  1. 显示选修02号课程的成绩前两名的学生学号。
select top 2 sno  
from XSKC.sc  
where cno='02'  
order by grade desc 
  1. 列出同时选修“1”号课程和“2”号课程的所有学生的学号;
select sno  
from XSKC.sc  
where cno='01' and sno in  
(select sno  
from XSKC.sc  
where cno='02')  
  1. 显示所有院系(要求不能重复,不包括空值),并在结果集中增加一列字段“院系规模”,其中若该院系人数>=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  
  1. 显示学生信息表中的学生总人数及平均年龄,在结果集中列标题分别指定为“学生总人数,平均年龄”;
select count(*) 学生总人数,avg(sage) 平均年龄  
from XSKC.student  
  1. 显示选修的课程数大于3的各个学生的选修课程数;
select sno,count(cno) 选修课程数  
from XSKC.sc  
group by sno  
having count(cno)>3  
  1. 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩;
select cno,count(cno) 课程总人数,max(grade) 最高成绩,min(grade) 最低成绩,avg(grade) 平均成绩  
from XSKC.sc  
group by cno  
order by cno desc  
  1. 显示平均成绩大于“赵菁菁”平均成绩的各个学生的学号、平均成绩;
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='赵菁菁')) 
  1. 显示IS系学生的学号、姓名、性别、年龄,并统计出IS系的学生个数;
select sno,sname,ssex,sage  
from XSKC.student  
where sdept='IS'  
select count(*) 学生个数  
from XSKC.student  
where sdept='IS'  
  1. 显示选修课程数最多的学号及选修课程数最少的学号;
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)  
  1. 显示每个院系的学生前两条记录,并组成新表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)  
  1. *显示选修各个课程的及格的人数;
select cno 课程号,  
sum(case when grade>=60 then 1 else 0 end) 及格人数  
from XSKC.sc  
group by cno  
  1. *显示各个院系男女生人数,其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”;
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  
  1. *列出有二门以上课程(含两门)不及格的学生的学号及该学生的平均成绩;
select sno,avg(grade) 平均成绩  
from XSKC.sc  
where grade<60  
group by sno  
having count(cno)>=2 
  • 0
    点赞
  • 4
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:酷酷鲨 设计师:CSDN官方博客 返回首页
评论

打赏作者

Re:从零开始的代码生活

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值