数据库系统头歌实验一 SQL的DDL语言和单表查询

第1关：创建供应商表S(SNO,SNAME,STATUS,CITY)

供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成；S表如下

USE mydata;#请在此处添加实现代码########## Begin ##########CREATE TABLE S(SNO char(10),SNAME char(10),STATUS char(10),CITY char(10));INSERT INTO S VALUES ('S1', '精益', '20', '天津');INSERT INTO S VALUES ('S2', '盛锡', '10', '北京');INSERT INTO S VALUES ('S3', '东方红', '30', '北京');INSERT INTO S VALUES ('S4', '丰泰盛', '20', '天津');INSERT INTO S VALUES ('S5', '为民', '30', '上海');########## End ###########以下代码禁止删除SELECT * FROM S;

第2关：将P表中的所有红色零件的重量增加6。

零件表P由零件代码（PNO）、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成；

P表如下：

USE mydata;#请在此处添加实现代码########## Begin ##########UPDATE PSET WEIGHT=WEIGHT+6WHERE COLOR='红';########## End ###########以下代码禁止删除SELECT * FROM P;

第3关：把P表中全部红色零件的颜色改成蓝色

零件表P由零件代码（PNO）、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成；

USE mydata;#请在此处添加实现代码########## Begin ##########UPDATE PSET COLOR='蓝'WHERE COLOR='红';########## End ###########以下代码禁止删除SELECT * FROM P;

第4关：将SPJ表中由S5供给J4的零件P6改为由S3供应

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。

SPJ表如下：

USE mydata;#请在此处添加实现代码########## Begin ##########UPDATE SPJSET SNO='S3'WHERE SNO='S5'AND PNO='P6'AND JNO='J4';########## End ###########以下代码禁止删除SELECT * FROM SPJ;

第5关：将SPJ表中所有天津供应商的QTY属性值减少11（用子查询方式）

1、供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成.

2、供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。

S表：

SPJ表：

USE mydata;#请在此处添加实现代码########## Begin ##########UPDATE SPJSET QTY=QTY-11WHERE SNO IN (SELECT SNOFROM SWHERE CITY='天津');########## End ###########以下代码禁止删除SELECT * FROM SPJ;

第6关：删除J表中的所有工程项目在天津的记录

工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成。

USE mydata;#请在此处添加实现代码########## Begin ##########DELETEFROM JWHERE CITY='天津';########## End ###########以下代码禁止删除SELECT * FROM J;

第7关：从SPJ表中删除供应商号是S2的记录

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。

USE mydata;#请在此处添加实现代码########## Begin ##########DELETEFROM SPJWHERE SNO='S2';########## End ###########以下代码禁止删除SELECT * FROM SPJ;

第8关：从SPJ表中删除零件名称为螺丝刀的记录（用子查询方式)

1、零件表P由零件代码（PNO）、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成；

2.供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。

USE mydata;#请在此处添加实现代码########## Begin ##########DELETEFROM SPJWHERE PNO IN(SELECT PNO FROM P WHERE PNAME='螺丝刀');########## End ###########以下代码禁止删除SELECT * FROM SPJ;

第9关：删除P表中所有的记录

零件表P由零件代码（PNO）、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成；

USE mydata;#请在此处添加实现代码########## Begin ##########DELETE FROM P;########## End ###########以下代码禁止删除SELECT * FROM P;

第10关：请将(S2，J6，P4，200)插入供应情况关系表SPJ

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。

USE mydata;#请在此处添加实现代码########## Begin ##########INSERTINTO SPJVALUES('S2','P4','J6','200');########## End ###########以下代码禁止删除SELECT * FROM SPJ;

第11关：求供应工程J1零件的供应商号码SNO(注意去重复)

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。

USE mydata;#请在此处添加实现代码########## Begin ##########SELECT DISTINCT SNOFROM SPJWHERE JNO='J1';########## End ##########

第12关：求供应工程J1零件P1的供应商号码SNO

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。

USE mydata;#请在此处添加实现代码########## Begin ##########SELECT DISTINCT SNOFROM SPJWHERE JNO='J1' AND PNO='P1';########## End ##########

第13关：找出所有供应商的姓名和所在城市

供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成.

USE mydata;#请在此处添加实现代码########## Begin ##########SELECT DISTINCT SNAME,CITYFROM S;########## End ##########

第14关：找出所有零件的名称、颜色、重量

零件表P由零件代码（PNO）、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成；

USE mydata;#请在此处添加实现代码########## Begin ##########SELECT DISTINCT PNAME,COLOR,WEIGHTFROM P;########## End ##########

第15关：找出使用供应商S1所供应零件的工程号码

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。

USE mydata;#请在此处添加实现代码########## Begin ##########SELECT DISTINCT JNOFROM SPJWHERE SNO='S1';########## End ##########

第16关：在users表中新增一个用户，user_id为2019100904学号，name为’2019-物联网-李明’

users为选手信息表；

USE `sqlexp-sztuoj`;#请在此处添加实现代码########## Begin ##########INSERT INTO users(user_id,name)VALUES('2019100904','2019-物联网-李明');########## End ###########以下代码禁止删除SELECT * FROM users WHERE users.user_id = '2019100904';

第17关：在users表中更新用户 user_id为robot_2 的信息，name设为 ‘机器人二号’

USE `sqlexp-sztuoj`;#请在此处添加实现代码########## Begin ##########UPDATE usersSET name='机器人二号'WHERE user_id='robot_2';########## End ###########以下代码禁止删除SELECT * FROM users WHERE users.user_id = 'robot_2';

第18关：将solution表中所有 problem_id 为1003 题目的解答结果（result）设为 6

USE `sqlexp-sztuoj`;#请在此处添加实现代码########## Begin ##########UPDATE solutionSET result=6WHERE problem_id='1003';########## End ###########以下代码禁止删除SELECT * FROM solution WHERE solution.problem_id = 1003;

第19关：删除solution表中比赛contest_id 为1001的全部解答

USE `sqlexp-sztuoj`;#请在此处添加实现代码########## Begin ##########DELETEFROM solutionWHERE contest_id='1001';########## End ###########以下代码禁止删除SELECT * FROM solution WHERE solution.contest_id = 1001;

第20关：查询所有 contest 的 title 和 end_time。

contest为比赛信息表；

USE `sqlexp-sztuoj`;#请在此处添加实现代码########## Begin ##########SELECT title,end_timeFROM contest;########## End ##########

第21关：查询哪些选手的 user_id 提交过 solution，要求结果中 user_id 不重复

USE `sqlexp-sztuoj`;#请在此处添加实现代码########## Begin ##########SELECT DISTINCT user_idFROM solution;########## End ##########

第22关：查询 end_time 晚于 ‘2020-11-21 17:30:00’ 的 contest_id

contest为比赛信息表；

USE `sqlexp-sztuoj`;#请在此处添加实现代码########## Begin ##########SELECT contest_idFROM contestWHERE end_time>'2020-11-21 17:30:00';########## End ##########

第23关：查询 problem_id 在 1005~1009 之间的 problem 的 title

problem为题目表；

USE `sqlexp-sztuoj`;#请在此处添加实现代码########## Begin ##########SELECT titleFROM problemWHERE problem_id BETWEEN 1005 AND 1009;########## End ##########

第24关：查询 language 不在 0、1、3 中的 solution 的 code_length

solution：选手提交的题目解答

USE `sqlexp-sztuoj`;#请在此处添加实现代码########## Begin ##########SELECT code_lengthFROM solutionWHERE language NOT IN('0','1','3');########## End ##########

第25关：查询2018级选手信息（user_id 为学号的用户前4位为年级）

USE `sqlexp-sztuoj`;#请在此处添加实现代码########## Begin ##########SELECT*FROM usersWHERE user_id LIKE '2018%';########## End ##########

第26关：查询’生医’专业选手信息（name 中有 ‘生医’ 的选手）

USE `sqlexp-sztuoj`;#请在此处添加实现代码########## Begin ##########SELECT*FROM usersWHERE name LIKE '___生医%';########## End ##########

第27关：查询不属于任何比赛的solution的 solution_id 和 in_date（contest_id 为 NULL）

USE `sqlexp-sztuoj`;#请在此处添加实现代码########## Begin ##########SELECT solution_id,in_dateFROM solutionWHERE contest_id IS NULL;########## End ##########

第28关：查询 result 为 6 且 problem_id大于1010 的 solution_id与language

USE `sqlexp-sztuoj`;#请在此处添加实现代码########## Begin ##########SELECT solution_id,languageFROM solutionWHERE result=6 AND problem_id>1010;########## End ##########

第29关：查询提交过solution的选手人数

USE `sqlexp-sztuoj`;#请在此处添加实现代码########## Begin ##########SELECT COUNT(DISTINCT solution.user_id) FROM solution;########## End ##########

第30关：查询耗内存（memory）最多的solution的内存消耗大小与 solution_id

USE `sqlexp-sztuoj`;#请在此处添加实现代码########## Begin ##########SELECT memory,solution_idFROM solutionWHERE memory IN(SELECT MAX(memory)FROM solution);########## End ##########

第31关：查询每个题目的提交数

USE `sqlexp-sztuoj`;#请在此处添加实现代码########## Begin ##########SELECT problem_id,COUNT(solution.problem_id)FROM solutionGROUP BY problem_id;########## End ##########

第32关：查询提交数大于20的题目的题号

USE `sqlexp-sztuoj`;#请在此处添加实现代码########## Begin ##########SELECT problem_idFROM solutionGROUP BY problem_idHAVING COUNT(*)>20;########## End ##########

第33关：查找所有problem_id 为1001 的解答或 contest_id 为空的解答

USE `sqlexp-sztuoj`;#请在此处添加实现代码########## Begin ##########SELECT*FROM solutionWHERE problem_id=1001 OR contest_id IS NULL;########## End ##########