数据库开发基础-自己动手、丰衣足食

/*练习：
    通过代码操作，创建一个数据库，里面新建一个学生信息表
    内容包括：学号、姓名、性别、体重、年龄和语数外三门课的的分数及所在班级
    插入20条数据
     1、查姓王的同学的信息
     2、分别查每门课的最高分和最低分
     3、查男同学的所有名字，一列
     4、查每个班每门课程分别的最高分和最低分
 */ 
 create database student
 go
 use student
 go
 create table biao
 (
   code int,
   name varchar(20),
   sex char(10),
   tizhong decimal(18,1),
   age int,
   yuwen decimal(18,1),
   shuxue decimal(18,1),
   yingyu decimal(18,1),
   banji varchar(20),
 )
 go
 insert into biao values(1,'邓紫棋','女',50,25,90,88,70,'一班')
 insert into biao values(2,'李易峰','男',66,29,89,90,89,'一班')
 insert into biao values(3,'范冰冰','女',45,35,91,78,94,'一班')
 insert into biao values(4,'杨幂','女',46,30,92,85,78,'一班')
 insert into biao values(5,'唐嫣','女',44,33,88,80,87,'一班')
 insert into biao values(6,'吴亦凡','男',67,26,90,94,62,'一班')
 insert into biao values(7,'刘诗诗','女',52,28,92,83,95,'一班')
 insert into biao values(8,'周星驰','男',70,54,94,85,90,'一班')
 insert into biao values(9,'李小璐','女',48,35,87,86,89,'一班')
 insert into biao values(10,'柳岩','女',46,36,67,92,67,'一班')
 insert into biao values(11,'周杰伦','男',62,37,88,65,85,'二班')
 insert into biao values(12,'鹿晗','男',59,27,91,75,78,'二班')
 insert into biao values(13,'林志颖','男',65,42,90,90,88,'二班')
 insert into biao values(14,'谢娜','女',44,35,89,80,91,'二班')
 insert into biao values(15,'汪峰','男',78,45,94,78,77,'二班')
 insert into biao values(16,'章子怡','女',49,37,78,91,87,'二班')
 insert into biao values(17,'吴镇宇','男',79,55,79,87,67,'二班')
 insert into biao values(18,'陈学冬','男',60,27,93,67,78,'二班')
 insert into biao values(19,'王祖蓝','男',62,36,89,80,90,'二班')
 insert into biao values(20,'王宝强','男',60,32,95,90,89,'二班')
 go
 --查姓王的同学的所有信息
 select*from biao where name like '王%'
 --查语文分数最高的学生的所有信息
 select top 1*from biao order by yuwen desc
 --查数学分数最高的学生的所有信息
 select top 1*from biao order by shuxue desc
 --查英语分数最高的学生的所有信息
 select top 1*from biao order by yingyu desc
 
 --查所有男同学的名字
 select name from biao where sex='男'
 
 --查一班的语文最高分和最低分
 select top 1*from biao where banji='一班' order by yuwen desc  --最高分
 select top 1*from biao where banji='一班' order by yuwen       --最低分
 
 --查一班的数学最高分和最低分
 select top 1*from biao where banji='一班' order by shuxue desc
 select top 1*from biao where banji='一班' order by shuxue 
 
 --查一班的英语最高分和最低分
 select top 1*from biao where banji='一班' order by yingyu desc
 select top 1*from biao where banji='一班' order by yingyu 
 
 --查二班的语文最高分和最低分
 select top 1*from biao where banji='二班' order by yuwen desc
 select top 1*from biao where banji='二班' order by yuwen 
 
 --查二班的数学最高分和最低分
 select top 1*from biao where banji='二班' order by shuxue desc
 select top 1*from biao where banji='二班' order by shuxue 
 
 --查二班的英语最高分和最低分
 select top 1*from biao where banji='二班' order by yingyu desc
 select top 1*from biao where banji='二班' order by yingyu