3对学生表、课程表和成绩表的相关操作

课程表和成绩表的相关操作"/>

3对学生表、课程表和成绩表的相关操作

使用所建立的以下数据表,完成以下操作.
1 学生表(学号 姓名 专业名 性别 生日 照片 备注 团员否)
2 课程表（课程号 课程名称 任课教师 学分）
3 成绩表（学号 课程号 成绩 学分）
1 定义变量x,赋值为4,输出表达式x^2+4x-1的值
declare @x int,@s int
set @x=4
set @s=@x*@x+4*@x-1
select ‘x^2+4*x-1=’+convert(nchar(3),@s)

2 求出今天的102天前是哪一天
declare @a datetime
set @a=GETDATE()-102
select @a

3 从学生表中查出年龄(根据生日计算)在25-30岁之间的学生的姓名,生日,性别字段数据
declare @age int
select @age=YEAR(getdate())-YEAR(出生日期) from 学生表
select 姓名,性别,出生日期 from 学生表 where (@age>=25 and @age<=30)

4 定义变量y，把学生表中李大方的生日保存在变量y中，再根据这个 y的值求出年龄数，此人已经活了多少天，今年他的生日是已经过了，还是没有过(此处可能要使用到函数dateadd(日期部分类型，差值，原日期)
补充: 计算日期时间上的年,天,日,小时的加减,用 :
dateadd(类型,累加值,日期时间的基数)
类型:可以是 y/年,m/月,d/天 , 累加值表示按类型前推或后推指定数值
如: select DATEADD(d,-102, GETDATE())
求今天的前102天是哪一个日期
declare @y smalldatetime,@m smalldatetime,@n int
select @y=生日 from 学生表 where 姓名=‘李大方’;
set @m=DATEADD(Yyyy,year(GETDATE())-year(@y),@y)
select @y, @m
set @n=CONVERT (int, GETDATE()-@m)
if(@n>0)
select ‘此人的生日已经过了’+convert(nchar(3),@n)+‘天’
else
select ‘生日还没过,还差’+convert(nchar(3),-@n)+‘天’

引举:
商品表
商品号,商品名,生产生日 ,保质期天,保质期月
01 牛奶1 2013-6-5 180
02 饼干 2013-5-1 4
Select * from 商品表 where
convert(int,getdate()- dateadd(d,保质期,生产日期))<0
如果保质期
Select * from 商品表 where
convert(int,getdate()- dateadd(m,保质期,生产日期))<0

5 求出杨春比李大方大几岁
declare @age int
select @age=YEAR(getdate())-YEAR(出生日期) from 学生表
select ‘杨春比李大方大’+convert(varchar,((select @age from 学生表 where 姓名=‘杨春’)-(select @age from 学生表 where 姓名=‘李大方’)))+‘岁’

6 根据生日数据求出每个人的年龄,并把年龄填充到表中的年龄字段中
select *,YEAR(getdate())-YEAR(出生日期) as 年龄 from 学生表

7 判断今天是学生表中哪些人的生日
并按如下格式输出:
杨春 ,生日快乐!
陈明明,生日快乐!
declare @birth int,@xm char(6)
select @birth=CONVERT(int,getdate()-‘1988-12-1’)
if(@birth=0) select @xm=姓名 from 学生表 where (select @birth from 学生表)=0 +‘生日快乐’
else select’今天没有人过生日…’

8 查出表中所有姓杨的人,使用至少两种方法
（1）select * from 学生表 where LEFT(姓名,1)=‘李’
（2）select * from 学生表 where 姓名 like ‘李%’

9求学生全班人的平均年龄是多大?
declare @age int
select @age=YEAR(getdate())-YEAR(出生日期) from 学生表
select ‘全班人的平均年龄是：’+convert(varchar,AVG(@age))

10 查出所有团员男生数据
select * from 学生表 where 性别=‘男’ and 团员否=‘团员’

11 按性别降序排序查询数据
select * from 学生表 order by 性别 desc

12 查出身高前4名的学生数据
select top 4 * from 学生表 order by 身高

13 查出语文成绩较好的一半学生
select 姓名 from 学生表 where 学号 in
(select top 50 percent 学号 from 成绩表 where 课程名称=语文 order by 成绩)

14 新建两个表”学生1”,学生2,把学生表中的所有男生复制到学生1表中;
把所有女生团员复制到学生2表中
select * into 学生1 from 学生表 where 性别=‘男’
select * into 学生2 from 学生表 where 性别=‘女’ and 团员否=‘团员’

15 使用成绩表完成: 按成绩降序显示数据
select * from 成绩表 order by 成绩 desc

16按学号分组分段,且每个学生自己的各科成绩降序显示数据
select * from 成绩表 group by 学号 order by 成绩 desc

17 求选104课程的学生人数
select COUNT(*) from 成绩表 where 课程号=104

18 求出选101课程的人数比102选课人数多多少人
select (select COUNT() from 成绩表 where 课程号=101)-(select COUNT() from 成绩表 where 课程号=102)

附加题：
教课表
教师号 姓名 课程号 课时 人数 工作量
01 张三 101 64 110
01 张三 102 48 45
02 李四 103 72 95
法则: 上课人数基数是50人,如100人上一节课工作为2,
75人上一节课工作量为1.5; 不足50人算50人
最后统计出每个老师的总工作量
declare@a int,@b char(6),@c int
Select @a=人数 from 教课表
If（@a>=75））set @b=2
Else if(@>=50) set @b=1.5
Else set @b=1
Select *,@b as 工作量 from 教课表

19求101课程的成绩前两名记录
select top 2 * from 成绩表 where 课程号=101 order by 成绩

20 求101课程的平均成绩,最高成绩,最低成绩
select AVG(成绩) as 平均成绩,MAX(成绩) as 最高成绩,MIN(成绩) as 最低成绩 from 成绩表 where 课程号=101

21 查询学生表，结果先按性别升序排序，如性别相同者再按身高降序
select * from 学生表 order by 性别 asc,身高 desc

22查询出102课程的平均分比103课程平均分高多。
select (select AVG(成绩) from 成绩表 where 课程号=102)-(select AVG(成绩) from 成绩表 where 课程号=103)

23找出学生表中男生的入校成绩最高分
select top 1 姓名,入校成绩 from 学生表 where 性别=‘男’ order by 入校成绩

24 求出成绩表中102课程成绩高于本课程平均的人的姓名和性别。
declare @avg int
select @avg=AVG(成绩) from 成绩表 where 课程号=101
select 姓名,性别 from 学生表 where 学号 in(select 学号 from 成绩表 where 课程号=101 and 成绩>@avg)

25 查出成绩表中哪些课程选课人数>=3
select * ,count() as 选课人数 from 成绩表 group by 课程号 having COUNT()>=3

26 求男生中选课人数超过4门的有哪些人，列出姓名和性别
select 姓名,性别 from a where 学号 in (select 学号 from 成绩表 group by 学号 having COUNT(*)>=4)

27 统计成绩表中各个学生选课门数及该生平均分
select 学生表.姓名,count(成绩表.课程号) as 选课门数,AVG(成绩表.成绩) as 平均分 from 学生表,课程表,成绩表 where 学生表.学号=学生表.学号 and 课程表.课程号=成绩表.课程号 group by 学生表.姓名

28在成绩表中查询出选课门数>=2的情况
select 课程号,学分, COUNT() from 成绩表 group by 课程号,学号 having count()>=2

29把成绩表中的101,102课程成绩情况复制到一个新表“成绩temp”中
select * into 成绩temp from 成绩表 where 课程号 in(101,102)
select * from 成绩temp