期末复习

马上要考数据库原理及应用了，为此我进行了系统性复习，将第三章到第六章与sql语句相关的需要背的东西搜集了出来，方便自己复习。除此之外，还有计算机网络这一科目也是临近考试，对此我也需要进行进一步的复习
第三章
10.写出创建如下三张表的sql语句，要求在定义表的同时定义数据的完整性约束
（1）“图书”表结构如下：
书号：统一字符编码定长类型，长度为6，主键；
书名：统一字符编码，可变长类型，长度为30，非空；
作者名：普通编码定长字符类型，长度为10，非空；
出版日期：小日期时间型；
价格：定点小数，小数部分1位，整数部分3位
（2）“书店”表结构如下:
书店编号：统一字符编码定长类型，长度为6，主键；
店名：统一字符编码可变长类型，长度为30，非空；
电话；普通编码定长字符类型，8位长；
地址：普通编码可变长字符类型，40位长；
邮政编码:普通编码定长字符类型，6位长；
（3）“图书销售”表结构如下：
书号：统一字符编码定长类型，长度为6，非空；
书店编号：统一字符编码定长类型，长度为6，非空；
销售日期：小日期时间型，非空；
销售数量：微整形，大于等于1；
主键为（书号，书店编号，销售日期）；
其中“书号”为引用“图书”表中“书号”的外键；
“书店编号”为引用“书店”表中“书店编号”的外键
-- 创建"图书"表
CREATE TABLE 图书 (
书号 NVARCHAR(6) PRIMARY KEY,
书名 NVARCHAR(30) NOT NULL,
作者名 CHAR(10) NOT NULL,
出版日期 SMALLDATETIME,
价格 DECIMAL(4,1)
);

-- 创建"书店"表
CREATE TABLE 书店 (
书店编号 NVARCHAR(6) PRIMARY KEY,
店名 NVARCHAR(30) NOT NULL,
电话 CHAR(8),
地址 VARCHAR(40),
邮政编码 CHAR(6)
);

-- 创建"图书销售"表
CREATE TABLE 图书销售 (
书号 NVARCHAR(6) NOT NULL,
书店编号 NVARCHAR(6) NOT NULL,
销售日期 SMALLDATETIME NOT NULL,
销售数量 TINYINT CHECK (销售数量 >= 1),
PRIMARY KEY (书号, 书店编号, 销售日期),
FOREIGN KEY (书号) REFERENCES 图书(书号),
FOREIGN KEY (书店编号) REFERENCES 书店(书店编号)
);
11.为图书表添加“印刷数量”列，类型为整数，同时添加约束，要求此列的数值要大于等于1000
ALTER TABLE 图书
ADD 印刷数量 INT CHECK (印刷数量 >= 1000);
12.删除书店表中的邮政编码列
ALTER TABLE 书店
DROP COLUMN 邮政编码;
13.将图书销售表中的“销售数量”列的数据类型改为整型
ALTER TABLE 图书销售
ALTER COLUMN 销售数量 INT;

第四章
1．查询选课表中的全部数据。
SELECT * FROM SC;
2．查询计算机系学生的姓名和年龄。
SELECT Sname, Sage FROM Student WHERE Sdept = '计算机系';
3．查询考试成绩在70~80分之间的学生学号、课程号和成绩。
SELECT Sno, Cno, Grade FROM SC WHERE Grade BETWEEN 70 AND 80;
4．查询计算机系年龄在18~20分之间且性别为"男"的学生姓名、年龄。
SELECT Sname, Sage FROM Student
WHERE Sdept = '计算机系' AND Sage BETWEEN 18 AND 20 AND Ssex = '男';
5．查询c001课程的考试最高分。
SELECT MAX(Grade) FROM SC WHERE Cno = 'c001';
6．查询计算机系学生的最大年龄和最小年龄。
SELECT MAX(Sage), MIN(Sage) FROM Student WHERE Sdept = '计算机系';
7．统计每个系的学生人数。
SELECT Sdept, COUNT() AS StudentCount FROM Student GROUP BY Sdept;
8．统计每门课程的选课人数和考试最高分。
SELECT Cno, COUNT() AS StudentCount, MAX(Grade) AS MaxGrade
FROM SC GROUP BY Cno;
9．统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果。
SELECT Sno, COUNT() AS CourseCount, SUM(Grade) AS TotalGrade
FROM SC GROUP BY Sno ORDER BY CourseCount ASC;
10．查询总成绩超过200分的学生，列出学号和总成绩。
SELECT Sno, SUM(Grade) AS TotalGrade
FROM SC GROUP BY Sno HAVING SUM(Grade) > 200;
11．查询选了c002课程的学生姓名和所在系。
SELECT S.Sname, S.Sdept
FROM Student S JOIN SC ON S.Sno = SC.Sno
WHERE SC.Cno = 'c002';
12．查询考试成绩80分以上的学生姓名、课程号和成绩，并按成绩降序排列结果。
SELECT S.Sname, SC.Cno, SC.Grade
FROM Student S JOIN SC ON S.Sno = SC.Sno
WHERE SC.Grade > 80 ORDER BY SC.Grade DESC;
13．查询哪些学生没选课，列出学号、姓名和所在系。
SELECT S.Sno, S.Sname, S.Sdept
FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno
WHERE SC.Sno IS NULL;
14．查询与 Java 在同一学期开设的课程的课程名和开课学期。
SELECT C1.Cname, C1.Cpno
FROM Course C1, Course C2
WHERE C2.Cname = 'Java' AND C1.Cpno = C2.Cpno AND C1.Cname <> 'Java';
15．查询与李勇年龄相同的学生姓名、所在系和年龄。
SELECT S1.Sname, S1.Sdept, S1.Sage
FROM Student S1, Student S2
WHERE S2.Sname = '李勇' AND S1.Sage = S2.Sage AND S1.Sname <> '李勇';
16．用子查询实现如下查询：
(1）查询选了c001课程的学生姓名和所在系。
SELECT Sname, Sdept FROM Student
WHERE Sno IN (SELECT Sno FROM SC WHERE Cno = 'c001');
(2）查询数学系成绩80分以上的学生学号、姓名、课程号和成绩。
SELECT S.Sno, S.Sname, SC.Cno, SC.Grade
FROM Student S JOIN SC ON S.Sno = SC.Sno
WHERE S.Sdept = '数学系' AND SC.Grade > 80;
(3）查询计算机系考试成绩最高的学生的姓名。
SELECT Sname FROM Student
WHERE Sno IN (
SELECT SC.Sno FROM SC
WHERE Grade = (SELECT MAX(Grade) FROM SC)
) AND Sdept = '计算机系';
(4）查询数据结构考试成绩最高的学生的姓名、所在系、性别和成绩。
SELECT S.Sname, S.Sdept, S.Ssex, SC.Grade
FROM Student S JOIN SC ON S.Sno = SC.Sno
JOIN Course C ON SC.Cno = C.Cno
WHERE C.Cname = '数据结构' AND SC.Grade = (
SELECT MAX(Grade) FROM SC JOIN Course ON SC.Cno = Course.Cno
WHERE Course.Cname = '数据结构'
);
17.查询没选 Java 课程的学生姓名和所在系。18．查询计算机系没选课的学生姓名和性别。
SELECT S.Sname, S.Sdept
FROM Student S JOIN SC ON S.Sno = SC.Sno
JOIN Course C ON SC.Cno = C.Cno
WHERE C.Cname = 'Java';
18.查询计算机系没选课的学生姓名和性别
SELECT Sname, Ssex FROM Student
WHERE Sdept = '计算机系' AND Sno NOT IN (SELECT DISTINCT Sno FROM SC);
20.删除考试成绩低于50分的学生的选课记录。
DELETE FROM SC WHERE Grade < 50;
21.删除没人选课程记录。
DELETE FROM Course
WHERE Cno NOT IN (SELECT DISTINCT Cno FROM SC);
22.删除计算机系 Java 课程考试成绩不及格学生的 Java 选课记录
DELETE FROM SC
WHERE Cno = (SELECT Cno FROM Course WHERE Cname = 'Java')
AND Grade < 60
AND Sno IN (SELECT Sno FROM Student WHERE Sdept = '计算机系');
23.将第2学期开设的所有课程的学分增加2分。
UPDATE Course
SET Ccredit = Ccredit + 2
WHERE Cpno = 2;
24.将 Java 课程的学分改为3分。
UPDATE Course
SET Ccredit = 3
WHERE Cname = 'Java';
25.将计算机系学生的年龄增加1岁。
UPDATE Student
SET Sage = Sage + 1
WHERE Sdept = '计算机系';
26.将信息管理系学生的"计算机文化学"课程的考试成绩加5分。
UPDATE SC
SET Grade = Grade + 5
WHERE Cno = (SELECT Cno FROM Course WHERE Cname = '计算机文化学')
AND Sno IN (SELECT Sno FROM Student WHERE Sdept = '信息管理系');
27.查询每个系年龄大于等于20的学生人数，并将结果保到一个新永久表 Dept Age 中。
SELECT Sdept, COUNT() AS StudentCount
INTO DeptAge
FROM Student
WHERE Sage >= 20
GROUP BY Sdept;

第五章
3、利用第3章建立的 Studen 表、 Course 表和 SC 表，写出创建浦足下述要求的祸sql语句
4、(1）查询学生的学号、姓名、所在系、课程号、课程名、课程学分。
CREATE VIEW StudentCourseInfo AS
SELECT S.Sno, S.Sname, S.Sdept, C.Cno, C.Cname, C.Ccredit
FROM Student S
JOIN SC ON S.Sno = SC.Sno
JOIN Course C ON SC.Cno = C.Cno;
(2）查询学生的学号、姓名、选修的课程名和考试成绩。
CREATE VIEW StudentGradeInfo AS
SELECT S.Sno, S.Sname, C.Cname, SC.Grade
FROM Student S
JOIN SC ON S.Sno = SC.Sno
JOIN Course C ON SC.Cno = C.Cno;
(3）统计每个学生的选课门数，列出学号和选课门数。
CREATE VIEW StudentCourseCount AS
SELECT Sno, COUNT() AS CourseCount
FROM SC
GROUP BY Sno;
(4）统计每个学生的修课总学分，列出学号和选课门数。
CREATE VIEW StudentTotalCredit AS
SELECT SC.Sno, SUM(C.Ccredit) AS TotalCredit
FROM SC
JOIN Course C ON SC.Cno = C.Cno
GROUP BY SC.Sno;
(5）查询计算机系 Java 考试成绩最高的学生学号、姓名和 Java 考试成绩。
CREATE VIEW TopJavaStudent AS
SELECT S.Sno, S.Sname, SC.Grade
FROM Student S
JOIN SC ON S.Sno = SC.Sno
JOIN Course C ON SC.Cno = C.Cno
WHERE S.Sdept = '计算机系' AND C.Cname = 'Java'
AND SC.Grade = (
SELECT MAX(SC2.Grade)
FROM SC SC2
JOIN Course C2 ON SC2.Cno = C2.Cno
WHERE C2.Cname = 'Java'
);
4．利用第3题建立的视图，完成如下查询：
(1）查询考试成绩大于等于90的学生姓名、考试课程名和成绩。
SELECT Sname, Cname, Grade
FROM StudentGradeInfo
WHERE Grade >= 90;
(2）查询选课门数超过3门的学生的学号和选课门数。
SELECT Sno, CourseCount
FROM StudentCourseCount
WHERE CourseCount > 3;
(3）查询计算机系选课门数超过3门的学生的姓名和选课门数。
SELECT S.Sname, SCC.CourseCount
FROM Student S
JOIN StudentCourseCount SCC ON S.Sno = SCC.Sno
WHERE S.Sdept = '计算机系' AND SCC.CourseCount > 3;
(4）查询选课总学分超过10分的学生的学号、姓名、所在系和选课总学分。
SELECT S.Sno, S.Sname, S.Sdept, STC.TotalCredit
FROM Student S
JOIN StudentTotalCredit STC ON S.Sno = STC.Sno
WHERE STC.TotalCredit > 10;
5．修改第3题（4）定义的视图，使其查询每个学生的学号、平均成绩及选课门数。
ALTER VIEW StudentTotalCredit AS
SELECT SC.Sno, AVG(SC.Grade) AS AvgGrade, COUNT() AS CourseCount
FROM SC
GROUP BY SC.Sno;
6．修改第3题（5）定义的视图，使其查询全体学生中 Java 考试成绩最高的学生的学号、姓名、所在系和Java 考试成绩。
ALTER VIEW TopJavaStudent AS
SELECT S.Sno, S.Sname, S.Sdept, SC.Grade
FROM Student S
JOIN SC ON S.Sno = SC.Sno
JOIN Course C ON SC.Cno = C.Cno
WHERE C.Cname = 'Java'
AND SC.Grade = (
SELECT MAX(SC2.Grade)
FROM SC SC2
JOIN Course C2 ON SC2.Cno = C2.Cno
WHERE C2.Cname = 'Java'
);