【数据库】SQL语句建立视图

创建数据库XSKC

USE XSKC;

CREATE TABLE student(
	SNO CHAR(9) PRIMARY KEY CHECK(SNO>='000000000' AND SNO<='999999999'),
	SNAME CHAR(20) UNIQUE,
	SSEX CHAR(6),
	SBIRTHDATE DATE,
	SMAJOR VARCHAR(40)
);

CREATE TABLE Course(
	CNO CHAR(5) PRIMARY KEY,
	CNAME VARCHAR(20),
	CCREDIT SMALLINT,
	CPNO CHAR(5),
	FOREIGN KEY (CPNO) REFERENCES Course(CNO)
);

CREATE TABLE SC(
	SNO CHAR(9),
	CNO CHAR(5),
	GRADE INT CHECK(GRADE>0 AND GRADE<100),
	SEMESTER CHAR(5),
	TEACHINGCLASS CHAR(8),
	PRIMARY KEY(SNO,CNO),
	FOREIGN KEY(SNO) REFERENCES student(SNO),
	FOREIGN KEY(CNO) REFERENCES Course(CNO)
);
CREATE TABLE student1(
	SNO CHAR(9) PRIMARY KEY CHECK(SNO>='000000000' AND SNO<='999999999'),
	SNAME CHAR(20) UNIQUE,
	SSEX CHAR(6),
	SBIRTHDATE DATE,
	SMAJOR VARCHAR(40)
);
INSERT INTO student VALUES
('20180001','李勇','男','2000-3-8','信息安全'),('20180002','刘晨','女','1999-9-1','计算机科学与技术'),
('20180003','王敏','女','2001-1-8','计算机科学与技术'),('20180004','张立','男','2000-11-1','计算机科学与技术'),
('20180005','张新奇','男','2001-6-12','信息管理与信息系统'),('20180006','赵明','男','2000-6-12','数据科学与大数据技术'),
('20180007','王佳佳','女','2001-12-7','数据科学与大数据技术');

INSERT INTO Course VALUES
('81001','程序设计基础与C语言',4,NULL),
('81007','离散数学',4,NULL),
('81002','数据结构',4,'81001'),
('81005','操作系统',4,'81001'),
('81003','数据库系统概论',4,'81002'),
('81006','Python语言',3,'81002'),
('81004','信息系统概论',4,'81003'),
('81008','大数据技术概论',4,'81003');

INSERT INTO sc VALUES
('20180001','81001',85,'20192','81001-01'),
('20180001','81002',96,'20201','81002-01'),
('20180001','81003',87,'20202','81003-01'),
('20180002','81001',80,'20192','81001-02'),
('20180002','81002',98,'20201','81002-01'),
('20180002','81003',71,'20202','81003-02'),
('20180003','81001',81,'20192','81001-01'),
('20180003','81002',76,'20201','81002-02'),
('20180004','81001',56,'20192','81001-02'),
('20180004','81002',97,'20201','81002-02'),
('20180005','81003',68,'20202','81003-01');

操作一：建立计科专业学生选课视图CSSC_VIEW，包括学号、姓名、系部、课程号、成绩，并要求进行修改操作时仍需保证该视图只能对计算机系的学生进行操作

CREATE VIEW CSSC_VIEW
AS
	SELECT SC.SNO,SNAME,SMAJOR,CNO,GRADE FROM student,SC 
	WHERE SC.SNO=student.SNO AND SMAJOR='计算机科学与技术'
WITH  CHECK  OPTION

操作二：建立'李勇'同学选课的成绩视图CJ_VIEW，包括课程号、学号和成绩

CREATE VIEW CJ_VIEW
AS
	SELECT SC.SNO,CNO,GRADE FROM student,SC 
	WHERE SC.SNO=student.SNO AND SNAME='李勇'

操作三：创建“CJ90_VIEW”视图，包括李勇同学选课成绩为90分以上的学生的课程号、课程名、成绩和学分

CREATE VIEW CJ90_VIEW
AS
	SELECT SC.CNO,CNAME,GRADE,CCREDIT FROM sc,course,student WHERE SC.CNO IN(
	SELECT CNO FROM sc WHERE SNO IN(
	SELECT SNO FROM student WHERE SNAME='李勇')) 
	AND sc.CNO=course.CNO AND SC.SNO=STUDENT.SNO AND GRADE>90 AND SNAME<>'李勇'

操作四：创建视图“RSTJ_VIEW”，按专业统计男生人数和女生人数，显示专业、男生人数、女生人数

CREATE VIEW RSTJ_VIEW
AS
	SELECT SMAJOR,SUM(ssex='男'),SUM(SSEX='女')
	FROM student GROUP BY SMAJOR

操作五：创建视图“CJTJ_VIEW”,显示每门课程的课程名、学分、选课人数，不及格人数

CREATE VIEW CJTJ_VIEW
AS
	SELECT CNAME,CCREDIT,COUNT(*),SUM(GRADE<60) FROM course,sc 
	WHERE sc.CNO=course.CNO GROUP BY COURSE.CNO