LibreOffice Base SQL: Query Parameters

Today, I will continue the discussion of SQL queries with queries that involve parameters in queries. 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.

Parameters in Queries

Imagine that you need to query the database several times a day to know who is on what project in order to arrange meetings and disseminate information about the project. You could write a new query for each project, but this could become unmanageable over time. The SQL would look something like Continue reading →

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. Continue reading →

LibreOffice Base SQL: JOINing Tables

Today, I will continue the discussion of SQL queries with queries that involve more than one 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.

JOIN: The Table Linking Keyword

If you need to gather data from two tables, you might think you would need to run multiple SELECT commands, but you would have a difficult time linking or JOINing the two tables together.

The JOIN keyword allows us to do just that. With it we can tell the database how the two tables are related. Continue reading →

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. Continue reading →

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

Continue reading →