Getting Started in SQL Statement Fundamentals

Howdy, all, welcome back to our journey learning SQL. This post will deal with basic SQL statements. In fact, most of these SQL statements should be applicable to most major types of SQL databases (MySQL, Oracle, and so forth.)

The SELECT Statement (or Clause)

First up, we’ll start with the “Hello World” of SQL: SELECT. We’ll look at the formal conventions of the SELECT statement and some examples using the statement. A quick aside: SELECT is also often known as a clause in SQL settings. For the purpose of this article, the educational materials I’m walking through proposes clause and statement may be used interchangeably for these purposes.

SELECT is one of the most common tasks in querying data tables with SQL. Further, it has many clauses that may be combined to form a powerful query. Let’s look at the basic form of a SELECT statement. Below, you will use the SELECT statement to call in a column or some column names, separated by a comma if multiple columns, then FROM a table.

SELECT column1,column2,column3 FROM table_name;

So, breaking down again the select statement.

1. Specify a list of columns in the table which you want to query via the SELECT clause
2. Use a comma between each column you are querying, if multiple columns
   1. If you want to query all columns in a data, save yourself some time by using the * asterisk wildcard as a shortcut for selecting all columns
3. After you’ve called in the appropriate columns in the SELECT clause, follow it with FROM, where you indicate the appropriate table name

Sidebar 1: Random facts about the SELECT statement and SQL language

Time for a TV timeout! Did you know that that the SQL language is case insensitive? So if you use “SELECT” or “select”, you should get the same results. For the purposes of this education and sharing, SQL clauses / keywords / statements will be typed in all uppercase caps to make the code easier to read and stand out among all this text. 🙂

Okay, just one more sidebar note! It’s generally not encouraged to use the asterisk (*) select all columns wildcard in queries. Why? If you have a robust table with a ton of columns and a great depth of data beneath those columns, you could be placing unnecessary load on yourself, the SQL server and the SQL application (pgAdmin / PostgreSQL).

Application Example 1 for the SELECT Statement

Let’s jump into executing actual SQL commands against databases in pgAdmin!

Below, I’m going to open the file tree, select “dvdrental”, then click “Tools” in the top menu, and select “Query Tool” to execute arbitrary SQL queries and statements.

You should then see the screen below if you are in pgAdmin 4. If you are in pgAdmin 3, then it should appear as a new window.

Let’s have some fun, why not go against our own advice and query a whole table? Below, you can see in the query window, we’ve typed:

SELECT * FROM actor;

Into layman’s terms from above, we’re selecting (SELECT) all columns (*) from (FROM) table actor (actor).

Important: My image example doesn’t show it below, please, put a semicolon at the end of the line! (I got hasty making screen shots. 🙂 )

After you’ve typed the query, go to the lightning bolt above the window, and click “Execute/Refresh”. I’m just going to punch F5, because I’m about that keyboard shortcut life. In the future, I’ll likely introduce a command or action, note its keyboard shortcut and use that shortcut moving forward for any other examples.

The query should run and refresh. I now have a new tab in pgAdmin, with data output returned from my query. Let’s take a look below.

Okay, so we’ve got four columns returned: actor_id, first_name, last_name, and a last_updated. You’ll also note that below the column names are quick descriptions of the data type for each column. And of course, we see our beloved celebrity data returned below, all 200 rows.

Let’s examine further the data types listed below each column name. The integer below actor_id is pretty simple, numbers. Next, the character varying, below first_name and last_name. Character varying is essentially just string text. The (45) denotes the limit on character count length. Last, the timestame with YYYY-MM-DD and military style HH:MM:SS.XX time, without time zone. We won’t worry too much about the timestamp for now.

If you’re somewhat knowledgeable in SQL, you may rightly decry our glossing over of data types. For beginners, data types will be covered in more detail later. Data types will become increasingly important later, as we execute statements such as, WHERE, in which data types make or break the query. Promise, we’ll cover data types in more detail later.

Application Example 2 for the SELECT Statement

So we kind of broke our rules in the first SELECT statement SQL query example. However, some rules were made to be bent or broken, yes? In this example, we’ll follow best practices a bit more closely and select a column or columns by name from a table within the dvdrental database.

Remembering our SELECT column1,column2,column3 FROM table_name format, consider the below, and see it typed in (with closing semicolon on the statement!

SELECT first_name,last_name FROM actor;

Before we execute and refresh via F5, please note that I’ve not included spaces between the column names and comma in the statement. Alright, below is what we see when we execute and refresh.

In our screen shot, we see at bottom right, confirmation of the query execution. In the output window, we’ll only see what was queried: first_name, last_name. So we’ve left out the actor_id and last_updated columns.

One more note on our output, you’ll notice that all 200 rows were returned for this query. If you think about enterprise level data, that could be 200 million rows, zoinks! As we progress through our material, we’ll look at the aforementioned WHERE statements and other conditions / methods to limit or control the rows in query output.

Perfect Practice Makes Perfect

For the educational benefit, we’ll reinforce and apply what we’ve learned one more time. Let’s say that we’re a business and marketing analyst back in time when DVDs were still used (it’s okay to laugh!) We need to send a New Year’s promotional email (It’s January 2017 when this post was originally published) to all existing customers. We’re going to build and execute a query to that effect.

Below, you can see we’re still in the dvdrental database, in the arbitrary query code input window, with statement: SELECT first_name,last_name,email FROM customer;

One last quick note on syntax and formatting: you can go multi-line! In the below screenshot, we have typed the same query, but added formatting. Explained: SQL will read your code as one line until it runs into the closing semi-colon (;). A common practice is that for every keyword, a new line is created in the query. (Of course, the statement is not closed via semi-colon until appropriate.) I’ve also taken one more step below from various ranging coding practices (CSS, C++, etc.) and indented the ongoing portion of the query to help visually break up the code a bit.

Wrap-Up

Woohoo, we did it! We ran our first basic SQL queries in pgAdmin / PostgreSQL. We learned how to select all columns within a table and select separate desired columns within a table. Be sure to re-visit my other articles on learning SQL, visit the previous article on restoring an SQL database with table schema only.

In our next post, we’ll learn about using a SELECT DISTINCT statement.

Zach