Queries

The real power of a relational database lies in its ability to quickly retrieve and analyze your data by running a query. Queries allow you to pull information from one or more tables based on a set of search conditions you define. For example you could ask question such as: what are the available books in my library?, who bought this product in the last month?

Creating a query

LibreOffice Base allow us to design, execute and store a query. You can create a query with one of the following ways:

• Create a Query in Design View. This method allows us to design a query from scratch. Use this method because it will help you undestand the comcept of queries.
• Use Wizard to Create a Query. The wizard will guide you to create a query from a selected table.
• Create a Query in SQL View. SQL stands for Structured Query Language. SQL is the standard language for relational database management systems. If you want to really dive into databases you should learn the SQL language. This lesson however does not cover SQL.

To create a query select the Queries object from Database objects types and click on the Create Query in Design View Task.

A query needs at least one table or another query where it can retrieve the data. Select the Books table from the list to add it to the query.

Once you add the table close the Add Table or Query window. Now the Query Design window appears. The upper part of this window displays the data sources where the query retrieves data from. In the bottom part we set the query parameters.

Query parameters

Let’s now for example design a query that will ask the question: What books are available in my library? Show me the title and author of these books. To design a query we must specify it's parameters:

• Fields. The table fields to show in query results or use in the query's criteria.
• Sorting. You can specify sorting for the results by one or more fields.
• Criteria. This is where we actually set the conditions for the query. We can set multiple criteria in a query.
• Visible. If enabled the field will be visible in query results.

For our query we set the following query parameters:

Fields: Title, Author, isAvailable.
Sort: Ascending by Author.
Visible: Title, Author.
Criterion. isAvailable = 1. For boolean types the value "1" means "True" and "0" means "False".

Running the query

To run the query and display the results click on the Run Query button on the toolbar. The query results are now displayed in the Query window. Now click the save button to save the query with the name AvailableBooks.

Editing the Query

When a query is saved, is listed in the Queries window. You can now open, edit, delete or rename the query using the Query toolbar buttons on top of the main Base window.