기본 조회/정렬/필터/집계 실습

1) 기본 조회(SELECT) 실습

1-1. 전체 조회 vs 필요한 컬럼만 조회

실무에서는 SELECT *는 디버깅 외엔 지양(네트워크/IO 비용 증가).

-- (나쁜 습관) 전체 컬럼
SELECT * FROM customers;

-- (권장) 필요한 컬럼만
SELECT customer_id, email, name, status, created_at
FROM customers;

실습 문제

1. customers에서 name, phone, status만 조회하라.

select name,phome,status from customers;

2. products에서 sku, name, price만 조회하라.

select sku,name,price from products;

---

1-2. 별칭(alias)과 계산 컬럼

SELECT
  product_id AS id,
  name AS product_name,
  price,
  stock,
  price * stock AS stock_value
FROM products;

실습 문제

1. products에서 price * 1.1을 vat_included_price로 계산해 조회하라.

select
	price * 1.1 as vat_included_price
from products;

---

1-3. DISTINCT (중복 제거)

SELECT DISTINCT category
FROM products;

실습 문제

1. orders에서 status 종류를 중복 없이 조회하라.

select distinct status from orders;

---

2) 정렬(ORDER BY) 실습

2-1. 최신 가입자/최신 상품

SELECT customer_id, name, created_at
FROM customers
ORDER BY created_at DESC;

SELECT product_id, name, created_at
FROM products
ORDER BY created_at DESC
LIMIT 5; // 상위 5개 제한

실습 문제

1. products를 price 내림차순으로 정렬하고 상위 10개만 조회하라.

select * from products
order by price desc
limit 10; // 상위 10개 제한

2-2. 값이 같을 때 2차 정렬

SELECT product_id, category, name, price
FROM products
ORDER BY category ASC, price DESC, name ASC;

2. products를 category 오름차순, price 내림차순으로 다중 정렬하라.

select name,category,price from products
order by category asc, price desc;

---

3) 필터(WHERE) 실습

3-1. 비교 연산자(=, !=, >, >=, <, <=)

SELECT product_id, name, price
FROM products
WHERE price >= 50000;

실습 문제

1. stock이 100 이상인 상품만 조회하라.

SELECT product_id, name, stock
FROM products
WHERE stock >= 100;

2. price가 10000 미만인 상품만 조회하라.

SELECT product_id, name, price
FROM products
WHERE price < 10000;

---

3-2. 범위(BETWEEN) / 목록(IN)

-- 1만원~5만원 사이
SELECT product_id, name, price
FROM products
WHERE price BETWEEN 10000 AND 50000; // 10000이상 50000이하

-- 특정 카테고리만
SELECT product_id, name, category
FROM products
WHERE category IN ('DEVICE','STORAGE');

실습 문제

1. orders에서 ordered_at이 최근 7일 이내인 것만 조회하라.

-- 힌트
-- WHERE ordered_at >= NOW() - INTERVAL 7 DAY

select * from orders
where ordered_at >= now() - interval 7 day;

---

3-3. 패턴 검색(LIKE)

-- name에 'Cable'이 포함된 상품
SELECT product_id, name
FROM products
WHERE name LIKE '%Cable%';

VALUE에 % 가 들어간다면 \%로 검색하면된다.

예) 50%,60%를 검색 -> WHERE name LIKE '%0\%%'

실습 문제

1. email이 user1로 시작하는 고객을 조회하라.

   (예: user10@example.com, user11@example.com등)

SELECT * FROM customers
WHERE email LIKE 'user1%';

---

3-4. NULL 체크(IS NULL / IS NOT NULL)

현재 샘플은 phone이 모두 존재하지만, 실무 필수 문법이므로 형태만 익힌다.

SELECT customer_id, name, phone
FROM customers
WHERE phone IS NULL;

SELECT customer_id, name, phone
FROM customers
WHERE phone IS NOT NULL;

---

3-5. AND / OR / 괄호 우선순위

SELECT product_id, category, name, price
FROM products
WHERE (category = 'DEVICE' OR category = 'STORAGE')
  AND price >= 100000;

실습 문제

1. category가 ACCESSORY이고 price가 10000 이상 30000 이하인 상품을 조회하라.

SELECT product_id, category, name, price
FROM products
WHERE category = 'ACCESSORY' AND (price between 10000 and 30000);

---

4) 집계(AGGREGATION) 실습

4-1. 전체 집계(COUNT, MIN, MAX, AVG, SUM)

-- 상품 평균 가격
SELECT round(AVG(price),1) AS avg_price
FROM products;

-- 최고/최저 가격
SELECT MIN(price) AS min_price, MAX(price) AS max_price
FROM products;

-- 전체 재고 합
SELECT SUM(stock) AS total_stock
FROM products;

실습 문제

1. orders에서 총 주문 건수를 조회하라.

select count(*) as order_count from orders;

2. payments에서 결제 방법(method)별 건수를 세지 말고, 먼저 전체 결제 건수를 조회하라.

select count(*) as payments_cnt from payments;

---

4-2. GROUP BY (카테고리별 상품 수/평균가)

SELECT
  category,
  COUNT(*) AS product_count,
  AVG(price) AS avg_price
FROM products
GROUP BY category
ORDER BY product_count DESC;

실습 문제

1. category별 총 재고(stock) 합계를 구하라.

SELECT
  category,
  SUM(stock) AS total_stock,
FROM products
GROUP BY category;

2. orders에서 status별 주문 건수를 구하라.

SELECT
  status,
  count(*) AS order_count,
FROM orders
GROUP BY status;

---

4-3. HAVING (그룹 결과 필터)

WHERE는 “행(row) 필터”, HAVING은 “그룹 결과 필터”

-- 상품이 3개 이상인 카테고리만
SELECT category, COUNT(*) AS cnt
FROM products
GROUP BY category
HAVING COUNT(*) >= 3
ORDER BY cnt DESC;

실습 문제

1. orders에서 status별 건수를 구하되, 2건 이상인 status만 출력하라.

select status, count(*) as cnt from orders
group by status
having cnt >= 2;

---

5) 서비스 관점 집계

조회/필터/정렬/집계를 한 번에 사용

5-1. 최근 14일 주문 중 취소(CANCELLED) 제외하고 최신순 20건

SELECT order_id, customer_id, status, ordered_at, total_amount
FROM orders
WHERE ordered_at >= NOW() - INTERVAL 14 DAY
  AND status <> 'CANCELLED'
ORDER BY ordered_at DESC
LIMIT 20;

5-2. 고객별 주문 건수 TOP 5

SELECT customer_id, COUNT(*) AS order_cnt
FROM orders
GROUP BY customer_id
ORDER BY order_cnt DESC
LIMIT 5;

5-3. 결제수단(method)별 총 결제금액 합계

SELECT method, SUM(paid_amount) AS total_paid
FROM payments
GROUP BY method
ORDER BY total_paid DESC;

5-4. 주문 상태(status)별 총 매출(orders.total_amount) 합계

SELECT status, SUM(total_amount) AS total_sales
FROM orders
GROUP BY status
ORDER BY total_sales DESC;

5-5. 평균 주문금액(취소 제외)

SELECT AVG(total_amount) AS avg_order_amount
FROM orders
WHERE status <> 'CANCELLED';

5-6. “고액 주문” 기준: 주문금액 100,000 이상 주문 수

SELECT COUNT(*) AS high_value_orders
FROM orders
WHERE total_amount >= 100000;

---

7) 주의

• WHERE는 개별 행 필터, HAVING은 그룹 결과 필터
• ORDER BY는 최종 결과 정렬 (WHERE/GROUP BY 이후)
• 집계는 기본적으로 GROUP BY 대상 컬럼 + 집계 함수 조합으로만 선택 가능

---

연습 문제(MySQL)

A. 단일 쿼리 (10문제)

Q1. customers 테이블에서 ACTIVE 고객만 조회하고, created_at 최신순으로 10명만 출력하시오.

(출력: customer_id, email, name, created_at)

select customer_id, email, name ,created_at from customers
where status = "ACTIVE"
order by created_at desc
limit 10;

Q2. products에서 category='STORAGE' 인 상품 중 price >= 150000 인 상품을 가격 내림차순으로 조회하시오.

(출력: product_id, sku, name, price)

select product_id,sku,name,price from products
where category = 'STORAGE' and price >= 150000
order by price desc;

Q3. products에서 stock <= 30 인 “재고 부족” 상품을 stock 오름차순으로 조회하시오.

(출력: product_id, name, stock)

select product_id,name,stock from products
where stock<=30
order by stock asc;

Q4. orders에서 status='CANCELLED' 주문만 조회하시오. (출력: order_id, customer_id, ordered_at)

select order_id,customer_id,ordered_at from orders
where status='CANCELLED';

Q5. orders에서 최근 7일 이내 주문을 조회하시오. (출력: order_id, customer_id, status, ordered_at)

select order_id,customer_id,status,ordered_at from orders
where ordered_at >= now() - interval 7 day
order by ordered_at desc;

Q6. customers에서 email이 user% 패턴인 고객을 조회하시오. (출력: customer_id, email, name)

select customer_id, email, name from customers
where email like ('user%');

Q7. orders에서 total_amount가 100,000 이상인 주문을 total_amount 내림차순으로 조회하시오.

(출력: order_id, total_amount)

select order_id, total_amount from orders
where total_amount >=100000
order by total_amount desc;

Q8. products에서 name에 “Cable”이 포함된 상품을 조회하시오. (출력: product_id, name)

select product_id,name from products
where name like('%Cable%');

Q9. payments에서 결제수단별(method) 결제 건수를 조회하시오. (출력: method, cnt)

select method, count(*) as cnt from payments
group by method;

Q10. orders에서 status별 주문 건수를 조회하고, 건수 내림차순 정렬하시오. (출력: status, cnt)

select status, count(*) as cnt from orders
group by status
order by cnt desc;

B. 함수/집계/날짜 (10문제)

Q11. orders에서 주문일(ordered_at)을 날짜(YYYY-MM-DD)로만 보여 주문일별 주문 건수를 조회하시오.
(출력: order_date, cnt)

SELECT
  DATE(ordered_at) AS order_date,
  COUNT(*) AS cnt
FROM orders
GROUP BY DATE(ordered_at)
ORDER BY order_date;

Q12. payments에서 결제일(paid_at) 기준 월(YYYY-MM)별 결제 합계를 조회하시오.
(출력: pay_month, total_paid)

SELECT
  DATE_FORMAT(paid_at, '%Y-%m') AS pay_month,
  SUM(paid_amount) AS total_paid
FROM payments
GROUP BY DATE_FORMAT(paid_at, '%Y-%m')
ORDER BY pay_month;

Q13. customers에서 가입일(created_at) 기준 “며칠 전 가입했는지”를 day_diff로 출력하시오.
(출력: customer_id, name, day_diff)

SELECT
  customer_id,
  name,
  DATEDIFF(CURDATE(), DATE(created_at)) AS day_diff
FROM customers
ORDER BY day_diff desc;

Q14. products에서 카테고리별 평균가격(AVG)과 최고가(MAX), 최저가(MIN)를 조회하시오.
(출력: category, avg_price, max_price, min_price)

SELECT
  category,
  AVG(price) AS avg_price,
  MAX(price) AS max_price,
  MIN(price) AS min_price
FROM products
GROUP BY category;

Q15. orders에서 주문금액 구간을 분류해(CASE) 집계하시오.

• 0~49999: 'LOW'
• 50000~199999: 'MID'
• 200000 이상: 'HIGH'
  (출력: bucket, cnt)

SELECT
  CASE
    WHEN total_amount BETWEEN 0 AND 49999 THEN 'LOW'
    WHEN total_amount BETWEEN 50000 AND 199999 THEN 'MID'
    ELSE 'HIGH'
  END AS bucket,
  COUNT(*) AS cnt
FROM orders
GROUP BY bucket
ORDER BY bucket;

Q16. customers에서 phone이 NULL인 고객 수를 조회하시오.

SELECT
  COUNT(*) AS cnt
FROM customers
WHERE phone IS NULL;

Q17. products에서 sku가 'SKU-10%' 인 상품 수를 조회하시오.

SELECT
  COUNT(*) AS cnt
FROM products
WHERE sku LIKE 'SKU-10%';

Q18. orders에서 고객별 평균 주문금액을 구하되, 주문이 2건 이상인 고객만 출력하시오.
(출력: customer_id, avg_amount, order_cnt)

SELECT
  customer_id,
  AVG(total_amount) AS avg_amount,
  COUNT(*) AS order_cnt
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;

Q19. payments에서 결제수단별 평균 결제금액을 조회하시오. (출력: method, avg_paid)

SELECT
  method,
  AVG(paid_amount) AS avg_paid
FROM payments
GROUP BY method
ORDER BY method;

Q20. order_items에서 주문당 총 수량(quantity 합)을 구하고, 총 수량이 4 이상인 주문만 출력하시오.
(출력: order_id, total_qty)

SELECT
  order_id,
  SUM(quantity) AS total_qty
FROM order_items
GROUP BY order_id
HAVING SUM(quantity) >= 4
ORDER BY order_id;