웹개발자의 일상

알고리즘 문제 및 풀이사이트

https://www.hackerrank.com/onboarding?redirect=%2Fdashboard 

sql 문제풀이 

1. Japan Population: https://www.hackerrank.com/challenges/japan-population/problem
2. Weather Observation Station 2: https://www.hackerrank.com/challenges/weather-observation-station-2/problem
3. Weather Observation Station 18: https://www.hackerrank.com/challenges/weather-observation-station-18/problem
4. New Companies: https://www.hackerrank.com/challenges/the-company/problem

- 답안

(mysql로 풀이)

1. Japan Population

  - SUM

/*
Query the sum of the populations for all Japanese cities in CITY. 
The COUNTRYCODE for Japan is JPN.
*/

SELECT SUM(POPULATION) FROM CITY 
WHERE COUNTRYCODE ='JPN'

2. Weather Observation Station 2

  - 두자리 숫자까지 반올림

/*
The sum of all values in LAT_N rounded to a scale of  decimal places.
The sum of all values in LONG_W rounded to a scale of  decimal places.
*/
SELECT ROUND(SUM(LAT_N),2) AS lat, 
    ROUND(SUM(LONG_W),2) AS lon
FROM STATION

3. Weather Observation Station 18

 - ABS() 절대값

Consider p1(a,b)  and p2(c,d) to be two points on a 2D plane.

위와 같으므로 해당 쿼리에서 Manhattan distance 구하는 공식 |a-c| + |b-d|

/*
a: happens to equal the minimum value in Northern Latitude (LAT_N in STATION).
b: happens to equal the minimum value in Western Longitude (LONG_W in STATION).
c: happens to equal the maximum value in Northern Latitude (LAT_N in STATION).
d: happens to equal the maximum value in Western Longitude (LONG_W in STATION).

Query the Manhattan Distance between points p1 and p2 and round it to a scale of 4 decimal places.
*/

SELECT ROUND(ABS(ST.a-ST.c) + ABS(ST.b-ST.d),4)
FROM
(
SELECT MIN(LAT_N) AS a, MIN(LONG_W) AS b, MAX(LAT_N) AS c ,MAX(LONG_W) AS d  FROM  STATION 
) ST

4. New Companies

 - company_code, founder name 별 각 manager와 employee 수 구하기 , company_code순으로 정렬

Company 테이블에 company_code, founder name 존재

Employee Table (모든 컬럼값을 가지고있음)

  -> 주의사항 :  Employee에 없는경우도 생각해야함

                     현재상황은 Company 테이블과  Employee 테이블을 INNER JOIN 해도 결과가 나옴

output 결과

/*

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.
*/

SELECT  C.company_code ,
        C.founder ,
        COUNT(DISTINCT L.lead_manager_code ), 
        COUNT(DISTINCT S.senior_manager_code ), 
        COUNT(DISTINCT M.manager_code ),
        COUNT(DISTINCT E.employee_code )
FROM Company C
LEFT JOIN Lead_Manager L ON C.company_code = L.company_code
LEFT JOIN Senior_Manager S ON L.company_code = S.company_code
LEFT JOIN Manager M ON S.company_code = M.company_code
LEFT JOIN Employee E ON M.company_code = E.company_code
GROUP BY C.company_code, C.founder
ORDER BY C.company_code

알고리즘 문제 및 풀이사이트

https://leetcode.com/

sql 문제풀이 

1. Leetcode 595. Big Countries: https://leetcode.com/problems/big-countries/
2. Leetcode 620. Not Boring Movies: https://leetcode.com/problems/not-boring-movies/
3. Leetcode 182. Duplicate  Emails: https://leetcode.com/problems/duplicate-emails/
4. Leetcode 175. Combine Two Tables: https://leetcode.com/problems/combine-two-tables/

- 답안

Leetcode 595

SELECT name, population, area FROM World
WHERE area >= 3000000 
OR population >= 25000000

Leetcode 620

select id, movie, description, rating 
from Cinema
WHERE id%2 =1 #홀수 판별
AND description !='boring'
order by rating desc

Leetcode 182

 #중복이메일 찾기

SELECT email #,count(*) 
FROM Person
GROUP BY email
having count(*) > 1

Leetcode 175

SELECT P.firstName, P.lastName , A.city, A.state
FROM Person P
LEFT JOIN Address A
on P.personId = A.personId

인프런 인강 참고 

https://dev.mysql.com/doc/refman/8.0/en/numeric-functions.html

MSSQL홈페이지 numeric function 참고

소숫점 이하 숫자다루기

간단한 연산하기

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html

MSSQL홈페이지 string function 참고

[2022 시험일정]

[SQLD 시험범위 목차]

과목 1 데이터 모델링의 이해

  제 1장 데이터 모델링의 이해

    제1절 데이터 모델의 이해

    제2절 엔터티

    제3절 속성

    제4절 관계

    제5절 식별자

  제 2장 데이터 모델과 성능

    제1절 성능 데이터 모델링의 개요

    제2절 정규화와 성능

    제3절 반정규화와 성능

    제4절 대량 데이터에 따른 성능

    제5절 데이터베이스 구조와 성능

    제6절 분산 데이터베이스와 성능

과목 2 SQL 기본 및 활용

  제 1장 SQL 기본

    제 1절 관계형 데이터베이스 개요

    제 2절 DDL

    제 3절 DML

    제 4절 TCL

    제 5절 WHERE 절

    제 6절 함수(Function)

    제 7절 GROUP BY, Having 절

    제 8절 Order by 절

    제 9절 조인(Join)

  제 2장 SQL 활용

    제 1절 표준 조인

    제 2절 집합 연산자

    제 3절 계층형 질의와 셀프 조인

    제 4절 서브쿼리

    제 5절 그룹 함수

    제 6절 윈도우 함수

    제 7절 DCL

    제 8절 절차형 SQL

  제 3장 SQL 최적화 기본 원리

    제 1절 옵티마이저와 실행계획

    제 2절 인덱스 기본

    제 3절 조인 수행 원리

-- mysql IFNULL(값1,값2) : 값1이 null이면 값2출력, null이 아니면 값1
SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name') AS NAME ,SEX_UPON_INTAKE	
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC

-- oracle NVL(값1,값2) : 값1이 null이면 값2 출력, null이 아니면 값1
SELECT ANIMAL_TYPE, NVL(NAME, 'No name') AS NAME ,SEX_UPON_INTAKE	
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC

5. JOIN   완료

-없어진 기록찾기

-- LEFT OUTER JOIN으로 푸는 방법
SELECT A.ANIMAL_ID , A.NAME
FROM ANIMAL_OUTS A  -- 입양간 기록
LEFT OUTER JOIN ANIMAL_INS B -- 보호소 들어온 기록
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID IS NULL
ORDER BY A.ANIMAL_ID ASC

--  서브쿼리로 푸는 방법
SELECT  A.ANIMAL_ID , A.NAME FROM ANIMAL_OUTS A 
WHERE A.ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_INS)
ORDER BY A.ANIMAL_ID ASC

-- mysql
SELECT A.NAME, A.DATETIME FROM ANIMAL_INS A
LEFT OUTER JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE  B.ANIMAL_ID IS NULL
ORDER BY A.DATETIME ASC
limit 3

-- oracle
SELECT * FROM
(
SELECT A.NAME, A.DATETIME FROM ANIMAL_INS A
LEFT OUTER JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE  B.ANIMAL_ID IS NULL
ORDER BY A.DATETIME ASC
)
WHERE ROWNUM <= 3

-- 보호소 들어올때 중성화 x (SEX_UPON_INTAKE :"Intact" 표시)
-- 보호소 나갈때 중성화(SEX_UPON_OUTCOME : "Spayed" 또는 "Neutered" 표시)
    SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME
    FROM ANIMAL_INS A , ANIMAL_OUTS B
    WHERE A.ANIMAL_ID = B.ANIMAL_ID
    AND A.SEX_UPON_INTAKE like 'Intact%'   
    AND B.SEX_UPON_OUTCOME not like 'Intact%'

6. String, Date   완료

 - 이름에 el이 들어가는 동물 찾기

   오라클은 대소문자 구분하여 조회해야함 

   UPPER() : 대문자로 변환

   LOWER() : 소문자로 변환

SELECT ANIMAL_ID, NAME FROM ANIMAL_INS 
-- mysql
-- WHERE NAME LIKE '%el%' AND ANIMAL_TYPE='Dog'
--oracle 대소문자 구분! UPPER() : 대문자로 변환
WHERE UPPER(NAME) LIKE UPPER('%el%') AND ANIMAL_TYPE='Dog'
ORDER BY NAME ASC

- 이름에 el이 들어가는 동물 찾기

  CASE WHEN 조건 THEN 결과 ELSE 결과 END (오라클,mysql 동일)

--mysql
SELECT ANIMAL_ID, NAME, 
    CASE WHEN SEX_UPON_INTAKE like 'Neutered%' THEN 'O' 
    WHEN SEX_UPON_INTAKE like 'Spayed%' THEN 'O' 
    ELSE 'X'
    END
FROM ANIMAL_INS 
ORDER BY ANIMAL_ID

--oracle
SELECT ANIMAL_ID, NAME, 
    --REGEXP_LIKE 포함한대상
    CASE WHEN REGEXP_LIKE(SEX_UPON_INTAKE ,'Neutered') THEN 'O' 
    WHEN REGEXP_LIKE(SEX_UPON_INTAKE ,'Spayed') THEN 'O' 
    ELSE 'X'
    END
FROM ANIMAL_INS 
ORDER BY ANIMAL_ID

- 오랜기간 보호한 동물(2)

--mysql 두건조회
SELECT O.ANIMAL_ID, O.NAME 
FROM ANIMAL_OUTS O , ANIMAL_INS I
WHERE O.ANIMAL_ID = I.ANIMAL_ID
ORDER BY (O.DATETIME-I.DATETIME) DESC
limit 2

--oracle
-- 보호소들어온 동물 중 입양보낸정보 추출
-- O.DATETIME(입양일), I.DATETIME(보호시작일)
SELECT * FROM 
(
SELECT O.ANIMAL_ID, O.NAME 
FROM ANIMAL_OUTS O , ANIMAL_INS I
WHERE O.ANIMAL_ID = I.ANIMAL_ID
ORDER BY (O.DATETIME-I.DATETIME) DESC
)
WHERE ROWNUM <=2

- DATETIME에서 DATE로 형 변환

  mysql 날짜변환 : date_format(SYSDATE(),'%Y-%m-%d %T')

-- mysql
SELECT ANIMAL_ID, NAME, date_format(DATETIME,'%Y-%m-%d') FROM ANIMAL_INS 
ORDER BY ANIMAL_ID ASC

--oracle
SELECT ANIMAL_ID, NAME,TO_CHAR(DATETIME,'YYYY-MM-DD') FROM ANIMAL_INS 
ORDER BY ANIMAL_ID ASC

--mysql 1건 조회 
SELECT NAME FROM ANIMAL_INS 
ORDER BY DATETIME ASC 
LIMIT 1 

--오라클 1건 조회 
SELECT * FROM 
( SELECT NAME FROM ANIMAL_INS 
ORDER BY DATETIME ASC ) 
WHERE ROWNUM =1

SELECT NAME, COUNT(NAME) 
FROM ANIMAL_INS 
WHERE NAME IS NOT NULL 
GROUP BY NAME 
HAVING COUNT(NAME) >= 2 
ORDER BY NAME ASC

--mysql 
SELECT HOUR(DATETIME) , COUNT(DATETIME) 
FROM ANIMAL_OUTS 
WHERE HOUR(DATETIME) between '9' AND '20' 
GROUP BY HOUR(DATETIME) 
ORDER BY HOUR(DATETIME) ASC 

--오라클 SELECT TO_CHAR(DATETIME,'HH24') , COUNT(DATETIME) 
FROM ANIMAL_OUTS 
WHERE TO_CHAR(DATETIME,'HH24') between '09' AND '20' 
GROUP BY TO_CHAR(DATETIME,'HH24') 
ORDER BY TO_CHAR(DATETIME,'HH24') ASC

-- 변수 초기화 
SET @HOUR :=-1; 
SELECT (@HOUR := @HOUR+1) AS HOUR , 
	( SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @HOUR ) AS COUNT 
FROM ANIMAL_OUTS WHERE @HOUR <23

-- LEFT OUTER JOIN으로 A테이블 기준 조회하며, B.CNT가 NULL인 경우 0으로 출력 
SELECT A.NUM, NVL(B.CNT,0) 
FROM ( 
	--1~23 조회 
    SELECT LEVEL-1 AS NUM FROM DUAL CONNECT BY LEVEL <=24 
    ) A 
    LEFT OUTER JOIN 
    ( 
    --각 시간대별 입양건수 조회 
    SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR, COUNT(*) AS CNT 
    FROM ANIMAL_OUTS 
    GROUP BY TO_CHAR(DATETIME, 'HH24') 
    ) B 
 ON A.NUM = B.HOUR 
 ORDER BY A.NUM