20141031--SQL练习题
1 /* 通过代码操作:创建一个数据库,里面有一个学生信息表, 2 内容包括:学号,姓名,性别,体重,年龄,语数外三门课分数,班级 插入20条数据 3 执行以下查询操作: 4 1.查姓王的同学的信息 5 2.分别查每门课程最高分,最低分 6 3.查男同学的名字,只查一列 7 4.查每个班每门课程分别最高分最低分*/ 8 9 create database data01 10 go 11 use data01 12 go 13 create table xueshengxinxi--注意列之间用,隔开 14 ( 15 [No.] int, 16 name varchar(10), 17 sex varchar(10), 18 age int, 19 [weight] decimal(10,2), 20 Chinese int, 21 Math int, 22 English int 23 ) 24 insert into xueshengxinxi values(1,‘张蕾蕾‘,‘女‘,20,50,95,90,97) 25 insert into xueshengxinxi values(2,‘李鑫‘,‘男‘,21,65,90,90,88) 26 insert into xueshengxinxi values(3,‘刘莉莉‘,‘女‘,21,55,90,80,100) 27 insert into xueshengxinxi values(4,‘张峰‘,‘男‘,20,63,85,85,80) 28 insert into xueshengxinxi values(5,‘王乐‘,‘男‘,20,66,80,90,73) 29 insert into xueshengxinxi values(6,‘王馨茹‘,‘女‘,19,47,99,70,96) 30 insert into xueshengxinxi values(7,‘赵矿一‘,‘男‘,20,70,70,60,55) 31 insert into xueshengxinxi values(8,‘程依依‘,‘女‘,19,45,87,89,86) 32 insert into xueshengxinxi values(9,‘程依儿‘,‘女‘,19,45,90,85,87) 33 insert into xueshengxinxi values(10,‘孙厚‘,‘男‘,21,76,75,67,63) 34 insert into xueshengxinxi values(11,‘朱磊‘,‘男‘,20,67,70,78,71) 35 insert into xueshengxinxi values(12,‘王谦‘,‘男‘,22,71,80,81,74) 36 insert into xueshengxinxi values(13,‘孙丽娜‘,‘女‘,21,49,90,71,96) 37 insert into xueshengxinxi values(14,‘张乐乐‘,‘女‘,22,51,81,72,84) 38 insert into xueshengxinxi values(15,‘李忠‘,‘男‘,21,61,79,81,75) 39 insert into xueshengxinxi values(16,‘李艳艳‘,‘女‘,20,48,70,60,98) 40 insert into xueshengxinxi values(17,‘王萌萌‘,‘女‘,22,50,90,63,70) 41 insert into xueshengxinxi values(18,‘刘星‘,‘男‘,22,66,70,81,66) 42 insert into xueshengxinxi values(19,‘邹子冰‘,‘女‘,21,44,70,70,80) 43 insert into xueshengxinxi values(20,‘富国庆‘,‘男‘,22,70,80,50,70) 44 45 select *from xueshengxinxi 46 --查某姓的同学 47 select *from xueshengxinxi where name like ‘刘%‘ 48 --分别查每门课程最高分,最低分 49 select top 1 *from xueshengxinxi order by Chinese desc--Chinese最高分 50 select top 1 *from xueshengxinxi order by Math desc--Math最高分 51 select top 1 *from xueshengxinxi order by English desc--English最高分 52 select top 1 *from xueshengxinxi order by Chinese asc--Chinese最低分 53 select top 1 *from xueshengxinxi order by Math --Math最低分 54 select top 1 *from xueshengxinxi order by English --English最低分 55 -- 3.查男同学的名字,只查一列 56 select name from xueshengxinxi where sex=‘男‘ 57 -- 4.查每个班每门课程分别最高分最低分*/ 58 --把班级忘了, 59 alter table xueshengxinxi add Class varchar(10)--添加班级一列 60 update xueshengxinxi set Class=‘12级1班‘ where [No.] between 1 and 7 61 update xueshengxinxi set Class=‘12级2班‘ where [No.] between 8 and 14 62 update xueshengxinxi set Class=‘12级3班‘ where [No.] between 15 and 20 63 --查每个班每门课程分别最高分最低分 64 select top 1 *from xueshengxinxi where Class=‘12级1班‘ order by English 65 select top 1 *from xueshengxinxi where Class=‘12级1班‘ order by Chinese 66 select top 1 *from xueshengxinxi where Class=‘12级1班‘ order by Math 67 select top 1 *from xueshengxinxi where Class=‘12级1班‘ order by English desc 68 select top 1 *from xueshengxinxi where Class=‘12级1班‘ order by Chinese desc 69 select top 1 *from xueshengxinxi where Class=‘12级1班‘ order by Math desc 70 71 select top 1 *from xueshengxinxi where Class=‘12级2班‘ order by English 72 select top 1 *from xueshengxinxi where Class=‘12级2班‘ order by Chinese 73 select top 1 *from xueshengxinxi where Class=‘12级2班‘ order by Math 74 select top 1 *from xueshengxinxi where Class=‘12级2班‘ order by English desc 75 select top 1 *from xueshengxinxi where Class=‘12级2班‘ order by Chinese desc 76 select top 1 *from xueshengxinxi where Class=‘12级2班‘ order by Math desc 77 78 select top 1 *from xueshengxinxi where Class=‘12级3班‘ order by English 79 select top 1 *from xueshengxinxi where Class=‘12级3班‘ order by Chinese 80 select top 1 *from xueshengxinxi where Class=‘12级3班‘ order by Math 81 select top 1 *from xueshengxinxi where Class=‘12级3班‘ order by English desc 82 select top 1 *from xueshengxinxi where Class=‘12级3班‘ order by Chinese desc 83 select top 1 *from xueshengxinxi where Class=‘12级3班‘ order by Math desc
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。