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 = 鈥淭rue鈥 |
| Sort Order | Area Name ASC |
| Field(s) |
| Area Name, Available, Manager |
| Table(s) |
| Sports area |
| Criteria |
| Available = 鈥淭rue鈥 |
| 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 = 鈥淕rass鈥 AND supplier = 鈥淲eLay鈥 |
| Sort Order | AreaID DESC |
| Field(s) |
| AreaID, AreaName, SurfaceType, Supplier |
| Table(s) |
| Surface, Sports Area |
| Criteria |
| Surface Type = 鈥淕rass鈥 AND supplier = 鈥淲eLay鈥 |
| Sort Order |
| AreaID DESC |