Sqlzoom exercises and answers

0 SELECT basics

  1. Displays the population of Germany
SELECT population FROM world
  WHERE name = 'Germany '
  1. A country with an area of more than 5000000 square kilometers displays its name and per capita GDP for each country
SELECT name, GDP/population FROM world
  WHERE area > 5000000
  1. Displays the country name and population of "Ireland", "Iceland", "Denmark"
SELECT name, population FROM world
  WHERE name IN ('Ireland ', 'Iceland ', 'Denmark ');
  1. Displays the name and area of a country with an area between 200000 and 250000
SELECT name, area FROM world
  WHERE area BETWEEN 200000 AND 250000

1 SELECT names

  1. Find countries that start with Y
SELECT name FROM world
  WHERE name LIKE 'Y%'
  1. Country ending with Y
SELECT name FROM world
  WHERE name LIKE '%Y'
  1. Find all the countries whose names include the letter x
SELECT name FROM world
  WHERE name LIKE '%X%'
  1. Find all countries whose names end with land
SELECT name FROM world
  WHERE name LIKE '%land'
  1. Find all countries whose names begin with C and end with ia
SELECT name FROM world
  WHERE name LIKE 'C%ia'
  1. Find all the countries whose names include the letter oo
SELECT name FROM world
  WHERE name LIKE '%oo%'
  1. Find all countries whose names include three or more a's
SELECT name FROM world
  WHERE name LIKE '%a%a%a%'
  1. Find all the countries whose names have t as the second letter
SELECT name FROM world
 WHERE name LIKE '_t%'
ORDER BY name
  1. Find all countries whose names have two letters o, separated by two other letters
SELECT name FROM world
 WHERE name LIKE '%o__o%'
  1. Find all countries whose names are four letters
SELECT name FROM world
 WHERE name LIKE '____'
  1. Displays the names of all countries, whose capital and country names are the same
SELECT name
  FROM world
 WHERE name = capital
  1. Displays the names of all countries whose capital is the country name plus "City"
SELECT name
  FROM world
 WHERE capital= concat(name, ' City')
  1. Find out all capitals and their country names, and the capitals should appear in the country names
SELECT capital, name
  FROM world
 WHERE capital LIKE concat('%',name, '%')
  1. Find all capitals and their country names, and the capital is an extension of the country name
SELECT  name,capital
  FROM world
 WHERE capital LIKE concat(name, '_%')
  1. Display the country name and its extension words. For example, the capital is an extension of the country name
select name,
  REPLACE(capital,name,'')
  FROM world
WHERE capital LIKE concat(name, '_%')

SELECT Quiz

  1. 3
  2. 5
  3. 5
  4. 3
  5. 3
  6. 3
  7. 3

2 SELECT from world

  1. Read the notes in this table and observe the results of running a simple SQL command.
SELECT name, continent, population FROM world
  1. How to use WHERE to filter records. Displays the name of a country with a population of at least 200 million. 200 million is 200 million, with eight zeros.
SELECT name FROM world
WHERE population>200000000
  1. Find the name of a country with a population of at least 200 million (200 million) and GDP per capita.
SELECT name, GDP/population FROM world
WHERE population>200000000
  1. Displays the country names and population in millions of people on the South America n continent. Divide the population by one million (1000000) to get the population in millions.
SELECT name,population/1000000
FROM world
WHERE continent='South America'
  1. Displays the country name and population of France, Germany and Italy.
SELECT name, population
FROM world
WHERE name in ('France', 'Germany', 'Italy')
  1. Displays the country with the word "United" as its name.
SELECT name
FROM world
WHERE name LIKE '%United%'
  1. Show the name, population and area of a large country.
SELECT name, population, area
FROM world
WHERE population>250000000 OR area>3000000
  1. Show countries with large population or area, but not both. Displays the country name, population and area.
SELECT name,population,area
FROM world
WHERE (population>250000000 OR area>3000000) AND name NOT IN(SELECT name FROM world WHERE population>250000000 AND area>3000000)
-- WHERE population>250000000 XOR area>3000000
-- emm,I didn't know XOR at first, so I wrote a sub query in a stupid way. Hahaha
  1. For South America, it shows the GDP in millions of people and 2 decimal places in billions.
SELECT name, ROUND(population/1000000,2) population,ROUND(GDP/1000000000,2) GDP
FROM world
WHERE continent='South America'
  1. Shows the per capita GDP of trillion yuan countries, rounded to the nearest $1000.
SELECT name, ROUND(GDP/population,-3) GDP
FROM world 
WHERE GDP>1000000000000
  1. Show the name - but substitute Australasia for Oceania - for countries beginning with N.
SELECT name,CASE WHEN continent='Oceania' THEN 'Australasia' ELSE continent END continent
  FROM world
 WHERE name LIKE 'N%'
  1. 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 continent
FROM world
WHERE name LIKE 'A%' or name LIKE 'B%'
  1. 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
    Show the name, the original continent and the new continent of all countries.
SELECT name,continent cont_orig,
CASE WHEN continent='Oceania' THEN 'Australasia'
     WHEN continent IN ('Eurasia','Turkey') THEN 'Europe/Asia'
     WHEN continent='Caribbean' AND name LIKE 'B%' THEN 'North America'
     -- WHEN continent='Caribbean' THEN 'South America'
     WHEN continent='Caribbean' AND name NOT LIKE 'B%' THEN 'South America'
     ELSE continent END cont_new
FROM world
ORDER BY name
--(one)The beginning is incorrect. There are two places: 1. I didn't read the questions well,'Europe/Asia'Disassembled; 2. No sort
--(two)The comment line is written in someone else's answer, No NOT LIKE In part, the answer is still correct.
-- After checking, the reason is: case The function returns only the first qualified value.
-- B The first one satisfies the above one first and returns the value. Therefore, the following line can not be limited to non B start

BBC QUIZ

  1. 5
  2. 4
  3. 2
  4. 4
  5. 2
  6. 4
  7. 3

3 SELECT from Nobel

  1. Change the query to display the award information of the 1950 Nobel Prize.
SELECT yr, subject, winner
  FROM nobel
 WHERE yr = 1950
  1. Show who won the 1962 literature award.
SELECT winner
  FROM nobel
 WHERE yr = 1962
   AND subject = 'Literature'
  1. Displays the year and award of "Albert Einstein".
SELECT yr,subject
FROM nobel
WHERE winner='Albert Einstein'
  1. Show the winners of the Peace prize (Peace) in 2000 and beyond.
SELECT winner
FROM nobel
WHERE yr>=2000 AND subject='Peace'
  1. Show all the details of the winners of the literature award from 1980 to 1989 (including the beginning and end) (year, theme, winners).
SELECT *
FROM nobel
WHERE (yr BETWEEN 1980 AND 1989) AND subject='Literature'
  1. Show all the details of the presidential winner:
  • Theodore Roosevelt
  • Woodrow Wilson
  • Jimmy Carter
SELECT * FROM nobel
WHERE winner IN ('Theodore Roosevelt','Woodrow Wilson','Jimmy Carter')
  1. Displays the winner named John. (Note: foreigner's first name comes first and last name comes last)
SELECT winner FROM nobel
WHERE winner LIKE 'John%'
  1. It shows the winners of physics in 1980 and chemistry in 1984.
SELECT * FROM nobel
WHERE (yr=1980 AND subject='physics') OR (yr=1984 AND subject='chemistry')
  1. Check out the winners in 1980, excluding chemistry and medicine.
SELECT * FROM nobel
WHERE yr=1980 AND subject NOT IN ('chemistry','Medicine')
  1. Show early medicine winners (before 1910, excluding 1910) and recent literature winners (after 2004, including 2004)
SELECT * FROM nobel
WHERE (yr<1910 AND subject='Medicine') OR (yr>=2004 AND subject='Literature')
  1. Find all details of the prize won by PETER GRÜNBERG
SELECT * FROM nobel
WHERE winner = CONCAT('PETER GR',CHAR(252),'NBERG')
-- If based MySQL8.0,You need to be CHAR(252 using ASCII)Otherwise, it cannot be displayedÜ. It may also be that my font library is not fully installed or something, which needs to be confirmed?
  1. Find all details of the prize won by EUGENE O'Neil
SELECT * FROM nobel
WHERE winner = 'EUGENE O\'NEILL'
  1. Knights in order
    List the winner, year and award page of Jazz (the name of jazz starts with Sir). The latest winners are displayed first, and then sorted by name in the same year.
SELECT winner,yr,subject FROM nobel
WHERE winner LIKE 'Sir%'
ORDER BY yr DESC
  1. The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1.
    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 ('Physics','Chemistry'),subject,winner

Nobel Quiz

  1. 5
  2. 3
  3. 2
  4. 3
  5. 3
  6. 3
  7. 4

4 SELECT within SELECT

  1. List the name of each country, in which the population is higher than that of Russia.
SELECT name FROM world
  WHERE population >
     (SELECT population FROM world
      WHERE name='Russia')
  1. List the per capita GDP of each country in Europe, in which the per capita GDP is higher than the value of 'United Kingdom' in the UK.
SELECT name FROM world
  WHERE (GDP/population)>(SELECT GDP/population FROM world
      WHERE name='United Kingdom') AND continent='Europe'
-- The title is misleading. It is said to be a column per capita GDP,The results should be listed by country
  1. In the states where Argentina and Australia are located, list the name of the country and the continent. Sort by country name
SELECT name,continent FROM world
  WHERE continent IN
     (SELECT continent con FROM world
      WHERE name='Argentina' OR name='Australia') 
ORDER BY name
  1. Which country has a larger population than Canada but less than Poland? List the country name and population.
SELECT name,population FROM world
  WHERE population>
     (SELECT population FROM world
      WHERE name='Canada')  AND 
      population<
     (SELECT population FROM world
      WHERE name='Poland')  
  1. Displays the European country name and population of each country. The population is shown as a percentage of the population of Germany.
SELECT name,CONCAT(ROUND(population/(SELECT population FROM world
      WHERE name='Germany')*100,0),'%') FROM world
WHERE continent='Europe'
  1. Which countries have higher GDP than all countries in Europe? [just list name.] (in the records of some countries, GDP is NULL, and no data is filled in.)
SELECT name
  FROM world
 WHERE GDP>ALL(SELECT GDP FROM world WHERE GDP>0 AND continent='Europe')
 --WHERE GDP>(SELECT MAX(GDP) FROM world WHERE continent='Europe')
 -- You can also use MAX(),If used ALL()You need to pay attention to whether there is NULL Value, to be removed
  1. Find out the country with the largest AREA in each state, and list the continent, the name of the country and the AREA. (in the records of some countries, AREA is NULL and no data is filled in.)
SELECT continent, name, area FROM world x
  WHERE area>= ALL
    (SELECT area FROM world y
        WHERE y.continent=x.continent
          AND area>0)
  1. List the name of each continent and the name of the country in each continent, which is the first country name in the order of son and mother. (i.e. only one country per continent)
SELECT continent, name FROM world x
  WHERE name = (SELECT MIN(name) FROM world y WHERE x.continent=y.continent)
  -- Sure enough, half a bucket of water. The first reaction when you see the title is to sort in the sub query and take the first one
  -- Subquery cannot be ORDER BY,And directly compare the size
  1. Find out which states have a population of less than or equal to 25000000 Among these States, list the country name, continent, and population.
SELECT name, continent, population
FROM world x 
WHERE (SELECT MAX(population) FROM world y WHERE x.continent=y.continent)<=25000000 
  1. Some countries have three or more times the population of all other countries on the continent. List the country name and continent container.
SELECT name, continent
FROM world x 
WHERE population >=3*(SELECT MAX(population) FROM world y WHERE y.continent=x.continent AND y.name!=x.name)

Nested SELECT Quiz

  1. 3
  2. 2,5
  3. 1
  4. 4
  5. 2
  6. 1,5
  7. 2

5 SUM and COUNT

  1. Show the total population of the world.
SELECT SUM(population)
FROM world
  1. List all States, each only once.
SELECT DISTINCT continent
FROM world
  1. Find the total GDP of Africa.
SELECT SUM(GDP)
FROM world
WHERE continent='Africa'
  1. How many countries have an area of at least one million (1000000).
SELECT COUNT(*)
FROM world
WHERE area>1000000
  1. ("France", "Germany", "Spain") ("France", "Germany", "Spain") what is the total population?
SELECT SUM(population)
FROM world
WHERE name IN ('France','Germany','Spain')
  1. For each continent, show the number of continents and countries.
SELECT continent,COUNT(*)
FROM world
GROUP BY continent
  1. For each continent, show the number of continents and countries with at least 10 million people (10000000).
SELECT continent,COUNT(*)
FROM world
WHERE population>=10000000
GROUP BY continent
  1. List states with a population of at least 100 million (100 million) (1 million).
SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population)>100000000
-- group by Yes where Cannot run after where The aggregate function is used in. You need to use having

SUM and COUNT Quiz

  1. 3
  2. 1
  3. 4
  4. (4) 5
 SELECT region, SUM(area)
   FROM bbc 
  WHERE SUM(area) > 15000000 
  GROUP BY region

To be verified, in fact, in SQLZOO, this error displays "Invalid use of group function" instead of "invalid use of the WHERE function". If you try it locally, the answer should be wrong
5. 2
6. 3 5
7. 4
8. 4

The nobel table can be used to practice more SUM and COUNT functions

  1. Find out how many awards have been awarded in total.
SELECT COUNT(winner) FROM nobel
  1. List each award (subject) only once
SELECT DISTINCT subject
FROM nobel
  1. Find out the total number of physics awards.
SELECT COUNT(subject)
FROM nobel
WHERE subject='Physics'
  1. For each award (Subject), list the number of awards.
SELECT subject,COUNT(*)
FROM nobel
GROUP BY subject
  1. For each award (Subject), list the year when it was first awarded.
SELECT subject,MIN(yr)
FROM nobel
GROUP BY subject
  1. For each award (Subject), list the number awarded in 2000.
SELECT subject,COUNT(*)
FROM nobel
WHERE yr=2000
GROUP BY subject
  1. For each award (Subject), list how many different winners there are.
SELECT subject,COUNT(DISTINCT winner)
FROM nobel
GROUP BY subject
  1. For each award (Subject), list how many years it has been awarded.
SELECT subject,COUNT(DISTINCT yr)
FROM nobel
GROUP BY subject
  1. List the year in which there were three Physics winners in the same year.
SELECT yr
FROM nobel
WHERE subject='Physics'
GROUP BY yr
HAVING COUNT(yr)=3
  1. List who won the prize more than once.
SELECT winner
FROM nobel
GROUP BY winner
HAVING COUNT(*)>1
  1. List who won more than one award (Subject)
SELECT winner
FROM nobel
GROUP BY winner
HAVING COUNT(DISTINCT subject)>1
  1. In which year and which award, the same award (subject) is awarded to 3 people. Only the year 2000 and beyond are listed.
SELECT yr,subject
FROM nobel
WHERE yr>=2000
GROUP BY yr,subject
HAVING COUNT(DISTINCT winner)=3
-- group by It needs to be followed by two parameters

6 JOIN

  1. The player number of the German team and the player name of the German team are listed in this SQL. To find the German players
SELECT goal.matchid, goal.player FROM goal INNER JOIN eteam ON goal.teamid=eteam.id
  WHERE eteam.teamname='Germany'
  -- SELECT matchid, player FROM goal 
  -- WHERE teamid = 'GER'
  1. Only the ID, stadium, team1 and team2 of event 1012 are displayed
SELECT id,stadium,team1,team2
  FROM game
  WHERE id=1012

  1. Modify it to display the player name, team name, venue and date of each German goal.
SELECT go.player,go.teamid,ga.stadium,ga.mdate
  FROM game ga JOIN goal go ON (id=matchid)
  WHERE teamid = 'GER'
  1. List the players whose names are Mario (player LIKE 'Mario%'), team 1, team 2, team 2 and player who scored goals
SELECT ga.team1,ga.team2 ,go.player
  FROM game ga JOIN goal go ON (id=matchid)
  WHERE player LIKE 'Mario%'
  1. List player s, teams, coach es and scoring time in the first 10 minutes of each game
SELECT go.player, go.teamid, et.coach, go.gtime
  FROM goal go INNER JOIN eteam et ON go.teamid=et.id
 WHERE go.gtime<=10
  1. List the event date and team name of 'Fernando Santos' as the coach of team 1 team1.
SELECT ga.mdate, et.teamname
FROM game ga INNER JOIN eteam et ON ga.team1=et.id
WHERE et.coach='Fernando Santos'
  1. List the scoring players in the venue 'National Stadium, Warsaw'.
SELECT go.player
FROM goal go INNER JOIN game ga ON go.matchid=ga.id
WHERE ga.stadium='National Stadium, Warsaw'
  1. Modify it to list only the names of the players who shot into the German Longmen in all competitions.
SELECT DISTINCT go.player
  FROM game ga JOIN goal go ON go.matchid = ga.id 
    WHERE (ga.team1='GER' OR ga.team2='GER') AND go.teamid!='GER'
  1. List the team name, teamname and the total number of goals scored by the team
SELECT et.teamname, COUNT(et.teamname)
  FROM eteam et JOIN goal go ON et.id=go.teamid
 GROUP BY et.teamname
 -- It can also be used COUNT(*)
  1. List the name of the venue and the number of goals scored at the venue.
SELECT ga.stadium, COUNT(*)
FROM game ga JOIN goal go ON ga.id=go.matchid
GROUP BY ga.stadium
  1. In each event in which Poland 'POL' participates, list the event number matched, date and goal number.
SELECT go.matchid,ga.mdate,COUNT(*)
  FROM game ga JOIN goal go ON go.matchid = ga.id 
 WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY go.matchid,ga.mdate
  1. In each event in which Germany 'GER' participates, list the event number matched, date and Germany's goal number.
SELECT go.matchid,ga.mdate,COUNT(*)
FROM goal go JOIN game ga ON go.matchid = ga.id 
WHERE go.teamid='GER'
GROUP BY go.matchid,ga.mdate
  1. Sort your result by mdate, matchid, team1 and team2.
--<SQL Study guide 11.3.1 Examples can be referred to.
-- SUM Can be directly to CASE WHEN Sum, CASE WHEN Originally, the output is a field (column)
-- Use an outer join because there is a score of 0 in the table:0 , if you use an internal connection, these games will not be displayed
SELECT ga.mdate,ga.team1, 
  SUM(CASE WHEN go.teamid=ga.team1 THEN 1 ELSE 0 END) score1,
  ga.team2,
  SUM(CASE WHEN go.teamid=ga.team2 THEN 1 ELSE 0 END) score2
  FROM game ga LEFT JOIN goal go ON go.matchid = ga.id
  GROUP BY ga.mdate,go.matchid,ga.team1,ga.team2	

JOIN Quiz

  1. 4
  2. 3
  3. 1
  4. 1
  5. 2
  6. 3
  7. 2

7 More JOIN operations

  1. List the films that first appeared in 1962, [display id, title]
SELECT  id,title
 FROM movie
 WHERE yr=1962
  1. The first year of the film "Citizen Kane".
SELECT  yr
 FROM movie
 WHERE title='Citizen Kane' 
  1. List all movies of Star Trek Star Trek series, including id, title and yr (all movies in this system start with Star Trek as the name of the movie). In chronological order.
SELECT  id,title,yr
 FROM movie
 WHERE title LIKE 'Star Trek%' 
 ORDER BY 3
  1. What is the name of the movie with id 11768, 11955 and 21191?
SELECT  title
 FROM movie
 WHERE id IN (11768,11955,21191)
  1. What is the id number of actress' Glenn Close '?
SELECT  id
 FROM actor
 WHERE name='Glenn Close'
  1. What is the id number of the film "Casablanca"?
SELECT  id
 FROM movie
 WHERE title='Casablanca'
  1. List the actors of the film 'Casablanca'.
SELECT actor.name
 FROM casting INNER JOIN actor ON actor.id=casting.actorid
 WHERE casting.movieid=11768
  1. Displays a list of actors for the movie 'Alien'.
SELECT actor.name
 FROM casting INNER JOIN actor ON actor.id=casting.actorid
 INNER JOIN movie ON casting.movieid=movie.id
 WHERE movie.title='Alien'
  1. List the films in which actor Harrison Ford has performed.
SELECT movie.title
 FROM casting INNER JOIN actor ON actor.id=casting.actorid
 INNER JOIN movie ON casting.movieid=movie.id
 WHERE actor.name='Harrison Ford'
  1. List the films in which actor Harrison Ford has performed, but he is not the first protagonist.
SELECT movie.title
 FROM casting INNER JOIN actor ON actor.id=casting.actorid
 INNER JOIN movie ON casting.movieid=movie.id
 WHERE actor.name='Harrison Ford' AND casting.ord!=1
  1. List the films that first appeared in 1962 and its first protagonist.
SELECT movie.title,actor.name
 FROM casting INNER JOIN actor ON actor.id=casting.actorid
 INNER JOIN movie ON casting.movieid=movie.id
 WHERE movie.yr=1962 AND casting.ord=1
  1. What is the busiest year of John Travolta? Displays the year and the number of movies in that year.
SELECT movie.yr,COUNT(*)
  FROM casting INNER JOIN actor ON actor.id=casting.actorid
               INNER JOIN movie ON casting.movieid=movie.id
  WHERE actor.name='John Travolta'
  GROUP BY yr
  HAVING COUNT(yr)=
    (SELECT MAX(maxcount) FROM 
      (SELECT COUNT(*) maxcount 
       FROM casting INNER JOIN actor ON actor.id=casting.actorid
                    INNER JOIN movie ON casting.movieid=movie.id
       WHERE actor.name='John Travolta'
       GROUP BY movie.yr) maxall
    )
    -- 1,Each derived table must have its own alias and the table generated by sub query needs to be named
    -- 2,The combination of multiple sub queries and aggregation functions needs more practice, and the logical order between them is not very smooth
  1. List the names of the films in which actor Julie Andrews' participated and its first protagonist.
SELECT movie.title,actor.name FROM casting 
  INNER JOIN actor ON actor.id=casting.actorid
  INNER JOIN movie ON casting.movieid=movie.id
  WHERE (movieid IN (SELECT movieid FROM casting 
    INNER JOIN actor ON actor.id=casting.actorid
    INNER JOIN movie ON casting.movieid=movie.id
    WHERE actor.name='Julie Andrews')) AND casting.ord=1
  1. List, in alphabetical order, which actor has been the first protagonist for 30 times.
SELECT actor.name FROM casting 
  INNER JOIN actor ON actor.id=casting.actorid
  INNER JOIN movie ON casting.movieid=movie.id
  WHERE casting.ord=1
  GROUP BY actor.name
  HAVING COUNT(*)>=30
  ORDER BY 1
  1. List the titles and roles of the first films in 1978, and rank them by the number.
SELECT movie.title,COUNT(casting.actorid) FROM casting 
  JOIN actor ON actor.id=casting.actorid
  JOIN movie ON casting.movieid=movie.id
  WHERE movie.yr=1978
  GROUP BY movie.title
  ORDER BY 2 DESC, 1 ASC
  -- Intermittently correct the report and compare it for many times. The result should be no problem. The main problem lies in the ranking of the first column
  1. List the names of actors who have worked with actor Art Garfunkel.
SELECT DISTINCT actor.name FROM casting 
  JOIN actor ON actor.id=casting.actorid
  JOIN movie ON casting.movieid=movie.id
  WHERE casting.movieid IN (SELECT casting.movieid FROM casting 
    JOIN actor ON actor.id=casting.actorid
    WHERE actor.name='Art Garfunkel') AND actor.name!='Art Garfunkel'

JOIN Quiz 2

  1. 5.3 didn't look at the link relationship carefully, director and actor ID link
  2. 5
  3. 3
  4. 2
  5. 4
  6. 3
  7. 2

8 Using Null

  1. List the teachers who have NULL for their department.
SELECT name FROM teacher
WHERE dept IS NULL
  1. Note the INNER JOIN misses the teachers with no department and the departments with no teacher.
SELECT teacher.name, dept.name
 FROM teacher INNER JOIN dept
           ON (teacher.dept=dept.id)
  1. 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)
  1. Use a different JOIN so that all departments are listed.
SELECT teacher.name, dept.name
 FROM dept LEFT JOIN teacher
           ON (teacher.dept=dept.id)
 -- Or right outer link RIGHT OUTER
  1. 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'

The COALESCE(expression [,... n]) function returns a non NULL value from the first one. If all values are NULL, it returns NULL

SELECT name, COALESCE(mobile,'07986 444 2266')
FROM teacher
  1. 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 t.name, COALESCE(d.name,'None')
FROM teacher t LEFT OUTER JOIN dept d ON t.dept=d.id
  1. Use COUNT to show the number of teachers and the number of mobile phones.
SELECT COUNT(name),COUNT(mobile) FROM teacher
  1. se 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 OUTER JOIN dept d ON t.dept=d.id
  GROUP BY d.name
  -- be careful COUNT(*)and COUNT(t.name)The former is to count the rows after grouping, so even if Engineering No teacher, no accountant 1. The latter is not.
  1. 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 name, CASE WHEN dept=1 OR dept=2 THEN 'Sci' ELSE 'Art' END FROM teacher
  1. 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 name, 
  CASE WHEN dept=1 OR dept=2 THEN 'Sci' 
       WHEN dept=3 THEN 'Art' ELSE 'None' END FROM teacher

Using Null Quiz

  1. 5
  2. 3
  3. 5
  4. 2
  5. 1
  6. 1

9 Self join

  1. How many stops are in the database.
SELECT COUNT(*) FROM stops
  1. Find the id value for the stop 'Craiglockhart'
SELECT id FROM stops WHERE name='Craiglockhart' 
  1. Give the id and the name for the stops on the '4' 'LRT' service.
SELECT s.id,s.name FROM
stops s INNER JOIN route r ON r.stop=s.id
WHERE r.num=4 AND r.company='LRT'
  1. 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
  1. 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
  1. 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'
  1. Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith')
SELECT DISTINCT r1.company, r1.num FROM
route r1 INNER JOIN route r2 ON r1.num=r2.num AND r1.company=r2.company
WHERE r1.stop=115 AND r2.stop=137
-- DISTINCT Yes r1.company+r1.num All rows are de duplicated, not just for r1.company One parameter
  1. Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'
SELECT DISTINCT r1.company, r1.num FROM route r1 
INNER JOIN route r2 ON r1.num=r2.num AND r1.company=r2.company
INNER JOIN stops s1 ON r1.stop=s1.id
INNER JOIN stops s2 ON r2.stop=s2.id
WHERE s1.name='Craiglockhart' AND s2.name='Tollcross'
  1. 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 DISTINCT s2.name,r1.company,r1.num FROM route r1 
INNER JOIN route r2 ON r1.num=r2.num AND r1.company=r2.company
INNER JOIN stops s1 ON r1.stop=s1.id
INNER JOIN stops s2 ON r2.stop=s2.id
WHERE s1.name='Craiglockhart'  AND r1.company='LRT'
  1. 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.

This question is really difficult. The only one who has seen others' answers.
I began to think of two ideas:
1. Three tables are self connected. 1 and 3 are the starting and ending stations respectively, and fail;
2. One table (determine the starting point) is connected (the intermediate station corresponding to the end point is selected from the connection in Tables 2 and 3), but cannot be selected.

In summary, 1 ️⃣ The self connection relationship is not clear; two ️⃣ The logic of transfer is not clear.
one ️⃣: The self connection of three tables is the same as that of two tables. where the starting point will form a group of options, and then looking for the end point can only find the direct one.
two ️⃣: Transfer logic: there is no direct relationship between the starting station and the terminal station. We need to find other station group 1 that can be reached from the starting point and other station group 2 that can be reached from the terminal point, and then find duplicates in group 1 and group 2
Therefore, it should:
one ️⃣ The two tables are self connected, where is the starting point, forming table 1, that is, all lines including the starting point and all bus stops that can be reached by each line
two ️⃣ The two tables are self connected, and the end point is where to form table 2, the same as above
three ️⃣ Find out the bus stops in Table 1 and table 2.

SELECT x1.numx1,x1.comx1,x2.namex2,x2.numx2,x2.comx2
  FROM 
  (SELECT r2.num numx1,r2.company comx1,s2.name namex1 FROM route r1 
  INNER JOIN route r2 ON r1.num=r2.num AND r1.company=r2.company
  INNER JOIN stops s1 ON r1.stop=s1.id
  INNER JOIN stops s2 ON r2.stop=s2.id
  WHERE s1.name='Craiglockhart') x1 INNER JOIN
  (SELECT r3.num numx2,r3.company comx2,s3.name namex2 FROM route r3 
  INNER JOIN route r4 ON r3.num=r4.num AND r3.company=r4.company
  INNER JOIN stops s3 ON r3.stop=s3.id
  INNER JOIN stops s4 ON r4.stop=s4.id
  WHERE s4.name='Lochend') x2
  ON x1.namex1=x2.namex2
-- final ON Yes ON The association point of the two tables and the name of the bus stop/Bus stop id

Self connection can also be directly from and then connected in where. For example, question 9:

SELECT s2.name,r1.company,r1.num 
	FROM route r1,route r2,stops s1 ,stops s2 
	WHERE s1.name='Craiglockhart'  
		AND r1.company='LRT' 
		AND r1.num=r2.num 
		AND r1.company=r2.company 
		AND r1.stop=s1.id 
		AND r2.stop=s2.id

Self join Quiz

  1. 3
  2. 1,5
    The results of the two answers should be the same, but the website gives only 5 answers
  3. 4

0 summary

It took 3 days, and the complete time should be about 10 + hours. I completed a complete question brushing, of which only 1-2 questions didn't work out and saw other people's ideas. Generally speaking, I feel that sqlzoom is still quite basic, but it also uses many things that are not in the SQL learning guide.
When reviewing, pick out the key points and take a study note.

Tags: MySQL

Posted by runawaykinms on Sun, 08 May 2022 22:06:06 +0300