TIL_20240901
- 비트연산자!
[Lv.4] 저자별 카테고리별 매출액 집계하기
2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬해주세요.
WITH TOTAL_SALES AS
(
SELECT b.CATEGORY
, b.AUTHOR_ID
, SUM(bs.SALES * b.PRICE) AS TOTAL_SALES
FROM BOOK b RIGHT JOIN BOOK_SALES bs
ON b.BOOK_ID = bs.BOOK_ID
WHERE DATE_FORMAT(bs.SALES_DATE, '%Y-%m') = '2022-01'
GROUP BY b.AUTHOR_ID, b.CATEGORY
)
SELECT ts.AUTHOR_ID
, a.AUTHOR_NAME
, ts.CATEGORY
, ts.TOTAL_SALES
FROM TOTAL_SALES ts LEFT JOIN AUTHOR a
ON ts.AUTHOR_ID = a.AUTHOR_ID
ORDER BY ts.AUTHOR_ID, ts.CATEGORY desc
SELECT A.AUTHOR_ID, AUTHOR_NAME, CATEGORY, SUM((SALES * PRICE)) AS TOTAL_SALES
FROM BOOK_SALES S
JOIN BOOK B ON S.BOOK_ID = B.BOOK_ID
JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE YEAR(S.SALES_DATE) = 2022 AND MONTH(S.SALES_DATE) = 1
GROUP BY CATEGORY, AUTHOR_ID
ORDER BY A.AUTHOR_ID, CATEGORY DESC
[Lv.4] 식품분류별 가장 비싼 식품의 정보 조회하기
다음은 식품의 정보를 담은 FOOD_PRODUCT 테이블입니다. FOOD_PRODUCT 테이블은 다음과 같으며 PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE는 식품 ID, 식품 이름, 식품코드, 식품분류, 식품 가격을 의미합니다.
SELECT CATEGORY
, PRICE AS MAX_PRICE
, PRODUCT_NAME
FROM (
SELECT CATEGORY
, PRICE
, PRODUCT_NAME
, ROW_NUMBER() OVER (PARTITION BY CATEGORY ORDER BY PRICE DESC) AS RANKING
FROM FOOD_PRODUCT
WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
) AS r
WHERE RANKING = 1
ORDER BY MAX_PRICE DESC
SELECT CATEGORY
, PRICE AS MAX_PRICE
, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN
(SELECT CATEGORY, MAX(PRICE)
FROM FOOD_PRODUCT
GROUP BY CATEGORY
HAVING CATEGORY IN ('국', '김치', '식용유', '과자'))
)
ORDER BY MAX_PRICE DESC
;
[Lv.4] 년, 월, 성별 별 상품 구매 회원 수 구하기
다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블과 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블 입니다.USER_INFO 테이블은 아래와 같은 구조로 되어있으며 USER_ID, GENDER, AGE, JOINED는 각각 회원 ID, 성별, 나이, 가입일을 나타냅니다.
# GENDER가 NULL인 ROW는 포함하지 않기 위해 INNER JOIN
SELECT YEAR(os.SALES_DATE) AS YEAR
, MONTH(os.SALES_DATE) AS MONTH
, ui.GENDER AS GENDER
, COUNT(DISTINCT os.USER_ID) AS USERS
FROM ONLINE_SALE os INNER JOIN USER_INFO ui
ON os.USER_ID = ui.USER_ID
AND ui.GENDER IS NOT NULL
GROUP BY YEAR(os.SALES_DATE), MONTH(os.SALES_DATE), GENDER
ORDER BY YEAR, MONTH, GENDER
[Lv.4] 언어별 개발자 분류하기
다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블과 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블 입니다.USER_INFO 테이블은 아래와 같은 구조로 되어있으며 USER_ID, GENDER, AGE, JOINED는 각각 회원 ID, 성별, 나이, 가입일을 나타냅니다.
비트연산자
| 비트 연산자 |
설명 |
| & |
(AND) 대응되는 비트가 모두 1이면 1을 반환
b'1000' & b'1111' --- b'1000' |
| | |
(OR) 대응되는 비트 중 하나라도 1이면 1을 반환
b'1000' | b'1111' --- b'1111' |
| ^ |
(XOR) 대응되는 비트가 서로 다르면 1을 반환
b'1000' ^ b'1111' --- b'0111' |
| ~ |
(NOT) 비트가 1이면 0, 0이면 1로 반전시킴
~b'1000'--- b'0111' |
| << |
(Left shift) 지정한 수만큼 왼쪽으로 이동시킴
b'1100' << 1 --- b'0011' |
| >> |
(Right shift) 지정한 수만큼 오른쪽으로 이동시킴
b'1100' >> 2 --- b'0011' |
SELECT
(CASE
WHEN (SKILL_CODE & (SELECT SUM(CODE) FROM SKILLCODES WHERE CATEGORY = 'Front End'))
AND (SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME='Python')) > 0 THEN 'A'
WHEN (SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME='C#')) > 0 THEN 'B'
WHEN (SKILL_CODE & (SELECT SUM(CODE) FROM SKILLCODES WHERE CATEGORY = 'Front End')) > 0 THEN 'C'
ELSE NULL
END) AS GRADE, ID, EMAIL
FROM DEVELOPERS
GROUP BY GRADE, ID, EMAIL
HAVING GRADE IS NOT NULL
ORDER BY GRADE, ID
[Lv.4] 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기
HR_DEPARTMENT, HR_EMPLOYEES, HR_GRADE 테이블을 이용해 사원별 성과금 정보를 조회하려합니다. 평가 점수별 등급과 등급에 따른 성과금 정보가 아래와 같을 때, 사번, 성명, 평가 등급, 성과금을 조회하는 SQL문을 작성해주세요.
평가등급의 컬럼명은 GRADE로, 성과금의 컬럼명은 BONUS로 해주세요.
결과는 사번 기준으로 오름차순 정렬해주세요.
SELECT e.EMP_NO, e.EMP_NAME,
(CASE
WHEN AVG(SCORE) >= 96 THEN 'S'
WHEN AVG(SCORE) >= 90 THEN 'A'
WHEN AVG(SCORE) >= 80 THEN 'B'
ELSE 'C' END) AS GRADE,
(CASE
WHEN AVG(SCORE) >= 96 THEN E.SAL*0.2
WHEN AVG(SCORE) >= 90 THEN E.SAL*0.15
WHEN AVG(SCORE) >= 80 THEN E.SAL*0.1
ELSE 0 END) AS BONUS
FROM HR_EMPLOYEES e
INNER JOIN HR_GRADE g ON e.EMP_NO = g.EMP_NO
GROUP BY e.EMP_NO
ORDER BY 1;