Tag: sql

SELECT CONCAT('/home/grep/src/', b.board_id, '/', f.file_id, f.file_name, f.file_ext) as file_path
FROM used_goods_board AS b
    JOIN
    used_goods_file AS f
    ON b.board_id = f.board_id
WHERE b.views = (SELECT MAX(views) FROM used_goods_board)
ORDER BY f.file_id DESC;

문제에서 요구한 결과를 만들기 위해 쿼리에서 실제로 쓴 것들이다.

• 테이블 1회 JOIN 으로 두 테이블을 연결
• WHERE 로 조건에 맞는 행만 남김
• ORDER BY 로 정렬 (내림차순 포함)
• 서브쿼리를 사용
• 사용한 함수: CONCAT(), MAX()

SELECT route,
    CONCAT(ROUND(SUM(d_between_dist), 1), 'km') AS total_distance,
    CONCAT(ROUND(AVG(d_between_dist), 2), 'km') AS average_distance
FROM subway_distance
GROUP BY route
ORDER BY ROUND(SUM(d_between_dist), 1) DESC;

-- SELECT route,
--     CONCAT(ROUND(SUM(d_between_dist), 1), 'km') AS total_distance,
--     CONCAT(ROUND(AVG(d_between_dist), 2), 'km') AS average_distance
-- FROM subway_distance
-- GROUP BY route
-- ORDER BY total_distance DESC;

문제에서 요구한 결과를 만들기 위해 쿼리에서 실제로 쓴 것들이다.

WITH cars_with_fee AS (
    SELECT
        c.car_id,
        c.car_type,
        FLOOR(c.daily_fee * 30 * (100 - p.discount_rate) / 100) AS fee
    FROM
        car_rental_company_car AS c
    JOIN
        car_rental_company_discount_plan AS p ON c.car_type = p.car_type
    WHERE
        c.car_type IN ('세단', 'SUV')
        AND p.duration_type = '30일 이상'
)
SELECT
    car_id,
    car_type,
    fee
FROM
    cars_with_fee
WHERE
    car_id NOT IN (
        SELECT
            car_id
        FROM
            car_rental_company_rental_history
        WHERE
            end_date >= '2022-11-01' AND start_date <= '2022-11-30'
    )
    AND fee >= 500000 AND fee < 2000000
ORDER BY
    fee DESC,
    car_type ASC,
    car_id DESC;

SELECT pd.product_code, SUM(os.sales_amount) * pd.price as sales
FROM product as pd
    JOIN
    offline_sale as os
    ON pd.product_id = os.product_id
GROUP BY pd.product_id
ORDER BY sales DESC, pd.product_code ASC

문제에서 요구한 결과를 만들기 위해 쿼리에서 실제로 쓴 것들이다.

• 테이블 1회 JOIN 으로 두 테이블을 연결
• GROUP BY 로 묶어서 집계
• ORDER BY 로 정렬 (내림차순 포함)
• 사용한 함수: SUM()

-- 사번, 성명, 평가 등급, 성과금
-- 부서 정보
-- 사원 정보
-- 사원 평가 정보
WITH avg_emp AS (
    SELECT he.emp_no, he.emp_name, he.sal,
        CASE
            WHEN AVG(hg.score) >= 96 THEN 'S'
            WHEN AVG(hg.score) >= 90 THEN 'A'
            WHEN AVG(hg.score) >= 80 THEN 'B'
            ELSE 'C'
    END AS grade
    FROM hr_employees AS he
        LEFT JOIN
        hr_grade AS hg
        ON he.emp_no = hg.emp_no
    GROUP BY he.emp_no
)

SELECT emp_no, emp_name, grade,
    CASE
        WHEN grade = 'S' THEN sal * 0.2
        WHEN grade = 'A' THEN sal * 0.15
        WHEN grade = 'B' THEN sal * 0.1
        WHEN grade = 'C' THEN 0
        ELSE NULL
    END AS bonus
FROM avg_emp
ORDER BY emp_no

WITH quarter AS (
    SELECT 
        CASE
            WHEN(QUARTER(differentiation_date)) = 1 THEN '1Q'
            WHEN(QUARTER(differentiation_date)) = 2 THEN '2Q'
            WHEN(QUARTER(differentiation_date)) = 3 THEN '3Q'
            WHEN(QUARTER(differentiation_date)) = 4 THEN '4Q'
            ELSE NULL
    END AS quarter
    FROM ecoli_data
)

SELECT quarter, COUNT(*) AS ecoli_count
FROM quarter
GROUP BY quarter
ORDER BY quarter

문제에서 요구한 결과를 만들기 위해 쿼리에서 실제로 쓴 것들이다.

-- 코드를 입력하세요
WITH rm AS (SELECT ri.rest_id,
            ri.rest_name,
            ri.food_type,
            ri.favorites,
            ri.address,
            ROUND(AVG(review_score), 2) AS score
    FROM rest_info AS ri
        JOIN
        rest_review AS rr
        ON ri.rest_id = rr.rest_id
    WHERE ri.address LIKE "서울%"
    GROUP BY ri.rest_id, RI.REST_NAME, RI.FOOD_TYPE, RI.FAVORITES, RI.ADDRESS)

SELECT *
FROM rm
ORDER BY score DESC, favorites DESC;

문제에서 요구한 결과를 만들기 위해 쿼리에서 실제로 쓴 것들이다.

WITH rare_item AS (
    SELECT it.item_id
    FROM item_info AS ii
        JOIN
        item_tree AS it
        ON ii.item_id = it.parent_item_id
    WHERE rarity = "RARE"
)

# SELECT *
# FROM rare_item

SELECT ii.item_id, ii.item_name, ii.rarity
FROM rare_item AS ri
    JOIN
    item_info AS ii
    ON ri.item_id = ii.item_id
ORDER BY ii.item_id DESC

문제에서 요구한 결과를 만들기 위해 쿼리에서 실제로 쓴 것들이다.

• 테이블 2회 JOIN 으로 두 테이블을 연결
• WHERE 로 조건에 맞는 행만 남김
• ORDER BY 로 정렬 (내림차순 포함)
• 서브쿼리를 사용

WITH max_fish AS (
    SELECT fish_type, MAX(length) AS max_length
    FROM fish_info
    GROUP BY fish_type
)

SELECT fi.id, fni.fish_name, fi.length
FROM fish_info fi
JOIN 
    max_fish mf
    ON fi.fish_type = mf.fish_type AND fi.length = mf.max_length
JOIN 
    fish_name_info fni
    ON fi.fish_type = fni.fish_type
ORDER BY 
    fi.id;

문제에서 요구한 결과를 만들기 위해 쿼리에서 실제로 쓴 것들이다.

• 테이블 2회 JOIN 으로 두 테이블을 연결
• GROUP BY 로 묶어서 집계
• ORDER BY 로 정렬
• 서브쿼리를 사용
• 사용한 함수: MAX()