【SQLZOO学习打卡】Tutorial：Section A

Part 1. SELECT basics

@ World table：

1. Where clause: show the item

Note: strings (pieces of text that are data) should be in 'single quotes';

SELECT population FROM world
  WHERE name = 'Germany'

2. In: check if an item is in a list

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

3. Between: allows range checking (range specified is inclusive of boundary values) 

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

Part2. SELECT from WORLD Tutorial 

@ World table：

1. Where clause: filter records

select name, population, area from world
where area>=3000000 or population>=250000000

 2. Calculate per capita GDP = GDP/population

select name, GDP/population as 'per capita GDP'  from world
where population>=200000000

3. Item In millions: Divide item by 1000000

select name, population/1000000 'population in millions' from world
where continent='South America'

4. Item Like '%letter%' : Item includes 'letter'

select name from world
where name like '%United%'

5. XOR (Exclusive OR): one or the other (but not both)

select name, population, area from world
where area>=3000000 xor population>=250000000

6. ROUND(f,p): returns f rounded to p decimal places

select name, round(population/1000000,2), round(GDP/1000000000,2) from world
where continent='South America'

 7. ROUND(f,p): round to the nearest 10 (when p is -1) 

select name, round(GDP/population,-3) as 'Per-capita GDP' from world
where GDP >= 1000000000000
#trillion(1000000000000; that is 12 zeros)

8. LENGTH:  find the number of characters in a string 

select name,capital from world
where length(name)=length(capital)

9. LEFT(s,n): extract n characters from the start of the string 's'

select name, capital from world
where left(name,1)=left(capital,1) and name<>capital

10. Item NOT Like '%letter%' : exclude characters from results

select name from world
where name like '%a%'
and name like '%e%'
and name like '%i%'
and name like '%o%'
and name like '%u%'
and name not like '% %'

Part 3. SELECT from Nobel Tutorial 

@ Nobel table： 

1. * : all details 

 

select * from nobel
where winner in ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter', 'Barack Obama')

2. OR : together with 

select yr, subject, winner from nobel
where (subject='Physics' and yr=1980) or (subject='Chemistry' and yr=1984)

3. NOT IN: excluding 

select * from nobel
where yr= 1980 and
subject not in ('Chemistry', 'Medicine')

4. '' (2 single quotes): escaping single quotes within a quoted string

select * from nobel
where winner='EUGENE O''NEILL'

5. Order by

select winner, yr, subject from nobel
where winner like 'Sir%'
order by yr desc, winner

6. List order:  subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1 

select winner, subject from nobel
where yr=1984
order by (subject in ('Chemistry','Physics')), subject, winner