1. Level 1
가. SELECT
1) 오름차순 조회
•
ANIMAL_ID를 기준으로 오름차순으로 조회
→ 오름차순 정렬이 기본값이므로 ORDER BY 뒤에 ASC 값 생략 가능
→ ASC: Ascending
•
SELECT NAME, DATETIME FROM ANIMAL_INS ORDER BY ANIMAL_ID;
2) 내림차순 조회
•
ANIMAL_ID를 기준으로 내림차순으로 조회
•
DESC: Descending
•
SELECT NAME, DATETIME FROM ANIMAL_INS ORDER BY ANIMAL_ID DESC;
3) 조건 만족 조회
•
노령화 동물 조회
•
SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE INTAKE_CONDITION = 'Aged';
4) 조건 미만족 조회
•
노령화 해당 없는 동물 조회
•
SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE INTAKE_CONDITION != 'Aged';
5) 복수 조건으로 정렬 후 조회
•
특정 값을 정렬하려면 반드시 ORDER BY로 특정 열을 지정해야 함
→ 정렬의 기준이 되는 열이 기본값으로 지정되어있지 않음
•
NAME을 기준으로 내림차순으로 정렬하되, 같은 경우 DATETIME 기준으로 정렬함
•
SELECT ANIMAL_ID, NAME, DATETIME FROM ANIMAL_INS ORDER BY NAME ASC, DATETIME DESC;
6) 결과행 제한하여 조회
•
DATETIME이 가장 빠른 이름 조회(이름 1개)
•
LIMIT 명령은 표준 SQL X, MySQL, PostgreSQL에서만 지원
•
SELECT NAME FROM ANIMAL_INS ORDER BY DATETIME LIMIT 1;
참고) OFFSET 활용하여 시작 위치 변경 가능
•
SELECT NAME FROM ANIMAL_INS ORDER BY DATETIME LIMIT 1 OFFSET 3;
→ DATETIME이 4번째로 빠른 이름 조회(이름 1개 조회)
나. IS NULL
1) 'IS NULL' 조회
•
이름 없는 동물의 아이디
•
SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NULL;
•
NULL 값을 검색할 때, 기본 연산자 사용하지 않고 'IS NULL' 사용
2) 'IS NOT NULL' 조회
•
이름 있는 동물의 아이디
•
SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NOT NULL;
•
NULL이 아닌 값을 검색할 때, 'IS NOT NULL' 사용
3) NULL 값 치환
•
NULL 값을 다른 값으로 치환하여 조회
•
CASE 활용
→ 조회하려는 열 또는 속성 자리에 CASE문 삽입
→ SELECT ANIMAL_TYPE, CASE WHEN NAME IS NULL THEN 'No name' ELSE NAME END 'NAME', SEX_UPON_INTAKE FROM ANIMAL_INS;
•
COALESCE 활용
→ SELECT ANIMAL_TYPE, COALESCE(NAME, 'No name') 'Name', SEX_UPON_INTAKE FROM ANIMAL_INS;
다. 집계
1) 최소값
•
최초 DATATIME 1개 조회
•
SELECT MIN(DATETIME) 'DATETIME' FROM ANIMAL_INS;
or
•
SELECT DATETIME FROM ANIMAL_INS ORDER BY DATETIME ASC LIMIT 1;
2) 최대값
•
가장 최근의 DATATIME 1개 조회
•
SELECT MAX(DATETIME) 'DATETIME' FROM ANIMAL_INS;
or
•
SELECT DATETIME FROM ANIMAL_INS ORDER BY DATETIME DESC LIMIT 1;
3) 행의 개수
•
전체 행의 개수
•
SELECT count(*) FROM ANIMAL_INS;
4) 중복제거
•
Name에서 NULL이 아니면서 중복을 제거한 행의 개수를 구할 것
•
SELECT count(DISTINCT NAME) FROM ANIMAL_INS WHERE NAME IS NOT NULL;
2. Level 2
가. String, Data
1) 복수 조건으로 조회
•
동물 중 이름이 Lucy, Ella, Pickle, Rogan, Sabrina, Mitty인 동물의 아이디와 이름, 성별 및 중성화 여부를 조회할 것. 결과는 아이디 순으로 정렬되어 있을 것
•
OR 연산자 활용
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME = 'Lucy' OR NAME = 'Ella' OR NAME = 'Pickle'
OR NAME = 'Rogan' OR NAME = 'Sabrina' OR NAME = 'Mitty'
ORDER BY ANIMAL_ID;
SQL
복사
•
IN 활용
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY ANIMAL_ID;
SQL
복사
2) 일부 문자열이 포함된 결과 조회
•
이름에 "EL"이 들어가는 개의 아이디와 이름을 조회할 것. 이때 결과는 이름 순으로 정렬되며, 이름의 대소문자는 구분하지 않음.
•
Like + 중간 일치 활용
→ MySQL의 Like은 기본적을 대소문자 구분하지 않음
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE ANIMAL_TYPE = 'Dog'
AND NAME LIKE '%el%'
ORDER BY NAME;
SQL
복사
참고) 대소문자 구분 시, BINARY 적용할 것
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE ANIMAL_TYPE = 'Dog'
AND BINARY(NAME) LIKE '%el%'
ORDER BY NAME;
SQL
복사
3) 일부 문자열 검색 + 치환
•
CASE + 중간 일치 활용
→ OR 연산자 사용 시, 중복되더라도 조건 전체를 명시해야 함
ex) O, WHEN SEX_UPON_INTAKE LIKE '%Neutered%' OR SEX_UPON_INTAKE LIKE '%Spayed%'
X, WHEN SEX_UPON_INTAKE LIKE '%Neutered%' OR LIKE '%Spayed%
SELECT ANIMAL_ID, NAME,
CASE WHEN SEX_UPON_INTAKE LIKE '%Neutered%' OR SEX_UPON_INTAKE LIKE '%Spayed%'
THEN 'O'
ELSE 'X'
END '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;
SQL
복사
•
IF + 전방 일치 활용
SELECT ANIMAL_ID,
NAME,
IF (SEX_UPON_INTAKE LIKE 'Neutered%' or SEX_UPON_INTAKE LIKE'Spayed%', 'O', 'X')
AS '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;
SQL
복사
4) 형 변환(DATETIME → DATE)
•
각 동물의 아이디와 이름, 들어온 날짜를 조회하는 SQL문 작성
•
SUBSTRING 활용
SELECT ANIMAL_ID,
NAME,
SUBSTRING(DATETIME, 1, 10) AS 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;
SQL
복사
•
DATE_FORMAT 활용
SELECT ANIMAL_ID,
NAME,
DATE_FORMAT(DATETIME, '%Y-%m-%d') AS 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;
SQL
복사
나. Group By
내부처리 순서: WHERE → GROUP BY → HAVING - SELECT → ORDER BY
1) GROUP BY 개념
•
집계함수와 함께 사용되면 특정열의 요소에 대해 계산된 것을 조회 가능
•
조건으로 지정한 특정열의 행의 동일한 요소들을 그룹으로 묶어서 조회
•
Having 구에 명시되는 조건은 집계함수에 대한 조건을 추가하는 것
ex) COUNT > 3, HOUR BETWEEN 9 AND 19, SUM > 100 등
•
만약 GROUP BY에 두 개 이상의 컬럼이 추가된다면, 두 개의 컬럼의 조합으로 생겨날 수 있는 모든 그룹을 출력합니다. 예를 들어, 성별과 직업을 Group By에 전달한다면 각 성별로 존재하는 모든 직업에 대해 그룹을 생성합니다.
2) 개와 고양이의 수
•
동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문 작성
•
'ANIMAL_TYPE'에 Dog, Cat 외의 타입이 존재할 경우 WHERE 구 사용하여 제한
SELECT ANIMAL_TYPE,
count(ANIMAL_TYPE)
FROM ANIMAL_INS
WHERE ANIMAL_TYPE IN ('Dog', 'Cat')
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;
SQL
복사
3) 동명 동물 수 조회
•
HAVING: 집계 함수에 대한 조건구
→ 그룹화가 필요한 집계함수는 WHERE 구의 조건식으로 사용 불가
→ 그룹화 보다 WHERE 구 처리순서가 앞서기 때문
•
동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문 작성
SELECT NAME,
COUNT(NAME) AS COUNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT > 1
ORDER BY NAME;
SQL
복사
4) 6명 이상의 고객이 거주하는 도시와 해당 도시에 거주하는 고객의 수
•
문제: 고객 거주 도시 중 런던과 상파울로를 대상으로 6명 이상의 고객이 거주하는 도시와 해당 도시에 거주하는 고객의 수를 구할 것
•
결과
SELECT City,
COUNT(City) AS CITY_COUNT
FROM Customers
WHERE City IN ('London', 'São Paulo')
GROUP BY City
HAVING CITY_COUNT > 5
ORDER BY City;
SQL
복사
5) 입양 시각 조회
•
09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문 작성
•
그룹화에 대한 조건은 HAVING 구에서 처리함
→ WHERE 구는 GROUP BY에 앞서 처리되었기 때문에 그룹화에 대한 조건 추가할 수 없음
SELECT HOUR(DATETIME) AS HOUR,
COUNT(DATETIME)
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR BETWEEN 9 AND 19
ORDER BY HOUR;
SQL
복사
6) 중복 데이터만 조회
•
복수의 유저(30808, 30805)가 팔로워한 계정 중 중복된 팔로워(to_user_id)가 있는 데이터만 조회한다.
select * from relationships_userfollow
where from_user_id = 30808 or from_user_id = 30805
group by to_user_id
having count(to_user_id) > 1;
SQL
복사
7) 중복 제거 데이터 조회
•
복수의 유저(30808, 30805)가 팔로워한 계정 중 팔로워(to_user_id)에 대한 중복이 없는 데이터만 조회한다.
select * from relationships_userfollow
where from_user_id = 30808 or from_user_id = 30805
group by to_user_id;
SQL
복사
3. Level 3
가. Join
1) 없어진 기록 찾기
•
LEFT JOIN 활용
•
입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문 작성
SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS OUTS LEFT JOIN ANIMAL_INS INS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.ANIMAL_ID IS NULL
ORDER BY OUTS.ANIMAL_ID;
SQL
복사
2) 잘못된 정보 찾기
•
INNER JOIN 활용
•
보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문 작성
SELECT INS.ANIMAL_ID, INS.NAME
FROM ANIMAL_INS INS INNER JOIN ANIMAL_OUTS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.DATETIME > OUTS.DATETIME
ORDER BY INS.DATETIME;
SQL
복사
3) 주요 정보 찾기
•
LEFT JOIN 후 검색조건으로 IS NULL 활용
•
아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문 작성
SELECT INS.NAME, INS.DATETIME
FROM ANIMAL_INS INS LEFT JOIN ANIMAL_OUTS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE OUTS.DATETIME IS NULL
ORDER BY INS.DATETIME LIMIT 3;
SQL
복사
4) 두 테이블 간 날짜 차이
•
입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 SQL문 작성.
→ 이때 결과는 보호 기간이 긴 순으로 조회
•
DATEDIFF: 첫번째 인자에서 두번째 인자를 뺀 값을 반환
SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS OUTS INNER JOIN ANIMAL_INS INS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
ORDER BY DATEDIFF(OUTS.DATETIME, INS.DATETIME) DESC LIMIT 2;
SQL
복사
3) 두 테이블 조인해서 데이터 삭제
// 삭제
delete ogq_image_image_tag_set
from ogq_image_image_tag_set
inner join ogq_image_image on ogq_image_image_tag_set.image_id = ogq_image_image.id
where ogq_image_image.author_id = 1082785
SQL
복사
// 데이터가 너무 많을 경우, 5000개씩만 삭제
// MySQL 5.7용, 데이터 갱신 작업에 limit 허용 X
set @counter := 0;
delete ogq_image_image_tag_set
from ogq_image_image_tag_set
inner join ogq_image_image on ogq_image_image_tag_set.image_id = ogq_image_image.id
where ogq_image_image.author_id = 1082785
and (@counter := @counter + 1) <= 500;
// MySQL 8.0용
delete ogq_image_image_tag_set
from ogq_image_image_tag_set
inner join ogq_image_image on ogq_image_image_tag_set.image_id = ogq_image_image.id
where ogq_image_image.author_id = 1082785
order by ogq_image_image.id limit 500;
// 삭제 후 남은 개수 파악
select count(*) from ogq_image_image_tag_set
where image_id in (
select id from ogq_image_image
where author_id = 1082785
);
SQL
복사
나. 중첩 Join
1) 12월에 가입한 사람 중 업로드한 이미지의 개수
•
테이블 정보
image: id, name, author_id
image_author: id, name, user_id
user: id, date_joined
Plain Text
복사
•
쿼리
SELECT COUNT(i)
FROM image i INNER JOIN image_author a
ON i.`author_id` = a.id
INNER JOIN `user` u
ON a.`user_id` = u.id
WHERE DATE(u.date_joined) BETWEEN '2022-12-01' AND '2022-12-31'
SQL
복사
다. Group By + Join
1) 특정 기간에 좋아한 태그 TOP 10
•
Group By 명령이 포함된 질의에서 SELECT할 수 있는 대상은 Group By에 나열된 필드 및 해당 필드에 대한 집계 함수입니다.
•
SQL 문법 상 limit은 반드시 order by 뒤에 와야합니다.
SELECT tag.name, COUNT(tag.name) AS COUNT
FROM ogq_image_followedtag ftag INNER JOIN ogq_image_tag tag
ON ftag.tag_id = tag.id
WHERE DATE(ftag.regdate) BETWEEN '2001-10-01' AND '2022-12-30'
GROUP BY tag.name
ORDER BY COUNT DESC LIMIT 10;
SQL
복사
2) 집계 기준이 복잡한 경우
•
GROUP BY로 묶어야 하는 경우가 두 번으로 나눠진 경우를 조심해야 합니다.
•
예를 들어, 다음의 조건은 집계 기준을 나눌 수 있습니다. 특정 기간(3달 동안) 렌탈 횟수가 5회 이상인 차에 대해 매달 렌탈된 횟수를 출력하라. 앞의 조건을 보면 특정 기간의 렌탈 횟수와 월별 렌탈 횟수를 구분해야 합니다.
•
SELECT
MONTH(H.START_DATE) AS MONTH,
H.CAR_ID,
COUNT(*) AS RECORDS
FROM
CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H
JOIN
(
SELECT
CAR_ID
FROM
CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE
MONTH(START_DATE) BETWEEN 8 AND 10
GROUP BY
CAR_ID
HAVING
COUNT(*) >= 5
) AS J
ON
H.CAR_ID = J.CAR_ID
WHERE
MONTH(H.START_DATE) BETWEEN 8 AND 10
GROUP BY
MONTH(H.START_DATE), H.CAR_ID
HAVING
COUNT(*) > 0
ORDER BY
MONTH ASC, CAR_ID DESC;
SQL
복사
3) GROUP BY + DISTINCT
•
집계된 값에서 중복된 값에 대해 고려할 필요가 있습니다.
•
이하의 집계 대상이 GROUP BY로 그룹핑이 되지 않는 경우라면 중복이 발생할 수 있으므로 DISTINCT 구를 함께 적용해야 합니다.
SELECT COUNT(DISTINCT U.USER_ID) AS USERS, YEAR(O.SALES_DATE) AS YEAR, MONTH(O.SALES_DATE) AS MONTH, U.GENDER
FROM USER_INFO U JOIN ONLINE_SALE O
ON U.USER_ID = O.USER_ID
WHERE U.GENDER IS NOT NULL
GROUP BY YEAR, MONTH, U.GENDER
ORDER BY YEAR, MONTH, U.GENDER
SQL
복사
4. 기본조작
가. DCL
•
login
→ username: MJ
mysql -u MJ -p
Bash
복사
•
select database(or schema)
→ schema: pc_manager
use pc_manager;
SQL
복사
•
show tables in shema
show tables;
SQL
복사
•
describe tables in shcma
desc pc_list;
SQL
복사
•
mysql 종료
quit
SQL
복사
나. DDL & DML
•
Create
CREATE TABLE IF NOT EXISTS pc_list (
pc_id INT NOT NULL PRIMARY KEY,
user_id INT
);
SQL
복사
CREATE TABLE IF NOT EXISTS user_list(
user_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
start_time TIME NOT NULL,
end_time TIME,
pc_id INT NOT NULL, FOREIGN KEY (pc_id) REFERENCES pc_list(pc_id)
);
SQL
복사
•
Insert
INSERT INTO pc_list (pc_id) VALUES
(1),
(2),
(3),
(14),
(15),
(16);
SQL
복사
•
Select
// pc_list table 내 모든 데이터 선택
select * from pc_list;
SQL
복사
•
Insert, update
// user_list table에 4개의 열(user_id, start_time, end_time, pc_id)에
// (1, '09:10:05', NULL, 5) 데이터 추가
insert into user_list (user_id, start_time, end_time, pc_id) values (1, '09:10:05', NULL, 5);
// pc_list table에 pc_id = 5를 만족하는 user_id를 1로 변경함,
update pc_list set user_id = 1 where pc_id = 5;
update user_list set end_time = '09:30:00' where user_id = 1;
update pc_list set user_id = NULL where pc_id = 5;
SQL
복사
•
delete
// PC table에서 pc_id =15를 만족하는 행을 삭제함
DELETE FROM PC WHERE (pc_id = 15);
DELETE FROM PC WHERE (pc_id = 16);
SQL
복사
Reference
•
프로그래머스 SQL 연습문제