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.
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.
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.
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”.
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.