Topics / Databases / Database Queries

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.

Topics / Databases / Database Queries