First Step : 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
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!