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:MaterialColourPriceProduct IDDescription
Table:SOFASOFASOFASOFASOFA
Sort:Descending
Show:NoNoYesYesYes
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:NameHistoryMaths
Table:MARKSMARKSMARKS
Sort:Ascending
Show:YesNoNo
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:NameEnglishMaths
Table:MARKSMARKSMARKS
Sort:
Show:YesYesYes
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:RegistrationMakeModelPriceSold
Table:CARCARCARCARCAR
Sort:
Show:YesNoYesYesNo
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:RegistrationMakeModelPriceSold
Table:CARCARCARCARCAR
Sort:
Show:YesYesYesYesNo
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:DOBFNameSName
Table:STUDENTSTUDENTSTUDENT
Sort:
Show:NoYesYes
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:DOBFNameSName
Table:STUDENTSTUDENTSTUDENT
Sort:
Show:NoYesYes
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