구름 아카이브

Customers Who Never Order https://leetcode.com/problems/Customers-Who-Never-Order/ SELECT name AS Customers FROM Customers AS c LEFT JOIN Orders AS o ON o.customerId = c.id WHERE o.id is null

Employees Earning More Than Their Managers https://leetcode.com/problems/Employees-Earning-More-Than-Their-Managers/ SELECT employee.name AS Employee FROM employee INNER JOIN employee as Mannager ON employee.managerID = Mannager.id WHERE employee.salary > Mannager.salary

Rising Temperature https://leetcode.com/problems/Rising-Temperature/ SELECT today.id FROM weather AS today INNER JOIN weather AS yesterday ON DATE_ADD(yesterday.recordDate, INTERVAL 1 DAY) = today.recordDate WHERE today.temperature > yesterday.temperature # # {"headers": ["id", "recordDate", "temperature", "id", "recordDate", "temperature"], # # "values": # # [[2, "2015-01-02", 25, 1, "2015-01-0..

Big Countries https://leetcode.com/problems/Big-Countries/ SELECT name, population, area FROM WORLD WHERE area >=3000000 OR population >=25000000

Not Boring Movies https://leetcode.com/problems/Not-Boring-Movies/ SELECT id, movie, description, rating FROM Cinema WHERE MOD(id,2)= 1 AND description != 'boring' ORDER BY rating DESC

Duplicate Emails https://leetcode.com/problems/Duplicate-Emails/ SELECT email FROM Person GROUP BY Person.email HAVING COUNT(email) > 1

Combine Two Tables https://leetcode.com/problems/Combine-Two-Tables/ SELECT firstName, lastName, city, state FROM Person LEFT JOIN Address ON Person.personId =Address.personId # {"headers": # ["firstName", "lastName", "city", "state"], # "values": # [["Allen", "Wang", null, null], # ["Bob", "Alice", "New York City", "New York"]]}

*** 다시 풀어볼 것 Delete Duplicate Emails. - delete에 조인 활용하기 >>> 각각 지울 꺼 조인 활용해서 한번에 몰아서 지우는 방식 https://leetcode.com/problems/Delete-Duplicate-Emails/

https://leetcode.com/problems/Delete-Duplicate-Emails/ # email 가지고 group by를 한 뒤, 이 이메일이 가지고 있는 id값이 무엇이냐 # WHERE 절에 들어간 min ID들을 제외하고 삭제 # 서브쿼리가 동작하지 않음 DELETE >>> 지울 테이블 지정(조인을 활용 할 경우) FROM Person >>> 삭제를 해주고 싶은 테이블 명 WHERE Id NOT IN ( SELECT sub.min_id FROM( SELECT Email, MIN(id) AS min_id FROM Person GROUP BY Email ) sub )