LibreOffice Base SQL: Aggregation, Data Herding

Today, I will continue the discussion of SQL queries with queries that involve different ways to group or derive new data based on the data in a table. The SQL in this post is based on the TeamMembers database. For instructions on setting up the database in LibreOffice and a short description of the database and tables, see the post at http://wp.me/pvwht-a4.

Herding the Data in Tables

The word aggregation comes from the Latin with a literal meaning of “to herd.” So, an aggregation is a collection or gathering of things together. In this post I will show you how to herd some of the data in your tables. I will also show you how to get new data derived from the data in a table.

LibreOffice Base SQL: Ordering Results

Today, I will continue the discussion of SQL queries with the ordering of query results. The SQL in this post is based on the “TeamMembers” database. For instructions on setting up the database in LibreOffice and a short description of the database and tables, see the post at http://wp.me/pvwht-a4.

The ORDER BY Clause

Getting information from the database tables is great, but often you will need to sort them, giving them more meaning or making them easier to understand. The ORDER BY clause allows you to do just that. The ORDER BY clause always goes at the end of the SELECT statement.

LibreOffice Base SQL: Logical Operators

Today, I will continue the discussion of SQL queries by discussing the the logical operators available to narrow or broaden a search. The SQL in this post is based on the “TeamMembers” database. For instructions on setting up the database in LibreOffice and a short description of the database and tables, see the post at http://wp.me/pvwht-a4.

Rehashing AND and OR

In the post on Updating and Deleting Records, I wrote about using AND and OR to combine multiple conditions for a WHERE clause. This allows us to answer questions based on more than one field in the table. As I showed in the post, this allows us to narrow or broaden our target data closer to what we want.

Order of Precedence

Just like in mathematics, the logical operators in SQL have an order of precedence. What that means is that conditions related to certain operators are evaluated before the others regardless of their order. Here is the operator order

• Brackets ()
• NOT
• AND
• ALL, ANY, BETWEEN, IN, LIKE, OR, SOME

LibreOffice Base SQL: Basic Queries

Today, I begin the first of several posts on creating SQL queries in LibreOffice. I will start with the basics and work my way up to the more complicated. In each case, I will use the “TeamProject” database for my example queries. See the post at http://wp.me/pvwht-a4 to set up the database on your system.

SELECT Command Basics

The SELECT command is used to gather information about the data contained in a database. A SELECT command is also known as a query. The purpose of a query is to answer a question.