Skip to content

chesterheng/sql-zoo

BranchesTags

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

15 Commits
.vscode
.vscode
 
 
README.md
README.md
 
 

Repository files navigation

Live SQL Zoo Study Guide

Table of Contents

SQL Guide

Table Keywords Filters & Values
SELECT column1, AGGREGATE (column2) DISTINCT Binary Numeric and string comparison operator: =, <, <=, >, >=, !=, <>, ()
FROM [table A] LIMIT Add ALL or ANY when right side of the operator have multiple values
JOIN [table B] ASC List one value: = value or IS NULL
ON A.column=B.column DESC List many values: IN (value1, value2, ...)
WHERE [filter rows, then group] Inclusive ranges: BETWEEN min AND max
GROUP BY Logical Operators: NOT, AND, OR, XOR
HAVING [filter groups] Aggregate Functions: MIN, MAX, AVG, SUM, or COUNT
ORDER BY [sort rows]

  • The WHERE clause is applied before the GROUP BY clause.

  • The WHERE clause applies the condition to individual rows before the rows are summarized into groups by the GROUP BY clause.

  • The HAVING clause is applied after the GROUP BY clause.

  • The HAVING clause applies the condition to the groups after the rows are grouped into groups.

SELECT basics

world Table

name continent area population gdp
Afghanistan Asia 652230 25500100 20343000000
Albania Europe 28748 2831741 12960000000
Algeria Africa 2381741 37100000 188681000000
Andorra Europe 468 78115 3712000000
Angola Africa 1246700 20609294 100990000000

⬆ back to top

show the population of Germany

SELECT population 
FROM world 
WHERE name = 'Germany'
population
83149300

⬆ back to top

Show the name and the population for 'Sweden', 'Norway' and 'Denmark'

SELECT name, population 
FROM world
WHERE name IN ('Sweden', 'Norway', 'Denmark');
name population
Denmark 5822763
Norway 5367580
Sweden 10338368

⬆ back to top

show the country and the area for countries with an area between 200,000 and 250,000

SELECT name, area 
FROM world
WHERE area BETWEEN 200000 AND 250000
name area
Belarus 207600
Ghana 238533
Guinea 245857
Guyana 214969
Laos 236800
Romania 238391
Uganda 241550
United Kingdom 242900

⬆ back to top

SELECT from world

world Table

name continent area population gdp
Afghanistan Asia 652230 25500100 20343000000
Albania Europe 28748 2831741 12960000000
Algeria Africa 2381741 37100000 188681000000
Andorra Europe 468 78115 3712000000
Angola Africa 1246700 20609294 100990000000

⬆ back to top

show the name, continent and population of all countries

SELECT name, continent, population 
FROM world
name continent population
Afghanistan Asia 25500100
Albania Europe 2831741
Algeria Africa 37100000
Andorra Europe 78115
Angola Africa 20609294

⬆ back to top

Show the name for the countries that have a population of at least 200 million

SELECT name 
FROM world
WHERE population >= 200000000
name
Brazil
China
India
Indonesia
Nigeria
Pakistan
United States

⬆ back to top

Give the name and the per capita GDP for those countries with a population of at least 200 million

SELECT name, gdp/population as 'per capita GDP' 
FROM world
WHERE population >= 200000000
name per capita GDP
Brazil 9721.37
China 8724.3064
India 1891.7811
Indonesia 3804.7723
Nigeria 1822.8862
Pakistan 1377.0363
United States 59121.1921

⬆ back to top

Show the name and population in millions for the countries of the continent 'South America'

SELECT name, population/1000000 as 'population in millions' 
FROM world
WHERE continent = 'South America'
name population in millions
Argentina 44.9387
Bolivia 11.4699
Brazil 211.4426
Chile 19.1072
Colombia 49.3957
Ecuador 17.4729
Guyana 0.7828
Paraguay 7.2527
Peru 32.1314
Saint Vincent and the Grenadines 0.1106
Suriname 0.5814
Uruguay 3.5186
Venezuela 32.2195

⬆ back to top

Show the name and population for France, Germany, Italy

SELECT name, population 
FROM world
WHERE name IN ('France', 'Germany', 'Italy')
name population
France 67076000
Germany 83149300
Italy 60238522

⬆ back to top

Show the countries which have a name that includes the word 'United'

SELECT name 
FROM world
WHERE name LIKE '%United%'
name
United Arab Emirates
United Kingdom
United States

⬆ back to top

Show the countries that are big by area or big by population.

-- Show name, population and area
SELECT name, population, area 
FROM world
WHERE area > 3000000 OR population > 250000000
name population area
Australia 25690023 7692024
Brazil 211442625 8515767
Canada 38007166 9984670
China 1402378640 9596961
India 1361503224 3166414
Indonesia 266911900 1904569
Russia 146745098 17125242
United States 329583916 9826675

⬆ back to top

Show the countries that are big by area (more than 3 million) or big by population (more than 250 million) but not both

-- Exclusive OR (XOR)
-- Show name, population and area.
SELECT name, population, area 
FROM world
WHERE area > 3000000 XOR population > 250000000
name population areav
Australia 25690023 7692024
Brazil 211442625 8515767
Canada 38007166 9984670
Indonesia 266911900 1904569
Russia 146745098 17125242

⬆ back to top

For South America show population in millions and GDP in billions both to 2 decimal places

SELECT 
  name, 
  ROUND(population/1000000, 2) as 'population in millions', 
  ROUND(gdp/1000000000, 2) as 'GDP in billions'  
FROM world
WHERE  continent = 'South America'
name population in millions GDP in billions
Argentina 44.94 637.49
Bolivia 11.47 37.51
Brazil 211.44 2055.51
Chile 19.11 277.08
Colombia 49.4 309.19
Ecuador 17.47 104.3
Guyana 0.78 3.09
Paraguay 7.25 29.44
Peruv 32.13 211.4
Saint Vincent and the Grenadines 0.11 0.73
Suriname 0.58 5.21
Uruguay 3.52 59.18
Venezuela 32.22 255.09

⬆ back to top

Show per-capita GDP for the trillion dollar countries to the nearest $1000

SELECT name, ROUND(gdp/population, -3) as 'per-capita GDP' 
FROM world
WHERE gdp >= 1000000000000
name per-capita GDP
Australia 55000
Brazil 10000
Canada 43000
China 9000
France 39000
Germany 44000
India 2000
Indonesia 4000
Italy 32000
Japan 39000
Mexico 9000
Russia 10000
South Korea 22000
Spain 28000
United Kingdom 40000
United States 59000

⬆ back to top

Show the name and capital where the name and the capital have the same number of characters

SELECT name, capital 
FROM world
WHERE LENGTH(name) = LENGTH(capital)
name capital
Algeria Algiers
Angola Luanda
Armenia Yerevan
Botswana Gaborone
Cameroon Yaoundé
Canada Ottowa
Djibouti Djibouti
Egypt Cairo
Estonia Tallinn
Fiji Suva
Gambia Banjul
Georgia Tbilisi
Ghana Accra
Greece Athens
Luxembourg Luxembourg
Mauritania Nouakchott
Peru Lima
Poland Warsaw
Russia Moscow
Rwanda Kigali
San Marino San Marino
Singapore Singapore
Taiwan Taipei
Turkey Ankara
Zambia Lusaka

⬆ back to top

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
name capital
Algeria Algiers
Andorra Andorra la Vella
Barbados Bridgetown
Belize Belmopan
Brazil Brasília
Brunei Bandar Seri Begawan
Burundi Bujumbura
Guatemala Guatemala City
Guyana Georgetown
Kuwait Kuwait City
Maldives Malé
Marshall Islands Majuro
Mexico Mexico City
Monaco Monaco-Ville
Mozambique Maputo
Niger Niamey
Panama Panama City
Papua New Guinea Port Moresby
Sao Tomé and Príncipe São Tomé
South Korea Seoul
Sri Lanka Sri Jayawardenepura Kotte
Sweden Stockholm
Taiwan Taipei
Tunisia Tunis

⬆ back to top

Find the country that has all the vowels and no spaces in its name

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 '% %'
name
Mozambique

⬆ back to top

SELECT from nobel

nobel Table

yr subject winner
1960 Chemistry Willard F. Libby
1960 Literature Saint-John Perse
1960 Medicine Sir Frank Macfarlane Burnet
1960 Medicine Peter Madawar

⬆ back to top

Change the query shown so that it displays Nobel prizes for 1950

SELECT yr, subject, winner 
FROM nobel
WHERE yr = 1950
yr subject winner
1950 Chemistry Kurt Alder
1950 Chemistry Otto Diels
1950 Literature Bertrand Russell
1950 Medicine Philip S. Hench
1950 Medicine Edward C. Kendall
1950 Medicine Tadeus Reichstein
1950 Peace Ralph Bunche
1950 Physics Cecil Powell

⬆ back to top

Show who won the 1962 prize for Literature

SELECT winner
FROM nobel
WHERE yr = 1960 AND subject = 'Physics'
winner
John Steinbeck

⬆ back to top

Show the year and subject that won 'Albert Einstein' his prize.

SELECT yr, subject
FROM nobel
WHERE winner = 'Albert Einstein'
yr subject
1921 Physics

⬆ back to top

Give the name of the 'Peace' winners since the year 2000, including 2000.

SELECT winner
FROM nobel
WHERE 
  subject ='Peace' AND 
  yr >= 2000
winner
Tunisian National Dialogue Quartet
Kailash Satyarthi
Malala Yousafzai
European Union
Ellen Johnson Sirleaf
Leymah Gbowee
Tawakel Karman
Liu Xiaobo
Barack Obama
Martti Ahtisaari
Intergovernmental Panel on Climate Change
Al Gore
Grameen Bank
Muhammad Yunus
International Atomic Energy Agency
Mohamed ElBaradei
Wangari Maathai
Shirin Ebadi
Jimmy Carter
United Nations
Kofi Annan
Kim Dae-jung

⬆ back to top

Show all details (yr, subject, winner) of the Literature prize winners for 1980 to 1989 inclusive

SELECT * 
FROM nobel
WHERE 
  yr BETWEEN 1980 AND 1989 AND 
  subject='Literature'
yr subject winner
1989 Literature Camilo José Cela
1988 Literature Naguib Mahfouz
1987 Literature Joseph Brodsky
1986 Literature Wole Soyinka
1985 Literature Claude Simon
1984 Literature Jaroslav Seifert
1983 Literature William Golding
1982 Literature Gabriel García Márquez
1981 Literature Elias Canetti
1980 Literature Czeslaw Milosz

⬆ back to top

Show all details of the presidential winners

-- Theodore Roosevelt
-- Woodrow Wilson
-- Jimmy Carter
-- Barack Obama
SELECT * 
FROM nobel
WHERE winner IN ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter','Barack Obama')
yr subject winner
2009 Peace Barack Obama
2002 Peace Jimmy Carter
1919 Peace Woodrow Wilson
1906 Peace Theodore Roosevelt

⬆ back to top

Show the winners with first name John

SELECT winner 
FROM nobel
WHERE winner LIKE 'John %'
winner
John O'Keefe
John B. Gurdon
John C. Mather
John L. Hall
John B. Fenn
John E. Sulston
John Pople
John Hume
John E. Walker
John C. Harsanyi
John F. Nash Jr.
John C. Polanyi
John R. Vane
John H. van Vleck
John Cornforth
John R. Hicks
John Bardeen
John C. Kendrew
John Steinbeck
John Bardeen
John F. Enders
John Cockcroft
John H. Northrop
John R. Mott
John Galsworthy
John Macleod

⬆ back to top

Show the year, subject, and name of Physics winners for 1980 together with the Chemistry winners for 1984

SELECT *
FROM nobel
WHERE 
  (subject = 'Physics' AND yr = 1980) OR 
  (subject = 'Chemistry' AND yr = 1984)
yr subject winner
1984 Chemistry Bruce Merrifield
1980 Physics James Cronin
1980 Physics Val Fitch

⬆ back to top

Show the year, subject, and name of winners for 1980 excluding Chemistry and Medicine

SELECT * 
FROM nobel
WHERE 
  yr = 1980 AND 
  subject <>'Chemistry' AND 
  subject <>'Medicine'
yr subject winner
1980 Economics Lawrence R. Klein
1980 Literature Czeslaw Milosz
1980 Peace Adolfo Pérez Esquivel
1980 Physics James Cronin
1980 Physics Val Fitch

⬆ back to top

Show year, subject, and name of people who won 'Medicine' and 'Literature' prizes

-- Show year, subject, and name of people who won a 'Medicine' prize in an early year (before 1910, not including 1910) together with winners of a 'Literature' prize in a later year (after 2004, including 2004)
SELECT *
FROM nobel
WHERE
  (yr < 1910 AND subject ='Medicine') OR 
  (yr >= 2004 AND subject ='Literature')
yr subject winner
2015 Literature Svetlana Alexievich
2014 Literature Patrick Modiano
2013 Literature Alice Munro
2012 Literature Mo Yan
2011 Literature Tomas Tranströmer
2010 Literature Mario Vargas Llosa
2009 Literature Herta Müller
2008 Literature Jean-Marie Gustave Le Clézio
2007 Literature Doris Lessing
2006 Literature Orhan Pamuk
2005 Literature Harold Pinter
2004 Literature Elfriede Jelinek
1909 Medicine Theodor Kocher
1908 Medicine Paul Ehrlich
1908 Medicine Ilya Mechnikov
1907 Medicine Alphonse Laveran
1906 Medicine Camillo Golgi
1906 Medicine Santiago Ramón y Cajal
1905 Medicine Robert Koch
1904 Medicine Ivan Pavlov
1903 Medicine Niels Ryberg Finsen
1902 Medicine Ronald Ross
1901 Medicine Emil von Behring

⬆ back to top

Find all details of the prize won by PETER GRÜNBERG

SELECT *
FROM nobel
WHERE winner ='PETER GRÜNBERG'
yr subject winner
2007 Physics Peter Grünberg

⬆ back to top

Find all details of the prize won by EUGENE O'NEILL

SELECT *
FROM nobel
WHERE winner ='EUGENE O\'NEILL'
yr subject winner
1936 Literature Eugene O'Neill

⬆ back to top

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
winner yr subject
Sir Martin J. Evans 2007 Medicine
Sir Peter Mansfield 2003 Medicine
Sir Paul Nurse 2001 Medicine
Sir Harold Kroto 1996 Chemistry
Sir James W. Black 1988 Medicine
Sir Arthur Lewis 1979 Economics
Sir Nevill F. Mott 1977 Physics
Sir Bernard Katz 1970 Medicine
Sir John Eccles 1963 Medicine
Sir Frank Macfarlane Burnet 1960 Medicine
Sir Cyril Hinshelwood 1956 Chemistry
Sir Robert Robinson 1947 Chemistry
Sir Alexander Fleming 1945 Medicine
Sir Howard Florey 1945 Medicine
Sir Henry Dale 1936 Medicine
Sir Norman Angell 1933 Peace
Sir Charles Sherrington 1932 Medicine
Sir Venkata Raman 1930 Physics
Sir Frederick Hopkins 1929 Medicine
Sir Austen Chamberlain 1925 Peace
Sir William Ramsay 1904 Chemistry

⬆ back to top

Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

-- The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1. Is a boolean value
-- subject IN ('Physics','Chemistry') => 'Physics': 1, 'Chemistry': 1, rest: 0

SELECT winner, subject
FROM nobel
WHERE yr=1984
ORDER BY subject IN ('Physics','Chemistry'), subject, winner
winner subject
Richard Stone Economics
Jaroslav Seifert Literature
César Milstein Medicine
Georges J.F. Köhler Medicine
Niels K. Jerne Medicine
Desmond Tutu Peace
Bruce Merrifield Chemistry
Carlo Rubbia Physics
Simon van der Meer Physics

⬆ back to top

Using nested SELECT

world Table

name continent area population gdp
Afghanistan Asia 652230 25500100 20343000000
Albania Europe 28748 2831741 12960000000
Algeria Africa 2381741 37100000 188681000000
Andorra Europe 468 78115 3712000000
Angola Africa 1246700 20609294 100990000000

⬆ back to top

List each country in the same continent as 'Brazil'.

SELECT continent
FROM world
WHERE name = 'Brazil'

SELECT name
FROM world
WHERE continent = (SELECT continent FROM world WHERE name = 'Brazil')
continent
South America
name
Argentina
Bolivia
Brazil
Chile
Colombia
Ecuador
Guyana
Paraguay
Peru
Saint Vincent and the Grenadines
Suriname
Uruguay
Venezuela

⬆ back to top

List each country and its continent in the same continent as 'Brazil' or 'Mexico'.

SELECT continent
FROM world
WHERE name='Brazil' OR name='Mexico'

SELECT name, continent
FROM world
WHERE continent IN (SELECT continent FROM world WHERE name='Brazil' OR name='Mexico')
continent
South America
North America
name continent
Argentina South America
Bolivia South America
Brazil South America
Chile South America
Colombia South America
Ecuador South America
Guyana South America
Paraguay South America
Peru South America
Saint Vincent and the Grenadines South America
Suriname South America
Uruguay South America
Venezuela South America
Belize North America
Canada North America
Costa Rica North America
El Salvador North America
Guatemala North America
Honduras North America
Mexico North America
Nicaragua North America
Panama North America
Saint Kitts and Nevis North America
United States North America

⬆ back to top

Show the population of China as a multiple of the population of the United Kingdom

SELECT population
FROM world
WHERE name='United Kingdom'

SELECT population/(SELECT population FROM world WHERE name='United Kingdom') 
AS 'population of China/population of United Kingdom'
FROM world
WHERE name = 'China'
population
66435550
population of China/population of United Kingdom
21.1089

⬆ back to top

Show each country that has a population greater than the population of ALL countries in Europe.

SELECT population
FROM world
WHERE continent='Europe'

SELECT name
FROM world
WHERE population > ALL (SELECT population FROM world WHERE continent='Europe')
name
Bangladesh
Brazil
China
Egypt
Ethiopia
India
Indonesia
Iran
Japan
Mexico
Nigeria
Pakistan
Philippines
Russia
Turkey
United States
Vietnam

⬆ back to top

SELECT in SELECT

world Table

name continent area population gdp
Afghanistan Asia 652230 25500100 20343000000
Albania Europe 28748 2831741 12960000000
Algeria Africa 2381741 37100000 188681000000
Andorra Europe 468 78115 3712000000
Angola Africa 1246700 20609294 100990000000

⬆ back to top

List each country name where the population is larger than that of 'Russia'.

-- List each country name where the population is larger than that of 'Russia'.
SELECT name 
FROM world
WHERE population > (SELECT population FROM world WHERE name='Russia')
name
Bangladesh
Brazil
China
India
Indonesia
Nigeria
Pakistan
United States

⬆ back to top

Show the countries in Europe with a per capita GDP greater than 'United Kingdom'.

-- Show the countries in Europe with a per capita GDP greater than 'United Kingdom'.
SELECT name 
FROM world
WHERE 
  continent = 'Europe' AND 
  gdp/population > (SELECT gdp/population FROM world WHERE name='United Kingdom')
name
Andorra
Austria
Belgium
Denmark
Finland
Germany
Iceland
Ireland
Liechtenstein
Luxembourg
Monaco
Netherlands
Norway
San Marino
Sweden
Switzerland

⬆ back to top

List the name and continent of countries in the continents containing either Argentina or Australia.

-- 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 = (SELECT continent FROM world WHERE name = 'Argentina') OR 
  continent = (SELECT continent FROM world WHERE name = 'Australia') 
ORDER by name
name continent
Argentina South America
Australia Oceania
Bolivia South America
Brazil South America
Chile South America
Colombia South America
Ecuador South America
Fiji Oceania
Guyana South America
Kiribati Oceania
Marshall Islands Oceania
Micronesia, Federated States of Oceania
Nauru Oceania
New Zealand Oceania
Palau Oceania
Papua New Guinea Oceania
Paraguay South America
Peru South America
Saint Vincent and the Grenadines South America
Samoa Oceania
Solomon Islands Oceania
Suriname South America
Tonga Oceania
Tuvalu Oceania
Uruguay South America
Vanuatu Oceania
Venezuela South America

⬆ back to top

Which country has a population that is more than Canada but less than Poland?

-- Which country has a population that is more than Canada but less than Poland? 
-- Show the name and the population.
SELECT name, population 
FROM world 
WHERE 
  population > (SELECT population FROM world WHERE name = 'Canada') AND 
  population < (SELECT population FROM world WHERE name = 'Poland')
name population

⬆ back to top

Show the name and the population as a percentage of the population of Germany for each country in Europe

-- Show the name and the population of each country in Europe. 
-- Show the population as a percentage of the population of Germany.
SELECT 
  name, 
  CONCAT(ROUND(population/(SELECT population FROM world WHERE name = 'Germany')*100,0), '%') AS percentage
FROM world 
WHERE continent = 'Europe'
name percentage
Albania 3%
Andorra 0%
Austria 11%
Belarus 11%
Belgium 14%
Bosnia and Herzegovina 4%
Bulgaria 8%
Croatia 5%
Czech Republic 13%
Denmark 7%
Estonia 2%
Finland 7%
France 81%
Germany 100%
Greece 13%
Hungary 12%
Iceland 0%
Ireland 6%
Italy 72%
Kazakhstan 22%
Latvia 2%
Liechtenstein 0%
Lithuania 3%
Luxembourg 1%
Malta 1%
Moldova 3%
Monaco 0%
Montenegro 1%
Netherlands 21%
North Macedonia 2%
Norway 6%
Poland 46%
Portugal 12%
Romania 23%
San Marino 0%
Serbia 8%
Slovakia 7%
Slovenia 3%
Spain 57%
Sweden 12%
Switzerland 10%
Ukraine 50%
United Kingdom 80%
Vatican City 0%

⬆ back to top

Which countries have a GDP greater than every country in Europe?

-- Which countries have a GDP greater than every country in Europe?
SELECT name
FROM world
WHERE 
  gdp >= ALL(SELECT gdp FROM world WHERE continent = 'Europe' AND gdp > 0) AND 
  continent != 'Europe'
name
China
Japan
United States

⬆ back to top

Find the largest country (by area) in each continent

-- Find the largest country (by area) in each continent
-- Show the continent, the name and the area.
SELECT continent, name, area 
FROM world x 
WHERE area >= ALL (SELECT area FROM world y WHERE y.continent=x.continent)
continent name area
Africa Algeria 2381741
Oceania Australia 7692024
South America Brazil 8515767
North America Canada 9984670
Asia China 9596961
Caribbean Cuba 109884
Europe Kazakhstan 2724900
Eurasia Russia 17125242

⬆ back to top

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

-- List each continent and the name of the country that comes first alphabetically.
SELECT continent, name
FROM world x
WHERE name <= ALL(SELECT name FROM world y WHERE y.continent = x.continent)
continent name
Africa Algeria
Asia Afghanistan
Caribbean Antigua and Barbuda
Eurasia Armenia
Europe Albania
North America Belize
Oceania Australia
South America Argentina

⬆ back to top

Find the continents where all countries have a population <= 25000000.

-- 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 x.continent = y.continent)
name continent population
Antigua and Barbuda Caribbean 96453
Bahamas Caribbean 385340
Barbados Caribbean 287025
Cuba Caribbean 11209628
Dominica Caribbean 71808
Dominican Republic Caribbean 10358320
Grenada Caribbean 112003
Haiti Caribbean 11577779
Jamaica Caribbean 2726667
Saint Lucia Caribbean 178696
Trinidad and Tobago Caribbean 1363985

⬆ back to top

Some countries have populations more than three times that of any of their neighbours (in the same continent)

-- Some countries have populations more than three times that of any of their neighbours (in the same continent).
SELECT name, continent
FROM world x
WHERE 
  population > ALL(
    SELECT population*3 
    FROM world y 
    WHERE 
      x.continent = y.continent AND 
      y.population > 0 AND 
      y.name != x.name
    )
name continent
Brazil South America
Russia Eurasia

⬆ back to top

SUM and COUNT

world Table

name continent area population gdp
Afghanistan Asia 652230 25500100 20343000000
Albania Europe 28748 2831741 12960000000
Algeria Africa 2381741 37100000 188681000000
Andorra Europe 468 78115 3712000000
Angola Africa 1246700 20609294 100990000000

⬆ back to top

Total world population

-- Show the total population of the world.
SELECT SUM(population) AS 'total population'
FROM world
total population
7615648652

⬆ back to top

List of continents

-- List all the continents - just once each.
SELECT DISTINCT(continent)
FROM world
continent
Africa
Asia
Caribbean
Eurasia
Europe
North America
Oceania
South America

⬆ back to top

GDP of Africa

-- Give the total GDP of Africa
SELECT SUM(gdp) AS 'total GDP of Africa'
FROM world 
WHERE continent = 'Africa'
total GDP of Africa
1964824000000

⬆ back to top

Count the big countries

-- How many countries have an area of at least 1000000
SELECT COUNT(area)
FROM world 
WHERE area >= 1000000
COUNT(area)
29

⬆ back to top

Baltic states population

-- What is the total population of ('Estonia', 'Latvia', 'Lithuania')
SELECT SUM(population) AS 'total population'
FROM world 
WHERE name IN ('Estonia', 'Latvia', 'Lithuania')
total population
6028631

⬆ back to top

Counting the countries of each continent

-- For each continent show the continent and number of countries.
SELECT continent, COUNT(name) AS number
FROM world 
GROUP BY continent
continent number
Africa 53
Asia 47
Caribbean 11
Eurasia 2
Europe 44
North America 11
Oceania 14
South America 13

⬆ back to top

Counting big countries in each continent

-- For each continent show the continent and number of countries with populations of at least 10 million.
SELECT continent, count(name)
FROM world 
WHERE population >= 10000000
GROUP BY continent
continent number
Africa 31
Asia 26
Caribbean 3
Eurasia 1
Europe 15
North America 4
Oceania 1
South America 8

⬆ back to top

Counting big continents

-- List the continents that have a total population of at least 100 million.
SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population) > 100000000
continent
Africa
Asia
Eurasia
Europe
North America
South America

⬆ back to top

Basic SQL Join Types

customer_id first_name last_name email address city state zipcode
1 George Washington gwashington@usa.gov 3200 Mt Vernon Hwy Mount Vernon VA 22121
2 John Adams jadams@usa.gov 1250 Hancock St Quincy MA 02169
3 Thomas Jefferson tjefferson@usa.gov 931 Thomas Jefferson Pkwy Charlottesville VA 22902
4 James Madison jmadison@usa.gov 11350 Constitution Hwy Orange VA 22960
5 James Monroe jmonroe@usa.gov 2050 James Monroe Parkway Charlottesville VA 22902
order_id order_date amount customer_id
1 07/04/1776 $234.56 1
2 03/14/1760 $78.50 3
3 05/23/1784 $124.00 2
4 09/03/1790 $65.50 3
5 07/21/1795 $25.50 10
6 11/27/1787 $14.40 9

⬆ back to top

Inner Join

-- Inner Join
select first_name, last_name, order_date, order_amount
from customers c
inner join orders o
on c.customer_id = o.customer_id
first_name last_name order_date order_amount
George Washington 07/04/1776 $234.56
John Adams 05/23/1784 $124.00
Thomas Jefferson 03/14/1760 $78.50
Thomas Jefferson 09/03/1790 $65.50

⬆ back to top

Left Join

-- Left Join
select first_name, last_name, order_date, order_amount
from customers c
left join orders o
on c.customer_id = o.customer_id
first_name last_name order_date order_amount
George Washington 07/04/1776 $234.56
John Adams 05/23/1784 $124.00
Thomas Jefferson 03/14/1760 $78.50
Thomas Jefferson 09/03/1790 $65.50
James Madison NULL NULL
James Monroe NULL NULL

⬆ back to top

Right Join

-- Right Join
select first_name, last_name, order_date, order_amount
from customers c
right join orders o
on c.customer_id = o.customer_id
first_name last_name order_date order_amount
George Washington 07/04/1776 $234.56
Thomas Jefferson 03/14/1760 $78.50
John Adams 05/23/1784 $124.00
Thomas Jefferson 09/03/1790 $65.50
NULL NULL 07/21/1795 $25.50
NULL NULL 11/27/1787 $14.40

⬆ back to top

Full Join

-- Full Join
select first_name, last_name, order_date, order_amount
from customers c
full join orders o
on c.customer_id = o.customer_id
first_name last_name order_date order_amount
George Washington 07/04/1776 $234.56
Thomas Jefferson 03/14/1760 $78.50
John Adams 05/23/1784 $124.00
Thomas Jefferson 09/03/1790 $65.50
NULL NULL 07/21/1795 $25.50
NULL NULL 11/27/1787 $14.40
James Madison NULL NULL
James Monroe NULL NULL

⬆ back to top

JOIN

game Table

id mdate stadium team1 team2
1001 8 June 2012 National Stadium, Warsaw POL GRE
1002 8 June 2012 Stadion Miejski (Wroclaw) RUS CZE
1003 12 June 2012 Stadion Miejski (Wroclaw) GRE CZE
1004 12 June 2012 National Stadium, Warsaw POL RUS

goal Table

matchid teamid player gtime
1001 POL Robert Lewandowski 17
1001 GRE Dimitris Salpingidis 51
1002 RUS Alan Dzagoev 15
1002 RUS Roman Pavlyuchenko 82

eteam Table

id teamname coach
POL Poland Franciszek Smuda
RUS Russia Dick Advocaat
CZE Czech Republic Michal Bilek
GRE Greece Fernando Santos

⬆ back to top

Show the matchid and player name for all goals scored by Germany.

SELECT matchid, player 
FROM goal 
WHERE teamid = 'GER'
matchid player
1008 Mario Gómez
1010 Mario Gómez
1010 Mario Gómez
1012 Lukas Podolski
1012 Lars Bender
1026 Philipp Lahm
1026 Sami Khedira
1026 Miroslav Klose
1026 Marco Reus
1030 Mesut Özil

⬆ back to top

Show id, stadium, team1, team2 for just game 1012

SELECT id, stadium, team1, team2
FROM game 
WHERE id = 1012
id stadium team1 team2
1012 Arena Lviv DEN GER

⬆ back to top

Show the player, teamid, stadium and mdate for every German goal.

SELECT player, teamid, stadium, mdate
FROM game 
JOIN goal 
ON 
  id = matchid AND 
  teamid ='GER'
player teamid stadium mdate
Mario Gómez GER Arena Lviv 9 June 2012
Mario Gómez GER Metalist Stadium 13 June 2012
Mario Gómez GER Metalist Stadium 13 June 2012
Lukas Podolski GER Arena Lviv 17 June 2012
Lars Bender GER Arena Lviv 17 June 2012
Philipp Lahm GER PGE Arena Gdansk 22 June 2012
Sami Khedira GER PGE Arena Gdansk 22 June 2012
Miroslav Klose GER PGE Arena Gdansk 22 June 2012
Marco Reus GER PGE Arena Gdansk 22 June 2012
Mesut Özil GER National Stadium, Warsaw 28 June 2012

⬆ back to top

Show the team1, team2 and player for every goal scored by a player called Mario player LIKE 'Mario%'

SELECT team1, team2, player
FROM game 
JOIN goal 
ON 
  id = matchid AND 
  player LIKE 'Mario%'
team1 team2 player
GER POR Mario Gómez
NED GER Mario Gómez
NED GER Mario Gómez
IRL CRO Mario Mandžukic
IRL CRO Mario Mandžukic
ITA CRO Mario Mandžukic
ITA IRL Mario Balotelli
GER ITA Mario Balotelli
GER ITA Mario Balotelli

⬆ back to top

Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10

SELECT player, teamid, coach, gtime
FROM goal
JOIN eteam
ON id = teamid
WHERE gtime <= 10
player teamid coach gtime
Petr Jirácek CZE Michal Bílek 3
Václav Pilar CZE Michal Bílek 6
Mario Mandžukic CRO Slaven Bilic 3
Fernando Torres ESP Vicente del Bosque 4

⬆ back to top

List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.

SELECT mdate, teamname
FROM game
JOIN eteam
ON team1 = eteam.id
WHERE coach = 'Fernando Santos'
mdate teamname
12 June 2012 Greece
16 June 2012 Greece

⬆ back to top

List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'

SELECT player
FROM goal
JOIN game 
ON matchid = id
WHERE stadium ='National Stadium, Warsaw'
player
Robert Lewandowski
Dimitris Salpingidis
Alan Dzagoev
Jakub Blaszczykowski
Giorgos Karagounis
Cristiano Ronaldo
Mario Balotelli
Mario Balotelli
Mesut Özil

⬆ back to top

Show the name of all players who scored a goal against Germany.

SELECT DISTINCT player
FROM game
JOIN goal
ON id = matchid
WHERE 
  (team1 = 'GER' OR team2 = 'GER') AND 
  teamid <> 'GER'
player
Robin van Persie
Michael Krohn-Dehli
Georgios Samaras
Dimitris Salpingidis
Mario Balotelli

⬆ back to top

Show teamname and the total number of goals scored.

SELECT 
  teamname, 
  count(teamname) AS 'total number of goals'
FROM eteam 
JOIN goal 
ON id = teamid
GROUP BY teamname
teamname total number of goals
Croatia 4
Czech Republic 4
Denmark 4
England 5
France 3
Germany 10
Greece 5
Italy 6
Netherlands 2
Poland 2
Portugal 6
Republic of Ireland 1
Russia 5
Spain 12
Sweden 5
Ukraine 2

⬆ back to top

Show the stadium and the number of goals scored in each stadium.

SELECT stadium, COUNT(stadium) AS 'number of goals'
FROM game
JOIN goal 
ON id = matchid
GROUP BY stadium
stadium number of goals
Arena Lviv 9
Donbass Arena 7
Metalist Stadium 7
National Stadium, Warsaw 9
Olimpiyskiy National Sports Complex 14
PGE Arena Gdansk 13
Stadion Miejski (Poznan) 8
Stadion Miejski (Wroclaw) 9

⬆ back to top

For every match involving 'POL', show the matchid, date and the number of goals scored.

SELECT id, mdate, COUNT(id) AS 'number of goals'
FROM game
JOIN goal
ON id = matchid 
WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY id
id mdate number of goals
1001 8 June 2012 2
1004 12 June 2012 2
1005 16 June 2012 1

⬆ back to top

For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'

SELECT matchid, mdate, count(matchid) AS 'number of goals'
FROM goal
JOIN game
ON matchid = id
WHERE teamid = 'GER'
GROUP BY matchid
matchid mdate number of goals
1008 9 June 2012 1
1010 13 June 2012 2
1012 17 June 2012 2
1026 22 June 2012 4
1030 28 June 2012 1

⬆ back to top

List every match with the goals scored by each team as shown.

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, matchid, team1, team2
mdate team1 score1 team2 score2
1 July 2012 ESP 4 ITA 0
10 June 2012 ESP 1 ITA 1
10 June 2012 IRL 1 CRO 3

⬆ back to top

More JOIN operations

movie Table

id title yr director budget gross

actor Table

id name

casting Table

movieid actorid ord

⬆ back to top

1962 movies

-- List the films where the yr is 1962 [Show id, title]
SELECT id, title
FROM movie
WHERE yr = 1962
id title
10212 A Kind of Loving
10329 A Symposium on Popular Songs
10347 A Very Private Affair
10648 An Autumn Afternoon

⬆ back to top

When was Citizen Kane released?

-- Give year of 'Citizen Kane'.
SELECT yr 
FROM movie 
WHERE title = 'Citizen Kane'
yr
1941

⬆ back to top

Star Trek movies

-- List all of the Star Trek movies, include the id, title and yr (all of these movies include the words Star Trek in the title). 
-- Order results by year.
SELECT id, title, yr
FROM movie 
WHERE title LIKE '%star trek%' 
ORDER BY yr
id title yr
17772 Star Trek: The Motion Picture 1979
17775 Star Trek II: The Wrath of Khan 1982
17776 Star Trek III: The Search for Spock 1984
17777 Star Trek IV: The Voyage Home 1986
17779 Star Trek V: The Final Frontier 1989
17780 Star Trek VI: The Undiscovered Country 1991
17774 Star Trek Generations 1994
17770 Star Trek: First Contact 1996
17771 Star Trek: Insurrection 1998
17778 Star Trek Nemesis 2002
17773 Star Trek 2009

⬆ back to top

id for actor Glenn Close

-- What id number does the actor 'Glenn Close' have?
SELECT id
FROM actor
WHERE name = 'Glenn Close'
id
140

⬆ back to top

id for Casablanca

-- What is the id of the film 'Casablanca'
SELECT id
FROM movie
WHERE title = 'Casablanca'
id
11768

⬆ back to top

Cast list for Casablanca

-- Obtain the cast list for 'Casablanca'.
SELECT name
FROM actor
JOIN casting 
ON id = actorid 
WHERE movieid = 11768
name
Peter Lorre
John Qualen
Madeleine LeBeau
Jack Benny

⬆ back to top

Cast list for Casablanca

-- Obtain the cast list for 'Casablanca'.
SELECT name
FROM actor
JOIN casting 
ON id = actorid 
WHERE movieid = 11768
name
Peter Lorre
John Qualen
Madeleine LeBeau
Jack Benny

⬆ back to top

Alien cast list

-- Obtain the cast list for the film 'Alien'
SELECT name
FROM actor 
JOIN casting
ON actor.id = casting.actorid
where movieid = (
  SELECT id 
  FROM movie 
  WHERE title='Alien')
name
John Hurt
Sigourney Weaver
Yaphet Kotto
Harry Dean Stanton
Ian Holm
Tom Skerritt
Veronica Cartwright

⬆ back to top

Harrison Ford movies

-- List the films in which 'Harrison Ford' has appeared
SELECT title
FROM movie 
JOIN casting
ON movie.id = casting.movieid
WHERE actorid = (
  SELECT id 
  FROM actor 
  WHERE name = 'Harrison Ford')
title
A Hundred and One Nights
Air Force One
American Graffiti
Apocalypse Now
Clear and Present Danger
Cowboys & Aliens
Crossing Over

⬆ back to top

Harrison Ford as a supporting actor

-- List the films where 'Harrison Ford' has appeared - but not in the starring role. 
-- [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]
SELECT title
FROM movie 
JOIN casting
ON movie.id = casting.movieid
WHERE 
  actorid = (
    SELECT id 
    FROM actor 
    WHERE name = 'Harrison Ford') AND 
  ord <> 1
title
A Hundred and One Nights
American Graffiti
Apocalypse Now
Cowboys & Aliens

⬆ back to top

Lead actors in 1962 movies

-- List the films together with the leading star for all 1962 films.
SELECT title, name
FROM actor
JOIN casting
ON casting.actorid = actor.id
JOIN movie
ON movie.id = casting.movieid
WHERE 
  movie.yr = 1962 AND
  casting.ord = 1
title name
A Kind of Loving Alan Bates
A Symposium on Popular Songs Paul Frees
A Very Private Affair Brigitte Bardot
An Autumn Afternoon Chishu Ryu

⬆ back to top

Busy years for Rock Hudson

-- Which were the busiest years for 'Rock Hudson'?
-- show the year and the number of movies he made each year for any year in which he made more than 2 movies.
SELECT yr, count(*) AS 'number of movies'
FROM movie
JOIN casting
ON casting.movieid = movie.id
WHERE actorid = (SELECT id FROM actor WHERE name='Rock Hudson')
GROUP BY yr
having count(*) > 2
yr number of movies
1953 5
1961 3

⬆ back to top

Lead actor in Julie Andrews movies

-- List the film title and the leading actor for all of the films 'Julie Andrews' played in.
SELECT title, name
FROM actor 
JOIN casting
ON casting.actorid = actor.id
JOIN movie
ON movie.id = casting.movieid
WHERE 
  movieid IN (
    SELECT movieid 
    FROM casting 
    WHERE actorid IN (
      SELECT id 
      FROM actor 
      WHERE name = 'Julie Andrews')) AND 
  ord = 1
title name
10 Dudley Moore
Darling Lili Julie Andrews
Despicable Me Steve Carell
Duet for One Julie Andrews
Hawaii Julie Andrews
Little Miss Marker Walter Matthau

⬆ back to top

Actors with 15 leading roles

-- Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles.
SELECT name
FROM actor
JOIN casting
ON casting.actorid = actor.id
GROUP BY name
HAVING sum(case ord when 1 then 1 else 0 end) >= 15
name
Adam Sandler
Al Pacino
Anthony Hopkins
Antonio Banderas
Arnold Schwarzenegger
...

⬆ back to top

List the films released in the year 1978

-- List the films released in the year 1978 ordered by the number of actors in the cast, then by title.
SELECT title, COUNT(actorid) AS count
FROM movie
JOIN casting
ON movie.id = movieid
WHERE yr = 1978
GROUP BY title 
ORDER BY COUNT(actorid) DESC, title
title count
The Bad News Bears Go to Japan 50
The Swarm 37
Grease 28
American Hot Wax 27
The Boys from Brazil 26
... ...

⬆ back to top

List all the people who have worked with 'Art Garfunkel'.

SELECT DISTINCT name
FROM actor
JOIN casting
ON actor.id = actorid 
WHERE 
  movieid IN (
    SELECT movieid
    FROM actor
    JOIN casting
    ON actor.id = actorid AND name = 'Art Garfunkel') AND 
  name <> 'Art Garfunkel'
name
Mark Ruffalo
Ryan Phillippe
Mike Myers
Neve Campbell
Salma Hayek
...

⬆ back to top

Using NULL

teacher Table

id dept name phone mobile
101 1 Shrivell 2753 07986 555 1234
102 1 Throd 2754 07122 555 1920
103 1 Splint 2293
104 Spiregrain 3287
105 2 Cutflower 3212 07996 555 6574
106 Deadyawn 3345

dept Table

id name
1 Computing
2 Design
3 Engineering

⬆ back to top

List the teachers who have NULL for their department.

SELECT name
FROM teacher
WHERE dept IS NULL;
name
Spiregrain
Deadyawn

⬆ back to top

INNER JOIN misses the teachers with no department and the departments with no teacher.

SELECT
  teacher.name AS teacher, 
  dept.name AS dept
FROM teacher
INNER JOIN dept
ON teacher.dept = dept.id
teacher dept
Shrivell Computing
Throd Computing
Splint Computing
Cutflower Design

⬆ back to top

Use a different JOIN so that all teachers are listed.

SELECT teacher.name, dept.name
FROM teacher
LEFT JOIN dept
ON teacher.dept = dept.id
name name
Shrivell Computing
Throd Computing
Splint Computing
Spiregrain null
Cutflower Design
Deadyawn null

⬆ back to top

Use a different JOIN so that all departments are listed.

SELECT teacher.name, dept.name
FROM teacher
RIGHT JOIN dept
ON teacher.dept = dept.id
name name
Shrivell Computing
Throd Computing
Splint Computing
Cutflower Design
null Engineering

⬆ back to top

Using the COALESCE function to print the mobile number

-- Use COALESCE to print the mobile number. Use the number '07986 444 2266' if there is no number given. 
-- Show teacher name and mobile number or '07986 444 2266'
SELECT name, COALESCE(mobile, '07986 444 2266') AS 'mobile number'
FROM teacher;
name COALESCE(mobi..
Shrivell 07986 555 1234
Throd 07122 555 1920
Splint 07986 444 2266
Spiregrain 07986 444 2266
Cutflower 07996 555 6574
Deadyawn 07986 444 2266

⬆ back to top

Using the COALESCE function to print department name

-- 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 dept = dept.id
name COALESCE(dept..
Shrivell Computing
Throd Computing
Splint Computing
Spiregrain None
Cutflower Design
Deadyawn None

⬆ back to top

Use COUNT to show the number of teachers and the number of mobile phones.

SELECT count(id), count(mobile)
FROM teacher
count(id) count(mobile)
6 3

⬆ back to top

Use COUNT and GROUP BY dept.name to show each department and the number of staff.

-- Use a RIGHT JOIN to ensure that the Engineering department is listed.
SELECT dept.name, COUNT(teacher.name) 
FROM teacher
RIGHT JOIN dept
ON dept = dept.id
GROUP BY dept.name
name COUNT(teacher..
Computing 3
Design 1
Engineering 0

⬆ back to top

Use COUNT and GROUP BY dept.name to show each department and the number of staff.

-- Use a RIGHT JOIN to ensure that the Engineering department is listed.
SELECT dept.name, COUNT(teacher.name) 
FROM teacher
RIGHT JOIN dept
ON dept = dept.id
GROUP BY dept.name
name COUNT(teacher..
Computing 3
Design 1
Engineering 0

⬆ back to top

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 
  teacher.name, 
  CASE 
    WHEN dept = 1 OR dept = 2 THEN 'Sci' 
    ELSE 'Art' 
  END dept
FROM teacher
name dept
Shrivell Sci
Throd Sci
Splint Sci
Spiregrain Art
Cutflower Sci
Deadyawn Art

⬆ back to top

Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2, show 'Art' if the teacher's dept is 3 and 'None' otherwise.

SELECT 
  teacher.name, 
  CASE 
    WHEN dept = 1 OR dept = 2 THEN 'Sci' 
    WHEN dept = 3 THEN 'Art' 
    ELSE 'None' 
  END dept 
FROM teacher
name dept
Shrivell Sci
Throd Sci
Splint Sci
Spiregrain None
Cutflower Sci
Deadyawn None

⬆ back to top

Self JOIN

  • stops(id, name)
  • route(num, company, pos, stop)

⬆ back to top

How many stops are in the database.

SELECT COUNT(id)
FROM stops
COUNT(id)
246

⬆ back to top

Find the id value for the stop 'Craiglockhart'

SELECT id
FROM stops
WHERE name = 'Craiglockhart'
id
53

⬆ back to top

Give the id and the name for the stops on the '4' 'LRT' service.

SELECT id, name
FROM stops
JOIN route 
ON stops.id = route.stop
WHERE 
  num = '4' AND 
  company = 'LRT'
id name
19 Bingham
177 Northfield
149 London Road
194 Princes Street
115 Haymarket
53 Craiglockhart
179 Oxgangs
85 Fairmilehead
117 Hillend

⬆ back to top

Show the number of routes that visit either London Road (149) or Craiglockhart (53).

SELECT company, num, COUNT(*)
FROM route
WHERE stop = 149 OR stop = 53
GROUP BY num
HAVING COUNT(*) = 2
company num COUNT(*)
LRT 4 2
LRT 45 2

⬆ back to top

Shows the services from Craiglockhart (53) to London Road (149).

SELECT a.company, a.num, a.stop, b.stop
FROM route a
JOIN route b 
ON a.num = b.num
WHERE 
  a.stop = 53 AND 
  b.stop = 149
company num stop stop
LRT 4 53 149
LRT 45 53 149

⬆ back to top

Shows the services between 'Craiglockhart' and 'London Road'

SELECT a.company, a.num, stopa.name, stopb.name
FROM route a
JOIN route b ON a.num = b.num
JOIN stops stopa ON a.stop = stopa.id
JOIN stops stopb ON b.stop = stopb.id
WHERE 
  stopa.name = 'Craiglockhart' AND
  stopb.name = 'London Road'
company num name name
LRT 4 Craiglockhart London Road
LRT 45 Craiglockhart London Road

⬆ back to top

Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith')

SELECT DISTINCT a.company, a.num
FROM route a
JOIN route b ON a.num = b.num
WHERE 
  a.stop = 115 AND
  b.stop = 137
company num
LRT 12
LRT 2
LRT 22
LRT 25
LRT 2A
SMT C5

⬆ back to top

Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'

SELECT a.company, a.num
FROM route a
JOIN route b ON (a.num = b.num)
JOIN stops stopa ON (a.stop = stopa.id)
JOIN stops stopb ON (b.stop = stopb.id)
WHERE 
  stopa.name = 'Craiglockhart' AND 
  stopb.name = 'Tollcross'
company num
LRT 10
LRT 27
LRT 45
LRT 47

⬆ back to top

Give a distinct list of the stops which may be reached from 'Craiglockhart' by taking one bus.

-- Include 'Craiglockhart' itself, offered by the LRT company.
-- Include the company and bus no. of the relevant services.
SELECT DISTINCT stopb.name, b.company, b.num
FROM route a
JOIN route b ON (a.num = b.num AND a.company = b.company)
JOIN stops stopa ON (a.stop = stopa.id)
JOIN stops stopb ON (b.stop = stopb.id)
WHERE stopa.name = 'Craiglockhart'
name company num
Silverknowes LRT 10
Muirhouse LRT 10
Newhaven LRT 10
Leith LRT 10
... ... ...

⬆ back to top

Find the routes involving two buses that can go from Craiglockhart to Lochend.

-- Show the bus no. and company for the first bus, the name of the stop for the transfer, and the bus no. and company for the second bus.
SELECT a.num, a.company, stops.name, c.num, c.company
FROM route a 
JOIN route b ON a.num = b.num AND a.company = b.company
JOIN stops ON stops.id = a.stop
JOIN route c ON stops.id = c.stop
JOIN route d ON c.num = d.num AND c.company = d.company
WHERE 
  b.stop = (SELECT id FROM stops WHERE name = 'Craiglockhart') AND 
  d.stop =(SELECT id FROM stops WHERE name = 'Lochend')
Order BY a.num, a.company, stops.name, c.num

⬆ back to top

About

SQL Zoo Live Study Guide

napier.sqlzoo.net/

Topics

sql

Resources

Readme
Activity

Stars

1 star

Watchers

2 watching

Forks

1 fork
Report repository

Releases

No releases published

Packages

No packages published