第1关 请为三建工程项目建立一个供应情况的视图V_SPQ,包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)

sql
1
2
3
4
5
6
7
8
9
10
11
12
USE mydata;
#请在此处添加实现代码
########## Begin ##########
CREATE VIEW V_SPQ AS
SELECT SNO,PNO,QTY
FROM SPJ
JOIN J USING(JNO)
WHERE JNAME = '三建';

########## End ##########
#以下代码禁止删除
SELECT * FROM V_SPQ;

第2关 从视图V_SPQ找出三建工程项目使用的各种零件代码及其数

sql
1
2
3
4
5
6
7
8
9
USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT PNO ,SUM(QTY) SUM_QTY
FROM V_SPQ
GROUP BY PNO
ORDER BY SUM_QTY DESC

########## End ##########

第3关 从视图V_SPQ找出供应商S1的供应情况

sql
1
2
3
4
5
6
7
8
9
USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT PNO,QTY
FROM V_SPQ
WHERE SNO = 'S1'


########## End ##########

第4关 定义查询S2供应商的所有供应明细的视图V_SPJ2

sql
1
2
3
4
5
6
7
8
9
10
11
USE mydata;
#请在此处添加实现代码
########## Begin ##########
CREATE VIEW V_SPJ2 AS
SELECT *
FROM SPJ
WHERE SNO = 'S2';

########## End ##########
#以下代码禁止删除
SELECT * FROM V_SPJ2;

第5关 定义查询北京的供应商的编号、名称和城市的视图V_BJS

sql
1
2
3
4
5
6
7
8
9
10
USE mydata;
#请在此处添加实现代码
########## Begin ##########
CREATE VIEW V_BJS AS
SELECT SNO,SNAME,CITY
FROM S
WHERE CITY = '北京';
########## End ##########
#以下代码禁止删除
SELECT * FROM V_BJS;

第6关 定义查询各工程名称使用的各种颜色零件的个数的视图V_PJQ

sql
1
2
3
4
5
6
7
8
9
10
11
12
USE mydata;
#请在此处添加实现代码
########## Begin ##########
CREATE VIEW V_PJQ AS
SELECT JNAME,COLOR,SUM(QTY) SUM_QTY
FROM SPJ,P,J
WHERE SPJ.PNO = P.PNO AND SPJ.JNO = J.JNO
GROUP BY JNAME,COLOR;

########## End ##########
#以下代码禁止删除
SELECT * FROM V_PJQ ORDER BY V_PJQ.JNAME ASC, V_PJQ.COLOR ASC;

第7关 将视图V_SPQ中供应数量为400的供应商改为’S1’,并观察基本表SPJ的变化

sql
1
2
3
4
5
6
7
8
9
10
USE mydata;
#请在此处添加实现代码
########## Begin ##########
UPDATE V_SPQ
SET SNO = 'S1'
WHERE QTY=400;

########## End ##########
#以下代码禁止删除
SELECT * FROM SPJ;

第8关 建立比赛 1001 的所有解答的视图v_1001,并要求进行修改和插入操作时仍需保证该视图只有比赛1001的解答。

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
USE `sqlexp-sztuoj`;

#请在此处添加实现代码
########## Begin ##########
CREATE VIEW v_1001 AS
SELECT *
FROM solution
WHERE contest_id = '1001'
WITH CHECK OPTION;


########## End ##########
#保证下面一行MYSQL语句在16行的位置,以保证结果匹配
UPDATE v_1001 SET v_1001.contest_id = 1002;

第9关 建立2020级做了1003号题的选手视图v_user2020_1003(包括user_id、name、result)

sql
1
2
3
4
5
6
7
8
9
10
11
USE `sqlexp-sztuoj`;

#请在此处添加实现代码
########## Begin ##########
CREATE VIEW v_user2020_1003 As
SELECT DISTINCT u.user_id,name,result
FROM users u,solution s
WHERE u.user_id = s.user_id AND s.user_id LIKE "2020%" AND s.problem_id = 1003 ;
########## End ##########
#以下代码禁止删除
SELECT * FROM v_user2020_1003;

第10关 建立2020级做了1001号题且result为6的选手视图v_user2020_1001_6

sql
1
2
3
4
5
6
7
8
9
10
11
12
USE `sqlexp-sztuoj`;


#请在此处添加实现代码
########## Begin ##########
CREATE VIEW v_user2020_1001_6 AS
SELECT DISTINCT u.user_id,u.name,s.result,s.problem_id
FROM users u,solution s
WHERE u.user_id = s.user_id AND s.user_id LIKE "2020%" AND s.problem_id = 1001 AND s.result = 6;
########## End ##########
#以下代码禁止删除
SELECT * FROM v_user2020_1001_6;

第11关 将选手的user_id及解答的平均avgmemory定义为一个视图v_users_avgmemory

sql
1
2
3
4
5
6
7
8
9
10
USE `sqlexp-sztuoj`;

#请在此处添加实现代码
########## Begin ##########
CREATE VIEW v_users_avgmemory AS
SELECT user_id,AVG(memory) avgmemory
FROM solution
GROUP BY user_id;
########## End ##########
SELECT * FROM v_users_avgmemory;

第12关 删除视图v_1001

sql
1
2
3
4
5
6
7
8
9
USE `sqlexp-sztuoj`;

#请在此处添加实现代码
########## Begin ##########

DROP VIEW v_1001;
########## End ##########
#保证下面一行MYSQL语句在9行的位置,以保证结果匹配
SELECT v_1001.* FROM v_1001;

第13关 在视图v_users_avgmemory中查询平均memory在2000以下的选手信息及平均memory

sql
1
2
3
4
5
6
7
8
USE `sqlexp-sztuoj`;

#请在此处添加实现代码
########## Begin ##########
SELECT *
FROM v_users_avgmemory
WHERE avgmemory < 2000
########## End ##########

第14关 在视图v_1001中删除user_id为201902010318的记录

sql
1
2
3
4
5
6
7
8
9
USE `sqlexp-sztuoj`;

#请在此处添加实现代码
########## Begin ##########
DELETE FROM v_1001
WHERE user_id = 201902010318;
########## End ##########
#以下代码禁止删除
SELECT * FROM v_1001;

第15关 在视图v_users_avgmemory中插入一条记录(2020100904,1800)

sql
1
2
3
4
5
6
USE `sqlexp-sztuoj`;

#请在第5行开始添加实现代码,务必保证从第5行开始添加代码,以保证结果匹配
########## Begin ##########
INSERT INTO v_users_avgmemory VALUE(2020100904,1800)
########## End ##########

第16关 在视图v_user2020_1003中将user_id为20200202的result更改为10

sql
1
2
3
4
5
6
7
8
9
10
USE `sqlexp-sztuoj`;


########## Begin ##########
UPDATE v_user2020_1003
SET result = 10
WHERE user_id = '20200202';
########## End ##########
#以下代码禁止删除
SELECT v_user2020_1003.* FROM v_user2020_1003 WHERE v_user2020_1003.user_id = '20200202';

第17关 根据该需求给SPJ表插入相应数据

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
USE mydata;
#请在此处添加实现代码
########## Begin ##########
INSERT INTO SPJ (SNO,PNO,JNO,QTY)
SELECT
    'S3',
    'P4',
    JNO,
    NULL
FROM J
WHERE JNAME LIKE '%厂';

########## End ##########
#以下代码禁止删除
SELECT * FROM SPJ;

第18关 创建一个名为V_PJMAXSP的视图

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
USE mydata;
#请在此处添加实现代码
########## Begin ##########
CREATE VIEW TemTable AS
SELECT JNO,SNO,SUM(QTY) SUM_QTY
FROM SPJ
GROUP BY SNO,JNO
ORDER BY JNO,QTY DESC;

CREATE VIEW V_PJMAXSP AS
SELECT TemTable.JNO,TemTable.SNO,SNAME
FROM TemTable,S
WHERE SUM_QTY>=ALL(SELECT SUM_QTY FROM TemTable t WHERE TemTable.JNO=t.JNO)
    AND TemTable.SNO = S.SNO
ORDER BY JNO,SNO;
########## End ##########
#以下代码禁止删除
SELECT * FROM V_PJMAXSP;

第19关 创建一个名为test的视图

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
USE `sqlexp-sztuoj`;

#请在此处添加实现代码
########## Begin ##########
CREATE VIEW test AS
SELECT u.*
FROM users u
JOIN solution s USING(user_id)
WHERE NOT EXISTS(
    SELECT *
    FROM solution A
    WHERE A.user_id = '202002020217' AND result = 4 AND language = 1
        AND NOT EXISTS(
            SELECT *
            FROM solution B
            WHERE u.user_id = B.user_id AND A.problem_id = B.problem_id
        )
)
GROUP BY u.user_id
HAVING COUNT(DISTINCT s.contest_id) = 3;
########## End ##########
#以下代码禁止删除
SELECT * FROM test;

第20关 创建一个名为V_QTY_RANK的视图

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
USE mydata;
#请在此处添加实现代码
########## Begin ##########
CREATE VIEW V_QTY_RANK
AS
SELECT
    *,
    CASE
        WHEN QTY >= 500 THEN 'A'
        WHEN QTY BETWEEN 200 AND 500 THEN 'B'
        WHEN QTY < 200 THEN 'C'
        ELSE NULL
    END AS QTY_RANK
FROM SPJ
ORDER BY QTY_RANK;

########## End ##########
#以下代码禁止删除
SELECT * FROM V_QTY_RANK;