视图的创建.docx
《视图的创建.docx》由会员分享,可在线阅读,更多相关《视图的创建.docx(16页珍藏版)》请在冰豆网上搜索。
视图的创建
练习三视图的创建
一、准备知识:
1、建立视图:
•CREATEVIEW视图名称
•ASselect子句
二、练习内容:
(要求完成以下练习后把相关命令和远行结果截图打包后上传到10.2.3.75上交作业系统中)
一.问题描述:
为管理学生成绩信息,请建立3个表:
(id为bigint自动编程,成绩为整型,其他都为varchar)
S(id,Sno,SN,SD,SA)Sno,SN,SD,SA分别代表学号、学员姓名、所属班级、年龄
C(id,Cno,CN,CT)Cno,CN,CT分别代表课程编号、课程名称、教师名称
SC(id,Sno,Cno,Grade)Sno,Cno,Grade分别代表学号、所选修的课程编号、学习成绩
创建数据库的命令,在题目后面
并实现如下视图:
1.建立选修课程名称为’税收基础’的学员学号和姓名的视图view1
createviewview1as
selectS.Sno,S.SNfromS,SC,C
whereS.Sno=SC.Sno
andC.Cno=SC.Cno
andC.CN='税收基础';
2.建立选修课程编号为’C2’的学员姓名和所属班级的视图view2
createviewview2as
selectS.SN,S.SDfromS,SC
whereSC.Cno='C2'
andS.Sno=SC.Sno;
3.建立不选修课程编号为’C5’的学员姓名和所属班级的视图view3
createviewview3as
selectS.SN,S.SDfromS,SC
whereSC.Cno<>'C5'
andS.Sno=SC.Sno
groupbyS.SN,S.SD;
--select*fromview3
4.建立选修全部课程的学员姓名和所属班级的视图view4
--selectcount(*)fromC
createviewview4as
selectS.SN,S.SDfromS
whereSnoin(
selectsc.Snofrom(selectSC.SnoasSno,COUNT(*)assum1fromSC
groupbySC.Sno)assc
wheresum1=7)
5.建立选修课程超过5门的学员学号和所属单位的视图view5
createviewviewwuas
selectS.Sno,S.SDfromS,(selectSC.Sno,count(*)asmen5fromSCgroupbySno)asmen
wheremen.men5>=5
6.建立视图所有信息的视图(id,学号、学员姓名、所属班级、年龄,课程编号、课程名称,教师名称,学习成绩)viewall要求成绩显示为(不及格,及格,优秀)三种数据。
createviewviewallas
selectS.id,S.Snoas学号,S.SNas学员姓名,
S.SDas班级,S.SAas年龄,C.Cnoas课程编号,
C.CNas课程名称,C.CTas教师名称,
SC.Gas成绩fromS,SC,C
whereS.Sno=SC.Sno
andC.Cno=SC.Cno
andSC.Grade<60
union
selectS.id,S.Snoas学号,S.SNas学员姓名,
S.SDas班级,S.SAas年龄,C.Cnoas课程编号,
C.CNas课程名称,C.CTas教师名称,
SC.Gas成绩fromS,SC,C
whereS.Sno=SC.Sno
andC.Cno=SC.Cno
andSC.Grade>80
union
selectS.id,S.Snoas学号,S.SNas学员姓名,
S.SDas班级,S.SAas年龄,C.Cnoas课程编号,
C.CNas课程名称,C.CTas教师名称,
SC.Gas成绩fromS,SC,C
whereS.Sno=SC.Sno
andC.Cno=SC.Cno
andSC.Grade>60andSC.Grade<80
7.建立没有选修过“李明”老师讲授课程的所有学生姓名的视图view7
createviewview7as
selectS.SnofromSwhereS.Snonotin(selectSC.SnofromC,SC
whereC.Cno=SC.Cno
andC.CT='李明'
groupbySC.Sno
)
8.建立有二门以上(含两门)不及格课程的学生姓名及其平均成绩的视图view8
createviewview8as
selectSN,su.sumallfromS,(selecta.SnoasSno,COUNT(*)assum2,
AVG(a.Grade)assumallfrom(
selectSno,GradefromSC
whereGrade<60
)asa
groupbya.Sno)assu
whereS.Sno=su.Sno
andsum2>=2
9.建立既学过“1”号课程,又学过“2”号课程的所有学生姓名的视图view9
createviewview9as
Selecta.SNfrom(selectS.SNasSNfromC,S,SC
whereC.Cno=SC.Cno
andS.Sno=SC.Sno
andC.Cno='01')asa
(selectS.SNasSNfromC,S,SC
whereC.Cno=SC.Cno
andS.Sno=SC.Sno
andC.Cno='03'
)asb
Wherea.SN=b.SN
10.建立“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号的视图view10
createviewview10as
Selecta.SNfrom(selectS.SNasSN,SC.GradeasG1fromC,S,SC
whereC.Cno=SC.Cno
andS.Sno=SC.Sno
andC.Cno='01')asa
(selectS.SNasSN,SC.GradeasG2fromC,S,SC
whereC.Cno=SC.Cno
andS.Sno=SC.Sno
andC.Cno='03'
)asb
wherea.SN=b.SN
anda.G1>b.G2
11.建立“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩的视图view11
createviewview11as
Selecta.SN,a.G1,b.G2from(selectS.SNasSN,SC.GradeasG1fromC,S,SC
whereC.Cno=SC.Cno
andS.Sno=SC.Sno
andC.Cno='01')asa
(selectS.SNasSN,SC.GradeasG2fromC,S,SC
whereC.Cno=SC.Cno
andS.Sno=SC.Sno
andC.Cno='03'
)asb
wherea.SN=b.SN
anda.G1>b.G2
12.建立有三门以上(含三门)优秀课程的学生姓名及其平均成绩的视图view12
createviewview12as
selectS.SN,AVG(SC.Grade)asAGfromS,SC,(selectS.SN,COUNT(*)ascounfromC,S,SC
whereS.Sno=SC.Sno
andC.Cno=SC.Cno
andGrade>80
groupbyS.SN
)ascengji80
whereS.Sno=SC.Sno
andcengji80.coun>=3
groupbyS.SN
创建数据库表命令
createtableS(
idintidentity(1,1)NOTNULL,
Snovarchar(50),
SNvarchar(50),
SDvarchar(50),
SAvarchar(50)
);
INSERTINTOS(Sno,SN,SD,SA)
VALUES('0001','张三','一一班','21'),
('0002','李四','一一班','21'),
('0003','王五','一一班','21'),
('0004','小明','一一班','21'),
('0005','小虎','一一班','21'),
('0006','小胡','一一班','21'),
('0007','夏米','一二班','21'),
('0008','夏雨','一二班','21'),
('0009','刘星','一二班','21'),
('0010','夏雪','一二班','21'),
('0011','何东','一二班','21'),
('0012','何西','一二班','21'),
('0013','何南','一二班','21'),
('0014','何北','一三班','21'),
('0015','三多','一三班','21'),
('0016','阿凡达','一三班','21'),
('0017','爱丽丝','一三班','21'),
('0018','李冰','一三班','21'),
('0019','小飞','一三班','21'),
('0020','小悠','一三班','21'),
('0021','君文','一三班','21');
createtableC(
idintidentity(1,1)NOTNULL,
Cnovarchar(50),
CNvarchar(50),
CTvarchar(50)
);
INSERTINTOC(Cno,CN,CT)
VALUES('01','数学','张老师'),
('C2','税收基础','刘老师'),
('03','生物','王老师'),
('04','地理','李明'),
('C5','化学','陈老师'),
('06','物理','马老师'),
('07','体育','高老师');
createtableSC(
idintidentity(1,1)NOTNULL,
Snovarchar(50),
Cnovarchar(50),
Gradeint
);
INSERTINTOSC(Sno,Cno,Grade)
VALUES('0001','01',99),
('0001','C2',99),('0001','03',99),
('0001','04',69),('0001','C5',59),
('0001','06',99),('0001','07',99),
('0002','01',99),('0002','C2',49),
('0002','03',99),('0002','04',39),
('0002','06',99),('0002','07',99),
('0003','01',99),('0003','C2',99),
('0003','03',99),('0003','04',99),
('0003','C5',99),('0003','06',19),
('0004','01',99),('0004','C2',49),
('0004','03',99),('0004','04',45),
('0004','C5',99),('0004','06',56),
('0004','07',99),('0005','01',66),
('0005','03',99),('0005','04',33),
('0005','C5',99),('0005','06',99),
('0005','07',99),('0006','01',99),
('0006','C2',99),('0006','03',99),
('0006','06',99),('0006','07',99),
('0007','C2',99),('0007','03',99),
('0007','04',99),('0007','C5',99),
('0007','06',79),('0007','07',99),
('0008','01',99),('0008','C2',49),
('0008','03',99),('0008','04',89),
('0008','C5',39),('0008','06',79),
('0008','07',79),('0009','01',49),
('0009','C2',91),('0009','03',69),
('0009','04',59),('0009','C5',99),
('0009','07',99),('0010','01',88),
('0010','C2',77),('0010','03',67),
('0010','04',76),('0010','C5',98),
('0010','06',75),('0010','07',89),
('0011','01',99),('0011','C2',99),
('0011','03',99),('0011','04',99),
('0011','C5',99),('0011','06',99),
('0012','01',99),('0012','C2',99),
('0012','03',99),('0012','04',99),
('0012','C5',99),('0012','06',99),
('0012','07',99),('0013','01',88),
('0013','C2',77),('0013','03',67),
('0013','06',75),('0013','07',89),
('0014','01',88),('0014','06',75),
('0014','07',89),('0015','01',88),
('0015','C2',77),('0015','03',67),
('0015','C5',98),('0015','06',75),
('0015','07',89),('0016','01',88),
('0016','C2',77),('0016','03',67),
('0016','06',75),('0016','07',89),
('0017','01',88),('0017','04',76),
('0017','C5',98),('0017','06',75),
('0017','07',89),('0018','01',88),
('0018','C2',77),('0018','C5',88),
('0018','06',75),('0019','01',88),
('0019','C2',77),('0019','03',67),
('0019','04',76),('0019','07',59),
('0020','01',88),('0020','06',75),
('0020','07',59),('0021','01',88),
('0021','C2',77),('0021','03',67),
('0021','04',76),('0021','C5',88),
('0021','06',75),('0021','07',59);