TIL 20241022

이번 주 목표  Weekly Plan

짧은 산책 프로젝트 기획 초안 작성

수업 집중

오늘 목표  Daily Plan

짧은 산책 프로젝트 기획 초안 작성

수업 집중

오늘 한 것  Done Today

짧은 산책 프로젝트 기획 초안 작성

수업 집중

1. count

SELECT 
count(*),
count(cust_id),
count(city),
count(region),
count(1) -- count(*) 와 같음 : 속도가 미세하게 빨라질 수 있을 것 같다 -선생님
FROM customer
WHERE city = "서울";

 

2. 집계 함수

-- 집계 함수
SELECT 
sum(mileage),
avg(mileage),
min(mileage),
max(mileage)
FROM customer
WHERE city IN ("서울", "부산");

 

3. GROUP BY

SELECT
city,
count(*) as "고객수",
avg(mileage) as "평균마일리지"
FROM customer
GROUP BY city;

 

4. GROUP BY 문제 

-- [문제 1] 고객 테이블에서 직급별, 도시별로 회원 명수 및 평균 마일리지와 총 마일리지를 구해라 

SELECT 
contact_position, 
city, 
count(*) AS "회원 명수", 
avg(mileage) AS "평균 마일리지", 
sum(mileage) AS "총 마일리지"
FROM customer
GROUP BY contact_position, city

 

5. HAVING

-- HAVING
SELECT
city,
count(1) AS "고객수",
avg(mileage) AS "평균 마일리지"
FROM customer
GROUP BY city
HAVING count(1) > 1;

 

6. HAVING 문제

-- [문제 2] 고객 테이블에서 도시별 총 마일리지를 구해 주세요
-- [문제 3] 총 마일리지가 500 이상인 도시만 출력해 보세요
SELECT city, sum(mileage) AS "총 마일리지"
FROM customer
WHERE 1 = 1
GROUP BY city
HAVING sum(mileage) >= 500;

 

7. HAVING 심화

SELECT 
city,
count(1) AS "고객수"
FROM customer
WHERE 1=1
AND contact_position = "대표"
GROUP BY city;

 

8. group_concat 

한 줄로 붙이기

SELECT group_concat(name, ", ") as names from employee;

 

9. subquery

SELECT group_concat(city, ", ") AS cities from (
    select DISTINCT city 
    from customer 
);

 

10. 종합 문제

-- [문제 4] 도시별로 회사이름을 콤마로 출력해보세요
-- 결과 예시
-- 서울 : ABC상사, DEF상사, IHK상사 ...ABORT
-- 부산 : DEF상사 ...
-- 대전 : EEE상사 ...

SELECT city, group_concat(company_name, ", ") AS companies from (
    select DISTINCT company_name, city
    from customer )
GROUP BY city;

 

 

11. count 함수 안에 DISTINCT 가능

SELECT count(city) as cities,
count(DISTINCT city) as unique_cities
FROM customer;

 

 

12. count와 strftime을 이용한 GROUP BY

SELECT
strftime("%Y", order_date) as order_year,
count(*) AS order_cnt
FROM orders
GROUP BY order_year;

 

 

13. GROUP BY 와 strftime 를 이용한 분류

-- [문제 6] 연도별, 월별, 주문 개수를 출력해 주세요.
-- 2024 01 99
-- 2024 02 1
-- 2024 03 12
SELECT
strftime("%Y",order_date) as order_year,
strftime("%m",order_date) as order_month,
count(*) AS order_cnt
FROM orders
GROUP BY order_year, order_month;

 

 

14. 분기  계산, 반기 계산

분기는 3으로 나누고

-- 연도별, 분기별, 주문 갯수 
SELECT
strftime("%Y",order_date) as order_year,
(strftime("%m",order_date) -1)/ 3 + 1 as quater, -- 분기
count(*) AS order_cnt
FROM orders
GROUP BY order_year, quater;

 

반기는 6로 나누면 됨

-- 연도별, 반기별, 주문 갯수 
SELECT
strftime("%Y",order_date) as order_year,
(strftime("%m",order_date) -1)/ 6 + 1 as half, -- 반기
count(*) AS order_cnt
FROM orders
GROUP BY order_year, half;

 

 

 

15. 문제

-- [문제 7] 고객 테이블에서 마일리지가 300 이상인 사람은 VVIP, 
-- 200 이상인 사람은 VIP
-- 100 이상이면 일반고객
-- 100 미만이면 고객 -- customer_type
-- 고객 별로 평균 마일리지와 고객수를 구해 보세요
select 
CASE 
WHEN mileage >= 300 THEN "VVIP"
WHEN mileage >= 200 THEN "VIP"
WHEN mileage >= 100 THEN "일반고객"
WHEN mileage < 100 THEN "고객"
END AS 고객분류,
count(*) AS total_cnt,
sum(mileage) AS total_mileage
from customer
GROUP BY 고객분류;

 

 

16. JOIN

SELECT d.dept_id, d.dept_name, e.name, e.position 
FROM department d
JOIN employee e 
ON d.dept_id = e.dept_id
WHERE e.name = "홍길동";

 

 

17. INNER JOIN 시 JOIN 키워드 생략

SELECT d.dept_id, d.dept_name, e.name, e.position 
FROM department d, employee e 
WHERE 1=1
AND d.dept_id = e.dept_id
AND e.name = "홍길동";

 

18. JOIN 문제

-- [문제 8] order_table, customer_table join
-- 고객사명, 담당자명, 주문번호, 주문일자를 출력해 보세요 

SELECT 
c.company_name, c.contact_name, o.order_no, o.order_date
FROM orders o, customer c
WHERE o.cust_id = c.cust_id;

 

 

19. JOIN 심화

select 
c.cust_id,
c.contact_name,
c.company_name,
count(o.order_id) as order_cnt
from customer c join orders o  on c.cust_id = o.cust_id
GROUP BY c.cust_id, c.contact_name, c.company_name;

 

 

20. JOIN GROUP BY

-- 부서별로 사원 수를 세어 보세요.
select d.dept_name,
count(*)
from department d, employee e
where 1=1
and d.dept_id = e.dept_id
GROUP BY d.dept_name

 

21. JOIN 테이블 3개 

-- 고객 테이블과 주문 테이블 그리고 주문 상세 테이블을 조인하여 
-- 고객 아이디, 담당자명, 회사명, 주문 총액을 출력하는데
-- 주문 총액이 많은 순으로 정렬해 보겠습니다

SELECT
c.cust_id, 
c.contact_name, 
c.company_name,
sum(od.unit_price * od.quantity) as total_order_amount
FROM customer c 
JOIN orders o ON c.cust_id = o.cust_id
JOIN order_details od ON o.order_id = od.order_id
GROUP BY c.cust_id,
c.contact_name,
c.company_name
ORDER BY total_order_amount DESC;

 

22. LEFT JOIN

SELECT 
e.emp_id, e.name, e.position, d.dept_name 
FROM employee e
LEFT JOIN department d ON e.dept_id = d.dept_id;

 

23. 같은 테이블 LEFT JOIN

-- emp_id, name, position, manager_name, manager_position 
SELECT e.emp_id, e.name, e.position,
m.name as manager_name , m.position as manager_position
FROM employee e left join employee m on e.manager_no = m.emp_id;

 

24. select subquery

-- 문제
-- 사원 테이블에서 사원 번호, 사원명, 매니저 번호, 매니저 이름을 가져오는데 
-- subquery를 이용해서 가져올 수 있도록 하시오

SELECT e.emp_id, e.name, (
    SELECT m.emp_no from employee as m
    where e.manager_no = m.emp_id
), (
    SELECT m.name from employee as m
    where e.manager_no = m.emp_id
) from employee e

 

25. insert

insert into department(dept_no, dept_name) VALUES ("A6", "운영부");

 

26. update

update employee set name = "홍영미",eng_name = "hong youngmi", position = "이사" where emp_id = 28;

27. delete

delete from product where product_id = 92;

 

28. create table

create table customer_order_summary (
    cust_id INTEGER PRIMARY KEY,
    company_name varchar(50),
    order_count INTEGER,
    last_order_date date 
);

29. Table에 통째로 넣기

INSERT INTO customer_order_summary  (cust_id, company_name, order_count, last_order_date)
select 
c.cust_id, 
c.company_name, 
count(*),
max(o.order_date)
from customer c join orders o on c.cust_id = o.cust_id
GROUP BY c.cust_id, c.company_name;

 

create table customer_order_summary2 AS
select 
c.cust_id, 
c.company_name, 
count(*),
max(o.order_date)
from customer c join orders o on c.cust_id = o.cust_id
GROUP BY c.cust_id, c.company_name;

30. View Table

-- 제품별로 주문 수량을 먼저 알고 싶어요 
CREATE view view_product_order_summary as
SELECT
p.product_id, p.product_name,
sum(od.quantity) as total_quantity,
sum(od.quantity * od.unit_price) as total_price
FROM product p join order_details od on p.product_id = od.product_id
GROUP BY p.product_id, p.product_name
ORDER BY total_quantity desc;

select * from view_product_order_summary;

 

 

 

---

정리  Memo

분기는 3으로 나누고

-- 연도별, 분기별, 주문 갯수 
SELECT
strftime("%Y",order_date) as order_year,
(strftime("%m",order_date) -1)/ 3 + 1 as quater, -- 분기
count(*) AS order_cnt
FROM orders
GROUP BY order_year, quater;

 

반기는 6로 나누면 됨

-- 연도별, 반기별, 주문 갯수 
SELECT
strftime("%Y",order_date) as order_year,
(strftime("%m",order_date) -1)/ 6 + 1 as half, -- 반기
count(*) AS order_cnt
FROM orders
GROUP BY order_year, half;

 

group_concat

한 줄로 붙이기

SELECT group_concat(name, ", ") as names from employee;

 

INNER JOIN 시 JOIN 키워드 생략

SELECT d.dept_id, d.dept_name, e.name, e.position 
FROM department d, employee e 
WHERE 1=1
AND d.dept_id = e.dept_id
AND e.name = "홍길동"

 

 

KPT

Keep

열심히 하는 것

Problem

게임을 많이 하는 것

Try

게임을 줄이고 집중하기

소감  Diary

TIL 을 오랜만에 적는다. 

보람차다.