Foundational SQL Syntax

SQL is a wonderful language that provides a connection between our business problems and data. It helps us scour vast amounts of data to extract meaningful information that can provide insight to real business problems. The 3 examples below will help explore some of the basic SQL syntax in real world scenarios.

Let's take the position of a manger in a dvd rental business. We will be using the dvd rental database.

What is the total lifetime amount paid by all customers in our dvd rental business?

To answer this question, we need a list of each customer and the total amount they have paid to our business in the course of their lifetimes. As a brief recap of the basics, the "select" statement helps us pick what columns want and the "from" statement helps us select what schema (table) we take it from.

So we're going to select 2 columns: The 1st column is a field that already exists (customer_id) to help us identify each customer. The 2nd column is a field that we need to create ourselves. We want it to show the total amount our customers have paid. To do that we will use the "SUM" function and put the column "amount" inside parenthesis so SQL will add all of the individual transaction amounts together. We write the line "Group by 1" to ask SQL to add it by the 1st column (customer_id). Basically we are saying "for each customer_id (Group by 1) take the sum of all of their transactions (SUM(p.amount)) and name that column 'amountpaid' (as amountPaid)."

As a side note, the "p." is clarifying which schema the column is coming from. When we write "p" after "payment" (from payment p), that is setting a variable that we can use to specify our columns. It's just a little convenience trick that helps us be more specific when we write code. The line "Order by 1 Asc" is also clarifying that we want to sort our table from least to greatest by the 1st column (column_id).

What are all the February transactions that are above $1 in our stores?

In this example, we need to extract specific transactions from our database that took place within February and have a transaction amount above $1.

In the first line of code, we grab a couple columns that we need for the table. Most of the labels are self explanatory except for the column "payment_date" which includes the phrase "::date" after it. "::date" helps make format our "payment_date" column in a nicer format (yyyy-mm-dd) as it originally includes the exact time up to the second.

In the second line of code, we specify which schema we are drawing from.

In the third line of code, we use a "Where" condition to create a conditional statement to help filter out some rows. "EXTRACT (month FROM p.payment_date)" helps us get all the month values from each transaction and "= 2" specifies that we only want transactions where the month value = 2 (where the month is February).

The fourth line uses an "AND" statement to add another condition where we only want transactions with a dollar amount above $1 (p.amount > 1)

And the final line helps us format the table (Order by 2 Desc, 3 Asc). First we sort largest to smallest in column 2 (customer_id) and then smallest to largest in column 3 (amount).

What is the name of the customer who has made the most total lifetime payments?

In this final example, we are just looking for the one entry of the customer who has paid the most to our company.

The "With ___ as ( ___ )" statement is what we call a Common Table Expression (CTE). CTEs help us create tables that didn't exist before so we can get information that isn't listed in our original databases. In this case, we are creating a table that has the customer_id and the corresponding first & last name to each ID. The first & last name column doesn't exist yet so we need to make it. We do this by mashing together (concatenating) the "first_name" column and the "last_name" column as well as adding a space in between (' '). The whole statement looks like "CONCAT (first_name, ' ', last_name)" and we're also gonna call this new column "firstlast". We're gonna call this whole table "customerinfo" and SQL will store this away in its memory.

Next, we're gonna create our actual Select From statement. We're gonna select the customer_id, total amount paid by each customer (SUM(p.amount)), and their first & last name. However, the first & last name comes from a different table (the one from the CTE we just created) so we need to mash together our "payment" table and our "customerinfo" table.

To do this, we use a "Join" statement, joining the "customerinfo" table alongside our "payment" table. We want to line up each of the rows by a mutual field (column) that they share which happens to be the customer_id column. That's what the "On p.customer_id = ci.customer_id" line is for.

Our final two lines are just specifying that we want to sum all the amounts by each customer_id (column 1) and name (column 3) as well as order our results from greatest to least, only showing the top result (Limit 1).

These were some basic SQL statements that help show the principles of basic syntax such as SUM, GROUP BY, ORDER BY, JOINs, CTEs, EXTRACT, and more.