SQL (40) 썸네일형 리스트형 [SQL] LeetCode: 180. Consecutive Numbers 180. Consecutive Numbers 문제 Write an SQL query to find all numbers that appear at least three times consecutively. Return the result table in any order. 풀이1) JOIN 사용 SELECT DISTINCT a.num AS ConsecutiveNums FROM logs a LEFT JOIN logs b ON b.id = ( a.id + 1) LEFT JOIN logs c ON c.id = ( a.id + 2) WHERE a.num = b.num AND b.num= c.num 풀이2) 윈도우 함수 - LAG함수 사용 + FORM절 서브쿼리 SELECT DISTINCT num AS Conse.. [SQL] HackerLank : Challenges 문제 Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challeng.. [SQL] LeetCode: 184. Department Highest Salary 184. Department Highest Salary 문제 Write an SQL query to find employees who have the highest salary in each of the departments. Return the result table in any order. # 부서별로 가장 높은 연봉을 받는 직원 출력 똑같은 연봉을 받는 직원이 여러명이면 모두 출력해야 함. 나의 풀이 : JOIN 후 WHERE절 서브쿼리 이용 SELECT d.name AS Department ,e.name AS Employee ,e.salary AS Salary FROM employee e INNER JOIN department d ON e.departmentid = d.id WHERE (e.dep.. [SQL] HarkerLank: Top Earners 문제 We define an employee's total earnings to be their monthly salary X months worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table. Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as 2 space-separated integers. 풀이1) GROUP B.. [SQL] LeetCode: 196. Delete Duplicate Emails 196. Delete Duplicate Emails 문제 Write an SQL query to delete all the duplicate emails, keeping only one unique email with the smallest id. Note that you are supposed to write a DELETE statement and not a SELECT one. After running your script, the answer shown is the Person table. The driver will first compile and run your piece of code and then show the Person table. The final order of the Perso.. [SQL] LeetCode: 627. Swap Salary 627. Swap Salary 문제 Write an SQL query to swap all 'f' and 'm' values (i.e., change all 'f' values to 'm' and vice versa) with a single update statement and no intermediate temporary tables. Note that you must write a single update statement, do not write any select statement for this problem. 풀이 UPDATE salary SET sex = IF(sex = 'f', 'm','f') +) 다른 풀이 UPDATE salary SET sex = CASE WHEN sex = 'f' .. SQL로 EDA해보기(2) 데이터셋: Brazilian E-Commerce Public Dataset by Olist olist_orders_dataset → orders olist_order_payments_dataset → payments olist_customers_dataset → customers 1. orders 테이블 안에 몇 일 치의 데이터가 들어있나요? order_purchase_timestamp 컬럼을 기준으로 데이터의 시작 시점과 끝 시점을 알려주세요. 2. 고객들은 어떤 주(state)에 살고 있나요? customers 테이블 안에 customer_state 컬럼을 참고하여 olist 고객들이 어떤 주에 살고 있는지 알아봅시다. 이 정보를 주문 데이터(orders)와 연결하려면 어떤 컬럼을 사용해야 하나요? .. [SQL] HackerRank: Symmetric Pairs 문제 You are given a table, Functions, containing two columns: X and Y. Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1. Write a query to output all such symmetric pairs in ascending order by the value of X. List the rows such that X1 ≤ Y1. -> 대칭되는 쌍이 있는 (x,y) 찾기 풀이 SELECT f1.x ,f1.y FROM functions AS f1 INNER JOIN functions AS f2 ON f1.x = f2.y AND f1.y =f2.x.. 이전 1 2 3 4 5 다음
