SqlServer实验报告

实验内容     使用数据库和表（表中要有大量有效数据）. 1 学生表(学号  姓名  专业名  性别  生日  照片 备注 团员否) 2 课程表（课程号  课程名称  任课教师   学分）  3 成绩表（学号  课程号  成绩 学分） 实验内容: 1 查出大学英语男生的成绩情况:学号, 课程号,成绩 (尽量使用两种办法分别实现) 答: 第一种 select  学号,课程号,成绩 from 成绩表 where 学号 in (select  学号 from 学生表 where 性别='男') and 课程号 in (select 课程号 from 课程表 where 课程号='D005' ) 第二种 declare @kch nchar(4) select @kch=课程号 from 课程表 where 课程名称='英语' select  学号,课程号,成绩 from 成绩表 where 课程号=@kch and 学号 in (select 学号 from 学生表 where 性别='男') 2 查出选了高等数学(上和下)的学生情况:学号,姓名,生日,课程号,名称,成绩 答: select * into temp1 from 成绩表 where 课程号='D002' or 课程号='D003' order by 课程号 select temp1.学号,temp1.课程号,课程名称,temp1.成绩 into temp2 from temp1,课程表 where temp1.课程号=课程表.课程号 select 学生表.学号,姓名,出生日期,temp2.课程号,temp2.课程名称,temp2.成绩  from temp2,学生表 where temp2.学号=学生表.学号 order by 课程号 3 查出与李大方的大学英语成绩相同的其它学生的情况:学号,姓名,课程号,名称,成绩 答: declare @xh char(10) select @xh=学号 from 学生表 where 姓名='李大方' declare @kch nchar(4) select @kch=课程号 from 课程表 where 课程名称='英语' declare @cj numeric(4,1) select @cj =成绩 from 成绩表 where 学号=@xh select 学号,成绩表.课程号 ,课程表.课程名称,成绩 into 临时表 from 成绩表,课程表 where 成绩表.课程号=@kch and 成绩=@cj and  成绩表.课程号=课程表.课程号 select 学生表.学号,学生表.姓名,临时表.课程号 ,临时表.课程名称,临时表.成绩 into temp6 from 学生表,临时表 where  临时表.学号=学生表.学号 delete from temp6 where 姓名='李大方' select *from temp6 4 查出哪些学生没有选修大学英语:学号,姓名 答: select  姓名 ,学号 from 学生表 where 学号 not in (select 学号 from 成绩表 where 课程号='D005' ) 5 查出选修了大学英语学生成绩情况,并按成绩值降序显示:学号,课程号,成绩 答:select 学号,课程号,成绩 from 成绩表 where 课程号='D005'order by 成绩 desc 6 排序:先按性别排,性别相同则按是否团员排,再相同就按生日降序排. 答:select *from 学生表 order by 性别, 是否党员 ,出生日期 asc 7 根据生日计算,把学生表中年龄最小的两个男生同学查询出来 答:select top 2 *from 学生表 order by getdate()-出生日期 asc 8 把年龄较大的一半学生显示出来  答:select top 50 percent *from 学生表 order by getdate()-出生日期 desc 9  求出李大方比李四方大多少岁。  方法1：直接使用一条SQL命令实现 select convert(smallint,(datediff(day ,出生日期,GETDATE()))*1.0/365 ) -( select convert(smallint,(datediff(day ,出生日期,GETDATE()))*1.0/365 )   from 学生表 where  姓名='李四方') as 年龄  from 学生表 where  姓名='李大方'  方法2：使用变量法实现。定义变量，把李方大和李四方两个同学的年龄通过生日计算出来保存两个变量中，最后求出二人相差多少岁 declare @天数1 smallint declare @天数2 smallint select @天数1=(datediff(day ,出生日期,GETDATE())) from 学生表 where 姓名='李四方' select @天数2=(datediff(day ,出生日期,GETDATE())) from 学生表 where 姓名='李大方' print '李大方比李四方大'+convert(nchar(2),(@天数2- @天数1)/365)+'岁！' 10 向课程表中添加一个列：课程编码 nchar(4) , 要求课程编码的第一个字母必须是C，第2-4个字符必须是数字1-9中一个 alter table 课程表 add 课程编码 nchar(4) check (课程编码 like '[C][0-9][0-9][0-9]') 11 从学生表中查询出：学号，姓名，年龄 select  学号,姓名,year(getdate())-Year(出生日期)  as 年龄 from 学生表 12 查询出生日为今天的学生信息 select * from 学生表 where (day(GETDATE())-day(出生日期))%365=0  13 从成绩表中查出有哪些课程被选 select 课程名称 from 课程表 where 课程号 in (select distinct 课程号 from 成绩表 ) 14 从成绩表中查询已选课程有几门 select count(课程名称) from 课程表 where 课程号 in (select distinct 课程号 from 成绩表 ) 或者 select count(distinct 课程号) from 成绩表 15 从学生表中，查出1985—1991年之间出生的学生 select * from 学生表 where year(出生日期) between 1985 and 1991 16 使用between and方法，查出成绩表中成绩在80-95之间的情况 select *from 成绩表 where 成绩 between 80 and 95 17 从成绩表中查出课程101，104课程的情况 select * into temp5 from 成绩表 where 课程号='D001'or 课程号='D002' select *from temp5,课程表 where temp5.课程号=课程表.课程号 18 查出家庭住址中含有“脾县”二字的学生情况 select *from 学生表 where 家庭住址 like  '%[郫][县]%' 19 查出所有姓李和杨的学生情况，请使用至少两种方法实现 第一种 select *from 学生表 where left(姓名,1)='李'or left(姓名,1)='杨' 第二种 select *from 学生表 where substring (姓名,1,1)='李'or substring (姓名,1,1)='杨' 第三种 select *from 学生表 where left(姓名,1) like '[李杨]' 第四种 select *from 学生表 where 姓名 like '[李杨]%' 第五种 select *from 学生表 where left(姓名,1) in('李','杨') 20 把学生表拆分成男，女生两个独立的表. 再把两个表合并到一个新表”学生总” select* into temp1 from 学生表 where 5>6 select * into 男生表 from 学生表 where 性别='男' select * into 女生表 from 学生表 where 性别='女' insert into temp1   select *from 男生表 insert into temp1   select *from 女生表 21 查出计算机基础和大学英语课程的成绩情况  select 课程名称,成绩 from 课程表,成绩表  where  课程表.课程号=成绩表.课程号 and (成绩表.课程号='D005'or 成绩表.课程号='D008') order by 课程名称