Solutions to the challenges on http://sqlzoo.net
jisaw / sqlzoo-solutions Goto Github PK
View Code? Open in Web Editor NEWSolutions to the challenges on http://sqlzoo.net
Solutions to the challenges on http://sqlzoo.net
Solutions to the challenges on http://sqlzoo.net
--#13
/*
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,
CASE WHEN continent = 'Oceania' THEN 'Australasia'
WHEN continent = 'Eurasia' OR name = 'Turkey' THEN 'Europe/Asia'
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
ORDER BY name
should be
SELECT name, continent,
CASE WHEN continent = 'Oceania' THEN 'Australasia'
WHEN continent = 'Eurasia' OR name = 'Turkey' THEN 'Europe/Asia'
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
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 cast
FROM movie JOIN casting on id=movieid
WHERE yr = 1978
GROUP BY title
ORDER BY cast DESC, title
On question 10 in the self-join section, there is no suggestion that the data needs to be ordered, and it seems you've somehow stumbled upon the order they were looking for but it's utterly baffling. Why this ordering, why some num
s use LENGTH
and others don't, etc.? Was this trial and error?
It seems GROUP BY mdate,team1,team2 should be updated to mdate,matchid,team1,team2 otherwise it returns an error. Since you are using ORDER BY on matchid, first GROUP BY should be used for it.
What do you think?
Line 141 in b7389ce
"group by" need "matchid" column for order by
SELECT mdate,
team1,
SUM(CASE WHEN teamid = team1 THEN 1 ELSE 0 END) AS score1,
team2,
SUM(CASE WHEN teamid = team2 THEN 1 ELSE 0 END) AS score2 FROM
game LEFT JOIN goal ON (id = matchid)
GROUP BY mdate,team1,team2
ORDER BY mdate, matchid, team1, team2
SELECT mdate,
team1,
SUM(CASE WHEN teamid = team1 THEN 1 ELSE 0 END) AS score1,
team2,
SUM(CASE WHEN teamid = team2 THEN 1 ELSE 0 END) AS score2 FROM
game LEFT JOIN goal ON (id = matchid)
GROUP BY mdate, matchid, team1,team2
ORDER BY mdate, matchid, team1, team2
I found that the solution posted as one today is incorrect.
The following worked for me:
SELECT name,
CONCAT(ROUND(
(population*100) /
(SELECT population FROM world WHERE name = 'Germany'), 0
), '%')
FROM world
WHERE continent = 'Europe'
Query should be
select
mdate,
team1,
SUM(CASE WHEN teamid = team1 THEN 1 ELSE 0 END) AS score1,
team2,
sum(case when teamid=team2 then 1 else 0 end) as score2
from game left join goal
on (id=matchid)
group by matchid, mdate, team1, team2
order by mdate, matchid, team1, team2
The original answer doesn't have group by matchid, only groups by mdate, team1, team2.
But the question is to "List every match with the goals scored by each team as shown" instead of list every date. If the two teams had more than one match on certain date, the original answer will be problematic.
I fix the error
Keep getting
Error:
Incorrect syntax near the keyword 'IN'.
not sure why
--#14
/*
The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1.
Show the 1984 winners ordered by subject and winner name; but list Chemistry and Physics last.
*/
SELECT winner, subject, subject IN ('Physics','Chemistry')
FROM nobel
WHERE yr=1984
ORDER BY subject IN ('Physics','Chemistry'),subject,winner
will be
SELECT winner, subject
FROM nobel
WHERE yr=1984
ORDER BY subject IN ('Physics','Chemistry'),subject,winner
Incorporating NULL into answer
SELECT name FROM world
WHERE gdp > ALL(SELECT gdp FROM world WHERE gdp IS NOT NULL
AND continent = 'Europe')
AND continent != 'Europe'
For instance https://sqlzoo.net/wiki/The_JOIN_operation?answer=1
This "cheat mode" does not work for all pages, though.
Please mention that in the README, some people might find it more convenient than going through SQL files (personally I prefer only checking on GitHub if I really can't find the solution by myself though).
Thanks! :-)
The query as is throws the above error. I fixed it with the following syntax:
SELECT company, num, COUNT(*) AS visits
FROM route WHERE stop=149 OR stop=53
GROUP BY company, num
HAVING COUNT(*)=2
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.