Query design
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, it is only necessary to state the following:
- fields
- tables
- criteria
- sort order
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
| Field(s) | Area Name, Available, Manager |
| Table(s) | Sports area |
| Criteria | Available = “True” |
| Sort Order | Area Name ASC |
| Field(s) |
| Area Name, Available, Manager |
| Table(s) |
| Sports area |
| Criteria |
| Available = “True” |
| Sort Order |
| Area Name ASC |
Example 1 - 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:
| Field(s) | AreaID, AreaName, SurfaceType, Supplier |
| Table(s) | Surface, Sports Area |
| Criteria | Surface Type = “Grass” AND supplier = “WeLay” |
| Sort Order | AreaID DESC |
| Field(s) |
| AreaID, AreaName, SurfaceType, Supplier |
| Table(s) |
| Surface, Sports Area |
| Criteria |
| Surface Type = “Grass” AND supplier = “WeLay” |
| Sort Order |
| AreaID DESC |
