Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL Commands

##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%'

BETWEEN

BETWEEN allows range checking - note that it is inclusive.

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.