##SELECT
Strings should be in 'single quotes'
Show the population of Germany
SELECT population
FROM world
WHERE name = 'Germany'Show the per capita gdp: gdp/population for each country where the area is over 5,000,000 km2
SELECT name, gdp/population
FROM world
WHERE area > 5000000##AND ####We use AND to ensure that two or more conditions hold true.
Show the name and continent where the area is less then 2000 and the gdp is more than 5000000000
SELECT name, continent
FROM world
WHERE area < 2000
AND gdp > 5000000000##IN ####The word IN allows us to check if an item is in a list.
Show the name and the population for 'Denmark', 'Finland', 'Norway', 'Sweden'
SELECT name, population
FROM world
WHERE name
IN ('Denmark', 'Finland', 'Norway', 'Sweden')##LIKE ####The word LIKE permits pattern matching - % is the wildcard.
Show each country that begins with G
SELECT name
FROM world
WHERE name
LIKE 'G%'Show the area in 1000 square km. Show area/1000 instead of area
SELECT name, area/1000
FROM world
WHERE area
BETWEEN 207600
AND 244820##Crazy Stuff
In which years was the Physics prize awarded but no Chemistry prize. (WARNING - this question is way too hard for this level, you will need to use sub queries or joins).
SELECT DISTINCT yr
FROM nobel
WHERE subject
= 'Physics'
AND yr
NOT IN
(SELECT yr
FROM nobel
WHERE subject
= 'Chemistry')#Nested Select
List each country name where the population is larger than 'Russia'.
SELECT name
FROM world
WHERE population
>
(SELECT population
FROM world
WHERE name
= 'Russia')List the name and continent of countries in the continents containing 'Belize', 'Belgium'.
SELECT name, continent
FROM world
WHERE continent
IN
(SELECT continent
FROM world
WHERE name
IN ('Belize', 'Belgium'))##ALL ####We can use the word ALL to allow >= or > or < or <=to act over a list.
Which countries have a GDP greater than any country in Europe? [Give the name only.]
SELECT name
FROM world
WHERE gdp
> ALL
(SELECT gdp
FROM world
WHERE continent
= 'Europe')#SUM, COUNT, MAX & AVG
The functions SUM, COUNT, MAX and AVG are "aggregates", each may be applied to a numeric attribute resulting in a single row being returned by the query. (These functions are even more useful when used with the GROUP BY clause.)
The total population and GDP of Europe.
SELECT SUM(population), SUM(gdp)
FROM bbc
WHERE region = 'Europe'How many countries have an area of at least 1000000
SELECT COUNT(name)
FROM world
WHERE area
>= 1000000##GROUP BY
For each continent show the continent and number of countries.
SELECT continent, COUNT(name)
FROM world
GROUP BY continent
##DISTINCT
By default the result of a SELECT may contain duplicate rows. We can remove these duplicates using the DISTINCT key word.
What are the regions?
SELECT DISTINCT region FROM bbc##ORDER BY
ORDER BY permits us to see the result of a SELECT in any particular order. We may indicate ASC or DESC for ascending (smallest first, largest last) or descending order.
Show the name and population for each country with a population of more than 100000000. Show countries in descending order of population.
SELECT name, population
FROM bbc
WHERE population > 100000000
ORDER BY population DESC##HAVING
List the continents with total populations of at least 100 million.
SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population)
>= 100000000##JOIN ON ####You can combine two steps into a single query with a JOIN ON.
You will get all the game details and all the goal details if you use:
SELECT *
FROM game
JOIN goal
ON (id=matchid)##IS NULL / IS NOT NULL
##LIMIT
##GROUP BY
##Execute $db.execute("SELECT name FROM congress_members WHERE years_in_congress > #{minimum_years}").
##AS
##Start a sqlite session with a schema already loaded. ####sqlite3 database.db < schema.sql