여우비오냐의 IT 기술
SQL 코딩테스트 정리 본문
https://school.programmers.co.kr/learn/courses/30/lessons/131120 (기본 SELECT)
SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH) = 3 AND TLNO IS NOT NULL AND GENDER='W'
ORDER BY MEMBER_ID
2.
https://school.programmers.co.kr/learn/courses/30/lessons/132201?language=mysql (COALESCE, 이중 정렬)
SELECT PT_NAME, PT_NO, GEND_CD, AGE,
COALESCE(TLNO, 'NONE') AS TLNO
FROM PATIENT
WHERE GEND_CD = 'W' AND AGE <= 12
ORDER BY AGE DESC, PT_NAME ASC;
3.
https://school.programmers.co.kr/learn/courses/30/lessons/59405?language=mysql (LIMIT)
SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME LIMIT 1;
4.
https://school.programmers.co.kr/learn/courses/30/lessons/131535?language=mysql (COUNT)
SELECT COUNT(*)
FROM USER_INFO
WHERE YEAR(JOINED) = 2021 AND AGE <= 29 AND AGE>=20
5.
https://school.programmers.co.kr/learn/courses/30/lessons/131536?language=mysql (GROUP BY)
SELECT USER_ID , PRODUCT_ID
FROM ONLINE_SALE
GROUP BY 1, 2
HAVING COUNT(*) >= 2
ORDER BY USER_ID, PRODUCT_ID DESC
6.
https://school.programmers.co.kr/learn/courses/30/lessons/131537?language=mysql(UNION ALL)
SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") SALES_DATE,
PRODUCT_ID,
USER_ID,
SALES_AMOUNT
FROM ONLINE_SALE
WHERE sales_date >= '2022-03-01' and sales_date < '2022-04-01'
UNION ALL
SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") SALES_DATE,
PRODUCT_ID,
NULL AS USER_ID,
SALES_AMOUNT
FROM OFFLINE_SALE
WHERE sales_date >= '2022-03-01' and sales_date < '2022-04-01'
ORDER BY SALES_DATE , PRODUCT_ID , USER_ID
7.
(https://school.programmers.co.kr/learn/courses/30/lessons/59408?language=mysql) (DISTINCT)
SELECT COUNT(DISTINCT NAME) AS COUNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL;
8.
(https://school.programmers.co.kr/learn/courses/30/lessons/59040?language=mysql) (group by)
SELECT ANIMAL_TYPE, COUNT(*) AS count
from ANIMAL_INS
group by ANIMAL_TYPE
order by ANIMAL_TYPE
9.
(https://school.programmers.co.kr/learn/courses/30/lessons/59041?language=mysql) (group by,having, count)
SELECT NAME, COUNT(*) AS count
FROM ANIMAL_INS
group by NAME
having COUNT(NAME) >= 2
order by name
10.
(https://school.programmers.co.kr/learn/courses/30/lessons/59412?language=mysql) (GROUP BY, between)
SELECT HOUR(DATETIME) as HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) between 9 and 19
GROUP BY HOUR(DATETIME)
order by HOUR(DATETIME)
11.
(https://school.programmers.co.kr/learn/courses/30/lessons/131530?language=mysql) (div, GROUP BY)
SELECT (PRICE div 10000) * 10000 as PRICE_GROUP, COUNT(*) as PRODUCTS
from product
group by (PRICE div 10000)
order by PRICE_GROUP
12.
(https://school.programmers.co.kr/learn/courses/30/lessons/164668?language=mysql) (group by, having)
SELECT u.USER_ID, u.NICKNAME, sum(b.PRICE) as TOTAL_SALES
FROM USED_GOODS_USER as u
left join USED_GOODS_BOARD as b
on b.WRITER_ID = u.USER_ID
where b.STATUS = "DONE"
group by u.USER_ID
having sum(b.PRICE) >=700000
order by 3
13.
(https://school.programmers.co.kr/learn/courses/30/lessons/131114?language=mysql) (IFNULL, LIKE)
SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, IFNULL(FREEZER_YN, 'N') as FREEZER_YN
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '경기도%'
ORDER BY WAREHOUSE_ID
14.
(https://school.programmers.co.kr/learn/courses/30/lessons/59044?language=mysql) (left join, limit)
SELECT i.NAME, i.DATETIME
FROM ANIMAL_INS as i
left join ANIMAL_OUTS as o
on i.ANIMAL_ID = o.ANIMAL_ID
where o.ANIMAL_ID is NULL
order by i.DATETIME
limit 3
15.
(https://school.programmers.co.kr/learn/courses/30/lessons/59043?language=mysql) (left join)
SELECT i.ANIMAL_ID, i.NAME
FROM ANIMAL_INS AS i
inner join ANIMAL_OUTS AS o
on i.ANIMAL_ID = o.ANIMAL_ID
WHERE o.DATETIME < i.DATETIME
order by i.DATETIME
16.
(https://school.programmers.co.kr/learn/courses/30/lessons/131117?language=mysql) (INNER JOIN)
SELECT o.PRODUCT_ID, p.PRODUCT_NAME, (p.PRICE*sum(o.AMOUNT)) as TOTAL_SALES
FROM FOOD_ORDER as o
INNER JOIN FOOD_PRODUCT as p
on o.PRODUCT_ID = p.PRODUCT_ID
WHERE o.PRODUCE_DATE like '2022-05%'
group by o.PRODUCT_ID
order by 3 DESC, 1
17.
(https://school.programmers.co.kr/learn/courses/30/lessons/144854?language=mysql) (INNER JOIN)
SELECT BOOK_ID, AUTHOR_NAME, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') as PUBLISHED_DATE
FROM BOOK as b
inner join AUTHOR as a
on b.AUTHOR_ID = a.AUTHOR_ID
WHERE CATEGORY = '경제'
order by 3
18.
(https://school.programmers.co.kr/learn/courses/30/lessons/131124?language=mysql) (GROUP BY, INNER JOIN)
SELECT p.MEMBER_NAME, r.REVIEW_TEXT, DATE_FORMAT(r.REVIEW_DATE, '%Y-%m-%d') as REVIEW_DATE
FROM REST_REVIEW as R
INNER JOIN MEMBER_PROFILE as P
on R.MEMBER_ID = P.MEMBER_ID
WHERE R.MEMBER_ID = (
SELECT MEMBER_ID
FROM REST_REVIEW
GROUP BY MEMBER_ID
order by COUNT(*) DESC limit 1
)
order by 3, 2
19.
(https://school.programmers.co.kr/learn/courses/30/lessons/133027?language=mysql) (GROUP BY, LEFT JOIN)
select h.FLAVOR
from (
select FLAVOR, sum(TOTAL_ORDER) as TOTAL_ORDER
from FIRST_HALF
group by FLAVOR
) as h
left join (
select FLAVOR, sum(TOTAL_ORDER) as TOTAL_ORDER
from JULY
group by FLAVOR
) as j
on j.FLAVOR = h.FLAVOR
order by (h.TOTAL_ORDER + j.TOTAL_ORDER) DESC
limit 3
20.
(https://school.programmers.co.kr/learn/courses/30/lessons/59045?language=mysql) (left join)
SELECT o.ANIMAL_ID, o.ANIMAL_TYPE, o.NAME
from ANIMAL_OUTS as o
left join ANIMAL_INS as i
on i.ANIMAL_ID=o.ANIMAL_ID
where i.SEX_UPON_INTAKE like 'Intact%' and (not o.SEX_UPON_OUTCOME like 'Intact%')
order by ANIMAL_ID
21.
(https://school.programmers.co.kr/learn/courses/30/lessons/131534?language=mysql) (GROUP BY, JOIN, COUNT, DISTINCT)
SELECT DATE_FORMAT(O.SALES_DATE, '%Y') AS YEAR,
DATE_FORMAT(O.SALES_DATE, '%m') AS MONTH,
COUNT(DISTINCT U.USER_ID) AS PUCHASED_USERS,
ROUND( COUNT(DISTINCT U.USER_ID) /
(SELECT COUNT(*)
FROM USER_INFO
WHERE joined LIKE '2021%')
,1)
AS PUCHASED_RATIO
FROM USER_INFO U
JOIN ONLINE_SALE O
ON U.USER_ID = O.USER_ID
WHERE U.JOINED LIKE '2021%'
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH
;
22.
(https://school.programmers.co.kr/learn/courses/30/lessons/131118?language=mysql)(ROUND, AVG, JOIN, GROUP)
SELECT REST_INFO.REST_ID, REST_NAME, FOOD_TYPE, FAVORITES, ADDRESS,
ROUND(AVG(REST_REVIEW.REVIEW_SCORE),2) as SCORE
FROM REST_INFO
JOIN REST_REVIEW ON REST_INFO.REST_ID = REST_REVIEW.REST_ID
GROUP BY REST_INFO.REST_ID
HAVING ADDRESS LIKE "서울%"
ORDER BY SCORE DESC, FAVORITES DESC