題目 The PADS

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:

【題目】

• 輸入: 原始資料含有人名+職業
• 輸出: 根據題目指定的格式分兩段作輸出
  (1)將原始資料輸出, 且指定要以 NAME(職稱第一字母)
  (2)先算出每個職業的人數, 再以指定格式:There are a total of 人數 職稱s. 輸出

【解題邏輯】
第一段輸出只需要使用字串處理技巧
第二段則需要先用group by聚合函數算出結果後, 將其按照人數、職稱優先性輸出(字串做排序即可)

    -- Oracle
    select C.name||'('||substr(C.occupation,1,1)||')'
    from OCCUPATIONS C
    union all
    select 'There are a total of '||sum(1)||' '||lower(occupation)|| 's.'
    from OCCUPATIONS
    group by occupation
    order by 1
    ;
    
    >>> 輸出:
    Aamina(D)
    Ashley(P)
    Belvet(P)
    Britney(P)
    Christeen(S)
    Eve(A)
    Jane(S)
    Jennifer(A)
    Jenny(S)
    Julia(D)
    Ketty(A)
    Kristeen(S)
    Maria(P)
    Meera(P)
    Naomi(P)
    Priya(D)
    Priyanka(P)
    Samantha(A)
    There are a total of 3 doctors.
    There are a total of 4 actors.
    There are a total of 4 singers.
    There are a total of 7 professors.
    BlogScoringEnvironmentFAQAbout UsSupportCareersTerms Of ServicePrivacy Policy