SQLZOO题选

SELECT_basics

1

SELECT population FROM world
  WHERE name = 'Germany'

2

SELECT name, population FROM world 
WHERE name in ('Sweden', 'Norway', 'Denmark')

3

SELECT name, area
From world
WHERE area BETWEEN 200000 AND 250000

SELECT_names

1

// Find the country that start with Y
SELECT name FROM world
  WHERE name LIKE 'Y%'
// The % is a wild-card it can match any characters

2

// Find the countries that end with y
SELECT name FROM world
  WHERE name LIKE '%y'

3

// Find the countries that contain the letter x
SELECT name FROM world
  WHERE name LIKE '%x%'

5

// Find the countries that start with C and end with ia
SELECT name FROM world
  WHERE name LIKE 'C%ia'

7

// Find the countries that have three or more a in the name
SELECT name FROM world
  WHERE name LIKE '%a%a%a%'

8

// Find the countries that have "t" as the second character.
SELECT name FROM world
 WHERE name LIKE '_t%'
ORDER BY name
// You can use the underscore as a single character wildcard.

10

// Find the countries that have exactly four characters.
SELECT name FROM world
 WHERE name LIKE '____'

11

// Find the country where the name is the capital city.
SELECT name
  FROM world
 WHERE name = capital
// name LIKE capital 

12

// Find the country where the capital is the country plus "City".
SELECT name
  FROM world
 WHERE capital LIKE concat(name, ' City')

14

// Find the capital and the name where the capital is an extension of name of the country.
SELECT capital, name
FROM world
WHERE capital LIKE concat(name, '_%')

15

// Show the name and the extension where the capital is an extension of name of the country.
SELECT name,REPLACE(capital,name,'') AS extension FROM world
WHERE capital Like CONCAT(name, '_%'); 
// or
SELECT name,MID(capital,length(name)+1) AS extension FROM world	
WHERE capital Like CONCAT(name, '_%');		# MID()与SUBSTRING()相同
// REPLACE('vessel', 'e', 'a') -> 'vassal'

SELECT_from_WORLD_Tutorial

8

// Exclusive OR (XOR). Show the countries that are big by area (more than 3 million) or big by population (more than 250 million) but not both. Show name, population and area.
SELECT name,population,area
FROM world
WHERE (area < 3000000 and population > 250000000) or (area >3000000 and population <250000000)

9

// For South America show population in millions and GDP in billions both to 2 decimal places.
SELECT name, ROUND(population/1000000,2), ROUND(gdp/1000000000,2) FROM world
WHERE continent = 'South America';

10

// Show per-capita GDP for the trillion dollar countries to the nearest $1000.
SELECT name, ROUND(gdp/population/1000, 0)*1000 
FROM world
WHERE gdp >= 1000000000000

11

// Show the name and capital where the name and the capital have the same number of characters.
SELECT name, capital
  FROM world
 WHERE LEN(name) = LEN(capital)

12

// Show the name and the capital where the first letters of each match. Don't include countries where the name and the capital are the same word.
SELECT name, capital
FROM world
WHERE LEFT(name, 1) = LEFT(capital, 1) AND name != capital

检索，排序，过滤，创建计算字段

distinct去重检索

// List all the continents - just once each.
SELECT DISTINCT continent
FROM world

and和not in

// Show the year, subject, and name of winners for 1980 excluding Chemistry and Medicine
SELECT yr, subject, winner
FROM nobel
WHERE yr = 1980 AND subject not in ('Chemistry', 'Medicine')

order排序

// List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.
SELECT winner, yr, subject
FROM nobel
WHERE winner LIKE 'Sir%'
ORDER BY yr DESC, winner

limit限制查询行数

// 分页查询employees表，每5行一页，返回第2页的数据
SELECT *
FROM employees
LIMIT 5, 5

聚合，分组，子查询，表链接

聚合函数

// Show the total population of the world.
SELECT SUM(population)
FROM world

sum()、max()、min()、count()函数

// 查找排除最大、最小salary之后的当前(to_date = '9999-01-01' )员工的平均工资avg_salary。
// 如果工资最高和最低的员工都只有一名
SELECT (SUM(salary)-MAX(salary)-MIN(salary))/(COUNT(salary)-2) avg_salary
FROM salaries
WHERE to_date = '9999-01-01'
// 如果工资最高和最低的员工都有多名
select avg(salary) as avg_salary 
from salaries
where to_date='9999-01-01'
and salary!=(select max(salary) from salaries where to_date='9999-01-01')
and salary!=(select min(salary) from salaries where to_date='9999-01-01');

GROUP BY指定聚合函数的计算依据，聚合函数只负责运算

// For each continent show the continent and number of countries.
SELECT continent, count(name)
FROM world
GROUP BY continent

Having基于聚合运算结果进行筛选（而不是WHERE）

// List the continents that have a total population of at least 100 million
SELECT continent 
FROM world
GROUP BY continent
HAVING SUM(population) >= 100000000

WHERE基于子查询筛选

// List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.

SELECT name, continent
FROM world
WHERE continent in (
SELECT continent 
FROM world
WHERE name in ('Argentina', 'Australia')
)
ORDER BY name

// Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)
SELECT name
FROM world
WHERE gdp > all(
SELECT gdp 
FROM world
WHERE continent = 'Europe' AND gdp is not NULL
)

表链接和case when

SELECT mdate, team1
,SUM(case when teamid = team1 then 1 else 0 end) as score1
,team2
,SUM(case when teamid = team2 then 1 else 0 end) as score2
FROM game LEFT JOIN goal ON matchid = id
GROUP BY mdate, matchid, team1, team2

窗口函数、时间函数

IS NOT NULL+CASE WHEN

// Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2 and 'Art' otherwise.
SELECT name, CASE WHEN dept in ('1', '2') THEN 'Sci' ELSE 'Art' END 
FROM teacher
WHERE name is not NULL

COALESCE处理空值

// Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string 'None' where there is no department.
SELECT teacher.name, COALESCE(dept.name, 'None')
FROM teacher LEFT JOIN dept ON teacher.dept = dept.id

窗口函数RANK() OVER

// Use PARTITION to show the ranking of each party in S14000021 in each year. Include yr, party, votes and ranking (the party with the most votes is 1).
SELECT yr, party, votes
,RANK() OVER(PARTITION BY yr ORDER BY votes DESC) posn
FROM ge 
WHERE constituency = 'S14000021'

// You can use COUNT and GROUP BY to see how each party did in Scotland. Scottish constituencies start with 'S'
// Show how many seats for each party in Scotland in 2017.
SELECT party, COUNT(*)
FROM (
SELECT constituency, party
,RANK() OVER(PARTITION BY constituency ORDER BY votes DESC) posn
FROM ge
WHERE constituency LIKE 'S%' AND yr = 2017
) rank
WHERE posn = 1
GROUP BY party

窗口函数SUM() OVER

SELECT emp_no, salary
,SUM(SALARY) OVER(ORDER BY emp_no) running_total
FROM salaries
WHERE to_date = '9999-01-01'

MONTH()和DAY()

SELECT name, DAY(whn),
 confirmed, deaths, recovered
 FROM covid
WHERE name = 'Italy'
AND MONTH(whn) = 3
ORDER BY whn