join6 [프로그래머스 / MySQL] 성분으로 구분한 아이스크림 총 주문량 문제 💡 상반기 동안 각 아이스크림 성분 타입과 성분 타입에 대한 아이스크림의 총주문량을 총주문량이 작은 순서대로 조회하는 SQL 문을 작성해주세요. 이때 총주문량을 나타내는 컬럼명은 TOTAL_ORDER로 지정해주세요. 테이블 형태 풀이 SELECT I.INGREDIENT_TYPE, SUM(F.TOTAL_ORDER) AS TOTAL_ORDER FROM FIRST_HALF AS F JOIN ICECREAM_INFO AS I ON F.FLAVOR = I.FLAVOR GROUP BY I.INGREDIENT_TYPE ORDER BY TOTAL_ORDER 해설 FROM FIRST_HALF AS F JOIN ICECREAM_INFO AS I ON F.FLAVOR = I.FLAVOR 성분타입 별 아이스크림 총 주문.. 2024. 2. 2. [leetcode / MySQL] 185. Department Top Three Salaries 문제 💡 A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department. Write a solution to find the employees who are high earners in each of the departments. Return the result table in any order. 테이블 형태 풀이 SELECT Department, Employee, S.. 2024. 2. 1. [프로그래머스 / MySQL] 조건에 맞는 도서와 저자 리스트 출력하기 문제 💡 '경제'카테고리에 속하는 도서들의 도서 ID(BOOK_ID), 저자명(AUTHOR_NAME), 출판일(PUBLISHED_DATE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 출판일을 기준으로 오름차순 정렬해주세요. 테이블 형태 풀이 SELECT B.BOOK_ID , A.AUTHOR_NAME , DATE_FORMAT(B.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE FROM BOOK AS B JOIN AUTHOR AS A ON B.AUTHOR_ID = A.AUTHOR_ID WHERE B.CATEGORY = '경제' ORDER BY B.PUBLISHED_DATE 해설 FROM BOOK AS B JOIN AUTHOR AS A ON B.AUTHOR_ID = A.. 2024. 2. 1. [leetcode / MySQL] 1084. Sales Analysis III 문제 💡 Write a solution to report the products that were only sold in the first quarter of 2019. That is, between 2019-01-01 and 2019-03-31 inclusive. Return the result table in any order. 테이블 형태 풀이 SELECT p.product_id , product_name FROM Product as p JOIN Sales as s ON p.product_id=s.product_id GROUP BY s.product_id HAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) = '2019-01-01' AND MAX(s.. 2024. 1. 30. [leetcode / MySQL] 184. Department Highest Salary 문제 💡 Write a solution to find employees who have the highest salary in each of the departments. Return the result table in any order. 테이블 형태 풀이 SELECT D.name AS Department , E.name AS Employee , salary AS Salary FROM Employee E INNER JOIN Department D ON E.departmentId = D.id WHERE (departmentId, salary) IN (SELECT departmentId, MAX(salary) FROM Employee GROUP BY departmentId) 해설 FROM Employee E.. 2024. 1. 30. [leetcode / MySQL] 183. Customers Who Never Order 문제 💡 Write a solution to find all customers who never order anything. Return the result table in any order. 테이블 형태 풀이 SELECT name AS Customers FROM Customers AS C LEFT JOIN Orders AS O ON C.id = O.customerId WHERE O.id IS NULL ORDER BY C.id 해설 SELECT name AS Customers FROM Customers AS C LEFT JOIN Orders AS O ON C.id = O.customerId WHERE O.id IS NULL ORDER BY C.id Orders(O) 테이블에는 주문한 고객들의 custom.. 2024. 1. 29. 이전 1 다음
