SQL is a language that can be used to search for (or manipulate) data in a database. An exploration of the language can be found in the slides above.
You have two database tables available to you:
sid
), town
, postcode
, gender
of its students, the country
, its phase
(e.g. 'Secondary' or 'Primary'), and the totalpoints
the school has accrued up to June 25th 2019 from using the DrFrostMaths system, although points data for most schools has been shuffled between schools.uid
, their school using its sid
identifier, the lastlogin
date/time, their totalpoints
, numquestions
answered, and totalwatch
is the time in seconds spent watching videos on the site.Simply type your SQL query directly into the box. If you run the two pieces of code below, it should hopefully be clear how it works: (use the Fullscreen links to see the results tables more clearly)
Note that only the first 100 rows of your table will be displayed if the number of rows is in excess of this.
Select just the name
and town
fields of the dfm_school
table.
Select just the distinct countries used by DrFrostMaths. Remember that you can use the DISTINCT
keyword before a field name to avoid duplicates.
Get the names of all schools using DrFrostMaths in Hungary.
Get the names and total points of all schools with over a million points.
Get the list of countries which have a school with over a million points.
Get the list of users who have between 1000 and 2000 points.
Get the names (and points) of all schools starting with the letter T.
Get the names of any schools with the word 'House' anywhere within the name.
Get the names of any schools whose 3rd letter is 'z'.
Get the total time (in seconds) students have spent watching videos on the site.
Get the number of students whose first name is Dave.
Get the average number of points of students whose surname begins with the letter 'Q'.
Get the total number of points by UK town.
Get the number of schools by country which start with the letter 'A'.
Get the names of all students at Tiffin School. You should avoid first determining the sid of this school and should use a single query.