Queries
A new social network called Hippopotom-us has been developed. One feature of the site allows new users to create a new account.
When a user signs up, where is all the data stored?
Watch what happens when users enter data to create their Hippopotom-us account. Click quick populate to save some time typing.
Front-end interface - click applicant to create account
Back-end database (on the server)
| id | Nickname | Age | Gender | hippoHandle | faveFruit |
|---|
Query Builder
| criteria |
|---|
When a database table has a lot of records, it can be difficult to find all of the information that we need quickly.
For example, we need to know all of the users who like apples. Or all of the users who are 13 years old.
We need to query the database. This is kind of like asking the database a question:
Hey, database! Show me all users whose favourite fruit is banana?
banana is the thing that we are looking for. It is known as the criteria.
Quickly populate the above database table.
Run these 3 queries. Is the expected result correct?
| Query | Expected result |
|---|---|
| people who like orange | 2 records |
| people aged 13 | 6 records |
| people aged 13 who like orange | 1 record |
Intro to SQL
A common language that can help us query a database table is SQL.
The following video will introduce the SQL and how we can use it to interrogate a database.
Download the csv file about the gaming industry and save it in a project folder. This data was scraped from ign.com, a popular gaming community review site.data file
SQL Queries
Import ignData.csv into a new table in a database on your localhost server in XAMPP.
Don't forget to add a new field at the beginning of the table which will be an auto-incrementing primary key!
Solve the queries and paste the SQL into your worksheet document where indicated, along with the number of records selected.
1. Create a query that displays all of the records where the score is 5.0
2. Create a query that displays all of the records where the score phrase is Amazing
3. Create a query that displays the platform and score fields where the platform is iPad
Food For Thought
Queries are a fundamental feature of database technology. They allow us to interrogate huge data sets and quickly find only the data we are interested in. We simply have to decide what the criteria is - what are we looking for?
essential vocabulary
database querycriteriaimport evidence table integer screenshot record text ascending field double descending data type Yes/No