[AI SCHOOL 5기] SQL 프로그래밍 실습 - Merge

1. INNER JOIN

SELECT 
    l.Title,
    r.Name
FROM 
    albums AS l
INNER JOIN
    artists AS r
ON 
    r.ArtistId = l.ArtistId;

SELECT
   Title, 
   Name
FROM
   albums
INNER JOIN artists USING(ArtistId);

---

2. LEFT JOIN

SELECT
    Name, 
    Title
FROM
    artists
LEFT JOIN albums ON artists.ArtistId = albums.ArtistId
ORDER BY 
    Name;

SELF JOIN

SELECT m.firstname || ' ' || m.lastname AS 'Manager',
       e.firstname || ' ' || e.lastname AS 'Receives reports from'
FROM
    employees e
INNER JOIN 
    employees m 
ON 
    m.employeeid = e.reportsto
ORDER BY 
    manager;

• 'A 테이블'과 A 테이블의 복사본인 'B 테이블'을 합치기

---

3. Grouping Data

SELECT
    albumid,
    COUNT(trackid)
FROM
    tracks
GROUP BY
    albumid
ORDER BY 
    COUNT(trackid) DESC;

SELECT
    albumid,
    COUNT(trackid)
FROM
    tracks
GROUP BY
    albumid
HAVING
    albumid = 1;

SELECT
    albumid,
    COUNT(trackid)
FROM
    tracks
WHERE
    COUNT(trackid) BETWEEN 18 AND 20
GROUP BY
    albumid;

• 에러발생: WHERE문에는 집계함수 사용 불가
• WHERE가 집계함수보다 우선적으로 실행되기 때문

SELECT
    tracks.albumid,
    title,
    MIN(tracks.milliseconds),
    MAX(tracks.milliseconds),
    ROUND(AVG(tracks.milliseconds), 2)
FROM
    tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
GROUP BY
    tracks.albumid;

• ROUND는 n번째 자리까지 나타나도록 반올림

---

4. Subquery

SELECT AVG(SUM(bytes))
FROM tracks
GROUP BY albumid;

• SELECT 문에서 집계함수의 결과 값에 바로 중첩하여 집계함수 적용 불가

SELECT
    AVG(SIZE)
FROM
    (SELECT
         SUM(bytes) AS SIZE
     FROM
         tracks
     GROUP BY
         albumid);