---1、用create database语句创建“学籍管理”数据库，要求主数据文件逻辑文件名为：xjgl，物理文件名为：d:\姓名\xjgl.mdf,其他项可采用默认值或自定义。

create database 学籍管理

on primary

(

        name='xjgl',

        filename='d:\王蕾\xjgl.mdf'

)

---2、用create table命令为“学籍管理”数据库创建“xsxx”和“xkk”两个表，两个表的结构如下：

create table xsxx

(

        学号 char(12) primary key,

        姓名 varchar(20),

        性别 varchar(50) default '男',

        出生年月 datetime

)

create table xkk

(

        学号 char(12),

        课程号 char(4),

        成绩 decimal(18,2)

)

---3、用insert into命令插入记录，两个表的记录如图。

insert into xsxx values('201710624128','史雪慧','女','1998-11-19')

insert into xsxx values('201710624127','沈晓敏','女','1999-01-01')

insert into xsxx values('201710624129','魏波','男','1998-01-23')

insert into xsxx values('201410624101','张三','男','1995-02-01')

insert into xkk values('201410624101','0101',96)

insert into xkk values('201410624101','0102',62)

insert into xkk values('201410624101','0103',82)

insert into xkk values('201710624128','0101',100)

insert into xkk values('201710624128','0102',90)

insert into xkk values('201710624127','0101',50)

---4、用SQL语句修改表的结构和记录：

----（1）为“xsxx”添加一列：高考成绩，类型为float，可以为空。

alter table xsxx add 高考成绩 float null

----（2）把“xsxx”的性别的长度变为2。

alter table xsxx alter column 性别 varchar(2)

----（3）把“xkk”中课程号为0102的成绩增加2分。

update xkk set 成绩=成绩+2 where 课程号='0102'

----（4）删除成绩不及格的记录。

delete from xkk where 成绩<60

----（5）修改表增加约束，把“xkk”中的成绩限制在大于等于零并且小于等于100的范围内。

alter table xkk add constraint check_cj check(成绩>=0 and 成绩<=100)

---5、用SELECT语句查询：

----（1）在“xsxx”中查询姓名含有“三”这个字的学生信息。

select * from xsxx where 姓名 like '%三%'

----（2）在“xsxx”中查询男性且出生年月在1998年以前出生的学生信息。

select * from xsxx where 性别='男' and year(出生年月)<1998

----（3）在“xsxx”中查询姓名是三个字的学生信息。

select * from xsxx where 姓名 like '___'

----（4）在“xkk”中查询每个人的平均成绩。

select 学号,AVG(成绩) 平均成绩 from xkk group by 学号

----（5）查询学生选课情况，内容包括姓名，课程号，成绩。

select xsxx.姓名,课程号,成绩 from xsxx,xkk where xsxx.学号=xkk.学号

----（6）查询还没有选课的学生的信息。（即在xkk表中没有相应的xsxx的学号）

select * from xsxx where 学号 not in(select 学号 from xkk)

----（7）在“xkk”中查询共有几门课被选。

select COUNT(distinct 课程号) 选课门数 from xkk

----（8）查询每门课的平均成绩高于60的选课信息。

select 课程号,AVG(成绩) 平均成绩 from xkk group by 课程号 having AVG(成绩)>=60

----（9）查询成绩在70以上每门课的平均成绩。

select 课程号,AVG(成绩) 平均成绩 from xkk where 成绩>=70 group by 课程号

----（10）查询和你选了同一门课的其他人的姓名。

select distinct(姓名) from xkk inner join xsxx on xkk.学号=xsxx.学号 where xkk.学号 <>'201710624128'

---6、创建视图v_lr，查询“xsxx”和“xkk”，显示每个人的选课情况，要求学号、姓名、性别、课程号、成绩。

create view v_lr

as

SELECT     dbo.xsxx.学号, dbo.xsxx.姓名, dbo.xsxx.性别, dbo.xkk.课程号, dbo.xkk.成绩

FROM         dbo.xkk INNER JOIN

                      dbo.xsxx ON dbo.xkk.学号 = dbo.xsxx.学号

---7、创建一个触发器cfq,实现在试图v_lr中插入记录从而实现插入基本表中的功能。例如：当执行insert into v_lr(学号、姓名、性别、课程号、成绩) values(‘201410624102’,’张三丰’,’男’,’0101’,60)时，分别在xsxx和xkk中分别插入相应记录。

create trigger cfq on v_lr

instead of insert

as

declare @学号 char(12),@姓名 varchar(20),@性别 varchar(2),@课程号 char(4),@成绩 decimal(18,2)

select @学号=学号,@姓名=姓名,@性别=性别,@课程号=课程号,@成绩=成绩 from

inserted

insert into xsxx(学号,姓名,性别) values(@学号,@姓名,@性别)

insert into xkk(学号,课程号,成绩) values(@学号,@课程号,@成绩)

go

insert into v_lr values('201410624102','张三丰','男','0101',60)