오답노트
-
Distinct : 중복 제거 함수
-
사용법 : Distinct 컬럼
Select DISTINCT position from sparta_employees;
-
-
Between : 사이의 값 나타내는 함수
-
사용법 : Between N1 and N2
select * from sparta_employees where salary BETWEEN 40000 and 60000;
-
-
table 생성 시 컬럼 정의 후 ‘,’ 입력 필수
id product_name price category 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 ‘%문자%’
-
products테이블에서 제품 이름에 '프로'가 포함된 모든 제품을 선택하는 쿼리를 작성해주세요.select * from products where product_name like '%프로%';
-
not = <> / 대소문자 구분 필수
-
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)
-
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(날짜 컬럼 또는 값)
-
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() : 현재 시간 반환
- SQLite
-
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 를 일 단위로 반환
- SQLite
-
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)
-
lol_users테이블에서 각 유저의 레이팅(rating) 순위를 계산하는 쿼리를 작성해주세요! 전체 지역(region) 기준이고 순위는 레이팅이 높을수록 높아야해요. (e.g. rating 1400 유저의 순위 > rating 1350 유저의 순위)select *, RANK() over (order by rating desc) as rank from lol_users;- 서브쿼리
조회할 것 : 전체 컬럼 + 랭킹 랭킹 : lol_user의 특정 레코드의 rating 값보다 큰 다른 레코드의 rating 값들이 몇 개나 되는지 count 후 + 1 한 값이 랭킹Select * (select count(*) from lol_users lu2 where lu2.rating > lu1.rating) + 1 as rank from lol_users lu1 order by rating desc;
- 서브쿼리
-
limit : 상위 N개만 조회하는 함수
- 사용법 : limit 숫자
-
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);
- 서브쿼리 Ver.
-
order by를 두 번 이상 사용할 때 (⇒ ‘,’ 사용 / and (X))
-
lol_users테이블에서 지역별로 레이팅이 높은 순으로 유저들을 정렬해서 나열하는 쿼리를 작성해주세요!select * from lol_users order by region, rating desc;⇒ order by의 기준 컬럼을 두 개 이상으로 적용할 땐, and가 아닌 ‘,’ 를 사용
-
-
‘~~~별’ 로 묶는 함수 (group by)
-
lol_users테이블에서 지역별로 평균 레이팅을 계산하는 쿼리를 작성해주세요!select region, AVG(rating) from lol_users group by region;⇒ ‘—별’ 에 대한 값이 궁금할 땐 group by 사용
-
-
Insert into 여러 번 중복 없이 삽입 가능
-
테이블 생성
id name birth_date gender last_visit_date 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 Create table patients( id INT Primary key, name varchar(3), birth_date date, gender varchar(2), last_visit_date date ); 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');
-
-
Alter table (테이블명) Add Column (컬럼명 데이터 타입) : 이미 생성된 테이블에 컬럼 추가하기
- 사용법 : Alter table 테이블 Add Column 컬럼 데이터 타입
-
orders 테이블
id product_id quantity order_date 101 1 2 2023-03-01 102 2 1 2023-03-02 103 3 5 2023-03-04 create table orders_2( id INT primary key, product_id INT, quantity INT order_date date, Foreign key (product_id) references products_2(id) ); -- quantity INT 뒤 ','를 붙이지 않아 quantity 는 생성되지 않고 다른 컬럼들만 생성된 경우, Alter table orders_2 ADD Column quantity INT 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'); -- 만약 새로운 컬럼을 추가 후 데이터를 삽입하고 싶다면, Update 함수 사용 Update orders_2 set quantity = 2 where id = 101;
-
From절 서브쿼리
-
lol_feedbacks테이블에서 평균 만족도 점수가 가장 높은 날짜를 찾는 쿼리를 작성해주세요!select feedback_date, AVG(satisfaction_score) as satisfaction_avg from lol_feedbacks group by feedback_date order by avg(satisfaction_score) desc limit 1;- 서브쿼리
select date from ( select date, avg(score) as avg_score from feedbacks group by date ) AS Subquery order by avg_score desc limit 1;-
알고 싶은 것 : 날짜
-
찾아올 테이블
날짜 평균 만족도 -
가장 높은 날짜 : order by ~ desc limit 1
-
- 서브쿼리
-
-
Round : 반올림하고 싶을 때
- 사용법 : Round(숫자) ⇒ 버림하고 싶을 땐, Cast 사용
-
doctors테이블에서 각 의사의 근무 기간을 계산하는 쿼리를 작성해주세요!select *, ROUND(JULIANDAY('now') - JULIANDAY(hire_date)) as working_days from doctors;
- 사용법 : Round(숫자) ⇒ 버림하고 싶을 땐, Cast 사용
-
Date(’now’, ‘-n years’) : 현재 날짜에서 특정 기간을 뺀 값
- 사용법
- SQLite
- Date(’now’, ‘-n 년/월/일’) : 현재 날짜에서 특정 기간을 뺀 값
- MySQL
- DATE_SUB(CURDATE(), Interval n 년/월/일)
- SQLite
- DATE_SUB vs. DATEDIFF
- DATE_SUB : 날짜가 반환됨. (특정 날짜 - 특정 기간)
- DATEDIFF : 일 수가 반환됨. (특정 날짜 - 특정 날짜)
-
doctors테이블에서 현재 날짜 기준으로 5년 이상 근무(hire_date)한 의사 수를 계산하는 쿼리를 작성해주세요!select count(*) as fiveyears_doc from doctors where hire_date <= DATE('now', '-5 years');⇒ MySQL 기준
select count(*) as fiveyears_doc from doctors where hire_date <= DATE_SUB(CURDATE(), Interval 5 YEAR);
- 사용법
-
Foreign key 컬럼 references 연결테이블(연결컬럼) : 외래키 만드는 법
- 사용법
- Foreign Key (참조할 컬럼) References 참조될 테이블(참조될 컬럼)
- (departments 테이블의 id) → (employees 테이블의 departmet_id) 외래키 참조
-
테이블 생성
-
employees 테이블
id department_id name 1 101 르탄이 2 102 배캠이 3 103 구구이 4 101 이션이 -
departments 테이블
id name 101 인사팀 102 마케팅팀 103 기술팀
create table departments( id INT Primary key, name varchar(4) ); create table employees( id INT Primary key, department_id INT, name varchar(3), Foreign Key (department_id) references departments(id) ); Insert into departments (id, name) values (101, '인사팀'), (102, '마케팅팀'), (103, '기술팀'); Insert into employees (id, department_id, name) values (1, 101, '르탄이'), (2, 102, '배캠이'), (3, 103, '구구이'), (4, 101, '이션이'); -
- 사용법
-
Left Join vs. Inner join
⇒ 위 그림의 A, B 집합은 각 테이블을 연결하는 외래키 집합으로 생각하면 됨.
⇒ A/B : 왼/오 순서로 각 시나리오별 join 쿼리를 돌렸을 때,
- A ⊃ B
A B 1 1 2 2 3 3 4 1 5 -
Inner
A B 1 1 1 1 2 2 3 3 -
Left
A B 1 1 1 1 2 2 3 3 4 NULL 5 NULL -
Right
A B 1 1 1 1 2 2 3 3 -
Full
A B 1 1 1 1 2 2 3 3 4 NULL 5 NULL
-
- A ⊂ B
A B 1 1 2 2 3 3 5 4 5 - Inner
A B 1 1 2 2 3 3 5 5 - Left
A B 1 1 2 2 3 3 5 5 - Right
A B 1 1 2 2 3 3 NULL 4 5 5 - Full
A B 1 1 2 2 3 3 NULL 4 5 5
- Inner
- A = B
A B 1 1 2 2 3 3 4 4 - Inner
A B 1 1 2 2 3 3 4 4 - Left
A B 1 1 2 2 3 3 4 4 - Right
A B 1 1 2 2 3 3 4 4 - Full
A B 1 1 2 2 3 3 4 4
- Inner
- A ≠ B
A B 1 1 2 2 4 6 5 7 - Inner
A B 1 1 2 2 - Left
A B 1 1 2 2 4 NULL 5 NULL - Right
A B 1 1 2 2 NULL 6 NULL 7 - Full
A B 1 1 2 2 4 NULL 5 NULL NULL 6 NULL 7
- Inner
-
모든 주문의 주문 ID와 주문된 상품의 이름을 나열하는 쿼리를 작성해주세요!
Select orders_2.id, products_2.name from orders_2 left join products_2 on orders_2.product_id = products_2.id;⇒ 기본 문법
Select 테이블명.컬럼명 from 테이블1 left join 테이블2 on 테이블1.컬럼 = 테이블2.컬럼;
- A ⊃ B
-
IS NULL
⇒ 아래 쿼리를 아래 두 테이블에서 진행해봤을 때, 나오는 결과값은
select * from products_2 left join orders_2 on products_2.id = orders_2.product_id;-
products 테이블
id name price 1 랩톱 1200 2 핸드폰 800 3 타블렛 400 4 워치 600 -
orders 테이블
id product_id quantity order_date 101 1 2 2023-03-01 102 2 1 2023-03-02 103 3 5 2023-03-04 104 1 5 2023-03-05 -
결과값
id name price id product_id quantity order_date 1 랩톱 1200 101 1 2 2023-03-01 1 랩톱 1200 104 1 5 2023-03-05 2 핸드폰 800 102 2 1 2023-03-02 3 타블렛 400 103 3 5 2023-03-04 4 워치 600 [NULL] [NULL] [NULL] [NULL] ⇒ 여기서 판매되지 않은 상품을 찾기 위해선 order 테이블의 컬럼이 NULL 값을 갖는 프로덕트의 id와 이름을 찾아야 함.
select products_2.id, products_2.name from products_2 left join orders_2 on products_2.id = orders_2.product_id where orders_2.quantity is null;
-
판매되지 않은 상품의 ID와 이름을 찾는 쿼리를 작성해주세요!
select products_2.id, products_2.name from products_2 left join orders_2 on products_2.id = orders_2.product_id where orders_2.quantity is null;
-
