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.

Advertisements

One comment on “LibreOffice Base SQL: Basic Queries

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s