数据库开发基础-自己动手、丰衣足食
时间:2021-07-01 10:21:17
帮助过:3人阅读
/*练习:
2 通过代码操作,创建一个数据库,里面新建一个学生信息表
3 内容包括:学号、姓名、性别、体重、年龄和语数外三门课的的分数及所在班级
4 插入20条数据
5 1、查姓王的同学的信息
6 2、分别查每门课的最高分和最低分
7 3、查男同学的所有名字,一列
8 4、查每个班每门课程分别的最高分和最低分
9 */
10 create database student
11 go
12 use student
13 go
14 create table biao
15 (
16 code
int,
17 name
varchar(
20),
18 sex
char(
10),
19 tizhong
decimal(
18,
1),
20 age
int,
21 yuwen
decimal(
18,
1),
22 shuxue
decimal(
18,
1),
23 yingyu
decimal(
18,
1),
24 banji
varchar(
20),
25 )
26 go
27 insert into biao
values(
1,
‘邓紫棋‘,
‘女‘,
50,
25,
90,
88,
70,
‘一班‘)
28 insert into biao
values(
2,
‘李易峰‘,
‘男‘,
66,
29,
89,
90,
89,
‘一班‘)
29 insert into biao
values(
3,
‘范冰冰‘,
‘女‘,
45,
35,
91,
78,
94,
‘一班‘)
30 insert into biao
values(
4,
‘杨幂‘,
‘女‘,
46,
30,
92,
85,
78,
‘一班‘)
31 insert into biao
values(
5,
‘唐嫣‘,
‘女‘,
44,
33,
88,
80,
87,
‘一班‘)
32 insert into biao
values(
6,
‘吴亦凡‘,
‘男‘,
67,
26,
90,
94,
62,
‘一班‘)
33 insert into biao
values(
7,
‘刘诗诗‘,
‘女‘,
52,
28,
92,
83,
95,
‘一班‘)
34 insert into biao
values(
8,
‘周星驰‘,
‘男‘,
70,
54,
94,
85,
90,
‘一班‘)
35 insert into biao
values(
9,
‘李小璐‘,
‘女‘,
48,
35,
87,
86,
89,
‘一班‘)
36 insert into biao
values(
10,
‘柳岩‘,
‘女‘,
46,
36,
67,
92,
67,
‘一班‘)
37 insert into biao
values(
11,
‘周杰伦‘,
‘男‘,
62,
37,
88,
65,
85,
‘二班‘)
38 insert into biao
values(
12,
‘鹿晗‘,
‘男‘,
59,
27,
91,
75,
78,
‘二班‘)
39 insert into biao
values(
13,
‘林志颖‘,
‘男‘,
65,
42,
90,
90,
88,
‘二班‘)
40 insert into biao
values(
14,
‘谢娜‘,
‘女‘,
44,
35,
89,
80,
91,
‘二班‘)
41 insert into biao
values(
15,
‘汪峰‘,
‘男‘,
78,
45,
94,
78,
77,
‘二班‘)
42 insert into biao
values(
16,
‘章子怡‘,
‘女‘,
49,
37,
78,
91,
87,
‘二班‘)
43 insert into biao
values(
17,
‘吴镇宇‘,
‘男‘,
79,
55,
79,
87,
67,
‘二班‘)
44 insert into biao
values(
18,
‘陈学冬‘,
‘男‘,
60,
27,
93,
67,
78,
‘二班‘)
45 insert into biao
values(
19,
‘王祖蓝‘,
‘男‘,
62,
36,
89,
80,
90,
‘二班‘)
46 insert into biao
values(
20,
‘王宝强‘,
‘男‘,
60,
32,
95,
90,
89,
‘二班‘)
47 go
48 --查姓王的同学的所有信息
49 select*from biao
where name
like ‘王%‘
50 --查语文分数最高的学生的所有信息
51 select top 1*from biao
order by yuwen
desc
52 --查数学分数最高的学生的所有信息
53 select top 1*from biao
order by shuxue
desc
54 --查英语分数最高的学生的所有信息
55 select top 1*from biao
order by yingyu
desc
56
57 --查所有男同学的名字
58 select name
from biao
where sex
=‘男‘
59
60 --查一班的语文最高分和最低分
61 select top 1*from biao
where banji
=‘一班‘ order by yuwen
desc --最高分
62 select top 1*from biao
where banji
=‘一班‘ order by yuwen
--最低分
63
64 --查一班的数学最高分和最低分
65 select top 1*from biao
where banji
=‘一班‘ order by shuxue
desc
66 select top 1*from biao
where banji
=‘一班‘ order by shuxue
67
68 --查一班的英语最高分和最低分
69 select top 1*from biao
where banji
=‘一班‘ order by yingyu
desc
70 select top 1*from biao
where banji
=‘一班‘ order by yingyu
71
72 --查二班的语文最高分和最低分
73 select top 1*from biao
where banji
=‘二班‘ order by yuwen
desc
74 select top 1*from biao
where banji
=‘二班‘ order by yuwen
75
76 --查二班的数学最高分和最低分
77 select top 1*from biao
where banji
=‘二班‘ order by shuxue
desc
78 select top 1*from biao
where banji
=‘二班‘ order by shuxue
79
80 --查二班的英语最高分和最低分
81 select top 1*from biao
where banji
=‘二班‘ order by yingyu
desc
82 select top 1*from biao
where banji
=‘二班‘ order by yingyu
数据库开发基础-自己动手、丰衣足食
标签: