sql

CreateDatabase

create database my2017

go

use my2017

go

create table Department -- 大科室

(

DeptNo char ( 6 ) not null,

DeptName varchar ( 50 ) not null,

constraint PK_Department primary key ( DeptNo )

)

create table [Group] -- 小科室

(

GroupNo char ( 4 ) not null,

GroupName varchar ( 50 ) not null,

DeptNo char ( 6 ) not null,

[Month] char ( 2 ),

Number int check ( Number <= 80 ),

constraint PK_Group primary key ( GroupNo ),

constraint FK_Group_Department foreign key ( DeptNo ) references Department ( DeptNo )

)

create table Patient -- 病人

(

PID char ( 14 ) not null,

PName varchar ( 50 ) not null,

[Date] datetime default(getdate()) ,

GroupNo char ( 4 ) not null,

constraint PK_Patient primary key ( PID ),

constraint FK_Patient_Group foreign key ( GroupNo ) references [Group] ( GroupNo ) on delete cascade

)

insert into Department values ( '111' , ' 内科 ' )

insert into Department values ( '222' , ' 外科 ' )

insert into Department values ( '333' , ' 骨科 ' )

go

insert into [Group] values ( '11' , ' 呼吸科 ' , '111' , '3' , 68 )

insert into [Group] values ( '12' , ' 心内科 ' , '111' , '4' , 43 )

insert into [Group] values ( '13' , ' 胸外科 ' , '222' , '3' , 52 )

insert into [Group] values ( '14' , ' 骨外科 ' , '333' , '3' , 28 )

insert into [Group] values ( '15' , ' 外伤科 ' , '222' , '4' , 39 )

go

insert into Patient values ( '8801101' , ' 李一 ' , '1987-2-18' , '11' )

insert into Patient values ( '8801102' , ' 王二 ' , '1995-5-12' , '13' )

insert into Patient values ( '8801103' , ' 张三 ' , '1988-10-24' , '14' )

insert into Patient values ( '8801104' , ' 邓四 ' , '1992-7-3' , '12' )

insert into Patient values ( '8801105' , ' 刘五 ' , '1985-4-25' , '14' )

insert into Patient values ( '8801106' , ' 赵六 ' , '1982-6-30' , '11' )

insert into Patient values ( '8801107' , ' 陈七 ' , '1994-1-11' , '12' )

insert into Patient values ( '8801108' , ' 周八 ' , '2000-3-13' , '11' )

go

--创建索引

create index idx_ordername on Orders ( OrderName )

go

--创建视图

create view vw_statistics

as

select ProductID , SUM ( Quantity ) 销售数量 , SUM ( Quantity * UnitPrice ) 金额

from OrderDetails

group by ProductID

go