����tv

It is important to take time to design any queries that may need to be implemented later.

Query design does not need to include specific SQL (Structured Query Language) commands. SQL will only be used during implementation.

At the design stage at Higher level, it is necessary to state the following:

• fields
• tables
• search criteria
• sort order
• calculations
• grouping

Short hand can be used for the terms Ascending (ASC) and Descending (DESC).

Sports centre example

Example 1 - Availability query

Here is the design for a query that should return all sports areas that are available for use.

The areas' names should be listed in ascending order.

The query should return:

• area name
• whether it is available
• the name of the manager responsible for that area

Query Design: List of Sports Areas by name in ascending order

Example 2 - Surface supplier query

Here is the design for a query that will return all sports areas where the surface used is grass and the supplier is the company called 'WeLay'.

The areas' IDs should be listed in descending order.

The query will list:

• area ID
• area name
• surface type
• supplier of the surface

Query Design:

Example 3 - Calculate cost to hire sports area query

Here is the design for a query that will return all sports areas with a HireCost less than £50 and which are available to hire. For this example, let’s assume that the person hiring can spend £50.

The hire cost should be listed in descending order.

The query will list:

• area ID
• area name
• surface type
• hire cost
• whether it is availble

Query Design: