My Engineer Study Story

기본적인 SQL 기본 문법 Remind 용!

1. NESTED queries

Query 안에 다른 queries 을 넣어 활용 가능

family member 중 가장 많은 책을  읽은 멤버 리턴

=>

SELECT *

FROM family_members

WHERE num_books_read = (SELECT MAX(num_books_read) FROM family_members);

2. NULL

favorite book이 없는 row 리턴

=> SELECT * FROM family_members WHERE favorite_book is NULL;

3. Date

1980년 9월 1일 이후에 태어난 row 리턴

=> SELECT * FROM celebs_born WHERE birthdate > '1980-09-01';

4. Inner joins (테이블 명시 꼭 하기!)

기존 테이블과 조인 테이블 모두 데이터가 존재해야 조회됨

각 캐릭터 이름을 해당 캐릭터를 연기하는 배우와 연결하기

=>

SELECT character.name, character_actor.actor_name
FROM character
INNER JOIN character_actor
ON character.id = character_actor.character_id

 ***Outer join  => 기존 테이블에만 데이터가 존재하면 조회됨

5. Multiple joins

두개의 조인을 사용하여 각 캐릭터 이름을 해당 캐릭터를 연기하는 배우와 쌍으로 연결하기

=>

SELECT character.name, actor.name

FROM character

INNER JOIN character_actor

ON character.id = character_actor.character_id

INNER JOIN actor

ON character_actor.actor_id = actor.id;

6. Joins with WHERE

Willow Rosenber 라는 이름이 없고 How I Met Your Mother 쇼에 없는 캐릭터 및 TV 프로그램 목록

=>

SELECT character.name, tv_show.name
FROM character
INNER JOIN character_tv_show
ON character.id = character_tv_show.character_id
INNER JOIN tv_show
ON tv_show.id = character_tv_show.tv_show_id
WHERE character.name != 'Willow Rosenberg' AND tv_show.name != "How I Met Your Mother";

7. LEFT Joins

원래 테이블의 내용은 보존하면서 조인테이블의 내용을 붙이고 싶을때

캐릭터 이름을 해당 캐릭터를 연기하는 배우와 일치시키는 쿼리

=>

SELECT character.name, actor.name
FROM character
LEFT JOIN character_actor
ON character.id = character_actor.character_id
LEFT JOIN actor
ON character_actor.actor_id = actor.id

8. Self Joins

직원의 이름과 상사의 이름을 반환하는 쿼리

=>

SELECT e.name AS employee_name, b.name AS boss_name
FROM employees AS e
INNER JOIN employees AS b
ON b.id = e.boss_id

9. LIKE

이름이 Robot 뒤에 2000년에서 2099년 사이의 연도를 반환하는 쿼리

=>

SELECT *
FROM robots
WHERE name LIKE "%Robot 20__";

10. CASE

사람의 경우 "talk", 개의 경우 "bark", 고양이의 경우 "meow"를 반환하는 sound 라는 열을 반환하는 쿼리

=>

SELECT *,
CASE WHEN species = 'human' THEN 'talk' 
WHEN species = 'dog' THEN 'bark'
WHEN species = 'cat' THEN 'meow'
END AS sound
FROM friends_of_pickles;

11. SUBSTR

NY에 있는 로봇을 모두 반환하는 쿼리

=>

SELECT *
FROM robots
WHERE SUBSTR(location, -2) == 'NY';

12. COALESCE

열 목록을 가져와서 null이 아닌 첫번째 열의 값을 반환해줌

열 목록 중 tank, gun, sword 순으로 우선시 하여 weapon 열에 반환하는 쿼리

=>

SELECT name, COALESCE(tank,gun,sword) AS weapon
FROM fighters;

기본적인 SQL 기본 문법 Remind 용!

1. WHERE

  1) WHERE ... Equals

          Family_members table에서 species가 dog인 row 리턴 하는 방법

     => SELECT * FROM family_members WHERE species = 'dog'; 

  2) WHERE ... Greater than

         Family_members에서 num_books_read가 190보다 큰 row 리턴하는 방법

    => SELECT * FROM family_members WHERE num_books_read > 190;

2. AND

  friends of pickles table에서 species가 dog이고 height가 45 밑인 row 리턴

  => SELECT * FROM friends_of_pickles WHERE species = 'dog' AND height_cm < 45;

3. OR

  friends of pickles 에서 dog 이거나 height가 밑인 row 리턴

  => SELECT * FROM friends_of_pickles WHERE species = 'dog' OR height_cm < 45;  

4. IN

OR 연산자 보다 실행 속도 빠름

IN 연산자에 다른 SELECT 문 넣기 가능 

동적인 WHERE 절을 만들 때 크게 활용!

cats나 dogs가 포함 되있지 않은 row 리턴하는 방법

=> SELECT * FROM friends_of_pickles WHERE species NOT IN ('cat', 'dog');

5. DISTINCT

복수 열에서도 사용가능

height가 50보다 큰 distinct species 리턴 하는 방법

=> SELECT DISTINCT species FROM friends_of_pickles WHERE height_cm > 50;

6. ORDER BY

height_cm을 기준으로 내림 차순하기

=> SELECT * FROM friends_of_pickles ORDER BY height_cm DESC;

7. LIMIT * of returned rows

height가 가장 높은 row 리턴

=> SELECT * FROM friends_of_pickles ORDER BY height_cm DESC LIMIT 1;

8. COUNT 

특정 조건의 row 줄 수 세기

species가 dog인 수 세기

=> SELECT COUNT(*) FROM friends_of_pickles WHERE species = 'dog';

9. SUM

읽은 책 총 합 구하기

=> SELECT SUM(num_books_read) FROM family_members;

10. AVG

읽은 책 평균 구하기

=> SELECT AVG(num_books_read) FROM family_members;

11. MAX and MIN

가장 많은 책을 읽은 row 구하기

=> SELECT MAX(num_books_read) FROM family_members;

12. GROUP BY

각 species에서 가장 높은 height 구하기

=> SELECT MAX(height_cm), species FROM friends_of_pickles GROUP BY species;

출처)

https://www.sqlteaching.com/?ref=blog.selectfromuser.com

- WITH 절을 이용하여 가상 테이블 만들어 주기

WITH 테이블명 AS

(

          SELECT 쿼리

          UNION ALL

          SELECT 쿼리

)

=> WITH절로 만들어진 데이터베이스는 저장되는 테이블이 아닌 임시테이블이다.

     생성 이후 쿼리문에서 계속 사용 가능하다.

문제

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

나의 정답

SELECT B.n , IFNULL(A.COUNT,0) AS COUNT
	FROM (
		SELECT  
			HOUR(DATETIME) AS HOUR,
			COUNT(DATETIME) AS COUNT
		FROM ANIMAL_OUTS
		GROUP BY HOUR
	) A
	RIGHT JOIN(
    	WITH RECURSIVE cte AS (
          SELECT 0 AS n
          UNION ALL
          SELECT n + 1 FROM cte WHERE n < 23
		)
		SELECT n FROM cte
	) B ON A.HOUR = B.n ;

먼저 이 문제 DB에서는 7시부터 19시 까지의 시간대별 데이터만 있어서 이외의 시간의 데이터를 별도로 생성해줘야했다.

그렇기 때문에 WITH 절을 사용하여 해당 시간대까지의 칼럼을 생성해주었다. 그 후 기존의 있는 테이블과 RIGHT JOIN을 하여 해당 시간대 별의 입양 건수를 조회하였다. RIGHT JOIN을 하는 이유는 생성한 CTE 테이블의 값은 모두 유지되어야 하기때문에 RIGHT JOIN을 하였다.

- OUTER JOIN 을 이용하여 두 테이블 조인하기

SELECT 칼럼명

FROM 첫번째 테이블

<LEFT | RIGHT | FULL > OUTER JOIN 두번째 테이블

ON <JOIN CONDITION>

WHERE 검색조건

OUTER JOIN은 첫번째 테이블 내용의 누락 없이 검색이 가능하며 두가지 테이블의 내용을 한번에 가지고 올 수 있습니다. 

LEFT 사용시 => 첫번째 테이블의 내용은 두번째 테이블과 연계되는 내용이 없더라도 모두 검색되어야 한다는 뜻

RIGHT 사용시 => 두번째 테이블의 내용은 모두 검색되어야 한다는 뜻

FULL 사용시 => 모든 테이블의 내용이 모두 검색되어야 한다는 뜻

문제

천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.

나의 정답

SELECT *
FROM ANIMAL_OUTS A
LEFT OUTER JOIN ANIMAL_INS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID IS NULL

해당 문제에서는 ANIMAL_INS에는 없는 데 ANIMAL_OUTS에 있는 데이터를 찾아 조회하는 문제였다.

그래서 첫번째 테이블을 ANIMAL_OUTS로 하여 LEFT 조인 하여 모든 내용이 조회되게 하였다. 

그렇게 조인한 테이블에서 ANIMAL_INS의 ANIMAL_ID가 NULL인 값만 조회하여 유실된 데이터를 조회하게 하였다.

참고 문제 <프로그래머스 SQL>

https://school.programmers.co.kr/learn/courses/30/lessons/59042

문제 

PRODUCT 테이블과 OFFLINE_SALE 테이블에서 상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요. 결과는 매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.

나의 정답

#상품 코드별 매출액 (판매가 * 판매량) 의 합계

SELECT A.PRODUCT_CODE, SUM(A.PRICE * B.SALES_AMOUNT) AS SALES
FROM PRODUCT A
JOIN OFFLINE_SALE B
ON A.PRODUCT_ID = B.PRODUCT_ID
GROUP BY A.PRODUCT_ID
ORDER BY SALES DESC, A.PRODUCT_CODE ASC

해당 문제 같은 경우 상품 코드 별 매출 총합을 구해야하는 문제였다. 기본적인 합계구하는 문제였기에 간단하게 기록해보고 싶어서 써봤다. 이번 문제에서는 먼저 PRODUCT 테이블 과 OFFLINE_SALE 테이블을 JOIN 한 후 PRODUCT_ID에 따라 그룹별로 나누어 주었다. 그 후 SUM 함수를 이용해 그룹 별 판매가 * 판매량을 구해 조회하는 문제 였다.

이때 알아야 할 것은 SUM 함수는 그룹 별로 적용 된다는 것이 중요한 점 인 것 같다.

문제 출처 <프로그래머스 SQL>

https://school.programmers.co.kr/learn/courses/30/lessons/131533

- COUNT를 이용한 동명 동물 수 찾기 문제

문제

동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요.

나의 정답

SELECT A.NAME, A.COUNT
FROM (
SELECT NAME,COUNT (NAME) AS COUNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
ORDER BY NAME) A
WHERE A.COUNT > 1

이 문제 같은 경우 먼저 서브 쿼리를 이용하여 NAME 별 COUNT를 하여 NAME 별 총 수를 구해주었다. 이때 NAME이 NULL 값은 제외해 주었다. 그 이후 메인 쿼리에서 COUNT가 1 보다 큰 수만 출력해주어 동물 이름이 두번 이상쓰인 이름만 조회하게 해주었다.

문제

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

나의 정답

SELECT HOUR(DATETIME) AS HOUR, COUNT(DATETIME) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR >= 9 AND HOUR < 20
ORDER BY HOUR ASC

이 문제같은 경우 시간대 별로 분류하여 각 시간대별 입양 건수를 구하는 문제였다. 해당 문제에서는 GROUP BY HAVING 구문또한 같이 사용해주었는데 이 구문은 시간대별로 구분한 후 HAVING 절에서 9시부터 19시까지만 조회할 수 있게 해주었다. 또한 이 구문에서는 원래 DB에서는 DATETIME TYPE을 HOUR(DATETIME)을 사용하여 HOUR만 추출하여 쉽게 쓸 수 있게 해주었다.

문제

PATIENT 테이블에서 12세 이하인 여자환자의 환자이름, 환자번호, 성별코드, 나이, 전화번호를 조회하는 SQL문을 작성해주세요. 이때 전화번호가 없는 경우, 'NONE'으로 출력시켜 주시고 결과는 나이를 기준으로 내림차순 정렬하고, 나이 같다면 환자이름을 기준으로 오름차순 정렬해주세요.

예시

- IFNULL을 이용하여 NULL값 대체해주는 방법

SELECT IFNULL(칼럼명(다른 표현식), "대체값") FROM 테이블

나의 정답

SELECT PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO, "NONE") AS TLNO
FROM PATIENT
WHERE AGE <= 12 AND GEND_CD = "W"
ORDER BY AGE DESC, PT_NAME ASC

- IF 문을 이용하여 NULL 값 대체해주는 방법

SELECT IF(칼럼명 IS NULL, "대체값"(NULL 일 경우), "대체값2"(NULL이 아닐경우)) FROM 테이블

나의 정답

SELECT PT_NAME, PT_NO, GEND_CD, AGE, IF (TLNO IS NULL, "NONE", TLNO) AS TLNO
FROM PATIENT
WHERE AGE <= 12 AND GEND_CD = "W"
ORDER BY AGE DESC, PT_NAME ASC

- CASE WHEN 구문을 이용하는 방법

SELECT 

CASE WEHN 칼럼명 IS NULL THEN "대체값"

WHEN 조건명 THEN "대체값2"

ELSE "대체값3"

END AS 별칭

FROM 테이블

나의 정답

SELECT PT_NAME, PT_NO, GEND_CD, AGE, 
CASE WHEN TLNO IS NULL THEN "NONE"
ELSE TLNO 
END AS TLNO
FROM PATIENT
WHERE AGE <= 12 AND GEND_CD = "W"
ORDER BY AGE DESC, PT_NAME ASC

또 다른 알아야 할것

- NULLIF 구문을 이용하는 방법

NULLIF(expr1, expr2)

=> expr1값과 expr2값을 비교해서 true가 아니면 expr1을 return, true 이면 NULL을 return해준다.

예시

SELECT NULLIF(1,1)
--> NULL

SELECT NULLIF(1,2)
--> 1

- LIKE 를 이용한 특정 문자열이 들어간 구문 출력하는 방법

다음은 식품공장의 정보를 담은 FOOD_FACTORY 테이블입니다. FOOD_FACTORY 테이블은 다음과 같으며 FACTORY_ID, FACTORY_NAME, ADDRESS, TLNO는 각각 공장 ID, 공장 이름, 주소, 전화번호를 의미합니다.

문제

FOOD_FACTORY 테이블에서 강원도에 위치한 식품공장의 공장 ID, 공장 이름, 주소를 조회하는 SQL문을 작성해주세요. 이때 결과는 공장 ID를 기준으로 오름차순 정렬해주세요.

나의 정답

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE "%강원도%"
ORDER BY FACTORY_ID ASC;

FOOD_FACTORY DB에서 ADDRESS에 강원도가 포함되있다면 해당 FACTORY ID, NAME, ADDRESS를 출력해줄 수 있다.

알아야 할것

1. --A로 시작하는 문자를 찾기--

SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE 'A%'

2. --A로 끝나는 문자 찾기--

SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '%A'

3. --A를 포함하는 문자 찾기--

SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '%A%'

4. --A로 시작하는 두글자 문자 찾기--

SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE 'A__'

5. --첫번째 문자가 'A''가 아닌 모든 문자열 찾기--

SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '[^A]'

6. --첫번째 문자가 'A'또는'B'또는'C'인 문자열 찾기--

SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '[ABC]'

SELECT 컬럼명 FROM 테이블 WHERE 컬럼명 LIKE '[A-C]'

- 복수개의 특정 문자를 찾아야 할 경우 REGEXP를 이용

문제

CAR_RENTAL_COMPANY_CAR 테이블에서 '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함된 자동차가 자동차 종류 별로 몇 대인지 출력하는 SQL문을 작성해주세요. 이때 자동차 수에 대한 컬럼명은 CARS로 지정하고, 결과는 자동차 종류를 기준으로 오름차순 정렬해주세요.

나의 정답

SELECT A.CAR_TYPE, COUNT(A.CAR_TYPE) AS CARS
FROM (
SELECT CAR_TYPE
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS REGEXP "통풍시트|가죽시트|열선시트") A
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASC

이번 문제같은 경우 서브쿼리를 사용하였는데 먼저 서브쿼리에서 REGEXP 구문을 사용하여 통풍시트, 가죽시트, 열선시트가 포함되있는 CAR TYPE을 구해주었다. 그 다음 그룹 별 COUNT를 하여 CAR TYPE 마다 해당 옵션들이 포함된 총 숫자를 구해주었다.