query_samples.sql

USE test_db;

-- ============================================================
-- 数据库工程作业查询样例
-- 覆盖：单表查询、多表连接查询、多表嵌套查询、EXISTS 查询、聚合操作查询
-- ============================================================

-- 1. 单表查询：查询当前待领养且已经完成绝育的猫咪信息
SELECT
    cat_id,
    cat_name,
    sex,
    color,
    age,
    sterilization_status,
    current_status,
    health_status
FROM Cat
WHERE current_status = '待领养'
  AND sterilization_status = '已绝育'
ORDER BY found_date DESC;

-- 2. 多表连接查询：查询领养申请、猫咪名称和领养人姓名
SELECT
    aa.app_id,
    aa.cat_id,
    c.cat_name,
    aa.adopter_id,
    p.person_name AS adopter_name,
    aa.apply_date,
    aa.app_status,
    aa.review_date
FROM AdoptionApplication aa
JOIN Cat c ON aa.cat_id = c.cat_id
JOIN Adopter a ON aa.adopter_id = a.adopter_id
JOIN Person p ON a.adopter_id = p.person_id
ORDER BY aa.apply_date DESC, aa.app_id DESC;

-- 3. 多表嵌套查询：查询由“猫咪救助社”志愿者救助过的猫咪
SELECT
    c.cat_id,
    c.cat_name,
    c.current_status,
    c.health_status
FROM Cat c
WHERE c.cat_id IN (
    SELECT rr.cat_id
    FROM RescueRecord rr
    WHERE rr.volunteer_id IN (
        SELECT v.volunteer_id
        FROM Volunteer v
        WHERE v.department = '猫咪救助社'
    )
)
ORDER BY c.cat_id;

-- 4. EXISTS 查询：查询至少存在一条领养申请记录的猫咪
SELECT
    c.cat_id,
    c.cat_name,
    c.current_status
FROM Cat c
WHERE EXISTS (
    SELECT 1
    FROM AdoptionApplication aa
    WHERE aa.cat_id = c.cat_id
)
ORDER BY c.cat_id;

-- 5. 聚合操作查询：按当前状态统计猫咪数量
SELECT
    current_status,
    COUNT(*) AS cat_count
FROM Cat
GROUP BY current_status
ORDER BY cat_count DESC;