SQL SELECT Statement

You are starting on an exciting path with plenty of new concepts, especially If you are new to the world of databases. Get ready to discover a language that is simple to learn, but at the same time very powerful for querying the database. In this tutorial you use SQL to query databases. A database query is an expression written in SQL language that describes the data you want to obtain from the database. For example, you could write an SQL query to return the names and emails of customers who have not sent orders for more than 6 months.

Database Foundation Concepts

Before we start with SQL queries, let’s review three basic database related concepts: Tables, Records and Attributes.

A table is a set of elements called records (or rows). The records stored in a table are not ordered, this is the reason why we describe the table as a “set” of records. Tables are one of the building blocks in databases. Some typical examples of tables are Customer table, Order table or Invoice table.

The second concept is records. Records are the elements stored in a table. All the records stored in one specific table have the same structure or schema. For example, in this image we can see the records in the Customer table, all with the same structure: customer_id, customer_name, email_address and last_order_date.

Table Customer

The third database concept is attributes (also called columns). In the previous image the record structure has four attributes: customer_id, customer_name, email_address and last_order_date. Every attribute should have a value.

All the attribute values in the same record describe the same element in the table. Another point is data types. Every attribute belongs to a data type. Possible data types are: text, numeric, date and other more specific types. For example the data type of the attribute last_order_date is date, while the data type of the attribute customer_id is integer.

SQL Syntax: SELECT and FROM

Understanding the basic concepts of databases is key to explaining SQL. The focus is database queries. The primary SQL statement for queries is SELECT.

SELECT is used to specify the list of attributes you want to obtain from the database.

SELECT is used in conjunction with the FROM clause, to specify the table for the data extraction.

For example, if you want to obtain the customer emails:

SELECT email_address FROM customer

After executing the previous SQL the database returns the results:

Alternatively, if you want to obtain the name of the customers and the date of the last order:

SELECT customer_name, last_order_date FROM customer

The result is

To obtain all the attributes of the table customer:

SELECT customer_id, customer_name, email_address, last_order_date
FROM   customer

And naturally the result is the complete table:

The * character is a shortcut meaning all of the columns when used inThis is a good point to explain the meaning of the “*” character in the SELECT For example, to return the complete table:

SELECT *
FROM   customer

Filtering records with WHERE

In the previous section you learned how to include specific attributes from the table into the query result using SELECT. So far, your query results included all the records in the table. In this section you learn how to include(or exclude) specific records from the table into the query result. It is very common to see the term filter in smart phone applications.his is the idea of the WHERE clause: filter records from the table and send the subset of records to the result set of the query.

As an example, suppose the marketing area asks for a report with the emails of those customers that did not place an order since Oct 1st 2019. You can use WHERE with a condition like:

last_order_date < ‘10/01/2019’.

The records where this condition is TRUE are part of the result. The complete query is:

SELECT email_address
FROM   customer
WHERE  last_order_date < ‘10/01/2019’

Here is the query result:

You can create complex WHERE clauses by combining several conditions. Here is a new Sales table for the next examples:

Table Sales

You are asked to create a report with all the articles sold in the SouthEast region during the first quarter of 2020. In the report, you need the attributes: article_code, branch, seller_id and the quantity sold. The query is:

SELECT article_code, branch, seller_id, quantity
FROM   sales
WHERE  region = ‘SOUTHEAST’
  AND  date >= ‘01/01/2020’ AND date <= ‘03/31/2020’

The WHERE clause has three conditions connected by the AND logical operator, meaning that only records where both the region and date conditions are TRUE are part of the result set. Using several conditions connected by the AND/OR logical operators enable you to create complex WHERE clauses. Here is the query result:

Suppose you want to create a report with all the sales of the article Mustang sold by seller Sexton during 2019. The query is:

SELECT article_code, date, quantity
FROM   customer
WHERE  seller_id = ‘SEXTON’
  AND  article_code = ‘Mustang’

And the results of the query will be:

Summary

In this first article you learned the ABCs of the SELECT statement, including:

• Creating a simple WHERE condition to filter rows
• Using the AND logical operator to connect more than one logical condition

This is only a small part of the expressive power of SELECT. Continue with the next articles to advance to more complex forms of SELECT. Learn SQL, increase your skills!