A fast food joint keeps a database of menu items and calories.

   Observations

1. The database table has 6 fields - the ID field is the Primary Key field.
2. There are 260 records of data.
3. The Other calories field is simply based on a calculation: Calories - Calories from Fat
4. The database file has a filesize of 432Kb - almost half a megabyte.

A very smart database engineer realised that this design was inefficient. One of the fields - Other calories didn't need to be stored in the database. It could just be calculated when required. She designed a query to make this happen:

   New observations

1. The query designer writes the name of the new field, followed by a :
2. The query designer wraps any existing field names in square brackets - this tells the database applicaiton I am an existing field
3. Common mathematical operators can be used in the calculation eg -   +  /  *
4. The new database file size is 3.7% smaller than the original. This doesn't sound like a lot, but for huge web-based databases eg Facebook, it is significant.

   Skill-up 1 - Creating a calculated field

Most popular database applications have a totals button in the query builder menu bar - Σ. It often looks like a sigma (see above).

Download the csv file about Premier League Football in England.

Import the data into your database application. The Team field can be the Primary key.

Follow these instructions carefully:

1. After importing the data, take a screenshot of the table in Design View.
2. Then, delete the Goal difference and Points fields. Take a screenshot of this new table design and paste it into your evidence document.
3. Create a new query which displays every field and every record in the table.
4. Create a Calculated field called Goal difference which uses the calculation Goals scored - Goals conceded.
5. Test your design. Does it work as expected? If not, fix any errors and try again.
6. Create another Calculated field called Points. This uses the following formula:
   • Games Won*3+Games drawn
7. Save this query as qryCalcFields

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

You should now have 3 queries saved in your database application.

   Evidence

In your evidence document, paste a screenshot of each of your 3 saved queries in Design View. Write 3 things that you now know about queries.