[해커랭크] [SQL] The PADs

스터디 팀원들과 리트코드 무료문제를 다 풀었다! 

이제 해커랭크로 고고

 

오늘 푼 문제: 

The PADS | HackerRank

The PADS | HackerRank

 

헷갈렸던 점: 두개의 쿼리를 작성해야하는 문제인데 한 쿼리로 다 풀려고 했다가 헤맸다.

 

샘플 아웃풋 :

Ashely(P)
Christeen(P)
Jane(A)
Jenny(D)
Julia(A)
Ketty(P)
Maria(A)
Meera(S)
Priya(S)
Samantha(D)
There are a total of 2 doctors.
There are a total of 2 singers.
There are a total of 3 actors.
There are a total of 3 professors.

 

풀이:

Oracle

SELECT Name ||'(' || UPPER(SUBSTR(Occupation,1,1))||')' as name
FROM OCCUPATIONS
ORDER BY name asc  ;
SELECT 'There are a total of '||COUNT(Occupation) || ' '|| LOWER(Occupation) || 's.' 
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(Occupation) asc , Occupation asc ;

 

mysql

SELECT CONCAT(Name, '(' , UPPER(SUBSTR(Occupation,1,1)), ')' ) as name
FROM OCCUPATIONS
ORDER BY name asc  ;
SELECT CONCAT('There are a total of ',COUNT(Occupation) , ' ', LOWER(Occupation), 's.' ) result
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(Occupation) asc , Occupation asc ;

 

oracle의 연결 연산자 : ||
mysql의 연결 연산자 함수 : CONCAT(A,B) 

# oracle에서의 || 연산자를 concat으로 바꿔주면 됩니다