Lim Seunghyun Space

문제 출처 https://leetcode.com/problems/capital-gainloss/description/ Capital Gain/Loss - LeetCode Can you solve this real interview question? Capital Gain/Loss - Table: Stocks +---------------+---------+ | Column Name | Type | +---------------+---------+ | stock_name | varchar | | operation | enum | | operation_day | int | | price | int | +------------ leetcode.com 문제 해설 각 stock_name별 Gain 혹은 Loss의..

LeetCode 문제 중 테이블에 중복된 데이터를 삭제하는 문제가 있다. 오로지 DELETE 문만 사용해야 하며 SELECT 문을 이용할 수 없다. DELETE JOIN은 중복된 데이터나 같은 rows나 테이블에 관련된 rows들을 삭제할 때 사용한다. Syntax DELETE table_a FROM table_a JOIN table_b ON table_a.attribute_name = table_b.attribute_name WHERE condition; 데이터 조회시 사용했었던 JOIN 문법과 유사하다. Referece Problem: https://leetcode.com/problems/delete-duplicate-emails/description/ https://www.geeksforgeeks...

LeetCode의 문제 중 칼럼 내의 성별을 남자는 여자로, 여자는 남자로 변경하는 SQL을 작성하는 문제가 있었다. 문제는 별도의 임시 테이블을 생성하지 않고 UPDATE문을 이용하는 게 조건이었다. CASE-WHEN을 이용하여 간단하게 작성할 수 있지만 UPDATE Salary SET sex = CASE WHEN sex = "f" THEN "m" WHEN sex = "m" THEN "f" END; 다른 방법으로도 할 수 있을거 같아서 조사해 보았다. 다른 방법으로는 조건 검증에 사용되는 IF() 함수를 사용하는 것으로 Syntax는 IF(condition, true_value, false_value) condition이 참이면 true_value로 거짓이면 false_value를 반환한다. IF() ..

Source https://leetcode.com/problems/market-analysis-i/ Market Analysis I - LeetCode Can you solve this real interview question? Market Analysis I - Table: Users +----------------+---------+ | Column Name | Type | +----------------+---------+ | user_id | int | | join_date | date | | favorite_brand | varchar | +----------------+---------+ u leetcode.com 문제 2019년도에 주문 이력이 있는 user_id와 join_date 그리고..

Source https://leetcode.com/problems/exchange-seats/description/ Exchange Seats - LeetCode Can you solve this real interview question? Exchange Seats - Table: Seat +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | student | varchar | +-------------+---------+ id is the primary key column for this table. Ea leetcode.com 문제 연속된 두 학생의 ID를 교환하는 쿼리를 작성 마지막 학생의 ..

문제 출처 https://leetcode.com/problems/rising-temperature/ Rising Temperature - LeetCode Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com 나의 풀이(MySQL) SELECT w2.id FROM Weather AS w1 JOIN Weather As w2 ON DATE(w1.recordDate) = DATE_SUB(w2.recordDate, INTERVAL 1 DAY) WHERE w1.temperature < w2.te..

문제 출처 https://leetcode.com/problems/delete-duplicate-emails/ Delete Duplicate Emails - LeetCode Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com 나의 풀이(MySQL) DELETE P1 FROM Person AS P1 JOIN Person AS P2 ON P1.email = P2.email WHERE P1.id > P2.id;

문제 출처 https://leetcode.com/problems/department-highest-salary/ Department Highest Salary - LeetCode Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com 나의 풀이(MySQL) select dep.name as Department, emp.name as Employee, emp.salary as Salary from -- part별 최대 급여를 받는 사람을 조회 (select e.name, e.salary,..

문제 출처 https://leetcode.com/problems/customers-who-never-order/ Customers Who Never Order - LeetCode Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com 나의 풀이 (not in 사용) select c.name as Customers from Customers as c where c.id not in (select distinct(customerId) from Orders) 나의 풀이(not exists 사..

문제 출처 https://leetcode.com/problems/duplicate-emails/ Duplicate Emails - LeetCode Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com 나의 풀이 select email as Email from Person group by email having count(email) > 1;