檢視
1、建立檢視CS，給出選課成績合格的學生編號、所選課程編號和該課程成績。
create view cs
as
select *
from sc
where score>=60
with check option
2、建立檢視LX1，顯示學生姓名、所選課程名稱和任課教師名稱。
create view LX1
as
select S.sname,C.cname,t.name
from S,C,sc,T
where S.Sid=sc.Sid and sc.Cid= C.Cid and T.tid=C.tid
3、建立檢視LX2，有學生姓名、所選課程名和所有課程成績都比原來多5分這幾個屬性構成。
create view LX2(sname,cname,score)
as
select sname,cname,score+5
from S,sc,C
where S.Sid=sc.Sid and C.Cid=sc.Cid
4、建立檢視LX3，顯示學生的學號及其平均成績。
create view LX3(學號,平均成績)
as
select Sid,avg(score)
from sc
group by Sid
5、建立檢視LX4，包含學生編號，學生所選課程數目和平均成績。
create view LX4(Sid,選修課程數目,平均成績)
as
select Sid,count(Cid),avg(score)
from sc
group by Sid
6、建立檢視LX5，顯示所有選修課程C語言的學生姓名。
create view LX5 (選修C語言學生姓名)
as
select sname
from S,sc,C
where S.Sid= sc.Sid and C.Cid=sc.Cid and cname='C語言'
7、建立檢視LX6，顯示每門課程的平均成績，最高成績，最低成績。
create view LX6(Cid,平均成績,最高成績,最低成績)
as
select Cid,avg(score),max(score),min(score)
from sc
group by Cid
8、插入元組（2010071101,107，55）到檢視CS中，若是在檢視的定義中存在with check option子句對插入有什麼影響。
create view cs
as
select *
from sc
where score>=60
with check option
insert
into cs
values('2010071101','107',55)
失敗，原因:是目標檢視指定了 WITH CHECK OPTION，而該操作的"55"不符合 CHECK OPTION 約束。
9、將檢視CS（包含定義with check option）中，所有課程編號為107的課程成績都減5分。這個操作能不能正確執行？
update cs
set score=score-5
where Cid='120'
10、在檢視CS中刪除編號為2010071206的學生的記錄。
delete
from cs
where Sid='2017071206'
11、刪除檢視CS。
drop view cs
資料表
create database jxgl
go
use jxgl
go
create table T(
Tid nchar(5) primary key,
name nchar(10) not null,
sex nchar(2) default '男',
workingtime datetime,
plandscape nchar(20),
degree nchar(10),
title nvarchar(6),
department nvarchar(20),
tel nchar(11)
)
create table S(
Sid nchar(10) primary key,
sname nchar(10),
sex nchar(2) default '男',
birthday Date,
age as datediff(yy,birthday,getdate()),
entrydate date,
plandscape nchar(10) default '團員',
nativeplace nvarchar(20),
class as substring(Sid,7,2)
)
create table C(
Cid nchar(3) primary key,
cname nvarchar(20) Not null,
category nchar(4) Not null check(category in('考試','考查')) default '考試',
credit int,
Tid nchar(5) foreign key references T(Tid) on update cascade
)
create table SC(
Sid nchar(10) foreign key references S(Sid) on update cascade on delete cascade,
Cid nchar(3),
score int check(score between 0 and 100),
primary key(Sid,Cid),
foreign key(Cid) references C(Cid) on update cascade on delete cascade
)
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95011', N'趙西明 ', N'男 ', CAST(0x0000768300000000 AS DateTime), N'群眾 ', N'碩士 ', N'副教授', N'軟體', N'13733152369')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95012', N'李小平 ', N'男 ', CAST(0x0000775E00000000 AS DateTime), N'黨員 ', N'碩士 ', N'教授', N'計算機系', N'13733152370')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95013', N'張爽 ', N'男 ', CAST(0x00007BC700000000 AS DateTime), N'群眾 ', N'本科 ', N'副教授', N'計算機系', N'13733152371')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95014', N'李麗寧 ', N'女 ', CAST(0x00007BC700000000 AS DateTime), N'黨員 ', N'碩士 ', N'副教授', N'計算機系', N'13733152372')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95015', N'張金明 ', N'男 ', CAST(0x0000812C00000000 AS DateTime), N'群眾 ', N'碩士 ', N'講師', N'計算機系', N'13733152373')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95016', N'李燕 ', N'女 ', CAST(0x000082C800000000 AS DateTime), N'黨員 ', N'碩士 ', N'講師', N'計算機系', N'13733152374')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95017', N'宛平 ', N'女 ', CAST(0x0000840B00000000 AS DateTime), N'群眾 ', N'博士 ', N'副教授', N'軟體', N'13733152375')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95018', N'陳江川 ', N'男 ', CAST(0x0000883900000000 AS DateTime), N'群眾 ', N'博士 ', N'講師', N'軟體', N'13733152376')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95019', N'郭新 ', N'男 ', CAST(0x00008CAA00000000 AS DateTime), N'黨員 ', N'博士 ', N'講師', N'軟體', N'13733152377')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071101', N'張佳 ', N'女 ', CAST(0x3F1D0B00 AS Date), CAST(0x3C3D0B00 AS Date), N'團員 ', N'江西南昌')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071102', N'好生 ', N'男 ', CAST(0xCD1E0B00 AS Date), CAST(0x3C3D0B00 AS Date), N'團員 ', N'廣州順德')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071203', N'徐克 ', N'男 ', CAST(0xBF210B00 AS Date), CAST(0x3C3D0B00 AS Date), N'團員 ', N'江西南昌')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071204', N'葉飛 ', N'女 ', CAST(0xD1210B00 AS Date), CAST(0x3C3D0B00 AS Date), N'黨員 ', N'上海')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071205', N'任偉 ', N'男 ', CAST(0x7B220B00 AS Date), CAST(0x3C3D0B00 AS Date), N'團員 ', N'北京順義')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071206', N'江賀 ', N'男 ', CAST(0x02200B00 AS Date), CAST(0x3D3D0B00 AS Date), N'黨員 ', N'福建廈門')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'101', N'計算機基礎', N'考試 ', 2, N'95011')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'102', N'C語言', N'考試 ', 3, N'95012')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'103', N'計算機組成原理', N'考試 ', 3, N'95012')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'104', N'組合語言', N'考試 ', 3, N'95014')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'105', N'Java語言', N'考查 ', 2, N'95015')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'106', N'作業系統', N'考試 ', 3, N'95016')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'107', N'資料結構', N'考試 ', 3, N'95017')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'108', N'編譯原理', N'考試 ', 3, N'95017')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'109', N'網路基礎', N'考試 ', 3, N'95017')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'110', N'資料庫原理', N'考試 ', 3, N'95017')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'120', N'SQLServer', N'考查 ', 2, N'95018')
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071101', N'101', 90)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071101', N'102', 70)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071101', N'103', 48)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071101', N'105', 80)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'102', 90)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'104', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'106', 68)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'107', 85)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'109', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'110', 65)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'120', 48)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'102', 65)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'104', 82)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'105', 79)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'107', 55)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'110', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'120', 67)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'101', 86)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'102', 86)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'104', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'105', 84)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'106', 95)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'108', 91)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'110', 82)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071205', N'101', 63)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071205', N'102', 84)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071205', N'103', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071206', N'107', 58)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071206', N'108', 74)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071206', N'109', 74)