SQL Tutorial Part 2: Keywords

by Posted on

In this post we will cover the four main keywords that you need to know in order to write basic queries (they are listed below). By the end of the article, you will able to write your own simple queries!

Before we start, we should re-state the main reason we use SQL in the first place. Most of the time, it is to reduce the amount of data we see. For example: if our candle sales table from the previous post contains 50 columns and 20 million rows, it contains more data than we can use directly. All of that data is useful because it can be used to answer a variety of questions, but most of the time we only need to answer one very specific question, so we only need a small portion of the data.

We use SQL to limit the data we see to exactly what we need to answer that question. For example, if the sales table referenced above contains sales data for the past twenty years but we only need to know about sales made on June 5, 2017, we probably only need a handful of those 20 million rows. We can use SQL to limit the rows we output to only those that have an entry of June 5, 2017 in the date column to create a smaller, more manageable data-set that can answer our question.

The keywords referenced above are exactly how we do that. They are:

  • SELECT
  • FROM
  • WHERE
  • GROUP BY

Now, how do they work?

Note: I have explained the keywords in the order in which they must be listed for your query to run, starting with SELECT. However, most of the time you will choose the table with the FROM keyword before you choose the columns with the SELECT keyword. This is because you need to know which table you’re using before you can decide which columns you want to use from that table (databases and tables are covered in more detail below). Even if you start by writing out the FROM keyword first, as I normally do, the keywords must still appear in the normal order: SELECT, FROM, WHERE, GROUP BY (you will start from the middle with FROM, then write SELECT above it and WHERE and GROUP BY below it).

The Keywords:

SELECT

What it does: Chooses which columns you see (and do not see).

How to use It: List the names of the columns you want to see after the keyword, separated by commas. Leave out the names of the columns you do not need to see. For example: SELECT date, size will return only the date and size columns of the Sales table (table selection is explained below in the FROM section) and ignore the price column.

In this example, the SELECT keyword chooses the date and size columns and ignores the price column. Table selection using the FROM keyword is explained below.

FROM

What it does: Chooses the table the data will come from.

How to use It: Write the name of the table after the keyword. For example: FROM Sales will specify that you want to use data from the Sales table.

Note: Most databases (databases are data repositories that you communicate with using SQL) store data in tables, and those databases often contain multiple tables. The FROM keyword specifies which of those tables you want to use data from.

In this example, the FROM keyword tells the query to use the Sales table and ignore the Customers table (the column selection using SELECT date, size is highlighted in the SELECT example above, but ignored here).

WHERE

What it Does: Chooses which rows you see (and do not see).

How to use It: List the conditions used to choose rows after the keyword. Include the word AND before any condition after the first one. These conditions will reference one or more columns, and will use the data in those columns to identify rows to return. For example: WHERE size = ‘medium’ will return only rows where there is a ‘medium’ entry in the size column. Any rows that do not match the criteria will be ignored.

In this example, the WHERE keyword chooses only rows where size = ‘medium’ and ignores the rest. Note: if there were a second condition, it would appear on a new row below size = ‘medium’ with the word AND in front. For example: AND date = ‘01/15/2020’.

GROUP BY

What it Does: Determines whether or not to show duplicate rows. GROUP BY is a bit more complicated than the other keywords, so I will elaborate on it in more detail later. For now, you can think of GROUP BY as a way to check the output for any rows that are exact matches and ignore the duplicates (technically, collapse the duplicates – but you can think of it as ignoring them for now) so each unique row only appears once.

How to use It: This is a bit of an oversimplification, but for now plan to use GROUP BY in one of two ways: (1) List all of the column names you put in the SELECT statement in the GROUP BY statement if you want to ignore duplicate rows. For example: you can use GROUP BY date, size as shown in the example below to ignore row 4, which is a duplicate of 3; or (2) leave out the GROUP BY statement entirely if you do not want to ignore duplicate rows.

In this example, all of the columns below the SELECT keyword are also listed below the GROUP BY keyword, this will ignore any duplicate rows. In this example you can see that row 3 and row 4 have exactly the same entries, so row 3 will be shown and row 4 will be ignored.

And now, after all of these transformations, the final output of our query!

Our SQL query has reduced the total amount of data from three columns and ten rows in the base table (Sales) to two columns and three rows in our output. Now we are left with only the exact data we need!

With these four keywords under your belt, you now understand the most important and fundamental concepts of SQL. Congratulations!

We will continue to elaborate on these concepts in more detail in later posts.

Published by andrew

Leave a Reply

Your email address will not be published. Required fields are marked *