Lim Seunghyun Space

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/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;

문제 출처 https://leetcode.com/problems/employees-earning-more-than-their-managers/ Employees Earning More Than Their Managers - 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 emp.name as Employee from Employee as emp join Employee as mgr on emp.managerId = mgr.id where e..

Transaction(트랜잭션) 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않게 여러 SQL들을 묶어서 하나의 작업처럼 처리하는 방법 모두 적용하거나 모두 취소되는 ALL OR NOTHING의 개념 명령어 BEGIN COMMIT / END ROLLBACK 사용법 BEGIN; SQL 1; SQL 2; ... SQL N; COMMIT; -- AUTOCOMMIT이 TRUE인 경우 END; BEGIN으로 시작하여 SQL문을 순서대로 실행하고, 실패없이 모두 성공하면 SQL문을 모두 반영시켜주는 COMMIT을 사용하거나 AUTOCOMMIT이 TRUE로 되어 있으면 END로 트랜잭션을 종료 SQL을 실행중 하나라도 에러가 발생시 BEGIN 이전 상태로 돌아감 (ROLLBACK) Reference TRANSAC..

사용법 DELETE FROM DELETE FROM -- Table 안의 모든 Record를 삭제 DELETE FROM WHERE -- Table 안의 WHERE 조건에 해당하는 Record를 삭제 TRUNCATE TRUNCATE TABLE -- 테이블에 대한 모든 Record를 제거 공통점 테이블의 레코드를 삭제해주는 용도 차이점 DELETE FROM은 조건을 이용하여 특정 레코드들만 삭제해줄 수 있다. TRUNCATE는 DELETE FROM에 비해 처리 속도가 빠르다. TRUNCATE시 바로 테이블에 적용됨 -> 트랜잭션을 사용한다 하더라도 바로 커밋되며 롤백이 불가능하다.

문제 : https://www.hackerrank.com/challenges/salary-of-employees/problem?isFullScreen=true Employee Salaries | HackerRank Print the names of employees who earn more than $2000 per month and have worked at the company for less than 10 months. www.hackerrank.com 문제 해설 Employee 테이블에서 10개월 미만 근무자 중 salary가 2000보다 큰 name을 조회 결과는 employee_id를 오른차순으로 정렬한 결과 나의 풀이 (MySQL) SELECT name FROM Employee WHERE s..