Database Queries
A major benefit of storing information in a database is the ability to perform queries.
A query is the tool that allows us to ask the database a question and get back any matching records (a search).
We create queries by choosing at least one set of criteria upon which we wish to search. Complex multiple criteria searches are possible and the results can be sorted in ascending or descending order.
Queries are performed using a special language called SQL, however most Database Management Systems also provide an easier visual method of creating a query.
These visual tools are sometimes referred to as a query-by-example.
Query-by-example
Below are some examples of searches (queries) and how they would be created using query-by-example.
Example 1
| Field: | Material | Colour | Price | Product ID | Description |
|---|---|---|---|---|---|
| Table: | SOFA | SOFA | SOFA | SOFA | SOFA |
| Sort: | Descending | ||||
| Show: | No | No | Yes | Yes | Yes |
| Criteria: | =’Leather’ | =’Brown’ | |||
| or: |
A search to select the price, product ID and description for all brown leather sofas, in descending order of price.
Example 2
| Field: | Name | History | Maths | ||
|---|---|---|---|---|---|
| Table: | MARKS | MARKS | MARKS | ||
| Sort: | Ascending | ||||
| Show: | Yes | No | No | ||
| Criteria: | >50 | ||||
| or: | >50 |
A search to select the name of all the students, in ascending order, with more than 50 marks in History or more than 50 marks in Maths.
Example 3
| Field: | Name | English | Maths | ||
|---|---|---|---|---|---|
| Table: | MARKS | MARKS | MARKS | ||
| Sort: | |||||
| Show: | Yes | Yes | Yes | ||
| Criteria: | <30 | <30 | |||
| or: |
A search to select the names and marks for all the students with less than 30 marks in both English and Maths.
Example 4
| Field: | Registration | Make | Model | Price | Sold |
|---|---|---|---|---|---|
| Table: | CAR | CAR | CAR | CAR | CAR |
| Sort: | |||||
| Show: | Yes | No | Yes | Yes | No |
| Criteria: | =’Ford’ | False | |||
| or: |
A search to select the registration, model and price of all the Ford cars that haven’t been sold yet.
Example 5
| Field: | Registration | Make | Model | Price | Sold |
|---|---|---|---|---|---|
| Table: | CAR | CAR | CAR | CAR | CAR |
| Sort: | |||||
| Show: | Yes | Yes | Yes | Yes | No |
| Criteria: | Like ‘F*’ | ||||
| or: |
A search to select the registration, make, model and price of all the cars where the make begins with the letter F.
Example 6
| Field: | DOB | FName | SName | ||
|---|---|---|---|---|---|
| Table: | STUDENT | STUDENT | STUDENT | ||
| Sort: | |||||
| Show: | No | Yes | Yes | ||
| Criteria: | <01/01/2000 | ||||
| or: |
A search to select the first name and surname of all students born before the year 2000.
Example 7
| Field: | DOB | FName | SName | |
|---|---|---|---|---|
| Table: | STUDENT | STUDENT | STUDENT | |
| Sort: | ||||
| Show: | No | Yes | Yes | |
| Criteria: | Between 01/01/2000 and 31/01/2000 | |||
| or: |
A search to select the first name and surname of all students born between 1st and 31st January 2000.
