스파르타 코딩클럽 데이터 분석 D-DAY 11

오늘은 팀원들과 함께 그동안 밀린 데일리 아티클을 풀어보았다. 

 

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 40000 and 60000

4.

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

SELECT hire_date

FROM sparta_employees

WHERE hire date < '2023-01-01'

 

1)

5.

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

 

SELECT product_name, price

FROM products

6.

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

 

SELECT *

FROM products

WHERE product_name like '%프로%'

7.

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

 

SELECT *

FROM poducts

WHER product_name like '갤%'

8.

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

SELECT sum(price) total_price

FROM products

 

9.

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

 

SELECT customer_id

FROM orderes

WHERE amount >=2 

 

10.

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

 

SELECT *

FROM orders

WHERE order_date>;2023-11-02' AND amount >=2

 

11.

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

 

SELECT *

FROM orders

WHERE amount<3 and shipping_fee >15000

12.

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

SELECT *

FROM orders

ORDER BY shipping_fee desc 

 

13.

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

 

SELECT name,track

FROM sparta_students

14.

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

SELECT *

FROM sparta_students

WHERE track <> 'Unity'

15.

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

SELECT *

FROM sparta_students

WHERE emrollment_year IN ('2021','2023')

16.

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

SELECT enrollmenst_year

FROM sparta_students

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

 

 

17.

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

 

SELECT name

FROM team_projects

WHERE aws_cost >=40000

18.

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

SELECT *

FROM team_projects

WHERE start_date like '%2022% 

19.

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

SELECT *

FROM team_projects

WHERE CURRENT_DATE BETWEEN start_date AND end_date

20.

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

 

SELECT name, DATEDIFF (start_date,end_date) as date

FROM team_projects

21.

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

 

SELECT name,rating,RANK() OVER (order by rating desc) raking_rate

FROM lol_users

 

22.

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

 

SELECT name

FROM lol_users

WHERE join_date = 

(SELECT max(join_date)

FROM lol_users) 

 

23.

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

 

SELECT *

FROM lol_users

ORDER BY region,rating desc

24.

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

 

SELECT region, AVG(rating) avg_rating

FROM lol_usres

GROUP BY region

 

25.

lol_feedbacks 테이블에서 만족도 점수(satisfaction_score)에 따라 피드백을 내림차순으로 정렬하는 쿼리를 작성해주세요!

 

SELECT *

FROM lol_feedbacks

ORDER BY satisfaction_score desc 

26.

lol_feedbacks 테이블에서 각 유저별로 최신 피드백을 찾는 쿼리를 작성해주세요!

 

SELECT user_name,max(feedback_date)

FROM lol_feedbacks

GROUP BY user_name

 

27.

lol_feedbacks 테이블에서 만족도 점수가 5점인 피드백의 수를 계산하는 쿼리를 작성해주세요!

 

SELECT count(*) 

FROM lol_feedbacks

WHERE sarisfation_score = 5

28.

lol_feedbacks 테이블에서 가장 많은 피드백을 남긴 상위 3명의 고객을 찾는 쿼리를 작성해주세요!

 

SELECT user_name ,count(*) feedback_count

FROM lol_feedbacks

GROUP BY user_name

ORDER BY feedback_count desc 

LIMIT 3

 

29.

lol_feedbacks 테이블에서 평균 만족도 점수가 가장 높은 날짜를 찾는 쿼리를 작성해주세요!

 

SELECT user_name, avg(satisfaction_score)  avg_score

FROM lol_feedbacks

GROUP BY user_name

ORDER BY 2 desc 

LIMIT 1

 

 

30.

doctors 테이블에서 전공(major)가 성형외과인 의사의 이름을 알아내는 쿼리를 작성해주세요!

 

SELECT name

FROM doctors

WHERE major = '성형외과' 

 

31.

doctors 테이블에서 각 전공 별 의사 수를 계산하는 쿼리를 작성해주세요!

 

SELECT major , count(*) 

FROM doctors

GROUP BY major 

32.

doctors 테이블에서 현재 날짜 기준으로 5년 이상 근무(hire_date)한 의사 수를 계산하는 쿼리를 작성해주세요!

 

SELECT COUNT(*)

FROM doctors

WHERE TIMESTAMPDIFF(Year, hire_date,CURRENT_DATE() ) >=5

 

33.

doctors 테이블에서 각 의사의 근무 기간을 계산하는 쿼리를 작성해주세요!

 

SELECT name, TIMESTAMPDIFF (Year, hire_date,CURRENT_DATE()) 

FROM doctors

 

34.

patients 테이블에서 각 성별(gender)에 따른 환자 수를 계산하는 쿼리를 작성해주세요!

 

SELECT gender,count(*)

FROM patients

GROUP BY gender

35.

patients 테이블에서 현재 나이가 40세 이상인 환자들의 수를 계산하는 쿼리를 작성해주세요!

SELECT count(*)

FROM patients

WHERE TIMESTAMPDIFF(YEAR,birth_date,CURRENT_DATE()) >=40

 

36.

patients 테이블에서 마지막 방문 날짜(last_visit_date)가 1년 이상 된 환자들을 선택하는 쿼리를 작성해주세요!

 

SELECT name

FROM patients

WHERE TIMESTAMPDIFF(YEAR, last_visit_date,CURRENT_DATE())>=1 

 

37.

patients 테이블에서 생년월일이 1980년대인 환자들의 수를 계산하는 쿼리를 작성해주세요!

 

SELECT count(*)

FROM patients

WHERE birth_date like '%1980%' 

38.

현재 존재하고 있는 총 부서의 수를 구하는 쿼리를 작성해주세요!

SELECT count(*)

FROM dpartments

 

39.

모든 직원과 그들이 속한 부서의 이름을 나열하는 쿼리를 작성해주세요!

 

SELECT e.name, d.name
FROM employees  e INNER JOIN departments d on e.department_id = d.id

 

40.

'기술팀' 부서에 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!

 

SELECT e.name

FROM employees  e INNER JOIN departments d on e.department_id = d.id

WHERE d.name ='기술팀' 

 

41.

부서별로 직원 수를 계산하는 쿼리를 작성해주세요!

SELECT d.name, count(e.name)

FROM departments d LEFT JOIN employees e on d.id = e.department_id

GROUP BY d.id

 

 

42.

직원이 없는 부서의 이름을 찾는 쿼리를 작성해주세요!

 

SELECT d.name

FROM departments d LEFT JOIN employees e on d.id = e.department_id

WHERE e.id is null 

 

43.

'마케팅팀' 부서에만 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!

 

SELECT e.name

FROM employees  e INNER JOIN departments d on e.department_id = d.id

WHERE d.name = '마케팅팀'

 

 

44.

모든 주문의 주문 ID와 주문된 상품의 이름을 나열하는 쿼리를 작성해주세요!

 

SELECT o.id, p.name

FROM products p INNER JOIN orders o on p.id = o.product_id

45.

총 매출(price * quantity의 합)이 가장 높은 상품의 ID와 해당 상품의 총 매출을 가져오는 쿼리를 작성해주세요!

 

SELECT p.id, sum(p.price * o.quantity) total_price 

FROM products p INNER JOIN orders o on p.id = o.product_id

ORDER BY 2 desc

LIMIT 1

46.

각 상품 ID별로 판매된 총 수량(quantity)을 계산하는 쿼리를 작성해주세요!

SELECT p.id, sum(o.quantity) total_quantity 

FROM products p INNER JOIN orders o on p.id = o.product_id

GROUP BY p.id

 

47.

2023년 3월 3일 이후에 주문된 모든 상품의 이름을 나열하는 쿼리를 작성해주세요!

 

SELECT p.name

FROM products p INNER JOIN orders o on p.id = o.product_id

WHERE o.order_date > '2023-03-03' 

 

48.

가장 많이 판매된 상품의 이름을 찾는 쿼리를 작성해주세요!

 

SELECT p.name, sum(o.quantity) total_quantity

FROM products p INNER JOIN orders o on p.id = o.product_id

ORDER BY 2 desc 

LIMIT 1

49.

각 상품 ID별로 평균 주문 수량을 계산하는 쿼리를 작성해주세요!

 

SELECT p.id, AVG(o.quantity) avg_quantity

FROM products p INNER JOIN orders o on p.id = o.product_id

GROUP BY p.id 

 

50.

판매되지 않은 상품의 ID와 이름을 찾는 쿼리를 작성해주세요!

 

SELECT p.id, p.name

FROM products p LEFT JOIN orders o on p.id = o.product_id

WHERE o.id is null 

 

 

아티클 내용에 내가 배우지 못한 내용들이 많아서 

기존에 정리해둔 SQLD 정리본을 찾아보면서도 풀었고, 

챗 지피티한테 물어가면서도 풀었다. 

다만 데이터 형식에 따라서 사용하는 함수가 달라지는 부분을 캐치하지 못한 부분이 아쉬웠다ㅜㅜ