[240909] SQL 걷기반 (~6강)

JunichiK·2024년 9월 9일

SQL 스터디

목록 보기
2/21

과제에서 필수 사용되는 문법 요약

  • 2) 이제 좀 벌었으니 flex 한 번 해볼까요?!

    1. 테이블 생성

      idproduct_namepricecategory
      1맥북 프로1200컴퓨터
      2다이슨 청소기300생활가전
      3갤럭시탭600컴퓨터
      4드롱기 커피머신200주방가전
      create table products(
	id INT Primary key,
	product_name varchar(30) Not Null,
	price INT,
	category varchar(30) Not Null,
);

INSERT into 'products' (`id`, `product_name`, `price`, `category`) VALUES ('1', '맥북 프로', '1200', '컴퓨터');
INSERT into 'products' (`id`, `product_name`, `price`, `category`) VALUES ('2', '다이슨 청소기', '300', '생활가전');
INSERT into 'products' (`id`, `product_name`, `price`, `category`) VALUES ('3', '갤럭시탭', '600', '컴퓨터');
INSERT into 'products' (`id`, `product_name`, `price`, `category`) VALUES ('4', '드롱기 커피머신', '200', '주방가전');

    2. products 테이블에서 제품 이름(product_name)과 가격(price)만을 선택하는 쿼리를 작성해주세요.

      select product_name, price from products

    3. products 테이블에서 제품 이름에 '프로'가 포함된 모든 제품을 선택하는 쿼리를 작성해주세요.

      select * from products where product_name like '%프로%';

    4. products 테이블에서 제품 이름이 '갤'로 시작하는 모든 제품을 선택하는 쿼리를 작성해주세요.

      select * from products where product_name like '갤%';

    5. products 테이블에서 모든 제품을 구매하기 위해 필요한 돈을 계산하는 쿼리를 작성해주세요.

      select SUM(price) from products;
  • 3) 상품 주문이 들어왔으니 주문을 처리해봅시다!

    1. 테이블 생성

      idcustomer_idproduct_idamountshipping_feeorder_date
      171913500002023-11-01
      213121100002023-11-02
      36541200002023-11-05
      4100832250002023-11-05
      535611150002023-11-09
      create table orders(
	id INT Primary key,
	customer_id INT,
	product_id INT,
	amount INT,
	shipping_fee INT,
	order_date date
	);

INSERT Into orders(id, customer_id, product_id, amount, shipping_fee, order_date) values (1, 719, 1, 3, 50000, '2023-11-01');
INSERT Into orders(id, customer_id, product_id, amount, shipping_fee, order_date) values (2, 131, 2, 1, 10000, '2023-11-02');
INSERT Into orders(id, customer_id, product_id, amount, shipping_fee, order_date) values (3, 65, 4, 1, 20000, '2023-11-05');
INSERT Into orders(id, customer_id, product_id, amount, shipping_fee, order_date) values (4, 1008, 3, 2, 25000, '2023-11-05');
INSERT Into orders(id, customer_id, product_id, amount, shipping_fee, order_date) values (5, 356, 1, 1, 15000, '2023-11-09');

    2. orders 테이블에서 주문 수량(amount)이 2개 이상인 주문을 진행한 소비자의 ID(customer_id)만 선택하는 쿼리를 작성해주세요!

      select customer_id from orders where amount >= 2;

    3. orders 테이블에서 2023년 11월 2일 이후에 주문된 주문 수량(amount)이 2개 이상인 주문을 선택하는 쿼리를 작성해주세요!

      select * from orders where order_date > '2023-11-02' and amount >= 2;

    4. orders 테이블에서 주문 수량이 3개 미만이면서 배송비(shipping_fee)가 15000원보다 비싼 주문을 선택하는 쿼리를 작성해주세요!

      select * from orders where amount < 3 and shipping_fee > 15000;

    5. orders 테이블에서 배송비가 높은 금액 순으로 정렬하는 쿼리를 작성해주세요!

      select * from orders order by shipping_fee desc;
  • 4) 이제 놀만큼 놀았으니 다시 공부해봅시다!

    1. 테이블 생성

      idnametrackgradeenrollment_year
      1르탄이Node.jsA2023
      2배캠이SpringB2022
      3구구이UnityC2021
      4이션이Node.jsB2022
      create table sparta_students(
	id INT Primary key,
	name varchar(30),
	track varchar(30),
	grade varchar(1),
	enrollment_year year
);

Insert into sparta_students(id, name, track, grade, enrollment_year) values (1, '르탄이', 'Node.js', 'A', 2023);
Insert into sparta_students(id, name, track, grade, enrollment_year) values (2, '배캠이', 'Spring', 'B', 2022);
Insert into sparta_students(id, name, track, grade, enrollment_year) values (3, '구구이', 'Unity', 'C', 2021);
Insert into sparta_students(id, name, track, grade, enrollment_year) values (4, '이션이', 'Node.js', 'B', 2022);

    2. sparta_students 테이블에서 모든 학생의 이름(name)과 트랙(track)을 선택하는 쿼리를 작성해주세요!

      select name, track from sparta_students;

    3. sparta_students 테이블에서 Unity 트랙 소속이 아닌 학생들을 선택하는 쿼리를 작성해주세요!

      select * from sparta_students where not track = 'Unity';

    4. sparta_students 테이블에서 입학년도(enrollment_year)가 2021년인 학생과 2023년인 학생을 선택하는 쿼리를 작성해주세요!

      select * from sparta_students where enrollment_year = 2021 or enrollment_year = 2023;

      답안지 : 

      select * from sparta_students where enrollment_year in (2021, 2023);

    5. sparta_students 테이블에서 Node.js 트랙 소속이고 학점이 ‘A’인 학생의 입학년도를 선택하는 쿼리를 작성해주세요!

      select enrollment_year from sparta_students where track = 'Node.js' and grade = 'A';
  • 5) 공부하다보니 팀 프로젝트 시간이 왔어요!

    1. 테이블 생성

      idnamestart_dateend_dateaws_cost
      1일조2023-01-012023-01-0730000
      2꿈꾸는이조2023-03-152023-03-2250000
      3보람삼조2023-11-202023-11-3080000
      4사조참치2022-07-012022-07-3075000
      create table team_projects(
	id INT Primary key,
	name varchar(5),
	start_date date,
	end_date date,
	aws_cost INT
);

Insert into team_projects(id, name, start_date, end_date, aws_cost) values (1, '일조', '2023-01-01', '2023-01-07', 30000);
Insert into team_projects(id, name, start_date, end_date, aws_cost) values (2, '꿈꾸는이조', '2023-03-15', '2023-03-22', 50000);
Insert into team_projects(id, name, start_date, end_date, aws_cost) values (3, '보람삼조', '2023-11-20', '2023-11-30', 80000);
Insert into team_projects(id, name, start_date, end_date, aws_cost) values (4, '사조참치', '2022-07-01', '2022-07-30', 75000);

    2. team_projects 테이블에서 AWS 예산(aws_cost)이 40000 이상 들어간 프로젝트들의 이름을 선택하는 쿼리를 작성해주세요!

      select name from team_projects where aws_cost >= 40000;

    3. team_projects 테이블에서 2022년에 시작된 프로젝트를 선택하는 쿼리를 작성해주세요! 단, start_date < ‘2023-01-01’ 조건을 사용하지 말고 쿼리를 작성해주세요!

      select * from team_projects where start_date like '2022%';

      ⇒ 답안지

      select * from team_projects where YEAR(start_date) = 2022;

    4. team_projects 테이블에서 현재 진행중인 프로젝트를 선택하는 쿼리를 작성해주세요. 단, 지금 시점의 날짜를 하드코딩해서 쿼리하지 말아주세요!

      select * from team_projects where end_date > DATE('now');

      ⇒ MySQL 기준

      SELECT * FROM team_projects WHERE CURDATE() BETWEEN start_date AND end_date;

    5. team_projects 테이블에서 각 프로젝트의 지속 기간을 일 수로 계산하는 쿼리를 작성해주세요!

      select JULIANDAY(end_date) - JULIANDAY(start_date) from team_projects;

      ⇒ MySQL 기준

      select name, DATEDIFF(end_date, start_date) as working_days from team_projects;
  • 6) 팀 프로젝트 열심히 했으니 다시 놀아볼까요?!

    1. 테이블 생성

      idnameregionratingjoin_date
      1르탄이한국13002019-06-15
      2배캠이미국15002020-09-01
      3구구이한국14002021-01-07
      4이션이미국13502019-11-15
      create table lol_users(
	id INT Primary key,
	name varchar(5),
	region varchar(5),
	rating INT,
	join_date date
);

Insert into lol_users(id, name, region, rating, join_date) values (1, '르탄이', '한국', 1300, '2019-06-15');
Insert into lol_users(id, name, region, rating, join_date) values (2, '배캠이', '미국', 1500, '2020-09-01');
Insert into lol_users(id, name, region, rating, join_date) values (3, '구구이', '한국', 1400, '2021-01-07');
Insert into lol_users(id, name, region, rating, join_date) values (4, '이션이', '미국', 1350, '2019-11-15');

    2. lol_users 테이블에서 각 유저의 레이팅(rating) 순위를 계산하는 쿼리를 작성해주세요! 전체 지역(region) 기준이고 순위는 레이팅이 높을수록 높아야해요. (e.g. rating 1400 유저의 순위 > rating 1350 유저의 순위)

      select *, RANK() over (order by rating desc) as rank from lol_users;

    3. lol_users 테이블에서 가장 늦게 게임을 시작한(join_date) 유저의 이름을 선택하는 쿼리를 작성해주세요

      select name from lol_users order by join_date desc limit 1;

    4. lol_users 테이블에서 지역별로 레이팅이 높은 순으로 유저들을 정렬해서 나열하는 쿼리를 작성해주세요!

      select * from lol_users order by region, rating desc;

    5. lol_users 테이블에서 지역별로 평균 레이팅을 계산하는 쿼리를 작성해주세요!

      select region, AVG(rating) from lol_users group by region;

오답노트

  • table 생성 시 컬럼 정의 후 ‘,’ 입력 필수

    idproduct_namepricecategory
    1맥북 프로1200컴퓨터
    2다이슨 청소기300생활가전
    3갤럭시탭600컴퓨터
    4드롱기 커피머신200주방가전
    create table products(
	id INT Primary key,
	product_name varchar(30) Not Null,
	price INT,
	category varchar(30) Not Null,
);

INSERT into 'products' (`id`, `product_name`, `price`, `category`) VALUES ('1', '맥북 프로', '1200', '컴퓨터');
INSERT into 'products' (`id`, `product_name`, `price`, `category`) VALUES ('2', '다이슨 청소기', '300', '생활가전');
INSERT into 'products' (`id`, `product_name`, `price`, `category`) VALUES ('3', '갤럭시탭', '600', '컴퓨터');
INSERT into 'products' (`id`, `product_name`, `price`, `category`) VALUES ('4', '드롱기 커피머신', '200', '주방가전');

  • 한 번 삽입된 테이블, 컬럼, 레코드를 수정 및 삭제하려면 Update, Delete 쿼리문 작성 필수

    • 사용법
      - Update 테이블 set 바꿀 컬럼 = 바꿀 값 where 고유키 = 고유값;
      - Delete from 테이블 where 고유키 = 고유값;

      create table products(
	id INT Primary key,
	product_name varchar(30) Not Null,
	price INT,
	category varchar(30) Not Null,
);

INSERT into 'products' (`id`, `product_name`, `price`, `category`) VALUES ('1', '맥북 프로', '1200', '컴퓨터');
INSERT into 'products' (`id`, `product_name`, `price`, `category`) VALUES ('2', '다이슨 청소기', '300', '생활가전');
INSERT into 'products' (`id`, `product_name`, `price`, `category`) VALUES ('3', '갤럭시탭', '600', '컴퓨터');
INSERT into 'products' (`id`, `product_name`, `price`, `category`) VALUES ('4', '드롱기 커피머신', '200', '주방가전');

Update products set price = 1500 where id = 1;

Delete from products where id = 4;

  • like ‘%’ : 특정 문자열 포함 함수

    • 사용법 : like ‘%문자%’

    1. products 테이블에서 제품 이름에 '프로'가 포함된 모든 제품을 선택하는 쿼리를 작성해주세요.

      select * from products where product_name like '%프로%';

  • not = <> / 대소문자 구분 필수

    1. sparta_students 테이블에서 Unity 트랙 소속이 아닌 학생들을 선택하는 쿼리를 작성해주세요!

      ⇒ 내가 쓴 답 :

      -- **u**nity 라는 트랙은 없음. **U**nity 라는 트랙은 있음.
select * from sparta_students where not track = 'unity';

      ⇒ 답안지 : 

      select * from sparta_students where track <> 'Unity';

  • In (A, B) : A 또는 B인 값을 구하는 함수

    • 사용법 : in (A, B)

    1. sparta_students 테이블에서 입학년도(enrollment_year)가 2021년인 학생과 2023년인 학생을 선택하는 쿼리를 작성해주세요!

      ⇒ 내가 쓴 답 :

      select * from sparta_students where enrollment_year = 2021 or enrollment_year = 2023;

      ⇒ 답안지 : 

      select * from sparta_students where enrollment_year in (2021, 2023);

  • Year : 날짜에서 연도만 반환하는 함수

    • 사용법 : YEAR(날짜 컬럼 또는 값)

    1. team_projects 테이블에서 2022년에 시작된 프로젝트를 선택하는 쿼리를 작성해주세요! 단, start_date < ‘2023-01-01’ 조건을 사용하지 말고 쿼리를 작성해주세요!

      ⇒ 내가 쓴 답 : 

      select * from team_projects where start_date like '2022%';

      ⇒ 답안지 : 

      select * from team_projects where YEAR(start_date) = 2022;

  • 지금을 나타내는 함수 (SQLite vs. MySQL)

    • 사용법
      • SQLite
        • Datetime(’now’) : 현재 날짜 + 시간 반환
        • Date(’now’) : 현재 날짜 반환
      • MySQL
        • NOW() : 현재 날짜 + 시간 반환
        • CURDATE() : 현재 날짜 반환
        • CURTIME() : 현재 시간 반환

    1. team_projects 테이블에서 현재 진행중인 프로젝트를 선택하는 쿼리를 작성해주세요. 단, 지금 시점의 날짜를 하드코딩해서 쿼리하지 말아주세요!

      select * from team_projects where end_date > DATE('now');

      ⇒ MySQL 기준

      SELECT * FROM team_projects WHERE CURDATE() BETWEEN start_date AND end_date;

  • 일 수로 환산하는 함수 (SQLite vs. MySQL)

    • 사용법
      • SQLite
        • JULIANDAY(날짜) : 날짜를 율리우스일로 변환
      • MySQL
        • DATEDIFF(A 날짜, B 날짜) : A - B 를 일 단위로 반환

    1. team_projects 테이블에서 각 프로젝트의 지속 기간을 일 수로 계산하는 쿼리를 작성해주세요!

      select JULIANDAY(end_date) - JULIANDAY(start_date) from team_projects;

      ⇒ MySQL 기준

      select name, DATEDIFF(end_date, start_date) as working_days from team_projects;

  • 랭킹 매기는 함수 (rank() over / 서브쿼리)

    • 사용법 : rank() over (order by 랭킹 매길 컬럼 desc/asc)

    1. lol_users 테이블에서 각 유저의 레이팅(rating) 순위를 계산하는 쿼리를 작성해주세요! 전체 지역(region) 기준이고 순위는 레이팅이 높을수록 높아야해요. (e.g. rating 1400 유저의 순위 > rating 1350 유저의 순위)

      select *, RANK() over (order by rating desc) as rank from lol_users;
      • 서브쿼리
        Select *
	(select count(*)
	from lol_users lu2
	where lu2.rating > lu1.rating) + 1 as rank
from lol_users lu1
order by rating desc;
        조회할 것 : 전체 컬럼 + 랭킹 랭킹 : lol_user의 특정 레코드의 rating 값보다 큰 다른 레코드의 rating 값들이 몇 개나 되는지 count 후 + 1 한 값이 랭킹

  • limit : 상위 N개만 조회하는 함수

    1. lol_users 테이블에서 가장 늦게 게임을 시작한(join_date) 유저의 이름을 선택하는 쿼리를 작성해주세요

      select name from lol_users order by join_date desc limit 1;
      • 서브쿼리 Ver.
        select name from lol_users
where join_date = (select max(join_date) from lol_users);

  • order by를 두 번 이상 사용할 때 (⇒ ‘,’ 사용 / and (X))

    1. lol_users 테이블에서 지역별로 레이팅이 높은 순으로 유저들을 정렬해서 나열하는 쿼리를 작성해주세요!

      select * from lol_users order by region, rating desc;

      ⇒ order by의 기준 컬럼을 두 개 이상으로 적용할 땐, and가 아닌 ‘,’ 를 사용

  • ‘~~~별’ 로 묶는 함수 (group by)

    1. lol_users 테이블에서 지역별로 평균 레이팅을 계산하는 쿼리를 작성해주세요!

      select region, AVG(rating) from lol_users group by region;

      ⇒ ‘—별’ 에 대한 값이 궁금할 땐 group by 사용

profile
JunichiK
represent ojeong-dong
이전 포스트

[240906] SQL 걷기반 (~1강)

다음 포스트

[240910] SQL 걷기반 (~마지막 연습문제)

0개의 댓글