Query Keywords - AND/OR
Data miners scrape data off webpages! They then analyse the data by creating queries. Using the results they will produce reports, for the benefit of anyone who will pay them, I suppose.
Queries depend on criteria. Often we can identify multiple criteria to extract a set of records from a database.
For example, imagine we have the following table:
| criteria | |||||||
|---|---|---|---|---|---|---|---|
| or |
Skill-up 1 - And/OR
Most popular database applications allow database engineers to create queries by using a visual interface. However, expert database engineers need to understand SQL - Stuctured Query Language - to design queries.
Download the csv file about social network users.
Import the data into your database application. There should be 7 fields and 28 records.
Figure out how to create/design, run/test and save the following query:
| qryUKUSA | this query shows all records where people over the age of 20 live in UK or USA | 7 records |
Hint! Your query should produce the same number of records as indicated.
data file
Be smart! Download the file and save it in a new project folder. Don't leave it in the downloads folder of your computer. You are not a newbie!
Challenge 1 - More complex 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.
| qrySingapore | this query shows all people living in Singapore who like noodles or HBO movies. | 2 records |
| qryEightPizza | this query shows all records where users are 18 or 28 or 38 and love any kind of pizza. | 2 records |
| qryUKUSACanada | this query shows all records where users from UK, USA and Canada are music or movie lovers. It is sorted in descending order of location. | 4 records |
You should now have 4 queries saved in your database application.
Challenge 2 - A chart
Design-Test-Redesign the following queries. Save them with the name provided eg qryAverage
Create a pie chart clearly showing the number of people from UK, USA and Canada who like movies.
Evidence
In your evidence document, paste a screenshot of each of your 4 saved queries in Design View and a screenshot of your pie chart. Write 3 things that you now know about queries.
Food For Thought
And and Or are magical words in Database Technology. They allow you to create more complex queries. Experience will teach you how to use them properly.
Homework
essential vocabulary
database querycriteriaimport evidence table integer screenshot record text ascending field double descending data type Yes/No