[Mysql] SQL 문법연습 걷기(1~6) +업데이트

🥑 학습내용

240906 업데이트 ) ** 오답수정 및 대체 가능 코드 추가

 

SQL 문법연습 걷기반 과제

1)아래와 같은 sparta_employees(직원) 테이블이 있습니다.

id	name	position	salary	hire_date
1	르탄이	개발자	30000	2022-05-01
2	배캠이	PM	40000	2021-09-25
3	구구이	파트장	35000	2023-06-01
4	이션이	팀장	50000	2021-07-09

1

sparta_employees 테이블에서 모든 직원의 이름(name)과 직급(position)을 선택하는 쿼리를 작성해주세요.

 

SELECT name, position

from sparta_employees

 

2

sparta_employees 테이블에서 중복 없이 모든 직급(position)을 선택하는 쿼리를 작성해주세요.

 

SELECT distinct(position)

from sparta_employees

 

3

sparta_employees 테이블에서 연봉(salary)이 40000과 60000 사이인 직원들을 선택하는 쿼리를 작성해주세요.

 

SELECT *

from sparta_employees

where salary between 40000 and 60000

 

4 

sparta_employees 테이블에서 입사일(hire_date)이 2023년 1월 1일 이전인 모든 직원들을 선택하는 쿼리를 작성해주세요.

 

SELECT sparta_emplyees

from sparta_employees

where hire_date<'2023-01-01'

 

---

 2) 여러분이 구매하고 싶은 상품들의 정보가 있는 products(상품) 테이블이 아래에 있습니다.

id	name	position	salary	hire_date
1	르탄이	개발자	30000	2022-05-01
2	배캠이	PM	40000	2021-09-25
3	구구이	파트장	35000	2023-06-01
4	이션이	팀장	50000	2021-07-09

 

5

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

 

SELECT product_name, price

from products

 

6 

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

 

SELECT *

from products

where product_name like '%프로%'

** '프로로 시작하는'이 아니라 '프로가 포함된' 모든 제품이기 때문에 프로 앞에도 %를 붙여주어야 한다.

 

7

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

 

SELECT *

from products

where product_name like '갤%'

 

8 

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

 

SELECT sum(price)

from products

 

 

---

 3) 이제 상품 주문이 들어왔으니 어떤 고객에게 어떤 주문이 들어왔는지를 파악할 수 있는 orders(주문) 테이블이 아래에 있습니다.

9

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

 

select customer_id

from orders

where amount>=2

** where amount>1 도 사용할 수 있다.

 

10

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

 

select *

from orders

where amount>=2 and order_date>='2023-11-02'

 

11

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

 

select *

from orders

where amount<3 and shippping_fee>15000

 

12

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

 

select *

from orders

order by shipping_fee desc

 

---

4) 아래와 같은 sparta_students(학생) 테이블이 있습니다.

13

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

 

SELECT name, track

from sparta_students

 

14

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

 

SELECT *

from sparta_students

where not track='Unity'

** WHERE track<>'Unity' 와 같다.

 

15

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

 

SELECT *

from sparta_students

where enrollment_year=2021 or enrollment=2023

** WHERE enrollment_year IN (2021,2023) 과 같다.

 

16

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

 

SELECT enrollment_year

from sparta_students

where track='Node.js' and grade='A'

 

 

---

5) 공부를 한 결과를 점검하기 위해 팀 프로젝트를 수행해야 합니다! 이제, 아래와 같은 team_projects(프로젝트) 테이블이 있습니다. 

17

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

 

SELECT name

from team_projects

where aws_coat>=40000

 

18 

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

 

SELECT *

from team_projects

where start_date like '2022%'

** 참고 WHERE year(start_date)=2022

 

19

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

 

('하드코딩'이 무엇인지 몰라서 첫 번째 제출한 답)

현재가정 : 2023-09-03

SELECT *

from team_projects

where end_date>='2023-09-03'

 

 하드코딩 : 상수나 변수에 들어가는 값(데이트)을 코드에 직접 입력해서 프로그램이 동작할 때를 말한다.

 

  

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

** WHERE now() 대신 curdate()를 사용할 수 있다.

NOW()
현재 날짜와 시간 정보를 반환한다.

 

 

 

20 

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

 

select name, datediff(end_date,start_date) "프로젝트 지속기간"

from team_projects

DATEDIFF()
두 기간 사이의 일수 계산
expr1-expr2(exp1 : 종료일, exp2 : 시작일)
날짜 포맷에 시간이 포함되어 있는 경우, 시간은 계산에 포함하지 않는다.
날짜 범위에서 벗어나는 값을 입력하는 경우 null을 반환한다

 

** 위 정보를 보고 풀었는데  datediff(day, start_date, end_date) 라는 의견도 있어서 DATEDIFF 함수에 대한 추가학습을 통해 답을 더 찾을 예정이다.

 

---

6) 아래와 같은 lol_users(LOL 유저 테이블)이 있습니다.

 

21

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

 

SELECT *, rank() over(order by rating desc) rating_rank

FROM lol_useres

 

 

22

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

 

SELECT name, max(join_date)

FROM lol_useres

select name
from lol_users
order by join_date desc
limit 1

** 위와 같이 작성할 수도 있다.

 

23

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

 

SELECT *

FROM lol_useres

order by region, rating desc

 

24

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

 

SELECT region, avg(rating) avg_rating

FROM lol_useres

group by region

 

---

생각보다 간단하지 않아서 푸느라 애먹었다. 팀원들과 어떤식으로 접근해서 이 코드가 나오게 되었는지 공유하였다. 같은 결과 값인데도 서로 다른 코드를 짠 것이 신기하기도 했다.

아직 모르거나, 제대로 적용해보지 않은 함수들이 많아서 조금 힘들긴 하지만 이렇게 쌓아가다 보면 더 발전하겠지!