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

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

LibreOffice Base SQL: Inserting Data Records

In most cases you will never use SQL to insert data into Libreoffice Base tables as this is mostly done through forms. However, it helps to know how the SQL works for those occasions when you get error messages when trying to input data through a form, and there are also times when you just need a small table with a few records for selection in a form. Creating a form in the latter case might take more time than just entering the data through SQL. Today, I am going to show you the basics of inserting records into a table. Continue reading