오늘도 데이터분석

[문제]

department-highest-salary 문제 바로 가기

[풀이]

SELECT de.name Department
     , em.name Employee
     , em.salary Salary
FROM employee em
    JOIN (
      SELECT departmentID, MAX(salary) max
      FROM employee
      GROUP BY departmentID) hi ON hi.departmentID = em.departmentID 
                                AND hi.max = em.salary 
    JOIN department de ON em.departmentID = de.id

- 윈도우 함수 MAX() 이용 풀이 

SELECT d.name AS Department
     , s.name AS Employee
     , maxsalary AS Salary
FROM(
    SELECT name
     , salary
     , departmentID
     , MAX(salary) OVER (PARTITION BY departmentID) AS maxsalary
    FROM employee) s
INNER JOIN department d ON d.id = s.departmentID
WHERE s.salary = s.maxsalary;

[문제]

Delete Duplicate Emails 문제 바로가기

[풀이]

- 서브쿼리 

DELETE 
FROM person
WHERE id NOT IN (
    SELECT sub.min_id
    FROM (
        SELECT email, MIN(id) min_id
        FROM person 
        GROUP BY email
    )sub)

DELETE, UPDATE는 하위 절에서 동일한 테이블 사용 못함. 그래서 sub테이블 만들어줌

참고 : https://dev.mysql.com/doc/refman/8.0/en/subquery-restrictions.html

- JOIN

DELETE p1
FROM person p1
    INNER JOIN person p2 ON p1.email = p2.email
WHERE p1.id > p2.id

swap salary 문제 바로가기

[풀이]

UPDATE salary 
SET sex  = CASE WHEN sex = 'm' THEN 'f'
                WHEN sex = 'f' THEN 'm'
                END;

UPDATE salary 
SET sex  = CASE WHEN sex = 'm' THEN 'f'
                ELSE 'm' END;

The PADS 문제 바로 가기 

[문제]

Generate the following two result sets:

1. 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).
2. Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:
   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.

There are a total of [occupation_count] [occupation]s.

문제정리

쿼리 두개 작성

1. 이름을 알파벳 순으로 정렬하고 이름 옆에 직업의 맨 앞 글자만 괄호쳐서 나타내는 쿼리 작성

2. 직업의 수와 직업이름을 소문자로 바꿔 결과값을 

"There are a total of [occupation_count] [occupation]s." 이 문장으로 나타내는 쿼리 작성 

   직업수로 내림차순 정렬하되, 동일한 값이면 직업이름순 정렬

[풀이]

- SUBSTR으로 문자열 앞 글자만 자름 

- 문자열 붙이는 CONCAT 함수 사용

-소문자로 바꾸는 LOWER함수 사용

SELECT CONCAT(name, '(', SUBSTR(occupation, 1, 1), ')')
FROM occupations
ORDER BY name;

SELECT CONCAT('There are a total of ', COUNT(*), ' ', LOWER(occupation),'s.')
FROM occupations
GROUP BY occupation
ORDER BY COUNT(*), occupation;

비교적 수월하게 두 쿼리를 작성했는데 계속 에러가 나서 문제가 뭔가 했더니 해결책은 ; 세미콜론 이었다.

UNION은 두 쿼리의 컬럼 수가 맞아야 하기 때문에 사용하지 못한다.

MySQL은 쿼리 끝에 세미콜론을 쓰지 않아도 되지만

오라클처럼 혹시나 해서 세미콜론을 써보니 결과값이 연달아서 잘 나온다. 

new companies 문제 바로가기 

[문제]

Amber's conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy: 

Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.

Note:

• The tables may contain duplicate records.
• The company_code is string, so the sorting should not be numeric. For example, if the company_codes are C_1, C_2, and C_10, then the ascending company_codes will be C_1, C_10, and C_2.

- 각 회사코드, 창립자이름, 리드매니저 총수, 시니어 매니저 총수, 매니저 총수, 직원 총수를 프린트하는 쿼리 작성
- 회사코드로 내림차순 정렬 
- 레코드는 중복될 수 있음
- 회사코드는 숫자가 아닌 문자  
   C_1,C_10, C_2 순서로 정렬되어야 함 

[풀이]

SELECT co.company_code
      ,co.founder
      ,COUNT(DISTINCT lm.lead_manager_code)
      ,COUNT(DISTINCT sm.senior_manager_code)
      ,COUNT(DISTINCT ma.manager_code)
      ,COUNT(DISTINCT em.employee_code)
FROM company co
     LEFT JOIN lead_manager lm ON lm.company_code = co.company_code
     LEFT JOIN senior_manager sm ON sm.company_code = lm.company_code
     LEFT JOIN manager ma ON ma.company_code = sm.company_code
     LEFT JOIN employee em ON em.company_code = ma.company_code
GROUP BY co.company_code ,co.founder
ORDER BY co.company_code

Weather Observation Station 20 문제 바로가기

[문제]

A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to 4 decimal places.

 => LAT_N의 중앙값을 구하고 소수점 4자리까지 나타태는 쿼리 작성

[풀이]

MYSQL

- LAT_N 보다 큰 데이터 수와 LAT_N보다 작은 데이터 수가 같을 때 => 중앙값  

SELECT ROUND(LAT_N,4) 
FROM station s
WHERE (SELECT COUNT(*)
       FROM STATION 
       WHERE LAT_N < s.LAT_N)
     =(SELECT COUNT(*) 
       FROM STATION 
       WHERE LAT_N > s.LAT_N);

- 윈도우함수 PERCENT_RANK 사용 

PERCENT_RANK 함수

: 맨 위 행을 0, 맨 아래(끝) 행을 1로 놓고 

각 행에 대해 현재 행이 위치하는 백분위 순위값 반환.

반환값의 범위는 0보다 크거나 같고 1보다 작거나 같다. (0 <= x <=1)

반환 값 = (rank - 1) / (total row - 1)

SELECT ROUND(LAT_N,4)
FROM (SELECT LAT_N, 
            PERCENT_RANK() OVER (ORDER BY LAT_N ASC) per
    FROM STATION) s2
WHERE per = 0.5

Oracle

- Median 함수  사용

SELECT ROUND(MEDIAN(Lat_N), 4)
FROM Station;

Weather Observation Station 19 문제 바로가기 

[문제]

*유클리디안 거리(유클리디안 유사성)

: n차원에서 두 점 사이의 거리를 구하는 공식 

2차원 상에서 두 점 P(x1, y1), P(x2,y2)이 있는 경우 

두 점간의 거리는 √{(x2-x1)² +(y2-y1)²} 

문제정리 
2차원에서 P(a,c) , P(b,d) 두 점간의 유클리디안 거리를 구하는 쿼리 입력, 
결과값은 소수점 4자리까지 반올림

a = LAT_N 최소값
b = LAT_N 최대값
c = LONG_W 최소값
d = LONG_W 최대값

[풀이]

이 문제에서 결과를 구하는 유클리디안 공식은 √{(b-a)² +(d-c)²} 

1.  WITH로 각 좌표의 a,b,c,d값을 넣음 

  (간단하기 때문에 WITH 안쓰고 바로 계산식에 넣어도 되지만 계산식이 더 깔끔하고 알아보기 쉽게 쓰고 싶어서 사용함 )

2. 제곱함수 POWER 사용

 : POWER(제곱할 숫자, 제곱횟수) 

   ex) POWER(2, 2) = 4

3. 제곱근 함수 SQRT 사용 

 : SQRT(제곱근할 숫자)

   ex) SQRT(16) = 4

4. ROUND 함수로 반올림 

WITH p AS (
SELECT MIN(lat_n) a
     , MAX(lat_n) b
     , MIN(long_w) c
     , MAX(long_w) d
FROM station)

SELECT ROUND(SQRT(POWER(b-a, 2)+POWER(d-c,2)),4)
FROM p

OUTPUT

184.1616

문제: 질의 결과처럼 데이터를 추출 할 수 있도록 sql 작성 

 

[SQL 코드]

SELECT  f1.emp_no AS 직원번호
      ,f1.fam_nm AS 쌍둥이1
      ,f2.fam_nm AS 쌍둥이2
FROM 
  (SELECT *
   FROM fam_c
   WHERE birth_ymd BETWEEN '20120101' AND '20151231'
   AND rel_type_cd = 'A27') f1
   ,fam_c f2
WHERE f1.emp_no = f2.emp_no
      AND f1.birth_ymd = f2.birth_ymd
      AND f1.fam_nm <> f2.fam_nm;

2) . 20210321 기준으로 재직중인 임직원이며. 생년월일이 20120101 에서 20151231인 자녀를 가진 임직원을 구하는 sql을 작성하세요.

[주어진 질의결과]

[SQL 코드]

SELECT  e.emp_no AS 직원번호
      ,f.fam_nm AS 자녀성명
      ,f.birth_ymd AS 자녀생년월일 
FROM emp_c e
   INNER JOIN fam_c f on e.emp_no = f.emp_no
WHERE e.retire_ymd >= '20210321' 
     AND f.birth_ymd BETWEEN '20120101' AND '20151231'
     AND f.rel_type_cd = 'A27';

[제가 한 질의결과]

3) . 20210321 기준으로 재직중인 임직원이며. 사내부부인 임직원의 직원번호를 구하는 sql을 작성하세요.

[주어진 질의결과]

[SQL 코드]

SELECT e.emp_no AS EMP_NO
FROM emp_c e
    INNER JOIN fam_rel_c fam_r ON e.emp_no = fam_r.emp_no
WHERE e.retire_ymd >= '20210321' 
     AND fam_r.end_ymd >= '20210321' 
     AND fam_r.rel_type_cd IN ('A02', 'A18')

[제가 한 질의결과]

3-2) . 현재 날짜 기준 재직중인 “이씨 성＂을 가진 직원의 자녀명수가 몇 명인지 리스트를 만드세요.

[SQL 코드]

SUBSTR  사용

SELECT emp.emp_no AS 직원번호 
     , emp.emp_nm AS 직원명
     , COUNT(*) AS 자녀명수
FROM emp_c emp
    INNER JOIN fam_c fam ON emp.emp_no = fam.emp_no
WHERE SUBSTR(emp.emp_nm, 1, 1) = '이'
      AND SYSDATE BETWEEN HIRE_YMD AND RETIRE_YMD
      AND rel_type_cd = 'A27'
GROUP BY emp.emp_no, emp.emp_nm ;

LIKE 사용 

SELECT emp.emp_no AS 직원번호 
     , emp.emp_nm AS 직원명
     , COUNT(*) AS 자녀명수
FROM emp_c emp
    INNER JOIN fam_c fam ON emp.emp_no = fam.emp_no
WHERE emp.emp_nm LIKE '이%'
      AND SYSDATE BETWEEN HIRE_YMD AND RETIRE_YMD
      AND rel_type_cd = 'A27'
GROUP BY emp.emp_no, emp.emp_nm ;

[제가 한 질의결과]

3-3) . 자녀 명수가 2명 이상인 직원의 리스트를 구하세요.  (직원번호 / 성명 / 자녀명수 ) 

[SQL 코드]

SELECT e.emp_no AS 직원번호
      ,e.emp_nm AS 직원성명
      ,COUNT(*) AS 자녀명수
FROM emp_c e
     INNER JOIN fam_c f ON e.emp_no = f.emp_no 
WHERE f.rel_type_cd = 'A27'
GROUP BY e.emp_no, e.emp_nm
HAVING  COUNT(*) >= 2;

[제가 한 질의결과]

3-4) . 현재 기준 조직의 부서별 직원이 몇 명인지 구하는 sql을 작성하세요. (조직코드, 조직명, 직원수)

[SQL 코드]

SELECT org.org_cd AS 조직코드 
     , org.org_nm AS 조직명
     , COUNT(emp.emp_no) AS 직원수
FROM org_c org
     INNER JOIN emp_c emp ON emp.org_cd = org.org_cd
WHERE SYSDATE BETWEEN STA_YMD AND END_YMD
GROUP BY org.org_cd, org.org_nm;

[제가 한 질의결과]