SELECT from WORLD

world(name, continent, area, population, gdp)

Easy questions

Show the name, continent and population of all countries.

SELECT name, continent, population FROM world

Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.

SELECT name FROM world WHERE population >= 200000000

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 gpd' FROM world WHERE population >= 200000000

Show the name and population in millions for the countries of the continent 'South America'. Divide the population by 1000000 to get population in millions.

SELECT name, population/1000000 AS 'pop in mil' FROM world WHERE continent = 'South America'

Show the name and population for France, Germany, Italy

SELECT name, population FROM world WHERE name in ('France','Germany','Italy');

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

SELECT name FROM world WHERE name LIKE '%United%'

Show the countries that are big by area or big by population. Show name, population and area.

SELECT name,population,area FROM world WHERE population > 250000000 OR area>3000000

Exclusive OR (XOR). Show the countries that are big by area or big by population but not both. Show name, population and area. Australia has a big area but a small population, it should be included. Indonesia has a big population but a small area, it should be included. China has a big population and big area, it should be excluded.United Kingdom has a small population and a small area, it should be excluded.

SELECT name,population,area FROM world WHERE population > 250000000 XOR area>3000000

Show the name and population in millions and the GDP in billions for the countries of the continent 'South America'. Use the ROUND function to show the values to two decimal places. For South America show population in millions and GDP in billions both to 2 decimal places.

SELECT name,ROUND(population/1000000,2) AS pop,ROUND(gdp/1000000000,2) AS 'gdp in bil' FROM world WHERE continent = 'South America'

Show the name and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000. 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

Harder questions

The CASE statement shown is used to substitute North America for Caribbean in the third column. Show the name - but substitute Australasia for Oceania - for countries beginning with N.

SELECT name, CASE WHEN continent='Oceania' THEN 'Australasia' ELSE continent END AS name2 FROM world WHERE name LIKE 'N%'

Show the name and the continent - but substitute Eurasia for Europe and Asia; substitute America - for each country in North America or South America or Caribbean. Show countries beginning with A or B.

SELECT name, CASE WHEN continent IN ('Europe','Asia') THEN 'Eurasia' WHEN continent IN ('North America','South America','Caribbean') THEN 'America' ELSE continent END
```
 FROM world
WHERE name LIKE 'A%' OR name LIKE 'B%'
```

Put the continents right… Oceania becomes Australasia. Countries in Eurasia and Turkey go to Europe/Asia. Caribbean islands starting with 'B' go to North America, other Caribbean islands go to South America. Order by country name in ascending order. Test your query using the WHERE clause with the following: WHERE tld IN ('.ag','.ba','.bb','.ca','.cn','.nz','.ru','.tr','.uk')

SELECT name, continent, CASE WHEN continent='Eurasia' OR name='Turkey' THEN 'Europe/Asia' WHEN continent='Oceania' THEN 'Australasia' WHEN continent='Caribbean' AND name LIKE 'B%' THEN 'North America' WHEN continent='Caribbean' AND name NOT LIKE 'B%' THEN 'South America' ELSE continent END FROM world WHERE tld IN ('.ag','.ba','.bb','.ca','.cn','.nz','.ru','.tr','.uk') ORDER BY name;

SELECT from Nobel

nobel(yr, subject, winner)

Easy questions

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

SELECT yr, subject, winner FROM nobel WHERE yr = 1950

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

SELECT winner FROM nobel WHERE yr>= 2000 AND subject='Peace'

Show who won the 1962 prize for Literature.

SELECT winner FROM nobel WHERE yr = 1962 AND subject = 'Literature'

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

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

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

SELECT winner FROM nobel WHERE yr>= 2000 AND subject='Peace'

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

SELECT yr, subject, winner FROM nobel WHERE yr BETWEEN 1980 AND 1989 AND subject='Literature'

Show all details of the presidential winners: Theodore Roosevelt, Woodrow Wilson, Jimmy Carter

SELECT * FROM nobel WHERE winner IN ('Theodore Roosevelt','Woodrow Wilson','Jimmy Carter')

Show the winners with first name John

SELECT winner FROM nobel WHERE winner LIKE 'John%'

Show the Physics winners for 1980 together with the Chemistry winners for 1984.

SELECT yr,subject,winner FROM nobel WHERE (subject='Physics' AND yr=1980) OR (subject='Chemistry' AND yr=1984)

Show the winners for 1980 excluding the Chemistry and Medicine

SELECT yr,subject,winner FROM nobel WHERE yr=1980 AND subject NOT IN ('Chemistry','Medicine')

Show 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 yr,subject,winner FROM nobel WHERE (yr<1910 AND subject='Medicine') OR (subject='Literature' and yr>=2004)

Harder questions

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

SELECT yr,subject,winner FROM nobel WHERE winner='Peter Grünberg'
SELECT yr,subject,winner FROM nobel WHERE winner LIKE 'Peter Gr%nberg'

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

SELECT yr,subject,winner FROM nobel WHERE winner LIKE 'Eugene O''Neill'
SELECT yr,subject,winner FROM nobel WHERE winner LIKE 'Eugene O%Neill'

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;

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

SELECT winner, subject FROM nobel WHERE yr=1984 ORDER BY subject IN ('Chemistry','Physics'),subject,winner

SELECT within SELECT

world(name, continent, area, population, gdp)

Easy questions

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')

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) as 'per capita gdp' FROM world WHERE name='United Kingdom')

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 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')

Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany. 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),'%')AS 'population' FROM world WHERE continent='Europe'

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>0)
SELECT name FROM world WHERE gdp > (SELECT MAX(gdp) FROM world WHERE continent='Europe' AND gdp>0)

Find the largest country (by area) in each continent, show the continent, the name and the area:

SELECT continent, name, area FROM world w1 WHERE area >= (SELECT MAX(area) FROM world w2 WHERE w1.continent=w2.continent)
SELECT continent, name, area FROM world w1 WHERE area >= ALL(SELECT area FROM world w2 WHERE w1.continent=w2.continent AND area>0)
SELECT w.continent,name,w.area FROM world w JOIN ( SELECT continent,MAX(area) AS area FROM world GROUP BY continent ) AS s ON s.continent=w.continent AND s.area=w.area
SELECT continent,name,area FROM world WHERE area IN (SELECT MAX(area) FROM world GROUP BY continent)

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

SELECT continent,name FROM world w1 WHERE name = (SELECT name FROM world w2 WHERE w1.continent=w2.continent ORDER BY name ASC LIMIT 1)
SELECT continent, name FROM world w1 WHERE name ⇐ ALL(SELECT name FROM world w2 WHERE w1.continent = w2.continent)

Hard questions

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 w1 WHERE 25000000 > (SELECT MAX(population) FROM world w2 WHERE w1.continent=w2.continent)
SELECT name,continent,population FROM world w1 WHERE 25000000 > ALL(SELECT population FROM world w2 WHERE w1.continent=w2.continent)
SELECT name,continent,population FROM world w1 WHERE continent IN (SELECT continent FROM world w2 GROUP BY continent HAVING MAX(population)⇐25000000)

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

SELECT name,continent FROM world w1 WHERE w1.population/3 > ALL(SELECT population FROM world w2 WHERE w1.continent=w2.continent AND w1.name!=w2.name)
SELECT name,continent FROM world w1 WHERE w1.population/3 > (SELECT MAX(population) FROM world w2 WHERE w1.continent=w2.continent AND w1.name!=w2.name)

SUM and COUNT

world(name, continent, area, population, gdp)

Show the total population of the world.

SELECT SUM(population) FROM world

List all the continents - just once each.

SELECT DISTINCT continent FROM world

Give the total GDP of Africa

SELECT SUM(gdp) FROM world WHERE continent='Africa'

How many countries have an area of at least 1000000

SELECT COUNT(*) FROM world WHERE area>=1000000

What is the total population of ('France','Germany','Spain')

SELECT SUM(population) FROM world WHERE name IN ('France','Germany','Spain')

For each continent show the continent and number of countries.

SELECT continent, COUNT(*) FROM world GROUP BY continent

For each continent show the continent and number of countries with populations of at least 10 million.

SELECT continent, COUNT(*) FROM world WHERE population>10000000 GROUP BY continent

List the continents that have a total population of at least 100 million.

SELECT continent FROM world GROUP BY continent HAVING SUM(population)> 100000000

The JOIN operation

game(id,mdate,stadium,team1,team2), goal(matchid,teamid,player,gtime), eteam(id,teamname,coach)

Easy questions

Modify it to show the matchid and player name for all goals scored by Germany.

SELECT matchid,player FROM goal WHERE teamid ='GER'

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

SELECT id,stadium,team1,team2 FROM game g WHERE g.id=1012;

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

SELECT player, teamid, stadium, mdate FROM game JOIN goal ON (id=matchid) WHERE teamid='GER'

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) WHERE player LIKE 'Mario%'

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 teamid=id WHERE gtime⇐10

List the 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'

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

SELECT player FROM game JOIN goal ON (id=matchid) WHERE stadium = 'National Stadium, Warsaw'

Hard questions

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

SELECT DISTINCT player FROM game JOIN goal ON matchid = id WHERE (team1='GER' OR team2='GER') AND teamid!='GER'
SELECT player FROM goal JOIN game ON matchid=id WHERE (team1='GER' OR team2='GER') AND teamid!='GER' GROUP BY player
(with count of goals) SELECT player, COUNT(*) AS goals FROM goal JOIN game ON matchid=id WHERE (team1='GER' OR team2='GER') AND teamid!='GER' GROUP BY player

Show teamname and the total number of goals scored.

SELECT teamname, COUNT(player) AS 'count of goals' FROM eteam JOIN goal ON id=teamid GROUP BY teamname

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

SELECT stadium, COUNT(*) AS '# goals' FROM game JOIN goal ON matchid=id GROUP BY stadium

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

SELECT matchid,mdate,COUNT(*) AS 'goals scored' FROM game JOIN goal ON matchid = id WHERE (team1 = 'POL' OR team2 = 'POL') GROUP BY matchid,mdate
SELECT matchid, mdate, COUNT(*) AS 'goals in match' FROM game JOIN goal ON matchid=id WHERE (team1='POL' OR team2='POL') GROUP BY mdate,matchid

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

SELECT matchid,mdate,COUNT(*) AS 'GER goals scored' FROM game JOIN goal ON matchid = id WHERE teamid = 'GER' GROUP BY matchid,mdate
SELECT matchid,mdate,COUNT(*) AS 'goals scored by GER' FROM game JOIN goal ON matchid=id WHERE teamid='GER' GROUP BY matchid,mdate

ist every match with the goals scored by each team as mdate,team1,score1,team2,score2 (using CASE). Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.

SELECT mdate,
       team1,
       SUM(CASE WHEN team1=teamid THEN 1 ELSE 0 END) AS score1,
       team2,
       SUM(CASE WHEN team2=teamid THEN 1 ELSE 0 END) AS score2
       FROM game LEFT JOIN goal ON matchid=id
       GROUP BY mdate,team1,team2
       ORDER BY mdate, matchid, team1, team2

Music tutorial

album(asin, title, artist, price, release, label, rank), track(album, dsk, posn, song)

Find the title and artist who recorded the song 'Alison'.

SELECT title, artist
  FROM album JOIN track
         ON (album.asin=track.album)
 WHERE song = 'Alison'

Which artist recorded the song 'Exodus'?

SELECT artist
  FROM album JOIN track
         ON (album.asin=track.album)
 WHERE song = 'Exodus'

Show the song for each track on the album 'Blur'

SELECT song
  FROM track t JOIN album a ON a.asin=t.album
 WHERE a.title = 'Blur'

For each album show the title and the total number of track.

SELECT title, COUNT(*)
  FROM album JOIN track ON (asin=album)
 GROUP BY title

For each album show the title and the total number of tracks containing the word 'Heart' (albums with no such tracks need not be shown).

SELECT title, COUNT(*)
  FROM album a JOIN track t ON (a.asin=t.album)
  WHERE t.song LIKE '%Heart%'
 GROUP BY title

A "title track" is where the song is the same as the title. Find the title tracks.

SELECT song
  FROM album a JOIN track t ON (a.asin=t.album)
  WHERE t.song=a.title

An "eponymous" album is one where the title is the same as the artist (for example the album 'Blur' by the band 'Blur'). Show the eponymous albums.

SELECT title
  FROM album a
  WHERE a.title=a.artist

Find the songs that appear on more than 2 albums. Include a count of the number of times each shows up.

SELECT song, COUNT(DISTINCT album)
  FROM album a JOIN track t ON (a.asin=t.album)
  GROUP BY song
  HAVING COUNT(DISTINCT album)>2

A "good value" album is one where the price per track is less than 50 pence. Find the good value album - show the title, the price and the number of tracks.

SELECT title, price, COUNT(song)
  FROM album a JOIN track t ON (a.asin=t.album)
  GROUP BY title,price
  HAVING a.price/COUNT(song)<0.5

List albums so that the album with the most tracks is first. Show the title and the number of tracks

NOT MATCHING THE RESULTS

SELECT title, COUNT(asin)
  FROM album a JOIN track t ON (a.asin=t.album)
  GROUP BY title
  ORDER BY COUNT(asin) DESC

More JOIN

movie(id,title,yr,director,budget,gross) actor(id,name) casting(movieid,actorid,ord)

Easy questions

List the films where the yr is 1962 [Show id, title]

SELECT id, title FROM movie WHERE yr=1962

Give year of 'Citizen Kane'.

SELECT yr FROM movie WHERE title='Citizen Kane'

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

What are the titles of the films with id 11768, 11955, 21191

SELECT title FROM movie WHERE id IN (11768, 11955, 21191)

What id number does the actress 'Glenn Close' have?

SELECT id FROM actor WHERE name='Glenn Close'

What is the id of the film 'Casablanca'

SELECT id FROM movie WHERE title='Casablanca'

Obtain the cast list for 'Casablanca'.

SELECT name FROM actor JOIN casting ON actorid=id WHERE movieid=(SELECT id FROM movie WHERE title='Casablanca')

Obtain the cast list for the film 'Alien'

SELECT name FROM actor JOIN casting ON actorid=id WHERE movieid=(SELECT id FROM movie WHERE title='Alien')

List the films in which 'Harrison Ford' has appeared

SELECT title FROM movie m JOIN casting ON movieid=m.id WHERE actorid=(SELECT id FROM actor WHERE name='Harrison Ford')

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 m JOIN casting ON movieid=m.id WHERE actorid=(SELECT id FROM actor WHERE name='Harrison Ford') AND ord>1

List the films together with the leading star for all 1962 films.

SELECT title,name FROM movie JOIN casting ON movie.id=movieid JOIN actor ON actor.id=actorid WHERE ord=1 AND yr=1962

Hard questions

Which were the busiest years for 'John Travolta', 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(*)
FROM movie m JOIN casting ON movieid=m.id
WHERE actorid=(SELECT id FROM actor WHERE name='John Travolta')
GROUP BY yr HAVING COUNT(*)>2

List the film title and the leading actor for all of the films 'Julie Andrews' played in.Did you get "Little Miss Marker twice"?

Julie Andrews starred in the 1980 remake of Little Miss Marker and not the original(1934).Title is not a unique field, create a table of IDs in your subquery.

SELECT title, name
FROM movie m1
JOIN casting ON id=movieid
JOIN actor ON actor.id=actorid
WHERE m1.id IN (SELECT id FROM movie JOIN casting ON id=movieid WHERE actorid=(SELECT id FROM actor WHERE name='Julie Andrews'))
AND ord=1

SELECT title, a.name
 FROM movie m
 JOIN casting ca On m.id=ca.movieid
 JOIN actor a On a.id=ca.actorid
  WHERE m.id IN (
   SELECT m.id
     FROM movie m
     JOIN casting c On m.id=c.movieid
     JOIN actor a On a.id=c.actorid
     WHERE a.name='Julie Andrews'
  )
  AND ord=1

Obtain a list, in alphabetical order, of actors who’ve had at least 30 starring roles.

SELECT name FROM actor WHERE id IN (SELECT actorid FROM casting WHERE ord=1 GROUP BY actorid HAVING COUNT(*)>=30) ORDER BY name

select a.name
 FROM actor a
 where a.id in ( select id from (
                   select id,count(*) as roles
                     from actor a
                     join casting ca on a.id=ca.actorid
                     where ca.ord=1
                     group by a.id) as s
                 where s.roles>=30)
 order by a.name

List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

SELECT title,COUNT(actorid) FROM movie JOIN casting ON movieid=id WHERE yr=1978 GROUP BY title ORDER BY COUNT(actorid) DESC,title

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

SELECT name FROM actor JOIN casting ON id=actorid
WHERE movieid IN (
    SELECT movieid FROM casting WHERE actorid=(
        SELECT id FROM actor WHERE name='Art Garfunkel'
    )
) AND name!='Art Garfunkel'
GROUP BY name

Using Null

teacher(id, dept, name, phone, mobile), dept(id, name)

Easy questions

List the teachers who have NULL for their department.

SELECT name FROM teacher WHERE dept IS NULL

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)

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)

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') FROM teacher

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') AS dept FROM teacher LEFT JOIN dept on teacher.dept=dept.id

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

SELECT COUNT(name),COUNT(mobile) FROM teacher

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 d.name,COUNT(t.name) FROM teacher t RIGHT JOIN dept d ON d.id=t.dept GROUP BY d.name

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 t.name, CASE WHEN d.id IN (1,2) THEN 'Sci' ELSE 'Art' END FROM teacher t LEFT JOIN dept d ON d.id=t.dept

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 t.name, CASE WHEN d.id IN (1,2) THEN 'Sci' WHEN d.id=3 THEN 'Art' ELSE 'None' END FROM teacher t LEFT JOIN dept d ON d.id=t.dept

Self join

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

Easy questions

How many stops are in the database.

SELECT COUNT(id) FROM stops

Find the id value for the stop 'Craiglockhart'

SELECT id FROM stops WHERE name='Craiglockhart'

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

SELECT id,name FROM stops JOIN route ON stop=id WHERE company='LRT' AND num='4'

The query shown gives the number of routes that visit either London Road (149) or Craiglockhart (53). Run the query and notice the two services that link these stops have a count of 2. Add a HAVING clause to restrict the output to these two routes.

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

Execute the self join shown and observe that b.stop gives all the places you can get to from Craiglockhart, without changing routes. Change the query so that it shows the services from Craiglockhart to London Road.

SELECT a.company, a.num, a.stop, b.stop
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
WHERE a.stop=53 AND b.stop=149

The query shown is similar to the previous one, however by joining two copies of the stops table we can refer to stops by name rather than by number. Change the query so that the services between 'Craiglockhart' and 'London Road' are shown. If you are tired of these places try 'Fairmilehead' against 'Tollcross'

SELECT a.company, a.num, stopa.name, stopb.name
FROM route a JOIN route b ON
  (a.company=b.company AND 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'

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.company=b.company AND a.num=b.num)
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name='Haymarket' AND stopb.name='Leith'

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

SELECT DISTINCT a.company, a.num
FROM route a JOIN route b ON
  (a.company=b.company AND 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'

Give a distinct list of the stops which may be reached from 'Craiglockhart' by taking one bus, including 'Craiglockhart' itself, offered by the LRT company. Include the company and bus no. of the relevant services.

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

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

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.\

ProgrammerInterview

salesperson(id, name, age, salary); customer(id, name, city, industry_type); orders(number, orderDate, cust_id, salesperson_id, amount);

The names of all salespeople that have an order with Samsonic:

SELECT name
FROM Salesperson s
JOIN Orders o ON o.salesperson_id=s.id
WHERE cust_id=(SELECT id FROM customer WHERE name='Samsonic')
GROUP BY name

The names of all salespeople that do not have any order with Samsonic:

SELECT name
FROM Salesperson s
LEFT JOIN Orders o ON o.salesperson_id=s.id
WHERE salesperson_id NOT IN (SELECT salesperson_id FROM Orders WHERE cust_id=(SELECT id FROM customer WHERE name='Samsonic'))
OR cust_id IS NULL
GROUP BY s.name

OR

SELECT name
FROM Salesperson
WHERE id NOT IN (SELECT salesperson_id FROM Orders WHERE cust_id=(SELECT id FROM customer WHERE name='Samsonic'))

The names of salespeople that have 2 or more orders:

SELECT name FROM salesperson WHERE id IN (
    SELECT salesperson_id FROM Orders GROUP BY salesperson_id HAVING COUNT(salesperson_id)>2
)

Write a SQL statement to insert rows into a table called highAchiever(Name, Age), where a salesperson must have a salary of 100,000 or greater to be included in the table.

INSERT INTO highAchiever(name,age)
(SELECT s.name,s.age FROM Salesperson s WHERE s.salary>100000)

OR

SELECT s.name,s.age
INTO highAchiever(name,age)
FROM Salesperson s WHERE s.salary>100000

Here is the problem: find the largest order amount for each salesperson and the associated order number, along with the customer to whom that order belongs to. You can present your answer in any database’s SQL – MySQL, Microsoft SQL Server, Oracle, etc:

SELECT o1.salesperson_id, s.Name, o1.Number AS OrderNumber, o1.Amount
FROM Orders o1
JOIN Salesperson s
ON s.ID = o1.salesperson_id
JOIN (
SELECT salesperson_id, MAX(Amount) AS MaxOrder
FROM Orders
GROUP BY salesperson_id
) AS o2 ON o2.salesperson_id=o1.salesperson_id
WHERE amount=MaxOrder
GROUP BY o1.salesperson_id,o1.Amount,s.Name,o1.Number

jstefek/SQLZoo.adoc

SELECT from WORLD

Easy questions

Show the name, continent and population of all countries.

Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.

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

Show the name and population in millions for the countries of the continent 'South America'. Divide the population by 1000000 to get population in millions.

Show the name and population for France, Germany, Italy

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

Show the countries that are big by area or big by population. Show name, population and area.

Show the name and population in millions and the GDP in billions for the countries of the continent 'South America'. Use the ROUND function to show the values to two decimal places. For South America show population in millions and GDP in billions both to 2 decimal places.

Show the name and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000. Show per-capita GDP for the trillion dollar countries to the nearest $1000.

Harder questions

The CASE statement shown is used to substitute North America for Caribbean in the third column. Show the name - but substitute Australasia for Oceania - for countries beginning with N.

Show the name and the continent - but substitute Eurasia for Europe and Asia; substitute America - for each country in North America or South America or Caribbean. Show countries beginning with A or B.

SELECT from Nobel

Easy questions

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

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

Show who won the 1962 prize for Literature.

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

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

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

Show all details of the presidential winners: Theodore Roosevelt, Woodrow Wilson, Jimmy Carter

Show the winners with first name John

Show the Physics winners for 1980 together with the Chemistry winners for 1984.

Show the winners for 1980 excluding the Chemistry and Medicine

Show 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)

Harder questions

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

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

List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.

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

SELECT within SELECT

Easy questions

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

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

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

Which country has a population that is more than Canada but less than Poland? Show the name and the population.

Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany. Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)

Find the largest country (by area) in each continent, show the continent, the name and the area:

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

Hard questions

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.

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

SUM and COUNT

Show the total population of the world.

List all the continents - just once each.

Give the total GDP of Africa

How many countries have an area of at least 1000000

What is the total population of ('France','Germany','Spain')

For each continent show the continent and number of countries.

For each continent show the continent and number of countries with populations of at least 10 million.

List the continents that have a total population of at least 100 million.

The JOIN operation

Easy questions

Modify it to show the matchid and player name for all goals scored by Germany.

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

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

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

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

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

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

Hard questions

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

Show teamname and the total number of goals scored.

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

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

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

Music tutorial

Find the title and artist who recorded the song 'Alison'.

Which artist recorded the song 'Exodus'?

Show the song for each track on the album 'Blur'

For each album show the title and the total number of track.

For each album show the title and the total number of tracks containing the word 'Heart' (albums with no such tracks need not be shown).

A "title track" is where the song is the same as the title. Find the title tracks.

An "eponymous" album is one where the title is the same as the artist (for example the album 'Blur' by the band 'Blur'). Show the eponymous albums.

Find the songs that appear on more than 2 albums. Include a count of the number of times each shows up.

A "good value" album is one where the price per track is less than 50 pence. Find the good value album - show the title, the price and the number of tracks.