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.

Here is a simple example of a SELECT with an ORDER BY:

 
SELECT "FirstName", "LastName"  
FROM "TeamMembers" 
ORDER BY "LastName"; 

This query will create a data set containing all the team members in the table sorted by last name. By default the sort is done in ascending (ASC) order. To reverse the order add descending (DESC) sort to the ORDER BY.

 
SELECT "FirstName", "LastName"  
FROM "TeamMembers" 
ORDER BY "LastName" DESC; 

ORDER BY Any Field in the Table

You do not have to include your sort field in the SELECT list. You can use any field in the table to sort the records.

 
SELECT "FirstName", "LastName"  
FROM "TeamMembers" 
ORDER BY "DateOfHire"; 

This query selects the first and last name of all the team members and sorts them by their date of hire. Note that the DateOfHire field is not in the select list. If you need confirmation that the sort was done correctly, add the DateOfHire field to the select list to see if the order of the resulting records change (it will not).

ORDER BY More than One Field

You can also sort by more than one field. The query will first sort by the first field given in the ORDER BY clause. If any of the first fields are the same, it will then sort them using the second field in the ORDER BY list. If you have a third field, it would sort if the first two were the same, etc.

SELECT "FirstName", "LastName" 
FROM "TeamMembers" 
ORDER BY "LastName", "FirstName"; 

This query selects the first and last name of all the team members, then sorts them by last name and first name. In other words, Johnny Walker would come in the list before Sandy Walker.

Works with WHERE

Just like with other clauses and operators, you can use the ORDER BY with the other phrasing in your SQL.

 
SELECT "FirstName", "LastName", "DateOfHire" 
FROM "TeamMembers" 
WHERE "DateOfHire" > '2005-01-01' 
ORDER BY "LastName", "FirstName"; 

This query selects all the team members who were hired after January 1, 2005, sorting the results by last name and then first name.

The ORDER BY clause allows you to sort the results of a query. You don’t have to use a field from the SELECT list, and you can sort your results by more than one field. The ORDER BY is always at the end of your query.

Next time, we will look into creating queries using more than one table.

Written with StackEdit.

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

Note that AND has greater precedence than OR. This means that AND conditions are evaluated before OR conditions. Let say we need a list of all the married members in New Town and New York. On first instinct, you might try the following SQL:

SELECT “FirstName”, “LastName”, “Married”
FROM “TeamMembers”
WHERE “City” = ‘New Town’
OR “City” = ‘New York’
AND “Married” = True;

However, because of the order of precedence, this evaluates to a list of team members in New Town and married members in New York. I added the married field here so you will see the unmarried team member that shows up in this query.

To correct this query so we get what we wanted, we just need to add brackets [()].

SELECT “FirstName”, “LastName”, “Married”
FROM “TeamMembers”
WHERE (“City” = ‘New Town’
OR “City” = ‘New York’)
AND “Married” = True;

This groups the two city conditions together, giving us the correct results.

Other Logical Operators

There are four other logical operators to consider today. We will cover ALL, ANY, and SOME at a later time. The four we will go over today are NOT, BETWEEN, LIKE, and IN.

NOT

NOT negates or reverses the condition of the statement. The truth table for the NOT operator is

Statement Result
NOT true False
NOT False True
SELECT “FirstName”, “LastName”
FROM “TeamMembers”
WHERE NOT “City” = ‘New York’;

This would give us a list of team members who are not in New York. Be aware that this query produces the same results as

SELECT “FirstName”, “LastName”
FROM “TeamMembers”
WHERE “City” <> ‘New York’;

which just demonstrates there is more than one way to do something in SQL.

BETWEEN

BETWEEN allows you to pull from a range of values. This is the same as asking whether something is greater than or equal to (>=) one value, and less than or equal to (<=) another value. In fact, you could use these two conditional operators and get the same results as BETWEEN, but as you will see, BETWEEN is shorter and more compact.

SELECT “FirstName”, “LastName”
FROM “TeamMembers”
WHERE “DateOfHire” BETWEEN
‘1990-01-01’ AND ‘2010-01-01’;

This would give use the names of all team members hired between January 1990 and January 2010. BETWEEN is inclusive, which means that it includes the start and end values. So, if someone was born January 1, 1990, their name is included in the list. You can also negate BETWEEN with a NOT.

SELECT “FirstName”, “LastName”
FROM “TeamMembers”
WHERE “DateOfHire” NOT BETWEEN
‘1990-01-01’ AND ‘2010-01-01’;

LIKE

LIKE is used to match patterns in strings of characters by using the wild card characters underscore (_) and percent (%). Underscore is a place holder for any single character, but the percent is a place holder for one or more characters. Let’s do some examples.

SELECT “FirstName”, “LastName”
FROM “TeamMembers”
WHERE “LastName” LIKE ‘_enson’;

This query includes the two last names Benson and Jenson. We can create a broader search with the percent wild card.

SELECT “FirstName”, “LastName”
FROM “TeamMembers”
WHERE “LastName” LIKE ‘%on’;

This query includes not only Benson and Jenson but Hamelton and Benton. LIKE is handy when you are not exactly sure just what it is you are looking for or how it is spelled.

We can use not as well to get a list of names who don’t end in “on.”

SELECT “FirstName”, “LastName”
FROM “TeamMembers”
WHERE “LastName” NOT LIKE ‘%on’;

This query gives us a list of names like Walker, Smith, and Donovan that do not end in “on.”

IN

IN checks to see if the value is in a set of possible values. We can manually create the search set or use an internal query to create the set. For today, we will create a manual set, but later we will discuss internal queries.

SELECT “FirstName”, “LastName”
FROM “TeamMembers”
WHERE “LastName”
IN (‘Walker’, ‘Smith’, ‘Donovan’);

This query gives you all the Walkers, Smiths, and Donovans. Note the set in brackets. Of course, we can negate this with a NOT to get a list of everyone except the Walkers, Smiths, and Donovans.

SELECT “FirstName”, “LastName”
FROM “TeamMembers”
WHERE “LastName”
NOT IN (‘Walker’, ‘Smith’, ‘Donovan’);

Logical operators help us to asks better questions when creating a query. Just like in mathematics, the operators are controlled by an order of precedence. AND and OR allow us to join more than one condition to the query. NOT negates any condition it is associated with. To select from a range of values, we can use BETWEEN. LIKE allows us to search the database using a pattern. Selecting only records matching a set of values is done using IN.

In my next post, we will discuss arranging the result set in a certain order.

Written with StackEdit.

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. The most basic form of the SELECT statement is

SELECT field1, field2,..fieldx FROM tablename;

The SELECT command lists a set of fields you want to extract FROM a certain table. Let’s look at a couple of examples. The results we get from a query is called a dataset. The dataset only contains the information you request. This is handy, because often you don’t need all the information the table.

SELECT “FirstName”, “DateOfHire” FROM “TeamMembers”;

This will display all the first names and hire dates for all the records in the “TeamMembers” table.

SELECT * from "TeamMembers";

The asterisks (*) is a special wild card that allows you to display all the fields. This query would display all the fields for all the records.

Distinct Fields

Sometimes, you just need to extract certain information from a table. For example, how many cities do the team members represent? The temptation is to enter the query

SELECT “City” FROM “TeamMembers”;

but this would show the city for every record, creating repetition. The correct way to do this is to add the keyword DISTINCT to the command.

SELECT DISTINCT “City” FROM “TeamMembers”;

This will display just one record for each city. Any repetition is ignored. You can use DISTINCT with more than one field.

SELECT DISTINCT “City”, “LastName” FROM “TeamMambers”; 

In this case, the query will ignore any records where the city and last name repeat. To see the records unfiltered, run the command without the DISTINCT keyword.

Adding WHERE

To filter our queries even more, we can use the WHERE clause. Just as we did with the UPDATE statement, we can use the WHERE clause to better answer the question we are asking. Using what we already known about the WHERE clause, we can do the following:

SELECT * FROM “TeamMembers” 
    WHERE “DateOfHire” > '2000-01-01'; 

This command will display a list of team members who were hired after January 1, 2000. Running this command will display all the team members except for 2.

SELECT * FROM “TeamMembers” 
    WHERE “DateOfHire” < ‘2000-01-01’;

Swap the sign to see the other two who were hired before January 1, 2000.

Aliases

Sometimes, you want to reference a field or group of fields by a different name. Aliases allow you to create an “alias” name in the resulting dataset. If for some reason, we needed the field “City” named “Place”, we could execute the following command.

SELECT “City” AS “Place” FROM “TeamMembers”; 

The AS clause defines the alias.

The real power of aliases comes when we combine more than more field into one, as in the following example.

SELECT “LastName” || ', ' || “FirstName” AS “FullName” 
    FROM “TeamMembers”; 

The double piping symbols (||), are used to concatenate, or join, multiple fields together. Here we concatenate the last name to a comma and space, then concatenate the result to the first name. We give the new concatenated string the alias “FullName”.

Altogether Now

Let me leave you with one last bonus query. This one will use everything discussed in this post except for DISTINCT. I couldn’t find a way to add DISTINCT to the mix without it feeling redundant.

SELECT “City”, 
    “LastName” || ', ' || “FirstName” AS “FullName” 
    FROM “TeamMembers” 
    WHERE “City” = 'New York'; 

In this query, we select multiple fields, use an alias, and a WHERE clause. This shows how you can combine different pieces to begin to create more complicated queries.

Next time, we will revisit the WHERE clause by examining the operators that allows us to create more complex filters.

Written with StackEdit.

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