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