백문이불여일타
해커랭크
INNER JOIN1)
African Cities
문제:
Given the CITY and COUNTRY tables, query the names of all cities where the CONTINENT is 'Africa'.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.
Input Format
The CITY and COUNTRY tables are described as follows:
Input Format
The CITY and COUNTRY tables are described as follows:
INNER JOIN2)
Population Census
문제:
Given the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is 'Asia'.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.
Input Format
The CITY and COUNTRY tables are described as follows:
INNER JOIN3)
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.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.
Input Format
The CITY and COUNTRY tables are described as follows:
리트코드
LEFT JOIN
183 Customers Who Never Order
스키마 
테이블
Write an SQL query to report all customers who never order anything.
Return the result table in any order.
The query result format is in the following example.
SELF JOIN
: alias를 잘 줘야함
181.Employees Earning More Than Their Managers
스키마
Create table If Not Exists Employee (id int, name varchar(255), salary int, managerId int)
Truncate table Employee
insert into Employee (id, name, salary, managerId) values ('1', 'Joe', '70000', '3')
insert into Employee (id, name, salary, managerId) values ('2', 'Henry', '80000', '4')
insert into Employee (id, name, salary, managerId) values ('3', 'Sam', '60000', 'None')
insert into Employee (id, name, salary, managerId) values ('4', 'Max', '90000', 'None')
Write an SQL query to find the employees who earn more than their managers.
Return the result table in any order.
The query result format is in the following example.
다른풀이
SELECT e2.name as Employee
FROM employee e1
INNER JOIN employee e2 ON e1.id = e2.managerID
WHERE
e1.salary < e2.salary
197. Rising Temperature
Create table If Not Exists Weather (id int, recordDate date, temperature int)
Truncate table Weather
insert into Weather (id, recordDate, temperature) values ('1', '2015-01-01', '10')
insert into Weather (id, recordDate, temperature) values ('2', '2015-01-02', '25')
insert into Weather (id, recordDate, temperature) values ('3', '2015-01-03', '20')
insert into Weather (id, recordDate, temperature) values ('4', '2015-01-04', '30')
Write an SQL query to find all dates' Id with higher temperatures compared to its previous dates (yesterday).
Return the result table in any order.
The query result format is in the following example.
그러나 제출을 하면 오류가 뜬다!!
이유 : 아이디가 날짜의 오름차순이 아니다.
즉, ID를 가지고 조인을 하면 안됨
다른풀이
DATEDIFF 함수
DATEDIFF
SELECT DATEDIFF('구분자','Start_Date','End_Date')
