SQL 101 - SELECT, FROM, WHERE Clauses


The goal of this post is to introduce you to SQL and take you through the minimum requirements of building a SQL query to pull data. We'll be coming out with SQL 201 soon, which will cover more advanced topics.


What is SQL and how can I use it?

SQL stands for Structured Query Language. SQL lets you access and manipulate data within databases. Not only can you pull data with SQL, you can also do most modifying actions to a database including, inserting, deleting, updating records.


Database tables

A database typically contains one or more tables, each can be uniquely identified by a table name. Below is an example table that we’ll be using to practice our SQL skills. The table below has the daily menu at a restaurant.


A basic SQL query

The query below is the most basic SQL query you can write to select data from a table.


SELECT * 
FROM daily_menu; 

The SELECT statement indicates that you’d like to select data. The “*” operator indicates all, meaning that you want to select all columns from the table. FROM indicates where you want to select this data from.


SELECT statement

Within the “A basic SQL query” section above, you’ve learned that “*” operator allows you to select all of the columns from a given table. What if you only want to select 1-2 columns? You can specify that within the SELECT clause. See the example below, we’re only going to select all menu item names and their id’s within the daily_menu table.


SELECT  item_name, item_id  
FROM daily_menu; 


Note that within the SELECT statement we can also do data aggregations (e.g. sum, if clauses, windowing functions), we don’t just need to select unmanipulated columns.


SELECT DISTINCT statement

As you can see from above, when we select item_name and item_id we get duplicate records. If we want to remove the duplicate records we can use the DISTINCT statement to remove duplicates.


SELECT DISTINCT  item_name, item_id  
FROM daily_menu; 


Note that within the SELECT statement we can also do data aggregations (e.g. sum, if clauses, windowing functions), we don’t just need to select unmanipulated columns.


WHERE Clause

Now what if we wanted to select only items and their id’s that were served on a particular day? We can use the WHERE clause to do this! The WHERE clause is used to extract only records that fulfill a specified condition. Let's say we only want to select the items and their id’s if they were served on “2017-07-27” (comparing two strings, assuming date is string data type). We can do that with the query below.


SELECT item_name, item_id  
FROM daily_menu 
WHERE date = ‘2017-07-27’  


We can also do numeric comparisons with a where clause. Let’s return item_id’s and item_name’s where unit sold is less than 100.


SELECT item_name, item_id  
FROM daily_menu 
WHERE units_sold < 100  


WHERE Clause Operators

There are a few operators we can use within the WHERE clause. The below chart outlines all of the operators you can use to compare values.

Operator Description Example
= Equal WHERE units_sold < 100
< > or != Not equal to (depending on SQL variant) WHERE units_sold != 100
> Greater than WHERE units_sold > 100
< Less than WHERE units_sold < 100
>= Greater than or equal to WHERE units_sold >= 100
<= Less than or equal to WHERE units_sold <= 100
BETWEEN Between an inclusive range WHERE units_sold BETWEEN 100 AND 300
LIKE Search for a specific pattern WHERE date like “%2017%”
Link to “LIKE” section
IN Specify multiple matches for a column WHERE item_name in (“Chicken”, “Steak”)
NOT Negates a condition WHERE NOT units_sold < 100

AND and OR Operators

We can combine multiple WHERE clause conditions by using the “AND” and “OR” operators. “AND” operator displays a record if all the conditions separated by AND is true. The “OR” operator displays a record if any of the conditions separated by OR is true. Below are some examples.

We will select all item_name’s and item_id’s that were sold on 2017-07-27 and had over 200 units sold.


SELECT item_name, item_id  
FROM daily_menu 
WHERE date = ‘2017-07-27’  
     AND units_sold > 200  


We will select all item_name’s and item_id’s that were sold on 2017-07-27 OR has a price over $20.00.

SELECT item_name, item_id  
FROM daily_menu 
WHERE date = ‘2017-07-27’  
     OR price > 20.00  


Conclusion

Hopefully now you feel confident in pulling simple data requests from a table. If you want more content like this, visit SQL 201 for a deeper dive into SQL commands.