SQLZOO Solved Query

1. Tutorials: Learn SQL in stages

0 SELECT basics
Some simple queries to get you started
1 SELECT name
- Some pattern matching queries
2 SELECT from World
- In which we query the World country profile table.
3 SELECT from Nobel
- Additional practice of the basic features using a table of Nobel Prize winners.
4 SELECT within SELECT
- In which we form queries using other queries.
5 SUM and COUNT
- In which we apply aggregate functions. more the same
6 JOIN
- In which we join two tables; game and goals. previously music tutorial
7 More JOIN operations
- In which we join actors to movies in the Movie Database.
8 Using Null
- In which we look at teachers in departments. previously Scottish Parliament
8 Numeric Examples
- In which we look at a survey and deal with some more complex calculations.
9 Self join
- In which we join Edinburgh bus routes to Edinburgh bus routes.

2.1. ALL = 모두 반환 <-> distinct 중복제거 1개 반환
2.2. 서브쿼리에서 부모쿼리의 where에서 하나의 행과 나머지 행을 비교하려면 서브쿼리에 all을 붙여 주어야 함 ex) 4-8: SELECT name, continent FROM world x WHERE population > ALL (SELECT population*3 FROM world y WHERE x.continent=y.continent AND x.name <> y.name)
2.3. GROUP BY 는 sum제외한 나머지 항목 해주면 됨 ex) SELECT mdate, team1,
2.4. SUM(CASE WHEN teamid = team1 THEN 1 ELSE 0 END) score1, team2, SUM(CASE WHEN teamid = team2 THEN 1 ELSE 0 END) score2 FROM game LEFT JOIN goal ON matchid = id GROUP BY mdate, matchid, team1, team2
2.5. 이너 조인을 레프트조인으로 바꿔 줄 수 있음
2.6. mysql 디폴트 조인 = 이너조인 = 교차조인
2.7. coalesce = ifnull=nullif=nvl과 같음. ex) coalesce(party,’none’) -> party가 NULL이면 ‘none’로 치환함
2.8. limi은 수백만 row 에서 샘플만 보기 위한 커맨드

Name		Name	Last commit message	Last commit date
Latest commit History 14 Commits
0_SELECT_basics.sql		0_SELECT_basics.sql
1_SELECT_name.sql		1_SELECT_name.sql
2_SELECT_from_World.sql		2_SELECT_from_World.sql
3_SELECT_from_Nobel.sql		3_SELECT_from_Nobel.sql
4_SELECT_within_SELECT.sql		4_SELECT_within_SELECT.sql
5.1_SUM_and_COUNT_practice.sql		5.1_SUM_and_COUNT_practice.sql
5_SUM_and_COUNT.sql		5_SUM_and_COUNT.sql
6_JOIN.sql		6_JOIN.sql
6_Music_Tutorial.sql		6_Music_Tutorial.sql
7_More_JOIN_operations.sql		7_More_JOIN_operations.sql
8+_Numeric_Examples.sql		8+_Numeric_Examples.sql
8_Scottish_Parliament_practice.sql		8_Scottish_Parliament_practice.sql
8_Using_Null.sql		8_Using_Null.sql
9_Self_join.sql		9_Self_join.sql
Readme.md		Readme.md