[SQL] 4. 고급 SQL 명령어

🖥️ 시작하며

🔍 Timestamps and Extract

• TIME : 시간만 포함

• DATE : 날짜만 포함

• TIMESTAMP : 시간과 날짜 포함

• TIMESTAMPZ : 시간, 날짜, 타임존 포함

-- 모든 시스템 설정을 보여줍니다.
SHOW ALL;

-- 현재 설정된 시간대를 보여줍니다.
SHOW TIMEZONE;

-- 현재 날짜와 시간을 타임스탬프 형식으로 반환합니다.
SELECT NOW();
-- 예시 출력: 2024-10-13 14:30:00.123456+09

-- 현재 날짜와 시간을 문자열 형식으로 반환합니다.
SELECT TIMEOFDAY();
-- 예시 출력: Sun Oct 13 14:30:00.123456 2024 KST

-- 현재 시간을 반환합니다 (날짜 제외).
SELECT CURRENT_TIME;
-- 예시 출력: 14:30:00.123456+09

-- 주어진 날짜에서 연도를 추출합니다.
SELECT EXTRACT(YEAR FROM TIMESTAMP '2024-10-13 14:30:00');
-- 예시 출력: 2024

-- 두 날짜 사이의 간격을 계산합니다.
SELECT AGE(TIMESTAMP '2024-10-13', TIMESTAMP '1990-01-01');
-- 예시 출력: 34 years 9 mons 12 days

-- 날짜를 지정된 형식의 문자열로 변환합니다.
SELECT TO_CHAR(TIMESTAMP '2024-10-13', 'mm-dd-yyyy');
-- 예시 출력: 10-13-2024

-- 주문 날짜에서 연도를 추출합니다.
SELECT order_id, EXTRACT(YEAR FROM order_date) AS order_year
FROM orders
LIMIT 5;
-- 예시 출력:
-- order_id | order_year
-- 1        | 2024
-- 2        | 2023
-- 3        | 2024
-- 4        | 2024
-- 5        | 2023

-- 주문 날짜와 현재 날짜 사이의 간격을 계산합니다.
SELECT order_id, AGE(CURRENT_DATE, order_date) AS order_age
FROM orders
LIMIT 5;
-- 예시 출력:
-- order_id | order_age
-- 1        | 2 years 3 mons 5 days
-- 2        | 1 year 6 mons 10 days
-- 3        | 9 mons 15 days
-- 4        | 2 mons 20 days
-- 5        | 1 year 1 mon 25 days

-- 주문 날짜를 지정된 형식의 문자열로 변환합니다.
SELECT order_id, TO_CHAR(order_date, 'mm-dd-yyyy') AS formatted_date
FROM orders
LIMIT 5;
-- 예시 출력:
-- order_id | formatted_date
-- 1        | 07-08-2022
-- 2        | 04-03-2023
-- 3        | 01-28-2024
-- 4        | 08-23-2024
-- 5        | 09-18-2023

SELECT EXTRACT(YEAR FROM payment_date)
FROM payment

📌 연습문제

SELECT DISTINCT(TO_CHAR(payment_date,'MONTH'))
FROM payment

SELECT COUNT(*)
FROM payment
WHERE TO_CHAR(payment_date, 'DY') = 'MON'

SELECT COUNT(*)
FROM payment
WHERE EXTRACT(DOW FROM payment_date) = 1

🔍 서브 쿼리

SELECT student, grade
FROM test_scores
WHERE grade > (SELECT AVG(grade) FROM test_scores)

SELECT title, rental_rate
FROM film
WHERE rental_rate > (SELECT AVG(rental_rate) FROM film)

-- 2005년 5월 29일부터 30일 사이에 반납된 영화의 ID와 제목을 선택
SELECT 
    film_id, 
    title
FROM 
    film
WHERE 
    film_id IN (
        -- 지정된 날짜 범위 내에 반납된 영화의 ID를 서브쿼리로 선택
        SELECT 
            inventory.film_id 
        FROM 
            rental
            INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
        WHERE 
            return_date BETWEEN '2005-05-29' AND '2005-05-30'
    )
-- 결과를 film_id 순으로 정렬 (선택사항)
ORDER BY 
    film_id;

SELECT first_name, last_name
FROM customer AS c
WHERE EXISTS
(SELECT * FROM payment as p
	WHERE p.customer_id = c.customer_id
	AND amount > 11)

SELECT first_name, last_name
FROM customer
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM payment
    WHERE amount > 11
)

🔍 Self JOIN

SELECT tableA.col, tableB.col
FROM table AS tableA
JOIN table AS tableB ON
tableA.some_col = tableB.other_col

SELECT emp.name, report.name AS rep
FROM employees AS emp
JOIN employees AS report ON
emp.emp_id = report.report_id

SELECT f1.title, f2.title, f1.length
FROM film as f1
INNER JOIN film as f2
ON f1.title != f2.title and f1.length = f2.length