-- 比较条件

-- 查询所有活跃状态的学生
SELECT *
FROM student
WHERE account_id IN (
    SELECT account_id
    FROM account
    WHERE status = '活跃'
);

-- 查询所有非管理员账户
SELECT *
FROM account
WHERE type <> '管理员';

-- 集合比较

-- 查询在多个场地有过预约的学生
SELECT s.student_id, s.name
FROM student s
JOIN appointment a ON s.student_id = a.student_id
GROUP BY s.student_id, s.name
HAVING COUNT(DISTINCT a.venue_id) > 1;

-- 查询同时是教练和学生的人员
SELECT p.info_id, p.phone, p.email
FROM personal_info p
WHERE p.info_id IN (SELECT student_id FROM student)
AND p.info_id IN (SELECT personal_info_id FROM coach);

-- 范围比较

-- 查询2023年10月1日到2023年10月31日期间的反馈
SELECT *
FROM feedback
WHERE date BETWEEN '2023-10-01' AND '2023-10-31';

-- 查询容量大于50的场地
SELECT *
FROM venue
WHERE capacity > 50;

-- 字符串相似比较

-- 查询名字以“张”开头的学生
SELECT *
FROM student
WHERE name LIKE '张%';

-- 查询电子邮件包含“example.com”的个人信息
SELECT *
FROM personal_info
WHERE email LIKE '%example.com%';

-- 多表连接

-- 查询每个学生的最新反馈
SELECT f.feedback_id, f.content, f.date, s.student_id, s.name
FROM feedback f
JOIN training_record tr ON f.feedback_id = tr.feedback_id
JOIN student s ON tr.student_id = s.student_id
WHERE (s.student_id, f.date) IN (
    SELECT student_id, MAX(date)
    FROM feedback f2
    JOIN training_record tr2 ON f2.feedback_id = tr2.feedback_id
    GROUP BY student_id
);

-- 查询每个教练负责的所有学生
SELECT c.coach_id, c.name AS coach_name, s.student_id, s.name AS student_name
FROM coach c
JOIN appointment a ON c.coach_id = a.coach_id
JOIN student s ON a.student_id = s.student_id
ORDER BY c.coach_id, s.student_id;

-- 嵌套查询

-- 查询有超过一次预约的学生
SELECT student_id, name
FROM student
WHERE student_id IN (
    SELECT student_id
    FROM appointment
    GROUP BY student_id
    HAVING COUNT(*) > 1
);

-- 查询没有任何反馈的预约
SELECT *
FROM appointment
WHERE appointment_id NOT IN (
    SELECT appointment_id
    FROM feedback
);

-- EXISTS 查询

-- 查询有预约记录的学生
SELECT *
FROM student s
WHERE EXISTS (
    SELECT 1
    FROM appointment a
    WHERE a.student_id = s.student_id
);

-- 查询有训练记录的教练
SELECT *
FROM coach c
WHERE EXISTS (
    SELECT 1
    FROM training_record tr
    WHERE tr.coach_id = c.coach_id
);