数据库复习

例题1（数据库）

--(1) 建立一个数据库，数据库名为：我班同学库。

create database 我班同学库

--(2) 用企业管理器在我班同学库中建立如下数据表。

--①同学表(学号char(6)，姓名nchar(4)，性别nchar(1)，出生日期--datetime，身高decimal(5,2)，民族nchar(5)，身份证号char(18)，宿--舍编号char(6))。

use 我班同学库

create table 同学表

(学号char(6) primary key,

姓名nchar(4) not null,

性别nchar(1) check ( 性别='男' or 性别='女'),

出生日期datetime not null,

身高decimal(5,2) not null,

民族nchar(5) default '汉族',

身份证号char(18) not null,

宿舍编号char(6) not null)

--②宿舍表(宿舍编号char(6)，宿舍电话号码char(12))。

use 我班同学库

create table 宿舍表

(宿舍编号char(6) primary key,

宿舍电话号码char(12)

check (宿舍电话号码like

'0551-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))

--(3)

use 我班同学库

create table 课程表

(课程号char(6) primary key,

课程名称varchar(10) not null)

use 我班同学库

create table 选课表

(学号char(6) foreign key (学号) references 同学表(学号) ,

课程号char(6) foreign key (课程号) references 课程表(课程号), 分数decimal(5,2) not null,

primary key (学号,课程号)

)

--(5) 创建一默认值m-性别，默认取男，并将其应用到同学表的性别列上create default m_性别as '男'

exec sp_bindefault m_性别,'同学表.性别'

--(6) 创建一规则r_分数，要求分数必须是在-100之间的数值，并将其应用到选课表的分数列上。

create rule r_分数as @分数between 0 and 100

exec sp_bindrule r_分数,'选课表.分数'

--3. 向以上数据表中输入所在班同学的真实数据。（每张表至少条数据）

--(1)

insert into 同学表(学号,姓名,性别,身高) values ('s10','张三','男',1.60)

--(2)

update 同学表set 性别='女' where 学号='s10'

--(3)

delete from 同学表where 学号='s10'

例题2（视图）

--1 创建学生成绩视图，要求查询所有选课学生的学号、姓名、课程名称及分数。

create view 学生成绩视图

as

select 学生表.学号,姓名,课程名称,分数

from 学生表,课程表,成绩表

where 学生表.学号=成绩表.学号and 课程表.课程号=成绩表.课程号

--2 创建班级学生视图，要求显示班级名称，学号，姓名，性别。

create view 班级学生视图

as

select 班级名称,学号,姓名,性别

from 学生表,班级表

where 学生表.班级号=班级表.班级号

select * from 班级学生视图

--3 通过班级学生视图，添加学号为，姓名为张三，性别为男的学生信息。insert into 班级学生视图(学号,姓名,性别) values ('001','张三','男')

4 通过班级学生视图，将学号为的学生性别改为女。

update 班级学生视图set 性别='女' where 学号= '001'

5 通过班级学生视图，删除学号为的学生信息。

delete from 班级学生视图where 学号='001'

6 创建教师任课门数统计视图，要求查询每位教师的教师号及其任课的门数。create view 教师任课门数统计视图

as

select 教师号, count(distinct 课程号) as 任课门数

from 成绩表

group by 教师号

select * from 教师任课门数统计视图

7 创建学生课程视图，要求查询选修两门以上课程的学生学号和选课门数。create view 学生课程视图

as

select 学号,count(distinct 课程号) as 选课门数

from 成绩表

group by 学号

having count(distinct 课程号)>=2

select * from 学生课程视图

8 修改学生成绩视图，要求查询选课在三门以上且各门课程均及格的学生的学号及其总成绩。

alter view 学生成绩视图

as

select 学号,count(distinct 课程号) as 选课门数,sum (分数) as 总成绩

from 成绩表