【数据库系统概论】实验三 SQL数据定义及更新

一、实验目的

1．掌握SQL Server数据库系统基本操作
2．掌握SQL Server数据库系统代码更新方法

二、实验内容

用数据定义语言创建数据库，数据库名为ST_，为本人学号。在数据库中创建schema T，在架构T下创建Student、Course、SC三张数据表（参见教材）并建立相关主码和索引。
增加一个关系Book(BNo，AUTHOR，PRICE，PRESS，SITE)，主关键字是BNO。其中BNO为书号（char(10)），AUTHOR为书名(varchar(30)，PRICE为价格(float)，PRESS为出版社(varchar(30)，SITE为出版地(varchar(30)。
在以上四张表中用SQL语言插入若干数据。涉及到完整性约束要求的，用update修改数据。要求学生数据不少于20，课程数据不小于6，选课数据不少于40。书籍信息不少于15。
保存完整的代码txt文件。存放于U盘中。
检索选修人数最多的课程
检索每门课程的最高分
查询“数据结构”价格最低的出版社
统计每个出版社图书的数量。
查询全部同学都选修了的课程。
查询至少选修了学号为“”同学选修全部课程的学生。

三、问题和要求

1．写出你操作并正确执行的代码及结果。

【数据库系统概论】实验三 SQL数据定义及更新

（5）检索选修人数最多的课程

SELECT * FROM T.Course WHERE Cno=(SELECT Top 1 cno FROM T.SC GROUP BY cno ORDER BY COUNT(*) DESC);

【数据库系统概论】实验三 SQL数据定义及更新

（6）检索每门课程的最高分

SELECT Cname 课程名,C.Cno 课程ID,MAX(Grade) 最高分 FROM T.SC SC,T.Course C WHERE SC.Cno=C.Cno GROUP BY C.Cno,Cname ORDER BY MAX(Grade) DESC;

【数据库系统概论】实验三 SQL数据定义及更新

（7）查询“数据结构”价格最低的出版社

SELECT * FROM T.Book WHERE Author LIKE '%数据结构%' ORDER BY Price DESC;
SELECT top 1 Press FROM T.Book WHERE Author LIKE '%数据结构%' ORDER BY Price DESC;

【数据库系统概论】实验三 SQL数据定义及更新

（8）统计每个出版社图书的数量

SELECT Press 出版社,COUNT(*) 出版数量 FROM T.Book GROUP BY Press ORDER BY COUNT(*) DESC;

【数据库系统概论】实验三 SQL数据定义及更新

（9）查询全部同学都选修了的课程

SELECT Cno, Cname FROM T.Course C WHERE NOT EXISTS
(SELECT * FROM T.Student S WHERE NOT EXISTS
(SELECT * FROM T.SC WHERE SC.Cno = C.Cno and SC.Sno = S.Sno));

【数据库系统概论】实验三 SQL数据定义及更新

（10）查询至少选修了学号为 “ 201215122 ” 同学选修全部课程的学生。

SELECT DISTINCT Sno 学号 FROM T.SC scx 
WHERE NOT EXISTS(SELECT * FROM T.SC scy WHERE scy.Sno = '202110810' AND NOT EXISTS(SELECT * FROM T.SC scz WHERE scz.Sno = scx.Sno AND scz.Cno = scy.Cno));

【数据库系统概论】实验三 SQL数据定义及更新

2．完整的txt代码

-- 创建数据库 ST_2022611788
CREATE DATABASE ST_2022611788;
-- 创建模式T
CREATE SCHEMA T;
-- 如果表存在先执行删除操作
DROP TABLE IF EXISTS T.SC;
DROP TABLE IF EXISTS T.Course;
DROP TABLE IF EXISTS T.Student;
DROP TABLE IF EXISTS T.Book;
-- 在模式T中创建表Student
CREATE TABLE T.Student(	
 Sno CHAR(9) PRIMARY KEY,            
 Sname CHAR(20) UNIQUE, 
 Ssex CHAR(2),
 Sage SMALLINT,
 Sdept CHAR(20)
 ); 
 -- 在模式T中创建表Course
CREATE TABLE T.Course(	
 Cno CHAR(4) PRIMARY KEY,
 Cname CHAR(40),            
 Cpno CHAR(4),               	                      
 Ccredit SMALLINT,
 FOREIGN KEY (Cpno) REFERENCES  T.Course(Cno) 
 ); 
  -- 在模式T中创建表SC
CREATE TABLE T.SC(
 Sno CHAR(9), 
 Cno CHAR(4),  
 Grade SMALLINT,
 PRIMARY KEY (Sno,Cno),                      
 FOREIGN KEY (Sno) REFERENCES T.Student(Sno), 
 FOREIGN KEY (Cno) REFERENCES T.Course(Cno) 
 ); 
 -- 增加一个关系T.Book
CREATE TABLE T.Book(
Bno CHAR(10),
Author VARCHAR(30),
Price FLOAT,
Press CHAR(30),
Site VARCHAR(30)
);
 -- 在表中插入T.Student的数据
INSERT INTO T.Student(Sno,Sname,Ssex,Sage,Sdept) VALUES ('202110767', '杨润玲', '女', 21, '大数据学院');
INSERT INTO T.Student VALUES ('202110768', '朱娅玲', '女', 21, '大数据学院');
INSERT INTO T.Student VALUES ('202110769', '徐江', '男', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110770', '周双凤', '女', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110771', '张秋玲', '女', 20, '大数据学院');
INSERT INTO T.Student VALUES ('202110772', '卢成思', '男', 20, '大数据学院');
INSERT INTO T.Student VALUES ('202110775', '胡梁蕊', '女', 21, '大数据学院');
INSERT INTO T.Student VALUES ('202110777', '李银娇', '女', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110780', '滕佳美', '女', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110794', '颜蓉', '女', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110806', '杨瑜', '男', 21, '大数据学院');
INSERT INTO T.Student VALUES ('202110810', '杨登洪', '女', 23, '大数据学院');
INSERT INTO T.Student VALUES ('202110817', '刘宇洁', '男', 19, '大数据学院');
INSERT INTO T.Student VALUES ('202110818', '张钰雪', '女', 21, '大数据学院');
INSERT INTO T.Student VALUES ('202110820', '王庆', '男', 20, '大数据学院');
INSERT INTO T.Student VALUES ('202110822', '陈天浪', '男', 23, '大数据学院');
INSERT INTO T.Student VALUES ('202110824', '贺铄清', '男', 21, '大数据学院');
INSERT INTO T.Student VALUES ('202110828', '魏列镜', '男', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110830', '撒月星', '男', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110831', '余艳红', '女', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110838', '方绍玉', '女', 21, '大数据学院');
  -- 在表中插入T.Course的数据
INSERT INTO T.Course(Cno,Cname,Cpno,Ccredit) VALUES ('1', '数据结构', NULL, 3);
INSERT INTO T.Course VALUES ('2', '操作系统', NULL, 3);
INSERT INTO T.Course VALUES ('3', '计算机组成原理', NULL, 3);
INSERT INTO T.Course VALUES ('4', '计算机网络', NULL, 3);
INSERT INTO T.Course VALUES ('5', '数据库系统原理及应用', '1', 3);
INSERT INTO T.Course VALUES ('6', 'C语言程序设计', NULL, 3);
INSERT INTO T.Course VALUES ('7', 'Python程序设计', NULL, 2);
INSERT INTO T.Course VALUES ('8', '大数据应用技术', NULL, 2);
-- 涉及到完整性约束要求，先插入T.Course的数据再更新关联的列
UPDATE T.Course SET Cpno = '5' WHERE Cno = '1' 
UPDATE T.Course SET Cpno = '3' WHERE Cno = '2' 
UPDATE T.Course SET Cpno = '6' WHERE Cno = '4' 
UPDATE T.Course SET Cpno = '7' WHERE Cno = '5' 
UPDATE T.Course SET Cpno = '6' WHERE Cno = '7' 
-- 在表中插入T.SC的数据
INSERT INTO T.SC(Sno,Cno,Grade) VALUES ('202110767', '1', 91);
INSERT INTO T.SC VALUES ('202110767', '5', 66);
INSERT INTO T.SC VALUES ('202110767', '6', 94);
INSERT INTO T.SC VALUES ('202110767', '7', 55);
INSERT INTO T.SC VALUES ('202110768', '1', 50);
INSERT INTO T.SC VALUES ('202110768', '2', 13);
INSERT INTO T.SC VALUES ('202110768', '8', 69);
INSERT INTO T.SC VALUES ('202110769', '1', 80);
INSERT INTO T.SC VALUES ('202110769', '3', 80);
INSERT INTO T.SC VALUES ('202110769', '5', 72);
INSERT INTO T.SC VALUES ('202110769', '6', 14);
INSERT INTO T.SC VALUES ('202110770', '1', 63);
INSERT INTO T.SC VALUES ('202110770', '2', 40);
INSERT INTO T.SC VALUES ('202110770', '3', 43);
INSERT INTO T.SC VALUES ('202110770', '4', 73);
INSERT INTO T.SC VALUES ('202110771', '1', 38);
INSERT INTO T.SC VALUES ('202110771', '2', 38);
INSERT INTO T.SC VALUES ('202110771', '3', 62);
INSERT INTO T.SC VALUES ('202110771', '5', 90);
INSERT INTO T.SC VALUES ('202110772', '1', 46);
INSERT INTO T.SC VALUES ('202110772', '6', 46);
INSERT INTO T.SC VALUES ('202110775', '1', 89);
INSERT INTO T.SC VALUES ('202110775', '7', 89);
INSERT INTO T.SC VALUES ('202110777', '1', 41);
INSERT INTO T.SC VALUES ('202110777', '8', 41);
INSERT INTO T.SC VALUES ('202110780', '1', 19);
INSERT INTO T.SC VALUES ('202110780', '7', 19);
INSERT INTO T.SC VALUES ('202110794', '1', 1);
INSERT INTO T.SC VALUES ('202110794', '6', 1);
INSERT INTO T.SC VALUES ('202110806', '1', 92);
INSERT INTO T.SC VALUES ('202110806', '5', 92);
INSERT INTO T.SC VALUES ('202110810', '1', 15);
INSERT INTO T.SC VALUES ('202110810', '4', 15);
INSERT INTO T.SC VALUES ('202110817', '1', 2);
INSERT INTO T.SC VALUES ('202110817', '3', 2);
INSERT INTO T.SC VALUES ('202110818', '1', 56);
INSERT INTO T.SC VALUES ('202110818', '2', 56);
INSERT INTO T.SC VALUES ('202110820', '1', 33);
INSERT INTO T.SC VALUES ('202110822', '1', 85);
INSERT INTO T.SC VALUES ('202110822', '5', 85);
INSERT INTO T.SC VALUES ('202110824', '1', 57);
INSERT INTO T.SC VALUES ('202110824', '3', 26);
INSERT INTO T.SC VALUES ('202110828', '1', 53);
INSERT INTO T.SC VALUES ('202110828', '2', 53);
INSERT INTO T.SC VALUES ('202110828', '5', 36);
INSERT INTO T.SC VALUES ('202110830', '1', 77);
INSERT INTO T.SC VALUES ('202110830', '5', 70);
INSERT INTO T.SC VALUES ('202110830', '6', 76);
INSERT INTO T.SC VALUES ('202110830', '7', 49);
INSERT INTO T.SC VALUES ('202110831', '1', 30);
INSERT INTO T.SC VALUES ('202110831', '2', 81);
INSERT INTO T.SC VALUES ('202110838', '1', 4);
INSERT INTO T.SC VALUES ('202110838', '5', 97);
INSERT INTO T.SC VALUES ('202110838', '6', 99);
-- 在表中插入T.Book的数据
INSERT INTO T.book(Bno,Author,Price,Press,Site) VALUES ('1', '计算机操作系统', 49.5, '清华大学出版社', '北京');
INSERT INTO T.book VALUES ('2', 'Java程序设计及实验', 66, '清华大学出版社', '北京');
INSERT INTO T.book VALUES ('3', 'python快速编程入门（第二版）', 49.8, '人民邮电出版社', '北京');
INSERT INTO T.book VALUES ('4', '计算机组成原理', 49.8, '电子工业出版社', '北京');
INSERT INTO T.book VALUES ('5', '数据结构（C语言）版', 58, '人民邮电出版社', '北京');
INSERT INTO T.book VALUES ('6', '数据库系统概论（第5版）', 42, '高等教育出版社', '北京');
INSERT INTO T.book VALUES ('7', '物联网安全实战', 55.3, '机械工业出版社', '重庆');
INSERT INTO T.book VALUES ('8', '数据结构与算法', 55.3, '机械工业出版社', '重庆');
INSERT INTO T.book VALUES ('9', 'C#程序设计教程 第4版', 48.3, '机械工业出版社', '重庆');
INSERT INTO T.book VALUES ('10', '深入理解计算机系统', 97.3, '机械工业出版社', '重庆');
INSERT INTO T.book VALUES ('11', '数学之美 第三版', 69, '人民邮电出版社', '北京');
INSERT INTO T.book VALUES ('12', '中文版Photoshop', 94, '水利水电出版社', '河北');
INSERT INTO T.book VALUES ('13', 'Excel完全自学教程', 45.6, '天津科学技术出版社', '天津');
INSERT INTO T.book VALUES ('14', 'Python GUI设计', 96, '吉林大学出版社', '北京');
INSERT INTO T.book VALUES ('15', 'SQL必知必会 第5版', 45, '人民邮电出版社', '北京');
INSERT INTO T.book VALUES ('16', 'Java数据结构和算法', 36.3, '清华大学出版社', '重庆');
INSERT INTO T.book VALUES ('17', '剑指offer', 56.3, '人民邮电出版社', '重庆');
INSERT INTO T.book VALUES ('18', '啊哈算法', 52.63, '电子工业出版社', '北京');
INSERT INTO T.book VALUES ('19', 'LeetCode常见算法', 86.9, '人民邮电出版社', '河北');
INSERT INTO T.book VALUES ('20', '程序是怎样跑起来的', 42.6, '高等教育出版社', '天津');
 -- 执行查询
SELECT * FROM T.Student;
SELECT * FROM T.Course;
SELECT * FROM T.SC;
SELECT * FROM T.Book;

-- 5.检索选修人数最多的课程
WHERE Cno=(SELECT Top 1 cno FROM T.SC GROUP BY cno ORDER BY COUNT(*) DESC);

-- 6.检索每门课程的最高分
SELECT Cname 课程名,C.Cno 课程ID,MAX(Grade) 最高分 FROM T.SC SC,T.Course C WHERE SC.Cno=C.Cno GROUP BY C.Cno,Cname ORDER BY MAX(Grade) DESC;

-- 7.查询“数据结构”价格最低的出版社
SELECT * FROM T.Book WHERE Author LIKE '%数据结构%' ORDER BY Price DESC;
SELECT top 1 Press FROM T.Book WHERE Author LIKE '%数据结构%' ORDER BY Price DESC;

-- 8.统计每个出版社图书的数量
SELECT Press 出版社,COUNT(*) 出版数量 FROM T.Book GROUP BY Press ORDER BY COUNT(*) DESC;

-- 9.查询全部同学都选修了的课程
SELECT Cno, Cname FROM T.Course C WHERE NOT EXISTS
(SELECT * FROM T.Student S WHERE NOT EXISTS
(SELECT * FROM T.SC WHERE SC.Cno = C.Cno and SC.Sno = S.Sno));

-- 10.查询至少选修了学号为 “ 201215122 ” 同学选修全部课程的学生。
SELECT DISTINCT Sno 学号 FROM T.SC scx WHERE NOT EXISTS(SELECT * FROM T.SC scy WHERE scy.Sno = '202110810' AND NOT EXISTS(SELECT * FROM T.SC scz WHERE scz.Sno = scx.Sno AND scz.Cno = scy.Cno));

秒客网

【数据库系统概论】实验三 SQL数据定义及更新

一、实验目的

二、实验内容

三、问题和要求

1．写出你操作并正确执行的代码及结果。

（5）检索选修人数最多的课程

（6）检索每门课程的最高分

（7）查询“数据结构”价格最低的出版社

（8）统计每个出版社图书的数量

（9）查询全部同学都选修了的课程

（10）查询至少选修了学号为 “ 201215122 ” 同学选修全部课程的学生。

2．完整的txt代码

相关文章