Searching Data (using SQL)

Recap

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:

1. dfm_school contains (mostly publically available) information about schools in Scotland, England and Wales, along with other schools that use DrFrostMaths outside of the UK. Fields include the school id (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.
2. dfm_user contains (completely anonymised and heavily modified) data about students using DFM. (Important note: first names and surnames have been completely shuffled, all sensitive fields have been removed and the schools students are at switched round). Fields include the student id number 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.

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: fields is an array containing the list of field names, and rows is 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.

Task #1: Selecting fields for SELECT queries

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.

Task #2: Simple WHERE conditions

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.

Task #3: Using wildcards

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'.

Task #4: Using aggregate functions (not in GCSE syllabus)

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'.

Task #5: Using GROUP BY

Get the total number of points by UK town.

Get the number of schools by country which start with the letter 'A'.

Task #6: Using INNER JOIN

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.