Chapter 34 Databases 3, Queries

Databases | Part 3 of 3

In Chapter 33 we saw how tables can be defined within databases, and relationships created between them; some one-to-one, and some one-to-many, and we even talked about many-to-many relationships. Here, we will talking about queries and how they help us get information from our database.

Queries

We use queries to fetch data from one or multiple tables at once. Queries are like questions to the database for specific data, and are written using Structured Query Language (SQL).

Microsoft Access enables you to configure a query using their query-by-example tool. Query-by example uses a visual editor iwith table selection and query definition area as illustrated below.

SQL is a language syntax that is often used by professional data engineers, as it allows for more complex operations that cannot be achieved with a visual editor. In Microsoft Access you can always switch to SQL view through the menu.

Query Structure

A query is basically a request to the database to provide us some information for e.g., "Give me all the records from the students table that have computer science in the Subject field".

A query is made up of four parts:

• SELECT ... FROM ...

Indicates which tables the data is coming from, and which fields to return.

In SQL this consists of the SELECT ... FROM clause for e.g.,

SELECT Name, Surname FROM Players

will return that name and surname fields from the players' table.

In query by example, you select the tables using the table selector, and then define which fields to show by ticking the 'show' checkbox.

• WHERE ... AND/OR ...

Indicates which records should get picked based on one or more criteria. In SQL this is referred to as the WHERE clause for e.g.,

SELECT Name, Surname FROM Players WHERE DateOfBirth > '01-01-2003'

which will return the Name and Surname fields from Players who were born after 1st of January 2003.

In query by example, you define filters in the Criteria section of the column. The 'Or' is provided for multiple OR criteria.

• ORDER BY ... ASC/DESC

This indicates which field is used to order the data returned. In SQL this referred to as the ORDER BY clause e.g.,

SELECT Name, Surname FROM Players WHERE DateOfBirth > '01-01-2003' ORDER BY Surname ASC

which will return the Name and Surname fields from Players who were born after 1st of January 2003, in ascending order of name.

In query by example, you define sorting by selecting Ascending or Descending in the Sort dropdown on the field you want to sort with.