SQL SELECT Statement

SQL Tutorial Home

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

customer_id customer_name email_address last_order_date
100 John Doe john.doe@superpro.com 09/28/2020
101 Mary Stanton mary@allinoemarket.com 09/28/2020
102 Dipali Gupta dipali.gupta@guptamarket.com 10/10/2020


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:

email_address
john.doe@superpro.com
mary@allinoemarket.com
dipali.gupta@guptamarket.com


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

customer_name last_order_date
John Doe 09/28/2020
Mary Stanton 09/28/2020
Dipali Gupta 10/10/2020


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:

customer_id customer_name email_address last_order_date
100 John Doe john.doe@superpro.com 09/28/2020
101 Mary Stanton mary@allinoemarket.com 09/28/2020
102 Dipali Gupta dipali.gupta@guptamarket.com 10/10/2020

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:

email_address
mary@allinoemarket.com


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

Table Sales

article_code branch region date article_family seller_id quantity
Harley 982 New Orleans SouthEast 02/06/2020 motorcycles Moine 2
Mustang Miami SouthEast 02/03/2020 cars Sexton 1
Honda N400 New York East 04/24/2020 motorcycles Gupta 1
Harley 982 Los Angeles West 05/05/2020 motorcycles Dann 1
Kenworth T680 San Francisco West 05/29/2020 trucks Garcia 3
Mustang Miami SouthEast 06/16/2020 cars Sexton 1
Harley 982 New York East 10/12/2020 motorcycles Gupta 1


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:

article_code branch quantity
Harley 982 New Orleans 2
Mustang Miami 1

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:

article_code date quantity
Mustang 02/03/2020 1
Mustang 06/16/2020 1

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!

IN THIS PAGE