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  

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 or !=
>	Greater than	WHERE units_sold > 100
=	Greater than or equal to	WHERE units_sold >= 100

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.