news0516 님의 블로그

44.모든 주문의 주문 ID와 주문된 상품의 이름을 나열하는 쿼리를 작성해주세요! select O.id, P.name  from product p inner join `ORDER` O on P.id = O.product_id 45. 총 매출(price * quantity의 합)이 가장 높은 상품의 ID와 해당 상품의 총 매출을 가져오는 쿼리를 작성해주세요! select p.id, price * quantity as from product p inner join  `ORDER` O on P.id = O.product_id  order by 2 desc limit 1 46. 각 상품 ID별로 판매된 총 수량(quantity)을 계산하는 쿼리를 작성해주세요! select p.id, o.quantity  fro..

38. 현재 존재하고 있는 총 부서의 수를 구하는 쿼리를 작성해주세요! SELECT count(DISTINCT name) FROM departments39. 모든 직원과 그들이 속한 부서의 이름을 나열하는 쿼리를 작성해주세요! SELECT e.name, d.name FROM employees e left join departments d on e.department_id = d.id40. '기술팀' 부서에 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요! SELECT e.name, d.name FROM employees e left join departments d on e.department_id = d.id where d.name='기술팀'; 41. 부서별로 직원 수를 계산하는 쿼리를 작성해주세요!..

34. patients 테이블에서 각 성별(gender)에 따른 환자 수를 계산하는 쿼리를 작성해주세요! SELECT gender, COUNT(1) as count_of_gender FROM patients group by 135. patients 테이블에서 현재 나이가 40세 이상인 환자들의 수를 계산하는 쿼리를 작성해주세요! SELECT count(1) as conut_of_age FROM patients  WHERE birth_date 36. patients 테이블에서 마지막 방문 날짜(last_visit_date)가 1년 이상 된 환자들을 선택하는 쿼리를 작성해주세요! SELECT name, last_visit_date FROM patients Where last_visit_date 37. pat..

30. doctors 테이블에서 전공(major)가 성형외과인 의사의 이름을 알아내는 쿼리를 작성해주세요! SELECT name,  major  FROM doctors WHERE major = '성형외과'31. doctors 테이블에서 각 전공 별 의사 수를 계산하는 쿼리를 작성해주세요! SELECT major, count(name) FROM doctors group by 132. doctors 테이블에서 현재 날짜 기준으로 5년 이상 근무(hire_date)한 의사 수를 계산하는 쿼리를 작성해주세요! SELECT count(name) FROM doctors Where hire_date 33. doctors 테이블에서 각 의사의 근무 기간을 계산하는 쿼리를 작성해주세요! SELECT name, 2024-..

1.lol_users 테이블에서 각 유저의 레이팅(rating) 순위를 계산하는 쿼리를 작성해주세요!  전체 지역(region) 기준이고 순위는 레이팅이 높을수록 높아야해요. (e.g. rating 1400 유저의 순위 > rating 1350 유저의 순위) SELECT name, rating, RANK() OVER(ORDER BY rating DESC) AS "순위" FROM lol_users22. lol_users 테이블에서 가장 늦게 게임을 시작한(join_date) 유저의 이름을 선택하는 쿼리를 작성해주세요 SELECT name, join_date  FROM lol_users  order by 2 desc  limit 123. lol_users 테이블에서 지역별로 레이팅이 높은 순으로 유저들을 정..

17. SELECT name FROM team_projects WHERE aws_cost >= 40000 18. SELECT * FROM team_projects WHERE start_date in (2022) 19. SELECT * FROM team_projects WHERE current_date BETWEEN start_date AND end_date 20. SELECT *,  DATEDIFF(day, end_date, start_date) as "기간" from team_projects

13. sparta_students 테이블에서 모든 학생의 이름(name)과 트랙(track)을 선택하는 쿼리를 작성해주세요! SELECT name, track FROM sparta_student 14. sparta_students 테이블에서 Unity 트랙 소속이 아닌 학생들을 선택하는 쿼리를 작성해주세요! SELECT name FROM sparta_student WHERE track NOT LIKE 'UNITY' /SELECT * FROM sparta_students WHERE track  'Unity'  15. sparta_students 테이블에서 입학년도(enrollment_year)가 2021년인 학생과 2023년인 학생을 선택하는 쿼리를 작성해주세요! SELECT name From spart..

9. orders 테이블에서 주문 수량(amount)이 2개 이상인 주문을 진행한 소비자의 ID(customer_id)만 선택하는 쿼리를 작성해주세요! SELECT customer_id FROM orders WHERE amount >= 2  10. orders 테이블에서 2023년 11월 2일 이후에 주문된 주문 수량(amount)이 2개 이상인 주문을 선택하는 쿼리를 작성해주세요! SELECT id FROM orders WHERE order_date > '2023-11-02' and amount >= 2  11. orders 테이블에서 주문 수량이 3개 미만이면서 배송비(shipping_fee)가 15000원보다 비싼 주문을 선택하는 쿼리를 작성해주세요! SELECT id FROM orders WHER..

5. products 테이블에서 제품 이름(product_name)과 가격(price)만을 선택하는 쿼리를 작성해주세요. SELECT product_name, price FROM products 6. products 테이블에서 제품 이름에 '프로'가 포함된 모든 제품을 선택하는 쿼리를 작성해주세요. SELECT product_name FROM products  Where product_name like '%프로%' 7. products 테이블에서 제품 이름이 '갤'로 시작하는 모든 제품을 선택하는 쿼리를 작성해주세요. SELECT product_name FROM products  Where product_name like '갤%' 8. products 테이블에서 모든 제품을 구매하기 위해 필요한 돈을 계..

1. sparta_employees 테이블에서 모든 직원의 이름(name)과 직급(position)을 선택하는 쿼리를 작성해주세요. SELECT name, potiton FROM sparta_employees 2. sparta_employees 테이블에서 중복 없이 모든 직급(position)을 선택하는 쿼리를 작성해주세요. SELECT DISTINCT position FROM sparta_employees 3. sparta_employees 테이블에서 연봉(salary)이 40000과 60000 사이인 직원들을 선택하는 쿼리를 작성해주세요. SELECT name,  salary From sparta_employees where salary BETWEEN 40000 and 60000 4. sparta_e..