实验6 SQL的多表查询-2

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

（查询结果仅显示前50条，即在查询语句末尾加LIMIT 50）

SELECT * FROM users, solution WHERE users.user_id = solution.user_id LIMIT 50;

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

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;

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

（查询结果仅显示前50条，即在查询语句末尾加LIMIT 50）

SELECT * FROM users LEFT JOIN solution ON users.user_id = solution.user_id LIMIT 50;

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

SELECT  users.user_id,reg_time,name,problem_id,result FROM users,solution WHERE users.user_id = solution.user_id LIMIT 50;

5. 用嵌套查询查找和problem_id为 1026 的题目属于同一个比赛的题目信息，结果按problem_id升序排序。

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 = 1026
    )
)
ORDER BY problem_id ASC;

6. 用连接查询查找和problem_id为 1026 的题目属于同一个比赛的题目信息，结果按problem_id升序排序。

SELECT DISTINCT problem.* FROM problem, contest_problem p1, contest_problem p2 WHERE 
    p1.contest_id = p2.contest_id AND
    problem.problem_id = p1.problem_id AND
    p2.problem_id = 1026 
ORDER BY problem_id ASC;

7. 用 EXISTS 实现查询做了 1032 号题的选手信息

SELECT user_id, reg_time, name FROM users WHERE EXISTS (
    SELECT * FROM solution WHERE 
        users.user_id = solution.user_id AND
        solution.problem_id = 1032
);

8. 用NOT EXISTS实现查询没做 1032 号题的选手信息

SELECT user_id, reg_time, name FROM users WHERE NOT EXISTS(
    SELECT * FROM solution WHERE 
        solution.user_id = users.user_id AND
        solution.problem_id = 1032
);

9. 用 EXISTS 实现查询和problem_id为 1026 的题目属于同一个比赛的题目信息，结果按problem_id升序排序。

SELECT DISTINCT * FROM problem WHERE EXISTS (
    SELECT * FROM contest_problem p1, contest_problem p2 WHERE 
        p1.contest_id = p2.contest_id AND 
        p1.problem_id = 1026 AND
        p2.problem_id = problem.problem_id
)
ORDER BY problem_id ASC;

10. 用 NOT EXISTS 实现查询参与过所有比赛的选手信息

SELECT * FROM users WHERE NOT EXISTS (
    SELECT * FROM contest WHERE NOT EXISTS (
        SELECT * FROM solution WHERE 
            contest.contest_id = solution.contest_id AND
            solution.user_id = users.user_id
    )
);