TIL 2 | SQL 문제 풀이

dereck·2024년 11월 20일

TIL

목록 보기

2/21

틀린 내용이나 더 좋은 방법이 있다면 댓글 적어주시면 감사드리겠습니다!

기초 1

sparta_employees 생성 및 데이터 삽입

create table if not exists sparta_employees
(
    id        int         not null primary key,
    name      varchar(45) not null,
    position  varchar(45) not null,
    salary    int         not null,
    hire_date date        not null
);
insert into sparta_employees (id, name, position, salary, hire_date)
VALUES (1, '르탄이', '개발자', 30000, '2022-05-01'),
       (2, '배캠이', 'PM', 40000, '2021-09-25'),
       (3, '구구이', '파트장', 35000, '2023-06-01'),
       (4, '이션이', '팀장', 50000, '2021-07-09');

문제 풀이

1-1. 모든 직원의 이름과 직급을 선택하는 쿼리

select name, position
from sparta_employees;

1-2. 중복 없이 모든 직급을 선택하는 쿼리

select distinct position
from sparta_employees;

1-3. 연봉이 40000 ~ 60000 사이인 직원들을 선택하는 쿼리

select *
from sparta_employees
where salary between 40000 and 60000;

1-4. 입사일이 2023년 1월 1일 이전인 모든 직원들을 선택하는 쿼리

select *
from sparta_employees
where hire_date < '2023-01-01';

기초 2

products 생성 및 데이터 삽입

create table if not exists products
(
    id           int         not null primary key,
    product_name varchar(45) not null,
    price        int         not null,
    category     varchar(45) not null
);
insert into products (id, product_name, price, category)
values (1, '맥북 프로', 1200, '컴퓨터'),
       (2, '다이슨 청소기', 300, '생활가전'),
       (3, '갤럭시 탭', 600, '컴퓨터'),
       (4, '드롱기 커피머신', 200, '주방가전');

문제 풀이

2-1: 제품 이름과 가격만을 선택하는 쿼리

select product_name, price
from products;

2-2. 제품 이름에 '프로'가 포함된 모든 제품을 선택하는 쿼리

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

2-3. 제품 이름이 '갤'로 시작하는 모든 제품을 선택하는 쿼리

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

2-4. 모든 제품을 구매하기 위해 필요한 돈을 계산하는 쿼리

select sum(price) as '모든 제품을 구매하기 위해 필요한 돈'
from products;

기초 3

orders 생성 및 데이터 삽입

create table if not exists orders
(
    id           int    not null primary key,
    customer_id  bigint not null,
    product_id   int    not null,
    amount       int    not null,
    shopping_fee int    not null,
    order_date   date   not null
);
insert into orders (id, customer_id, product_id, amount, shopping_fee, order_date)
values (1, 719, 1, 3, 50000, '2023-11-01'),
       (2, 131, 2, 1, 10000, '2023-11-02'),
       (3, 65, 4, 1, 20000, '2023-11-05'),
       (4, 1008, 3, 2, 25000, '2023-11-05');

문제 풀이

3-1. 주문 수량이 2개 이상인 주문을 진행한 소비자의 ID만 선택하는 쿼리

select customer_id
from orders
where amount >= 2;

3-2. 2023-11-02 이후에 주문된 주문 수량이 2개 이상인 주문을 선택하는 쿼리

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

3-3. 주문 수량이 3개 미만이면서 배송비가 15000원보다 비싼 주문을 선택하는 쿼리

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

3-4. 배송비가 높은 금액 순으로 정렬하는 쿼리

select *
from orders
order by shopping_fee desc;

기초 4

sparta_students 생성 및 데이터 삽입

create table if not exists sparta_students
(
    id              int         not null primary key,
    name            varchar(45) not null,
    track           varchar(45) not null,
    grade           char(5)     not null,
    enrollment_year year
);
insert into sparta_students (id, name, track, grade, enrollment_year)
values (1, '르탄이', 'Node.js', 'A', '2023'),
       (2, '배캠이', 'Spring', 'B', '2022'),
       (3, '구구이', 'Unity', 'C', '2021'),
       (4, '이션이', 'Node.js', 'B', '2022');

문제 풀이

4-1. 모든 학생의 이름과 트랙을 선택하는 쿼리

select name, track
from sparta_students;

4-2. Unity 트랙 소속이 아닌 학생들을 선택하는 쿼리

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

4-3. 입학년도가 2021년인 학생과 2023년인 학생을 선택하는 쿼리

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

4-4. Node.js 트랙 소속이고 학점이 'A'인 학생의 입학년도를 선택하는 쿼리

select *
from sparta_students
where track = 'Node.js' and grade = 'A';

기초 5

team_projects 생성 및 데이터 삽입

create table if not exists team_projects
(
    id         int         not null primary key,
    name       varchar(45) not null,
    start_date date        not null,
    end_date   date        not null,
    aws_cost   int         not null
);
insert into team_projects (id, name, start_date, end_date, aws_cost)
values (1, '일조', '2023-01-01', '2023-01-07', 30000),
       (2, '꿈꾸는 이조', '2023-03-15', '2023-03-22', 50000),
       (3, '보람삼조', '2023-11-20', '2023-11-30', 80000),
       (4, '사조참치', '2022-07-01', '2022-07-30', 75000);

문제 풀이

5-1. AWS 예산이 40000 이상 들어간 프로젝트들의 이름을 선택하는 쿼리

select *
from team_projects
where aws_cost >= 40000;

5-2. 2022년에 시작된 프로젝트를 선택하는 쿼리 (단, `start_date < '2023-01-01'` 사용 금지)

select *
from team_projects
where start_date between '2022-01-01' and '2022-12-31';

5-3. 현재 진행 중인 프로젝트를 선택하는 쿼리 (단, 지금 시점의 날짜 하드코딩 금지)

select *
from team_projects
where now() between start_date and end_date;

5-4. 각 프로젝트의 지속 기간을 일 수로 계산하는 쿼리

두 날짜 간의 차이를 구할 때는 datediff() 함수를 사용할 수 있다. 이 함수는 두 날짜 사이의 일 수 차이를 반환한다.

select datediff(end_date, start_date) as '프로젝트 지속 기간'
from team_projects;

기초 6

lol_users 생성 및 데이터 삽입

create table if not exists lol_users
(
    id        int         not null primary key,
    name      varchar(45) not null,
    region    varchar(45) not null,
    rating    int         not null,
    join_date date        not null
);
insert into lol_users (id, name, region, rating, join_date)
values (1, '르탄이', '한국', 1300, '2019-06-15'),
       (2, '배캠이', '미국', 1500, '2020-09-01'),
       (3, '구구이', '한국', 1400, '2021-01-07'),
       (4, '이션이', '미국', 1350, '2019-11-15');

문제 풀이

6-1. 각 유저의 레이팅 순위를 계산하는 쿼리, 이때 전체 지역 기준이며 순위는 레이팅이 높을 수록 높아야 함

select *, dense_rank() over (order by rating desc) as '레이팅 순위'
from lol_users;

6-2. 가장 늦게 게임을 시작한 유저의 이름을 선택하는 쿼리

select name
from lol_users
order by join_date desc
limit 1;

6-3. 지역별로 레이팅이 높은 순으로 유저들을 정렬해서 나열하는 쿼리

select *
from lol_users
order by region, rating desc;

6-4. 지역별로 평균 레이팅을 계산하는 쿼리

select region, round(avg(rating), 0) as '지역별 평균 레이팅'
from lol_users
group by region;

기초 7

lol_feedbacks 생성 및 데이터 삽입

create table if not exists lol_feedbacks
(
    id                 int         not null primary key,
    user_name          varchar(45) not null,
    satisfaction_score int         not null,
    feedback_date      date        not null
);
insert into lol_feedbacks (id, user_name, satisfaction_score, feedback_date)
values (1, '르탄이', 5, '2023-03-01'),
       (2, '배캠이', 4, '2023-03-02'),
       (3, '구구이', 3, '2023-03-01'),
       (4, '이션이', 5, '2023-03-03'),
       (5, '구구이', 4, '2023-03-04');

문제 풀이

7-1. 만족도 점수에 따라 피드백을 내림차순으로 정렬하는 쿼리

select *
from lol_feedbacks
order by satisfaction_score desc;

7-2. 각 유저별로 최신 피드백을 찾는 쿼리

select user_name, max(feedback_date) as '최신 피드백'
from lol_feedbacks
group by user_name;

7-3. 만족도 점수가 5점인 피드백의 수를 계산하는 쿼리

select count(*) as '만족도가 5점인 피드백의 수'
from lol_feedbacks
where satisfaction_score = 5;

7-4. 가장 많은 피드백을 남긴 상위 3명의 고객을 찾는 쿼리

select user_name, count(id) as '피드백을 남긴 횟수'
from lol_feedbacks
group by user_name
order by count(id) desc
limit 3;

7-5. 평균 만족도 점수가 가장 높은 날짜를 찾는 쿼리

select feedback_date, round(avg(satisfaction_score), 0) as avg_satisfaction
from lol_feedbacks
group by feedback_date
order by avg_satisfaction desc
limit 1;

기초 8

doctors 생성 및 데이터 삽입

create table if not exists doctors
(
    id        int         not null primary key,
    name      varchar(45) not null,
    major     varchar(45) not null,
    hire_date date        not null
);
insert into doctors (id, name, major, hire_date)
values (1, '르탄이', '피부과', '2018-05-10'),
       (2, '배캠이', '성형외과', '2019-06-15'),
       (3, '구구이', '안과', '2020-07-20');

문제 풀이

8-1. 전공이 성형외과인 의사의 이름을 알아내는 쿼리

select name
from doctors
where major = '성형외과';

8-2. 각 전공 별 의사 수를 계산하는 쿼리

select major, count(major) as '전공 별 의사 수'
from doctors
group by major;

8-3. 현재 날짜 기준으로 5년 이상 근무한 의사 수를 계산하는 쿼리

interval은 MySQL에서 날짜와 시간 연산에 사용되는 키워드로 연, 월, 일을 모두 고려하여 정확하게 n년 이전의 날짜를 계산합니다. (다양한 단위로 확장 가능: day, month, hour ...)

select name, count(hire_date) as '5년 이상 근무한 의사 수'
from doctors
where date_add(hire_date, interval 5 year) <= now()
group by name;

8-4. 각 의사의 근무 기간을 계산하는 쿼리

4년에 한 번 윤년이 오기 때문에 365로 나누는 것이 아닌 ((365*3) + 366) / 4의 결과인 365.25로 나눠준다.

select name, datediff(date(now()), hire_date) / 365.25 as '근무 기간 (단위: 년)'
from doctors;

기초 9

patients 생성 및 데이터 삽입

create table if not exists patients
(
    id              int         not null primary key,
    name            varchar(45) not null,
    birth_date      date        not null,
    gender          varchar(45) not null,
    last_visit_date date        not null
);
insert into patients (id, name, birth_date, gender, last_visit_date)
values (1, '르틴이', '1985-04-12', '남자', '2023-03-15'),
       (2, '배캠이', '1990-08-05', '여자', '2023-03-20'),
       (3, '구구이', '1982-12-02', '여자', '2023-02-18'),
       (4, '이션이', '1999-03-02', '남자', '2023-03-17');

문제 풀이

9-1. 각 성별에 따른 환자 수를 계산하는 쿼리

select gender, count(*) as '환자 수'
from patients
group by gender;

9-2. 현재 나이가 40세 이상인 환자들의 수를 계산

curdate() 함수를 사용하면 현재 날짜의 시간 정보를 제외한 연-월-일 형식의 값을 반환한다.

# 태어나면 1살인 경우 (1월 1일마다 한 살 추가)
select count(*) as '40세 이상인 환자들의 수'
from patients
where (year(curdate()) - year(birth_date) + 1) >= 40;

# 태어나면 0살인 경우 (생일마다 한 살 추가)
select count(*) as '40세 이상인 환자들의 수'
from patients
where (year(curdate()) - year(birth_date) +
       if(date_format(curdate(), '%m-%d') >= date_format(birth_date, '%m-%d'), 0, -1)) >= 40;

curdate() 함수를 이용하여 연도 값을 먼저 계산한다. 이후 나이 계산 방식에 따라 2가지로 나뉜다.

태어나면 0살인 경우 (생일마다 한 살 추가): date_format()으로 연-월-일 형식을 월-일로 변환하고, 생일 월-일의 값이 지금 월-일 값보다 같거나 작다면 (= 생일이거나 생일이 지났다면) 0을 더해주고, 아니라면 -1을 더해준다.
태어나면 1살인 경우 (1월 1일마다 한 살 추가): 현재 날짜와 태어난 날짜를 year() 함수를 이용하여 계산한 뒤 1을 더해준다.

9-3. 마지막 방문 날짜가 1년 이상된 환자들을 선택하는 쿼리

select *
from patients
where last_visit_date <= curdate() - interval 1 year;

9-4. 생년월일이 1980년대인 환자들의 수를 계산하는 쿼리

select count(*) as '1980년대생의 수'
from patients
where birth_date between '1980-01-01' and '1989-12-31';

기초 10

departments & employee 생성 및 데이터 삽입

외래키 설정을 했기 때문에 테이블 생성과 데이터 입력 및 테이블 삭제에 대한 순서를 지켜야 한다.

create table if not exists departments
(
    id   int         not null primary key,
    name varchar(45) not null
);
create table if not exists employee
(
    id            int         not null primary key,
    department_id int         not null,
    name          varchar(45) not null,
    foreign key (department_id) references departments(id)
);
insert into departments (id, name)
values (101, '인사팀'),
       (102, '마케팅팀'),
       (103, '기술팀');
insert into employee (id, department_id, name)
values (1, 101, '르탄이'),
       (2, 102, '배캠이'),
       (3, 103, '구구이'),
       (4, 101, '이션이');

문제 풀이

10-1. 현재 존재하는 총 부서의 수를 구하는 쿼리

중복된 부서 이름이 있을 경우 이러한 중복을 제외하고 고유한 부서 이름만 세기 위해 count(distinct name)을 사용했다.

select count(distinct name) as '총 부서 수'
from departments;

10-2. 모든 직원과 그들이 속한 부서의 이름을 나열하는 쿼리

inner join: 교집합. 겹치는 행이 없을 경우 그 행은 결과에서 제외
left join: 합집합. 왼쪽 테이블의 모든 행을 조회한다. 겹치지 않는 행에 대한 열의 값은 null로 처리된다.

select *
from employee e
         inner join departments d on e.department_id = d.id;

10-3. '기술팀'부서에 속한 직원들의 이름을 나열하는 쿼리

join시 테이블에 별칭을 사용해서 값을 지정할 때 더 편하게 사용할 수 있다.

select e.name
from employee e
         inner join departments d on e.department_id = d.id
where d.name = '기술팀';

10-4. 부서별로 직원 수를 계산하는 쿼리

select d.name, count(d.name) as '부서별 직원 수'
from employee e
         inner join departments d on e.department_id = d.id
group by d.name;

10-5. 직원이 없는 부서의 이름을 찾는 쿼리

select *
from employee e
         right join departments d on e.department_id = d.id
where e.department_id is null;

10-6. '마케팅팀'부서에만 속한 직원들의 이름을 나열하는 쿼리

select e.name
from employee e
         inner join departments d on d.id = e.department_id
where d.name = '마케팅팀';

기초 11

products_2 & orders_2 생성 및 데이터 삽입

create table if not exists products_2
(
    id    int         not null primary key,
    name  varchar(45) not null,
    price int         not null
);
create table if not exists orders_2
(
    id         int  not null primary key,
    product_id int  not null,
    quantity   int  not null,
    order_date date not null,
    foreign key (product_id) references products_2(id)
);
insert into products_2 (id, name, price)
values (1, '랩톱', 1200),
       (2, '핸드폰', 800),
       (3, '타블렛', 400);
insert into orders_2 (id, product_id, quantity, order_date)
values (101, 1, 2, '2023-03-01'),
       (102, 2, 1, '2023-03-02'),
       (103, 3, 5, '2023-03-04');

문제 풀이

11-1. 모든 주문의 주문 ID와 주문된 상품의 이름을 나열하는 쿼리

select o.id, p.name
from orders_2 o
         inner join products_2 p on o.product_id = p.id;

11-2. 총 매출의 합이 가장 높은 상품의 ID와 해당 상품의 총 매출을 가져오는 쿼리

select p.id, (p.price * o.quantity) as '총 매출'
from orders_2 o
         inner join products_2 p on o.product_id = p.id
order by (p.price * o.quantity) desc
limit 1;

11-3. 각 상품 ID 별로 판매된 총 수량을 계산하는 쿼리

select p.id, o.quantity
from orders_2 o
         inner join products_2 p on o.product_id = p.id;

11-4. 2023-03-03 이후에 주문된 모든 상품의 이름을 나열하는 쿼리

select p.name
from orders_2 o
         inner join products_2 p on o.product_id = p.id
where o.order_date > '2023-03-03';

11-5. 가장 많이 판매된 상품의 이름을 찾는 쿼리

select p.name
from orders_2 o
         inner join products_2 p on o.product_id = p.id
order by o.quantity desc
limit 1;

11-6. 각 상품 ID 별로 평균 주문 수량을 계산하는 쿼리

select p.id, avg(o.quantity) as '평균 주문 수량'
from orders_2 o
         inner join products_2 p on o.product_id = p.id
group by p.id;

11-7. 판매되지 않은 상품의 ID와 이름을 찾는 쿼리

select p.id, p.name
from products_2 p
         left join orders_2 o on p.id = o.product_id
where o.id is null;

dereck

이전 포스트

TIL 1 | JWT 알아보기

다음 포스트

TIL 2 | SQL 문제 풀이

TIL

기초 1

sparta_employees 생성 및 데이터 삽입

문제 풀이

1-1. 모든 직원의 이름과 직급을 선택하는 쿼리

1-2. 중복 없이 모든 직급을 선택하는 쿼리

1-3. 연봉이 40000 ~ 60000 사이인 직원들을 선택하는 쿼리

1-4. 입사일이 2023년 1월 1일 이전인 모든 직원들을 선택하는 쿼리

기초 2

products 생성 및 데이터 삽입

문제 풀이

2-1: 제품 이름과 가격만을 선택하는 쿼리

2-2. 제품 이름에 '프로'가 포함된 모든 제품을 선택하는 쿼리

2-3. 제품 이름이 '갤'로 시작하는 모든 제품을 선택하는 쿼리

2-4. 모든 제품을 구매하기 위해 필요한 돈을 계산하는 쿼리

기초 3

orders 생성 및 데이터 삽입

문제 풀이

3-1. 주문 수량이 2개 이상인 주문을 진행한 소비자의 ID만 선택하는 쿼리

3-2. 2023-11-02 이후에 주문된 주문 수량이 2개 이상인 주문을 선택하는 쿼리

3-3. 주문 수량이 3개 미만이면서 배송비가 15000원보다 비싼 주문을 선택하는 쿼리

3-4. 배송비가 높은 금액 순으로 정렬하는 쿼리

기초 4

sparta_students 생성 및 데이터 삽입

문제 풀이

4-1. 모든 학생의 이름과 트랙을 선택하는 쿼리

4-2. Unity 트랙 소속이 아닌 학생들을 선택하는 쿼리

4-3. 입학년도가 2021년인 학생과 2023년인 학생을 선택하는 쿼리

4-4. Node.js 트랙 소속이고 학점이 'A'인 학생의 입학년도를 선택하는 쿼리

기초 5

team_projects 생성 및 데이터 삽입

문제 풀이

5-1. AWS 예산이 40000 이상 들어간 프로젝트들의 이름을 선택하는 쿼리

5-2. 2022년에 시작된 프로젝트를 선택하는 쿼리 (단, start_date < '2023-01-01' 사용 금지)

5-3. 현재 진행 중인 프로젝트를 선택하는 쿼리 (단, 지금 시점의 날짜 하드코딩 금지)

5-4. 각 프로젝트의 지속 기간을 일 수로 계산하는 쿼리

기초 6

lol_users 생성 및 데이터 삽입

문제 풀이

6-1. 각 유저의 레이팅 순위를 계산하는 쿼리, 이때 전체 지역 기준이며 순위는 레이팅이 높을 수록 높아야 함

6-2. 가장 늦게 게임을 시작한 유저의 이름을 선택하는 쿼리

6-3. 지역별로 레이팅이 높은 순으로 유저들을 정렬해서 나열하는 쿼리

6-4. 지역별로 평균 레이팅을 계산하는 쿼리

기초 7

lol_feedbacks 생성 및 데이터 삽입

문제 풀이

7-1. 만족도 점수에 따라 피드백을 내림차순으로 정렬하는 쿼리

7-2. 각 유저별로 최신 피드백을 찾는 쿼리

7-3. 만족도 점수가 5점인 피드백의 수를 계산하는 쿼리

7-4. 가장 많은 피드백을 남긴 상위 3명의 고객을 찾는 쿼리

7-5. 평균 만족도 점수가 가장 높은 날짜를 찾는 쿼리

기초 8

doctors 생성 및 데이터 삽입

문제 풀이

8-1. 전공이 성형외과인 의사의 이름을 알아내는 쿼리

8-2. 각 전공 별 의사 수를 계산하는 쿼리

8-3. 현재 날짜 기준으로 5년 이상 근무한 의사 수를 계산하는 쿼리

8-4. 각 의사의 근무 기간을 계산하는 쿼리

기초 9

patients 생성 및 데이터 삽입

문제 풀이

9-1. 각 성별에 따른 환자 수를 계산하는 쿼리

9-2. 현재 나이가 40세 이상인 환자들의 수를 계산

9-3. 마지막 방문 날짜가 1년 이상된 환자들을 선택하는 쿼리

9-4. 생년월일이 1980년대인 환자들의 수를 계산하는 쿼리

기초 10

departments & employee 생성 및 데이터 삽입

문제 풀이

10-1. 현재 존재하는 총 부서의 수를 구하는 쿼리

10-2. 모든 직원과 그들이 속한 부서의 이름을 나열하는 쿼리

10-3. '기술팀'부서에 속한 직원들의 이름을 나열하는 쿼리

10-4. 부서별로 직원 수를 계산하는 쿼리

10-5. 직원이 없는 부서의 이름을 찾는 쿼리

10-6. '마케팅팀'부서에만 속한 직원들의 이름을 나열하는 쿼리

기초 11

products_2 & orders_2 생성 및 데이터 삽입

문제 풀이

11-1. 모든 주문의 주문 ID와 주문된 상품의 이름을 나열하는 쿼리

11-2. 총 매출의 합이 가장 높은 상품의 ID와 해당 상품의 총 매출을 가져오는 쿼리

5-2. 2022년에 시작된 프로젝트를 선택하는 쿼리 (단, `start_date < '2023-01-01'` 사용 금지)