This workshop is designed to build your confidence in querying data using SQL.
We will be working with the dataset in the init.sql
file. This file contains a set of SQL commands that will create a set of tables and fill them with data. We will connect to the mysql server running locally on our individual computers and tell it to run the file.
Please download the init.sql file.
Now import the init.sql into your database.
Here are the schema diagrams to help:
Column | Type | Modifiers |
---|---|---|
id | integer | not null default |
first_name | character varying(100) | not null |
surname | character varying(100) | not null |
location | character varying(100) |
Column | Type | Modifiers |
---|---|---|
id | integer | not null default |
name | character varying(100) | not null |
release_date | date | not null |
publisher_id | integer | foreign key (publishers.id) |
Column | Type | Modifiers |
---|---|---|
id | integer | not null default |
name | character varying(100) | not null |
Column | Type | Modifiers |
---|---|---|
book_id | integer | foreign key (books.id) |
author_id | integer | foreign key (authors.id) |
Please don't feel that you have to get through all of them or be able to answer them all right away! The idea is to introduce you to the kind of queries we do regularly with SQL.
These challenges cover the basics of SQL: selects, joins and conditions.
first_name | surname |
---|---|
Sharon | Smith |
Ted | Burns |
Stephen | Wistle |
Amanda | Bertwistle |
David | Grewal |
John | White |
Paul | Hallam-Wistle |
Paul | Jones |
id | first_name | surname | location |
---|---|---|---|
4 | Amanda | Bertwistle | Nazareth |
2 | Ted | Burns | London |
5 | David | Grewal |
id | first_name | surname | location |
---|---|---|---|
1 | Sharon | Smith | Nazareth |
2 | Ted | Burns | London |
4 | Amanda | Bertwistle | Nazareth |
6 | John | White | London |
7 | Paul | Hallam-Wistle | London |
8 | Paul | Jones | Nazareth |
id | first_name | surname | location |
---|---|---|---|
2 | Ted | Burns | London |
3 | Stephen | Wistle | |
5 | David | Grewal | |
6 | John | White | London |
7 | Paul | Hallam-Wistle | London |
id | first_name | surname | location |
---|---|---|---|
3 | Stephen | Wistle | |
4 | Amanda | Bertwistle | Nazareth |
7 | Paul | Hallam-Wistle | London |
'No Starch Press'
name | name |
---|---|
No Starch Press | Python Made Easy |
No Starch Press | JavaScript: The Really Good Parts |
Note: Only one author per row, so the book's name may need to be repeated.
name | first_name | surname |
---|---|---|
Python Made Easy | Sharon | Smith |
Python Made Easy | David | Grewal |
Python Made Easy | Amanda | Bertwistle |
SQL: Part 2 | Sharon | Smith |
JavaScript: The Really Good Parts | Stephen | Wistle |
JavaScript: The Really Good Parts | David | Grewal |
Java in Japanese | Paul | Jones |
Java in Japanese | Ted | Burns |
Java in Japanese | Stephen | Wistle |
Java in Japanese | David | Grewal |
Java in Japanese | Amanda | Bertwistle |
Elm Street | David | Grewal |
Elm Street | John | White |
Elm Street | Sharon | Smith |
CSS: Cansei | Amanda | Bertwistle |
CSS: Cansei | Paul | Hallam-Wistle |
Ruby Gems | Ted | Burns |
Ruby Gems | Paul | Hallam-Wistle |
C++ | Paul | Jones |
C++ | John | White |
C++ | David | Grewal |
C++ | Sharon | Smith |
CoffeeScript in Java | Paul | Hallam-Wistle |
CoffeeScript in Java | Stephen | Wistle |
Swift in 10 Days | Stephen | Wistle |
Swift in 10 Days | David | Grewal |
'Java in Japanese' and 'Ruby Gems'
These slightly trickier challenges will require you to use aggregate functions and/or subqueries.
first_name | surname |
---|---|
Paul | Hallam-Wistle |
David | Grewal |
Sharon | Smith |
Amanda | Bertwistle |
Stephen | Wistle |
name | count |
---|---|
McGraw-Hill | 4 |
The Big Publishing House | 3 |
No Starch Press | 2 |
Mega Corp Ltd | 1 |
name | count |
---|---|
Java in Japanese | 5 |
C++ | 4 |
Elm Street | 3 |
Swift in 10 Days | 2 |
CoffeeScript in Java | 2 |
Ruby Gems | 2 |
Highest: 'Java in Japanese' (5 authors)
Lowest: 'SQL: Part 2' (1 author)
David Grewal, 6