[SQL] LeetCode Easy Database Problem (1~10)

1. Combine Two Table (LEFT JOIN)

Person, Address 두 테이블

SELECT A.FirstName, A.LastName, B.City, B.State 
FROM Person A
LEFT JOIN Address B
ON A.PersonId = B.PersonId

// 그냥 JOIN = INNER JOIN

2. Second Hightest Salary (LIMIT, OFFSET)

SELECT E.Salary
FROM Employee E
ORDER BY E.Salary desc
LIMIT 1 OFFSET 1

//LIMIT A 출력할 행의 수
//OFFSET B 몇 번째 ROW부터 출력할지
//=LIMIT B, A
//SQL에서 첫번쨰 ROW = 0

//USING SUBQUERY 
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee 
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee)

3. Employee Earning More Than Their Managers (SELF JOIN)

SELECT A.Name
FROM Employee A
JOIN Employee B //INNER JOIN 가능 어차피 매니저는 제외되니까
ON A.ManagerId = B.Id
WHERE A.Salary > B.Salary

//WHERE 제외
SELECT A.Name
FROM Employee A
JOIN Employee B 
ON A.ManagerId = B.Id AND A.Salary > B.Salary
//조건을 조인 조건으로 넣어줘도 됨.

4. Duplicate Emails (HAVING, DISTINCT)

SELECT Email
FROM Person P
GROUP BY Email
**HAVING** COUNT(P.Email) > 1

SELECT Email
FROM (SELECT Email, COUNT(Email) AS c
			FROM Person
			GROUP BY Email
)AS **temp**
WHERE c > 1

SELECT **DISTINCT** A.Email
FROM Person A
JOIN Person B
ON A.Email = B.Email AND A.Id != B.Id //중복된다는 뜻

5. Customers who Never Order (IS NULL, NOT IN)

SELECT C.Name
FROM Customer C
LEFT JOIN Orders O
ON C.Id = O.CustomerId
WHERE O.CustomerId IS NULL

SELECT Id
FROM Customer
WHERE Id NOT IN (SELECT CustomerId From Orders)

6. Delete Duplicate Emails (DELETE)

SELECT Id, Email
FROM Person
WHERE Id IN (SELECT MIN(Id)
	FROM Person 
	GROUP BY Email)

DELETE (FROM) P2
FROM PERSON P1 
JOIN PERSON P2
ON P1.Email = P2.Email
AND P1.Id < P2.Id

DELETE 부서
FROM 직원
WHERE 직원.이름 = 스콧
AND 부서.부서넘버 = 직원.부서넘버

7. Rising Temperature 

SELECT A.Id
FROM Weather A
JOIN Weather B
ON DATEDIFF( A.recordDate, B.recordDate) = 1
WHERE A.Temperature > B.Temperature

8. Game Play Analysis

SELECT player_id, MIN(event_Date) as first_login
FROM Activity
GROUP BY player_id

9. Game Play Analysis 2

SELECT player_id, device_id
FROM (SELECT player_id, device_id, MIN(event_id) as firstlogin
			FROM Activity
			GROUP BY player_id) a
JOIN Activity b
ON a.firstlogin = b.event_date
AND a.player_id = b.player_id 

10. Big Countries

SELECT Name, Population, area
FROM World
WHERE area > 3000000 OR population > 25000000