간략 정리

테이블

Customers

Id	Name
1	Joe
2	Henry
3	Sam
4	Max

orders

id	CustomerId
1	3
2	1

INNER JOIN

SELECT C.Id, C.Name, O.Id AS orderid,
FROM Customers AS C
     INNER JOIN Orders AS O ON C.Id = O.CustomerId;

Id	Name	orderid
1	Joe	2
3	Sam	1

LEFT OUTER JOIN

SELECT C.Id, C.Name, O.Id AS orderid
FROM Customers AS C
     LEFT JOIN Orders AS O ON C.Id = O.CustomerId;

Id	Name	orderid
3	Sam	1
1	Joe	2
2	Henry	null
4	Max	null

RIGHT OUTER JOIN

SELECT C.Id, C.Name AS Customers
FROM Customers AS C
     RIGHT JOIN Orders AS O ON C.Id = O.CustomerId;

FULL OUTER JOIN

SELECT C.Id, C.Name AS Customers
FROM Customers AS C
     LEFT JOIN Orders AS O ON C.Id = O.CustomerId
     
UNION

SELECT C.ID, C.Name AS Customers 
FROM Customers AS C
     RIGHT JOIN Orders AS O ON C.Id = O.CustomerId;

해커랭크 문제풀이

Population Census

문제 바로가기
Given the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is ‘Asia’.

풀이

SELECT SUM(c.population)
FROM city AS c
    INNER JOIN country AS d ON c.countrycode = d.code
where continent = 'Asia';

African Cities

문제 바로가기
Given the CITY and COUNTRY tables, query the names of all cities where the CONTINENT is ‘Africa’.

풀이

SELECT c.name
FROM city AS c
    INNER JOIN country AS d ON c.countrycode = d.code
WHERE continent = 'Africa';

Average Population of Each Continent

문제 바로가기
Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.

SELECT d.continent, floor(avg(c.population))
FROM city AS c
    INNER JOIN country AS d ON c.countrycode = d.code
GROUP BY d.continent;

리트코드 문제풀이

183. Customers Who Never Order

문제 바로가기
Write an SQL query to report all customers who never order anything.
Return the result table in any order.

Input: 
Customers table:
+----+-------+
| id | name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Orders table:
+----+------------+
| id | customerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
Output: 
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

풀이

SELECT c.name AS customers
FROM customers AS c
    LEFT JOIN orders AS o ON c.id = o.customerid
WHERE o.id IS NULL;

181. Employees Earning More Than Their Managers

문제 바로가기
Write an SQL query to find the employees who earn more than their managers.

Input: 
Employee table:
+----+-------+--------+-----------+
| id | name  | salary | managerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | Null      |
| 4  | Max   | 90000  | Null      |
+----+-------+--------+-----------+
Output: 
+----------+
| Employee |
+----------+
| Joe      |
+----------+
Explanation: Joe is the only employee who earns more than his manager.

풀이

SELECT e2.name AS Employee
FROM Employee AS e1
     join Employee AS e2 ON e1.id = e2.managerId
WHERE e1.salary < e2.salary

197. Rising Temperature

문제 바로가기
Write an SQL query to find all dates’ Id with higher temperatures compared to its previous dates (yesterday).

Input: 
Weather table:
+----+------------+-------------+
| id | recordDate | temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+
Output: 
+----+
| id |
+----+
| 2  |
| 4  |
+----+
Explanation: 
In 2015-01-02, the temperature was higher than the previous day (10 -> 25).
In 2015-01-04, the temperature was higher than the previous day (20 -> 30).

풀이

SELECT w1.id AS Id
FROM weather AS w1
    JOIN weather AS w2 ON w1.recordDate = DATE_ADD(w2.recordDate, interval 1 day)
WHERE w1.temperature > w2.temperature;