[MySQL/HackerRank]Basic Join, Advanced Select 上

1. Join Query

Left Join

SELECT * FROM TableA A
LEFT JOIN TableB B ON
A.key = B.key WHERE B.key IS NULL

Right Join

SELECT * FROM TableA A 
RIGHT JOIN TableB B ON 
A.key = B.key WHERE A.key IS NULL

 

Inner Join

SELECT * FROM TableA A 
INNER JOIN TableB B ON 
A.key = B.key

Left Join

SELECT * FROM TableA A
LEFT JOIN TableB B ON
A.key = B.key

Right Join

SELECT * FROM TableA A
RIGHT JOIN TableB B ON
A.key = B.key

Full Outer Join

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

 

[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 공부 끝!