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'
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 wordSELECT name, capital
FROM world
where LEFT(name,1) = left(capital,1) AND name <> capital
-- Theodore Roosevelt-- Woodrow Wilson-- Jimmy Carter-- Barack ObamaSELECT*FROM nobel
WHERE winner IN ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter','Barack Obama')
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 <1910AND subject ='Medicine') OR
(yr >=2004AND subject ='Literature')
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: 0SELECT winner, subject
FROM nobel
WHERE yr=1984ORDER BY subject IN ('Physics','Chemistry'), subject, winner
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')
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'
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')
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')
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')
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
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')
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'
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'
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 WHEREy.continent=x.continent)
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 WHEREy.continent=x.continent)
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
WHERE25000000> ALL(SELECT population FROM world y WHEREx.continent=y.continent)
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*3FROM world y
WHEREx.continent=y.continentANDy.population>0ANDy.name!=x.name
)
-- What is the total population of ('Estonia', 'Latvia', 'Lithuania')SELECTSUM(population) AS'total population'FROM world
WHERE name IN ('Estonia', 'Latvia', 'Lithuania')
-- 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 >=10000000GROUP BY continent
-- List the continents that have a total population of at least 100 million.SELECT continent
FROM world
GROUP BY continent
HAVINGSUM(population) >100000000
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
-- 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
-- Obtain the cast list for the film 'Alien'SELECT name
FROM actor
JOIN casting
ONactor.id=casting.actoridwhere movieid = (
SELECT id
FROM movie
WHERE title='Alien')
-- List the films in which 'Harrison Ford' has appearedSELECT title
FROM movie
JOIN casting
ONmovie.id=casting.movieidWHERE 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
JOIN casting
ONmovie.id=casting.movieidWHERE
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 actor
JOIN casting
ONcasting.actorid=actor.idJOIN movie
ONmovie.id=casting.movieidWHEREmovie.yr=1962ANDcasting.ord=1
-- 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
ONcasting.movieid=movie.idWHERE actorid = (SELECT id FROM actor WHERE name='Rock Hudson')
GROUP BY yr
havingcount(*) >2
-- List the film title and the leading actor for all of the films 'Julie Andrews' played in.SELECT title, name
FROM actor
JOIN casting
ONcasting.actorid=actor.idJOIN movie
ONmovie.id=casting.movieidWHERE
movieid IN (
SELECT movieid
FROM casting
WHERE actorid IN (
SELECT id
FROM actor
WHERE name ='Julie Andrews')) AND
ord =1
-- Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles.SELECT name
FROM actor
JOIN casting
ONcasting.actorid=actor.idGROUP BY name
HAVINGsum(case ord when 1 then 1 else 0 end) >=15
-- 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
ONmovie.id= movieid
WHERE yr =1978GROUP BY title
ORDER BYCOUNT(actorid) DESC, title
List all the people who have worked with 'Art Garfunkel'.
SELECT DISTINCT name
FROM actor
JOIN casting
ONactor.id= actorid
WHERE
movieid IN (
SELECT movieid
FROM actor
JOIN casting
ONactor.id= actorid AND name ='Art Garfunkel') AND
name <>'Art Garfunkel'
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;
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.SELECTteacher.name, COALESCE(dept.name, 'None')
FROM teacher
LEFT JOIN dept
ON dept =dept.id
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.SELECTdept.name, COUNT(teacher.name)
FROM teacher
RIGHT JOIN dept
ON dept =dept.idGROUP BYdept.name
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.SELECTdept.name, COUNT(teacher.name)
FROM teacher
RIGHT JOIN dept
ON dept =dept.idGROUP BYdept.name
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.
SELECTteacher.name,
CASE
WHEN dept =1OR dept =2 THEN 'Sci'
WHEN dept =3 THEN 'Art'
ELSE 'None'
END dept
FROM teacher
Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'
SELECTa.company, a.numFROM 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)
WHEREstopa.name='Craiglockhart'ANDstopb.name='Tollcross'
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 DISTINCTstopb.name, b.company, b.numFROM route a
JOIN route b ON (a.num=b.numANDa.company=b.company)
JOIN stops stopa ON (a.stop=stopa.id)
JOIN stops stopb ON (b.stop=stopb.id)
WHEREstopa.name='Craiglockhart'
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.SELECTa.num, a.company, stops.name, c.num, c.companyFROM route a
JOIN route b ONa.num=b.numANDa.company=b.companyJOIN stops ONstops.id=a.stopJOIN route c ONstops.id=c.stopJOIN route d ONc.num=d.numANDc.company=d.companyWHEREb.stop= (SELECT id FROM stops WHERE name ='Craiglockhart') ANDd.stop=(SELECT id FROM stops WHERE name ='Lochend')
Order BYa.num, a.company, stops.name, c.num