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:
genderof its students, the
phase(e.g. 'Secondary' or 'Primary'), and the
totalpointsthe 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
totalwatchis the time in seconds spent watching videos on the site.
Two functions are provided for you to execute queries on this platform:
query("SELECT ...")executes an SQL query, and returns a table as a result. While you do not need to look at the contents of this table directly, it is a JSON with two values:
fieldsis an array containing the list of field names, and
rowsis an array of rows, where each row is an array of values, in the same order as the field list. If there is an error with your query, the JSON will only have a value
error, a string with the error message generated.
displayTable(table)takes a table (usually generated by
query(...)and outputs it in text form to the console.
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
town fields of the
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.