Queries
A new social network called Hippopotam-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 Hippopotam-us account. Click quick populate to save some time typing.
Front-end interface - click applicant to create account
Back-end database
| id | Nickname | Age | Gender | hippoHandle | faveFruit |
|---|
The social network connects people who have similar interests etc. For example, which users like oranges the most or which users have the same name.
We need to query the database. This is kind of like asking the database a question:
Hey, database! Show me all users called Jack?
Jack is the thing that we are looking for. It is known as the criteria.
Skill-up 1 - Creating a query
data file
Hint: Download it and save it in a project folder. Don't leave it in your downloads folder. Stay organised!
Reminder: check that the first row contains field names..
Make sure the fields have the following data types:
| ID | Autonumber |
| score_phrase | text |
| title | text |
| platform | text |
| score | double |
| genre | text |
| editor's choice | Yes/No (boolean) |
| release year | integer |
| qryAmazing | this query shows all records where score_phrase criteria is Amazing. | 53 records |
Hint! Your query should produce the same number of records as indicated.
Challenge 1 - Designing/Testing/Redesigning Queries
Design-Test-Redesign the following queries. Save them with the name provided eg qryAverage
Hint! Your queries should produce the same number of records as indicated.
| qryAverage | this query shows all records where score is 5. | 29 records |
| qryAboveAverage | this query shows all records where score is greater than 5. | 462 records |
| qryGoodANDAboveAverage | this query shows all records where the score_phrase is Good and the score is greater than 5. | 147 records |
| qryOkayORBelowAverage | this query shows all records where the score_phrase is Okay and the score is less than 5. Do not show the platform field in the result. Sort the records in ascending order of score | 0 records |
You should now have 5 queries saved in your database application.
Evidence
In your evidence document, paste a screenshot of each of your 5 saved queries in Design View. Make sure all of the information in the query-by-example grid is fully visible. Write 3 things that you now know about queries.
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?
Homework
essential vocabulary
database querycriteriaimport evidence table integer screenshot record text ascending field double descending data type Yes/No