实验2 SQL的多表查询

第1关：等值连接：求S表和J表城市相同的等值连接(列顺序还是按照S、J表)

任务描述
等值连接：求S表和J表城市相同的等值连接(列顺序还是按照S、J表)

相关知识
1、工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成。 J表如下图：

现已构建J表，结构信息如下：

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

现已构建S表，结构信息如下：

SELECT S.*,J.*
FROM S,J
WHERE S.city=J.city
1
2
3

第2关：查询供应情况，并显示供应商、零件和工程三者的名称

任务描述
查询供应情况，并显示供应商、零件和工程三者的名称

相关知识
1、供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成.
S表如下图：

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

现已构建P表，结构信息如下：

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

现已构建J表，结构信息如下：

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

现已构建SPJ表，结构信息如下：

SELECT SNAME,PNAME,JNAME,QTY
FROM S,P,J,SPJ
WHERE SPJ.SNO=S.SNO AND
    SPJ.PNO=P.PNO AND
    SPJ.JNO=J.JNO
1
2
3
4
5

第3关：找出上海厂商供应的所有零件号码

任务描述
找出上海厂商供应的所有零件号码(注意去重)

相关知识
1、供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成.
S表如下图：

现已构建S表，结构信息如下：

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

现已构建SPJ表，结构信息如下：

SELECT DISTINCT PNO 
FROM SPJ,S 
where SPJ.SNO=S.SNO AND
CITY="上海"
1
2
3
4

第4关：找出使用上海产的零件的工程名称

任务描述
找出使用上海产的零件的工程名称，结果按工程名称降序排序。

相关知识
1、供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成.
S表如下图：

现已构建S表，结构信息如下：

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

现已构建J表，结构信息如下：

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

现已构建SPJ表，结构信息如下：

SELECT DISTINCT JNAME
FROM J,SPJ,S
WHERE J.JNO=SPJ.JNO
    AND SPJ.SNO=S.SNO
    AND S.CITY='上海'
1
2
3
4
5

第5关 找出没有使用天津产的零件的工程号码

任务描述
找出没有使用天津产的零件的工程号码

相关知识
1、供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成.
S表如下图：

现已构建S表，结构信息如下：

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

现已构建SPJ表，结构信息如下：

SELECT DISTINCT JNO
FROM  SPJ
WHERE  JNO NOT IN(
SELECT JNO
FROM S,SPJ
WHERE S.SNO=SPJ.SNO AND CITY='天津')
1
2
3
4
5
6

第6关 求供应工程J1零件为红色的供应商号码SNO

任务描述
求供应工程J1零件为红色的供应商号码SNO

相关知识
1、零件表P由零件代码（PNO）、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成； P表如下图：

现已构建P表，结构信息如下：

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

现已构建SPJ表，结构信息如下：

SELECT SNO
FROM P,SPJ
WHERE P.PNO=SPJ.PNO AND JNO='J1' AND COLOR='红';
1
2
3

第7关 求没有使用天津供应商生产的红色零件的工程号 JNO

任务描述
求没有使用天津供应商生产的红色零件的工程号 JNO

相关知识
1、供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成.
S表如下图：

现已构建S表，结构信息如下：

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

现已构建P表，结构信息如下：

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

现已构建SPJ表，结构信息如下：

SELECT DISTINCT JNO
FROM  SPJ
WHERE JNO NOT IN(
SELECT JNO
FROM S,P,SPJ
WHERE S.SNO=SPJ.SNO AND 
P.PNO=SPJ.PNO AND 
COLOR='红' AND CITY='天津')
1
2
3
4
5
6
7
8

第8关 查询每个选手的信息及其提交的解答信息，没做题的选手不显示

任务描述
查询每个选手的信息及其提交的解答信息，没做题的选手不显示（查询结果仅显示前50条，即在查询语句末尾加LIMIT 50）

相关知识
1、users为选手信息表； users表如下图（仅显示前几条）：

现已构建users表，结构信息如下：

2、solution为选手提交的题目解答
solution表如下图（仅显示前几条）：

现已构建solution表，结构信息如下：

SELECT * 
FROM users,solution 
WHERE users.user_id=solution.user_id
LIMIT 50;
1
2
3
4

第9关 查询做了1001题且耗时大于500（time）的选手信息

任务描述
查询做了1001题且耗时大于500（time）的选手信息

相关知识
1、users为选手信息表； users表如下图（仅显示前几条）：

现已构建users表，结构信息如下：

2、solution为选手提交的题目解答
solution表如下图（仅显示前几条）：

现已构建solution表，结构信息如下：

SELECT DISTINCT users.user_id,reg_time,name
FROM users,solution
WHERE users.user_id=solution.user_id 
AND solution.problem_id=1001
AND solution.time>500;
1
2
3
4
5

第10关 查询所有选手信息及其提交的解答信息，没做题的选手也要显示

任务描述
查询所有选手信息及其提交的解答信息，没做题的选手也要显示 （查询结果仅显示前50条，即在查询语句末尾加LIMIT 50）

相关知识
1、users为选手信息表； users表如下图（仅显示前几条）：

现已构建users表，结构信息如下：

2、solution为选手提交的题目解答
solution表如下图（仅显示前几条）：

现已构建solution表，结构信息如下：

SELECT  DISTINCT *
FROM  users
LEFT JOIN solution
ON users.user_id=solution.user_id 
LIMIT 50
1
2
3
4
5

第11关 查询每个选手的信息、交的题目、和提交的结果，没做题的选手不显示

任务描述
查询每个选手的信息、交的题目、和提交的结果，没做题的选手不显示（查询结果仅显示前50条，即在查询语句末尾加LIMIT 50）

相关知识
1、users为选手信息表； users表如下图（仅显示前几条）：

现已构建users表，结构信息如下：

2、solution为选手提交的题目解答
solution表如下图（仅显示前几条）：

现已构建solution表，结构信息如下：

SELECT  users.user_id,reg_time,name,problem_id,result
FROM users, solution
where users.user_id=solution.user_id 
LIMIT 50
1
2
3
4

第12关 用嵌套查询查找和problem_id为 1009 的题目属于同一个比赛的题目信息

任务描述
用嵌套查询查找和problem_id为 1009 的题目属于同一个比赛的题目信息，结果按problem_id升序排序。

相关知识
1、problem为题目表； problem表如下图（仅显示前几条）：

现已构建problem表，结构信息如下：

2、contest_problem为比赛-题目关系；
contest_problem表如下图（仅显示前几条）：

现已构建contest_problem表，结构信息如下：

SELECT * FROM problem
WHERE problem_id IN(
    SELECT problem_id FROM contest_problem
    WHERE contest_id IN(
        SELECT contest_id FROM contest_problem
        WHERE problem_id = 1009
    )
)
ORDER BY problem_id ASC;
1
2
3
4
5
6
7
8
9

第13关 用连接查询查找和problem_id为 1009 的题目属于同一个比赛的题目信息

任务描述
用连接查询查找和problem_id为 1009 的题目属于同一个比赛的题目信息，结果按problem_id升序排序。

相关知识
1、problem为题目表； problem表如下图（仅显示前几条）：

现已构建problem表，结构信息如下：

2、contest_problem为比赛-题目关系；
contest_problem表如下图（仅显示前几条）：

现已构建contest_problem表，结构信息如下：

SELECT problem.problem_id,title,description,hint,time_limit,memory_limit FROM problem,contest_problem A,contest_problem B
WHERE A.problem_id = 1009 AND A.contest_id = B.contest_id AND problem.problem_id = B.problem_id
ORDER BY B.problem_id ASC;
1
2
3

第14关 用 EXISTS 实现查询做了 1032 号题的选手信息

任务描述
用 EXISTS 实现查询做了 1032 号题的选手信息

相关知识
1、users为选手信息表； users表如下图（仅显示前几条）：

现已构建users表，结构信息如下：

2、solution为选手提交的题目解答
solution表如下图（仅显示前几条）：

现已构建solution表，结构信息如下：

SELECT user_id,reg_time,name
FROM users
WHERE EXISTS (
    SELECT *
    FROM solution
    WHERE users.user_id=solution.user_id
    AND problem_id=1032
);
1
2
3
4
5
6
7
8

第15关 用 EXISTS 实现查询没做 1032 号题的选手信息

任务描述
用 EXISTS 实现查询没做 1032 号题的选手信息

相关知识
1、users为选手信息表； users表如下图（仅显示前几条）：

现已构建users表，结构信息如下：

2、solution为选手提交的题目解答
solution表如下图（仅显示前几条）：

现已构建solution表，结构信息如下：

SELECT user_id,reg_time,name
FROM users
WHERE NOT EXISTS (
    SELECT *
    FROM solution
    WHERE users.user_id=solution.user_id
    AND problem_id=1032
);
1
2
3
4
5
6
7
8

第16关 用 EXISTS 实现查询和problem_id为 1009 的题目属于同一个比赛的题目信息

任务描述
用 EXISTS 实现查询和problem_id为 1009 的题目属于同一个比赛的题目信息，结果按problem_id升序排序。

相关知识
1、problem为题目表； problem表如下图（仅显示前几条）：

现已构建problem表，结构信息如下：

2、contest_problem为比赛-题目关系；
contest_problem表如下图（仅显示前几条）：

现已构建contest_problem表，结构信息如下：

SELECT * FROM problem 
WHERE EXISTS(
    SELECT * FROM contest_problem A, contest_problem B
    WHERE A.problem_id = 1009 AND A.contest_id = B.contest_id AND problem.problem_id = B.problem_id
)
1
2
3
4
5

第17关 用 EXISTS 实现查询参与过所有比赛的选手信息

任务描述
用 EXISTS 实现查询参与过所有比赛的选手信息

相关知识
1、users为选手信息表； users表如下图（仅显示前几条）：

现已构建users表，结构信息如下：

2、contest为比赛信息表；
contest表如下图（仅显示前几条）：

现已构建contest表，结构信息如下：

3、solution为选手提交的题目解答
solution表如下图（仅显示前几条）：

现已构建solution表，结构信息如下：

SELECT users.user_id,reg_time,name
FROM users
WHERE NOT EXISTS(
    SELECT * FROM contest
    WHERE NOT EXISTS(
        SELECT * FROM solution
        WHERE user_id=users.user_id AND contest_id = contest.contest_id
    )
);
1
2
3
4
5
6
7
8
9