1. Join Query
SELECT * FROM TableA A
LEFT JOIN TableB B ON
A.key = B.key WHERE B.key IS NULL
SELECT * FROM TableA A
RIGHT JOIN TableB B ON
A.key = B.key WHERE A.key IS NULL
SELECT * FROM TableA A
INNER JOIN TableB B ON
A.key = B.key
SELECT * FROM TableA A
LEFT JOIN TableB B ON
A.key = B.key
SELECT * FROM TableA A
RIGHT JOIN TableB B ON
A.key = B.key
SELECT * FROM TableA A
FULL OUTER JOIN TableB B ON
A.key = B.key
* MySQL은 Fulll Outer Join 불가 → Union 사용하기
2. Problems
link: https://www.hackerrank.com/domains/sql?filters%5Bsubdomains%5D%5B%5D=join
Solve SQL Code Challenges
A special-purpose language designed for managing data held in a relational database.
www.hackerrank.com
[JOIN]
-- Population Census
SELECT SUM(CITY.POPULATION) FROM CITY
LEFT JOIN COUNTRY ON
CITY.COUNTRYCODE = COUNTRY.CODE
WHERE COUNTRY.CONTINENT = 'Asia'
-- African Cities
SELECT CITY.NAME FROM CITY
JOIN COUNTRY ON
CITY.COUNTRYCODE = COUNTRY.CODE
WHERE COUNTRY.CONTINENT = 'Africa'
-- Average Population of Each Continent
SELECT COUNTRY.CONTINENT, FLOOR(AVG(CITY.POPULATION)) FROM COUNTRY
JOIN CITY ON
CITY.COUNTRYCODE = COUNTRY.CODE
GROUP BY COUNTRY.CONTINENT
-- The Report
-- IF (조건, 만족, 불만족)
SELECT IF (GRADE < 8, NULL, NAME), GRADE, MARKS
FROM STUDENTS
JOIN GRADES
WHERE MARKS BETWEEN MIN_MARK AND MAX_MARK
ORDER BY GRADE DESC, NAME
[ALTERNATIVE QUERIES]
-- Draw The Triangle 1
SET @number = 21;
SELECT REPEAT('* ', @number := @number - 1)
FROM INFORMATION_SCHEMA.TABLES;
-- Draw The Triangle 2
SET @number = 0;
SELECT REPEAT('* ', @number := @number + 1)
FROM INFORMATION_SCHEMA.TABLES
WHERE @number < 20
[ADVANCED SELECT]
-- What type of traiangle
-- case when then (else) end
-- https://www.hackerrank.com/challenges/what-type-of-triangle/problem
SELECT
CASE
WHEN A+B>C AND A>C-B AND B>C-A THEN
CASE
WHEN A = B AND B = C THEN 'Equilateral'
WHEN A = B OR B = C OR A = C THEN 'Isosceles'
WHEN A != B OR B != C OR A != C THEN 'Scalene'
END
ELSE 'Not A Triangle'
END
FROM TRIANGLES
총 45문제 풀고, 골드5가 됐다. 이제 easy는 다 풀고 medium, hard 문제만 남았다🙄. 기초는 오늘의 내가 열심히 쌓았으니, 응용은 내일의 내가 열심히 해주길 바라며... 오늘은 sql 공부 끝!
'Database' 카테고리의 다른 글
[MySQL/프로그래머스]우유와 요거트가 담긴 장바구니 (0) | 2021.05.17 |
---|---|
[MySQL/프로그래머스]JOIN - 없어진 기록 찾기(SubQuery, LeftJoin, Using, On) (0) | 2021.05.17 |
[MySQL/HackerRank]Aggregation (0) | 2021.05.15 |
[MySQL/HackerRank]Basic Select (0) | 2021.05.15 |
[SQL] Join vs Sub-query, sharding (0) | 2021.04.09 |