[SQL] JOIN 연습

연습문제 1) 모든 개를 품종과 함께 나열하기

SELECT
	dogs.name AS dog_name,
    breeds.name AS breed_name
FROM
	dogs
INNER JOIN
	breeds ON dogs.breed_id = breeds.breed_id;

연습문제 2) 개마다 펫 여권 정보와 주인 정보 나열하기

SELECT 
	dogs.name as dog_name,
    pet_passports.blood_type as blood_type,
    pet_passports.allergies as allergies,
    pet_passports.last_checkup_date as last_checkup_date,
    owners.name as owner_name,
    owners.email as owner_email,
    owners.phone as owner_phone,
    owners.address as owner_address
FROM 
	dogs
LEFT JOIN
	pet_passports ON dogs.dog_id = pet_passports.dog_id
LEFT JOIN
	owners ON dogs.owner_id = owners.owner_id;

연습문제 3) 품종별 개 수 구하기

SELECT 
	b.name as breed_name,
    COUNT(*) as breed_count
FROM
	dogs as d
LEFT JOIN
	breeds as b USING(breed_id)
GROUP BY
	b.name
ORDER BY
	breed_count DESC;

연습문제 4) 주인별 키우는 개 개수, 평균 체중, 평균 나이 구하기

SELECT
	owners.name as owner_name,
    COUNT(dogs.dog_id) as dog_count,
    ROUND(AVG(dogs.weight), 2) as dog_avg_weight,
    ROUND(AVG(TIMESTAMPDIFF(YEAR, dogs.date_of_birth, CURDATE())), 1) as dog_avg_age
FROM 
	owners
LEFT JOIN
	dogs USING(owner_id)
GROUP BY
	owners.owner_id
ORDER BY
	dog_count DESC;

연습문제 5) 주인마다 키우는 개와 그 개의 재주 나열하기

SELECT
	owners.name as owner_name,
    dogs.name as dog_name,
    tricks.name as trick_name 
FROM 
	owners
INNER JOIN
	dogs USING(owner_id)
INNER JOIN
	dog_tricks USING(dog_id)
INNER JOIN
	tricks USING(trick_id)
ORDER BY
	owners.name,
    dogs.name;

연습문제 6) 품종별 가장 무거운 개 이름 찾기

SELECT 
	breeds.breed_id as breed_id,
	breeds.name as breed_name,
    dogs.name as fattest_dog_name,
    dogs.weight as weight
FROM 
	breeds 
INNER JOIN
	dogs USING(breed_id)
WHERE 1=1
	AND dogs.weight = (             -- 서브쿼리에 해당하는 쿼리를 메인 쿼리에 직접 넣어줘야 함 (mysql은 CTE에서 외부 쿼리를 참조할 수 x)
    					SELECT 
							MAX(inner_dogs.weight) 
                        FROM 
                            dogs as inner_dogs
                        WHERE 1=1
                            AND inner_dogs.breed_id = breeds.breed_id
                        );