利用sql建立教材数据库并定义以下基本表：学生（学号，年龄，性别，系名）教材（编号，书名，出版社编号，价格）订购（学号，书号，数量）出版社（编号，名称，地址）定义主码、外码、和价格、数数量的取值范围

create database MEDatabase
on primary(
name=MEData,
filename='d:\180521317\MEData_data.mdf',
size=10mb,
maxsize=50mb,
filegrowth=25%
)
log on(
name=MEDLOG,
filename='d:\180521317\MEData_log.idf',
size=10mb,
maxsize=unlimited, 
filegrowth=2mb
)
create table 学生
(
姓名 char(20),
学号 char(9)primary key,
年龄 smallint,
性别 char(2),
系名 char(20)
);
create table 教材
(
编号 char(20),
书名 char(50),
出版社编号 char(50),
价格 int check(价格 between 10 and 200),
primary key(书名,出版社编号)
);
create table 订购
(
学号 char(9),
书名 char(50),
数量 int check(数量>=30),
foreign key(学号) references 学生(学号)
);
create table 出版社
(
编号 char(50),
名称 char(50),
地址 char(80)
);

insert into 学生 values('赵刚', '1001',20,'男','计算机');
insert into 学生 values('刘丹', '1002',19,'女','计算机');
insert into 学生 values('张五', '1003',20,'男','理学院');
insert into 学生 values('黄贺', '1004',21,'男','电信院');
insert into 学生 values('程晨', '1005',18,'女','文学院');

insert into 教材 values('9787302','Java程序设计基础','ISBN978-302',69);
insert into 教材 values('9787148','数据结构与算法','ISBN978-148',59);
insert into 教材 values('9787408','C++程序设计','ISBN978-830',49);
insert into 教材 values('9787289','高等数学第二册','ISBN978-189',52);
insert into 教材 values('97873970','数据库原理概论','ISBN978-970',57);

insert into 订购 values('1001','Java程序设计基础',34);
insert into 订购 values('1001','C++程序设计',34);
insert into 订购 values('1002','高等数学第二册',36);
insert into 订购 values('1003','数据库原理概论',36);
insert into 订购 values('1004','数据结构与算法',35);
insert into 订购 values('1005','高等数学第二册',34);
insert into 订购 values('1002','Java程序设计基础',34);
insert into 订购 values('1003','数据库原理概论',34);
insert into 订购 values('1004','Java程序设计基础',34);

insert into 出版社 values('ISBN978-302','清华大学出版社','清华大学学研大厦A座');
insert into 出版社 values('ISBN978-148','清华大学出版社','清华大学学研大厦A座');
insert into 出版社 values('ISBN978-830','清华大学出版社','清华大学学研大厦B座');
insert into 出版社 values('ISBN978-189','清华大学出版社','清华大学学研大厦B座');
insert into 出版社 values('ISBN978-970','高教出版社','同济大学学研大厦A座');
#第二次实验
#1 查询订购高教出版社教材的学生姓名
select 姓名
from 学生
where 学号 in
(
select 学号
from 订购
where 书名 in
(
select 书名
from 教材
where 出版社编号 in
(
select 编号
from 出版社
where 名称='高教出版社'
)
)
);
#2 查询比所有高教出版的图书都贵的图书信息
select 教材.*
from 教材
where 价格 >
(
select 价格
from 教材
where 出版社编号 in
(
select 编号
from 出版社
where 名称='高教出版社'
)
);
#3 列出每位学生姓名、订购教材书名、价格。
select distinct 学生.姓名,教材.书名,价格
from 学生,教材,订购
where 学生.学号=订购.学号 and 订购.书名=教材.书名;

#第四次实验：
#1统计每位学生订数数量
select 姓名,学生.学号,sum(数量) 订购数量
from 订购,学生
where 学生.学号=订购.学号
group by 订购.学号;
#2-1 统计每位学生应缴书费。
select 姓名,sum(价格*数量) as 应缴学费
from 教材
left outer join 订购 on 订购.书名=教材.书名 
left outer join 学生 on 学生.学号=订购.学号
group by 姓名;
#2 -2统计每位学生应缴书费。
select 姓名,价格*数量 as 应缴学费
from 学生,教材,订购
where 学生.学号=订购.学号 and 订购.书名=教材.书名
group by 姓名;
#3 统计订购1000册以上的教材信息。
select *
from 教材
where 书名 in
(
select 书名
from 订购
where 数量>=35
);
#4-1 统计没有人定的教材信息。
select *
from 教材
where 书名 in
(
select 书名
from 订购
where 学号 not in
(
select 学号
from 学生
)
);
#4-2 统计没有人定的教材信息。(连接查询)
select 教材.*
from 教材,订购,学生
where 学生.学号=订购.学号 and 订购.书名=教材.书名 and 订购.学号!=学生.学号;
#第五次实验
#1 根据上面基本表的信息定义视图显示每个学生姓名、应缴书费
create view 书费名单(姓名,应缴书费)
as
select 姓名,sum(价格*数量) as 应缴学费
from 教材
left outer join 订购 on (订购.书名=教材.书名)
left outer join 学生 on(学生.学号=订购.学号)
group by 姓名;
#2 观察基本表数据变化时，视图中数据的变化。
create view 学生视图 (姓名,学号,年龄,性别,所属院系)
as
select *
from 学生;
select*
from 学生视图;
insert into 学生 values('陈硕','1008',22,'男','文学院');
select*
from 学生视图;
#3利用视图，查询交费最高的学生。
select 姓名,应缴书费 as 最高学费
from 书费名单
where 应缴书费 >=all
(
select 应缴书费
from 书费名单
);
#第六次实验
#建立学生的insert触发器，若向学生表中插入一条记录，则自动向订购表中插入一行，令该学生订购被订购数量最多的教材。
create trigger Insert_Student
on 学生
after insert
as
insert into 订购(学号)
select 学号
from inserted

insert into 学生 values('土鳖','10010',20,'男','计算机');

select *
from 订购;
select 学号,max(数量)
from 订购
group by 学号;
#第七次实验
#根据上面基本表的信息定义一个存储过程，完成下面功能：
#入口参数：教材编号
#1 显示教材信息
#2 如果没人买，删除该教材记录
#3 如果价格低于10元，涨价至10元。

#建立存储过程：
drop PROCEDURE Checked

CREATE PROCEDURE Checked
@编号 int
AS

select * from 教材 where 编号=@编号
if exists(select 数量 from 订购 where 数量 is null)
delete from 教材 where 编号=@编号
select * from 教材
if 10>(select 价格 from 教材 where 编号=@编号 )
begin
 update 教材 set 价格=10 where 编号=@编号
end
GO
#测试用:
exec Checked '97873489'