테이블 구조

문제1

Generate the following two result sets:

Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).
Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:

There are a total of [occupation_count][occupation]s.
where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercase occupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically.

SELECT name||'('||SUBSTR(occupation,1,1)||')' AS oc1
FROM occupations
ORDER BY 1
;
SELECT 'There are a total of ' || COUNT(name) || ' ' || LOWER(occupation) || 's.' 
FROM occupations 
GROUP BY occupation
ORDER BY COUNT(name), occupation
;

문자열 합치기 : CONCAT을 써도 되지만, || 이 가독성이 좋고 편리함.

문제2

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively.

SELECT Doctor,Professor,Singer,Actor
FROM
    (SELECT name,
           occupation,
           ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY Name) AS name_order
    FROM OCCUPATIONS)
PIVOT
    (
    MAX(name)
    FOR occupation IN 
    ('Doctor' AS Doctor, 'Professor' AS Professor,'Singer' AS Singer,'Actor' AS Actor)
    )
ORDER BY  name_order ;

Oracle 11 버전 이후부터 PIVOT 기능 사용 가능.
PIVOT 이 안될 때는 DECODE 문 사용.
PIVOT 내 구문은 무조건 연산함수를 써야하므로, MAX 문 사용.