Learn to Write Simple Queries with SQLite
In this series of lessons you’re going to learn how to query the sample database using the SQL Select statement. Once you have read this blog and viewed the series of videos you’ll be able to:
- Identify all the tables in a SQLite database.
- Understand how to list a table’s columns.
- Use the SQL Select statement to view columns, create text and mathematical results, and distinct values.
- Learn some techniques to debug your commands and fix mistakes.
Important! Please follow along and do the examples in your database while watching the video. If you haven’t already done so, get the sample database. It is free and dead simple to install.
Understanding Your Database
Start the lesson by watching this video.
A simple select statement consists of two parts. The first part describes what columns we want to view and the second part which table we’re viewing. A select statement looks like:
SELECT CustomerName FROM Customers;
In this example CustomerName is the column and Customers the table. The columns come after the SELECT keyword; whereas, the table is preceded by FROM.
In order to write queries you need to know what tables and columns are available in your databases. When using SQLite you can use the .Table command to list all the tables found in your database.
Once you find a table you would like to know more about, use the .Schema command to list all the columns in that table.
Select Multiple Columns
To select more than one column, just separate them with a comma. For instance,
SELECT CompanyName, City FROM Customers;
will return both the company name and city as a result.
In case you wondering you can mix the case when writing queries. That is
SELECT CompanyName FROM Customers;
Works just as well as
select CompanyName from Customers;
Tip! A common convention is to capitalize all the keywords. Some DBA’s feel is it easier to read commands written this way. Personally I don’t do that, but for this blog I WILL TRY. :)
Rename SQL Columns
You can rename the columns that result from your select statements. To do this use the AS statement like so:
SELECT CompanyName AS Company FROM Customers;
This displays the result as “Company” rather than “CompanyName.” This is a nice feature from a vanity point of view, but it also comes in handy when we want to return a calculate result and need to give it a name.
For instance, suppose you wanted to return the the CompanyName in all upper case. You would could write
SELECT UPPER(CompanyName) AS Company FROM Customers;
UPPER is known as a function. SQLite has many functions you can use to perform many types of calculations. We will look into all of them later, but for now, know that you can string several functions together to create complex expression to output the data how you need to do so.
Tip! If you make a mistake typing in a command, you can use the up arrow to retrieve the last line. You can then over type your mistake and reenter it.
Complex Column Expressions
Our employee table contains separate fields for first name and last name. Suppose the HR department needs to see a full name in upper case. How would you do this? Simple!
You could write
SELECT UPPER(FirstName) || " " || UPPER(LastName) AS FullName FROM Employees;
This statement may look complicated, but once we break it down, you’ll see it’s just made up of a bunch of simple elements.
- As you just learned, UPPER is used to return the upper case of a column.
- The || tells SQL to combine two values together. By the way, this is two “pipe” characters; this character is located on the key above the enter key.
- Any thing in double quotes (“) is literally displayed as is. In our case the ” ” means to output a single space.
If you read our expression, in English, it would read as “Take the uppercase of the FirstName combine it with a space and then add the uppercase of LastName to it.”
Tip! As these commands become more and more complex, it is easier to write and edit them in a word processor or simple tool like Notepad. Once you write the statement in your favorite editor, just copy it to the clipboard and paste it into the SQLite command window. Don’t forget to add the semicolon and to press enter though!
Do Math with SQL!
You can also get SQL to do math tricks for you. It can do some pretty complicated arithmetic if you allow it to do so. For today, I’ll show you how to multiply two numbers, but you can just as easily add, subtract, or divide. Later we’ll make it more involved so you can relive your Algebra II days. :)
Here are some common math operators you can use in SQL:
- * Multiply
- / Divide
- + Add
- – Subtract
We’ll use the OrderDetails table and calculate the total price by multiplying the unit price by quantity.
SELECT UnitPrice, Quantity, UnitPrice * Quantity AS Total FROM OrderDetails;
The bit “UnitPrice * Quantity” says to multiply UnitPrice by Quantity.
Be Distinct and Debug
So far we have used the select statement to retrieve all the records in a table regardless if some values repeat or not. If you wish, you can use the DISTINCT keyword to remove duplicates from your results. For instance if you wanted to just return a unique list of employees’ titles you would use this SQL statement:
SELECT DISTINCT Title FROM Employees;
The DISTINCT statement also works on more than one column. If you wanted, you could get a distinct listing customer cities and states by typing
SELECT DISTINCT City, State FROM Customers;
!Trivia: The ALL keyword is DISTINCT’s counterpart and is the default behavior. That is why we don’t write it into our statements.
Finding and Correcting Errors
Sooner or later you’ll mistype a select statement into the command line and the computer will yell at you — “Syntax Error!” or “Unknown Column!” What to do?
Just remember that the select statement is made up of two main parts: the SELECT and the FROM portion. In general, the database first looks to see if these keywords are present, and if they are not it triggers a Syntax error. This is just a fancy phrase for “you have misspelled a keyword or have it out of order” (e.g. you have the Distinct before Select as in DISTINCT SELECT).
Once the keywords are checked, the statement is inspected to make sure you have specified a table. Once the table is known it can check the columns. So the order is to first check for keywords, then for tables, and lastly for correct columns.
Whenever an error is displayed read it carefully. Chances are you misspelled something. The computer will tell you what you misspelled in quotes; it your job to then find the misspelling and correct it.
SQL Select Exercises
It’s important to practice! Use the sample database to answer these questions.
- What are the names of all the tables in our sample database?
- What are two ways to get the names of all of the columns in the orders table?
- Select company names and cities for all customers.
- What would the UnitPrice of all OrderDetails be if there was a half off sale?
The answers are revealed in the next video.
Answers to Exercises
Congratulations! You just learned how to use the select command to query a database. More tutorials are to follow, and in these we’ll dive into some of the areas we glossed over today such as mathematical operators and functions.
Remember! I want to remind you all that if you have other questions you want answered, then post a comment or tweet me. I’m here to help you.