next_academy_solution_Database_Drill:_SQL_Tutor.md

#SQL notes

SELECT within SELECT

select each country in Europe and show their population in percentage of population of Germany

SELECT name, CONCAT(ROUND(population/(SELECT population FROM world WHERE NAME = 'Germany')*100), '%') 
FROM world
WHERE continent = 'Europe'

ALL to run through a database list made with SELECT
- select countries with gdp higher than every country in europe ( gdp > 0 to avoid comparing NULL values )
```
SELECT name FROM world
WHERE gdp > ALL( SELECT gdp FROM world WHERE continent = 'Europe' AND gdp > 0 )
```

Scoping in SQL

Without using GROUP BY (need the details for rows involved)

Finding the largest country (by area) for each continent

SELECT continent, name, area FROM world x
WHERE area >= ALL( 
  SELECT area FROM world y
  WHERE x.continent = y.continent
  AND area > 0)

With GROUP BY (only suitable for aggregate functions)
- For each continent show the number of countries
```
SELECT continent, COUNT(name)
FROM world
GROUP BY continent
```

The HAVING clause allows use to filter the groups which are displayed. The WHERE clause filters rows before the aggregation, the HAVING clause filters after the aggregation.

WHERE

SELECT continent, COUNT(name)
FROM world
WHERE population>200000000
GROUP BY continent

HAVING

SELECT continent, SUM(population)
FROM world
GROUP BY continent
HAVING SUM(population)>500000000

JOIN -> left table will duplicate for right table

#SQL Zoo

SELECT within SELECT

8. List each continent and the name of the country that comes first alphabetically.

# Answer 1
SELECT continent, name FROM world x
WHERE name =
  (SELECT name FROM world y
  WHERE x.continent = y.continent
  ORDER BY name 
  LIMIT 1)

# Answer 2
SELECT continent, name FROM world x
WHERE name <= 
  ALL(SELECT name FROM world y
  WHERE y.continent = x.continent
  ORDER BY name)

9. Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.

SELECT name, continent, population FROM world x
WHERE 25000000 >= 
  ALL(SELECT population FROM world y
  WHERE y.continent = x.continent
  AND population > 0)
ORDER by name

10. Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

# Answer 1 (with <>, not taught by sqlzoo)
SELECT name, continent FROM world x
WHERE population >=  
  ALL(SELECT population*3 FROM world y
  WHERE y.continent = x.continent
  AND y.name <> x.name
  AND population > 0)

# Answer 2 (with only syntaxes taught by sqlzoo)
SELECT name, continent FROM world x
WHERE population >=  
  ALL(SELECT population*3 FROM world y
  WHERE y.continent = x.continent
  AND NOT y.name = x.name
  AND population > 0)

SUM and COUNT for nobel table

9. Show the years in which three prizes were given for Physics.

SELECT yr FROM nobel
WHERE subject = 'Physics'
GROUP BY yr
HAVING COUNT(winner) = 3

*10.*Show winners who have won more than once

SELECT winner FROM nobel
GROUP BY winner
HAVING COUNT(winner) > 1

11. Show winners who have won more than one subject.

SELECT winner FROM nobel
GROUP BY winner
HAVING COUNT(DISTINCT(subject)) > 1

12. Show the year and subject where 3 prizes were given. Show only years 2000 onwards.

SELECT yr,subject FROM nobel
WHERE yr>= 2000
GROUP BY yr,subject
HAVING COUNT(winner) = 3

13. List every match with the goals scored

# this question is buggy imo
# the original answer should look like 
SELECT mdate, team1,
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 JOIN goal ON matchid = id
GROUP BY id
ORDER BY mdate, matchid, team1, team2

# but turns out it wants matches with no goals too
# which in this case LEFT JOIN must be used (isnt taught in this chapter yet)
SELECT mdate,
team1,
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 id
ORDER BY mdate DESC

KevinSia/next_academy_solution_Database_Drill:_SQL_Tutor.md