SQL 101: An Introduction

Here at Triple Whale, we love data. And the only way you can love data is if you know how to analyze it. 

Structured Query Language (SQL) is a programming language for storing and processing information in a relational database. 

If you’re an ecommerce brand, you’ve got plenty of data just waiting to be stored and processed in a relational database, and SQL is a powerful way to draw insights from the sea of data in front of you. 

Triple Whale has made it easy to chat with your data, asking questions that behind the scenes get translated into queries that pop out an answer. But, a basic understanding of how SQL works will go a long way in understanding how to use Triple Whale effectively to get your questions about your data answered. In this article, we’ll give a brief introduction to SQL, SQL syntax, and basic commands you’ll need to get started. 

What is SQL?

SQL is a programming language for accessing and manipulating databases. Here are just a few of the things SQL can do:

• Execute queries against a database
• Create complex reports
• Create customized views

Why Are We Using SQL in Triple Whale?

It’s a simple yet powerful programming language which allows marketers and brand owners alike to investigate relationships within their data. Other benefits of SQL are:

• Easy data retrieval/manipulation
• Data is managed securely
• Able to join data from different sources and integrations
• Facilitates powerful data visualization
• Data is always consistent and up-to-date

What is a Relational Database?

A SQL database is a place where you can keep and work with data that's organized in a special way, called relational data. Imagine it like a super-powered spreadsheet that's set-up to handle a lot of information very efficiently. Instead of a simple spreadsheet, though, this data is kept in tables that look a bit like sheets but can talk to each other because they're related.

You manage all this with something called a Relational Database Management System (RDBMS), which is a fancy way of saying "a tool that lets you organize, guard, and get to your data easily." This tool makes sure everything stays orderly and safe, and lets lots of people use the data at the same time without messing it up.

Table Conventions

There are rules around how tables should be named in relational databases:

1. They should be lowercase, so: customers should be used over Customers
2. Names should have no spaces and use an underscore instead for two word fields. For example, product_id, not product id.
3. Tables should refer to collective groups or be plural, so orders instead of order.

The reason for naming conventions is to simplify things - it’s rare for a database to have a small number of tables. Organizational rules will help keep things straight! 

Common SQL Commands

Some of the most used SQL commands that form the backbone of many SQL queries are:

SELECT:  Retrieves data from one or more columns.

FROM:  Specifies the table to query data from.

WHERE:  Filters data based on conditions.

ORDER BY:  Sorts the results in ascending (ASC) or descending (DESC) order.

Common SQL Functions

SQL functions are built-in operations that can be applied to data within a SQL query to perform calculations, modify data, aggregate values, and more. These functions can be broadly categorized into several types, but here are a few common ones:

COUNT():  Returns the number of rows matching the query.

SUM():  Calculates the total of a numeric column.

AVG():  Computes the average value of a numeric column.

MAX()/MIN():  Finds the maximum/minimum value in a column.

UPPER()/LOWER():  Converts text to upper/lower case.

SQL Join Types

SQL joins are used to combine rows from two or more tables based on a related column shared between them. Each type of join serves different needs, and they can be used to answer specific questions about the data by combining information in various ways. Here are a few common types of joins in SQL:

INNER JOIN:  Returns rows when there is a match in both tables.

LEFT (OUTER) JOIN:  Returns all rows from the left table, and matched rows from the right table.

RIGHT (OUTER) JOIN:  Returns all rows from the right table, and matched rows from the left table.

FULL (OUTER) JOIN:  Returns rows when there is a match in one of the tables.

Conditional and Comparison Operators

Conditional and comparison operators in SQL are used to perform tests on data and to set conditions in queries. They allow you to filter, compare, and evaluate data based on specific criteria. Some common operators are:

= (Equal to): checks if the values of two operands are equal or not; if yes, then the condition becomes true.

!= or <> (not equal to): checks if the values of two operands are equal or not; if the values are not equal, the condition becomes true.

> (greater than): checks if the value of the left operand is greater than the value of the right operand; if yes, the condition becomes true.

< (less than): checks if the value of the left operand is less than the value of the right operand; if yes, then the condition becomes true.

>= (greater than or equal to): checks if the value of the left operand is greater than or equal to the value of the right operand; if yes, then the condition becomes true.

<= (less than or equal to): checks if the value of the left operand is less than or equal to the value of the right operand; if yes, then the condition becomes true.

AND: Allows the existence of multiple conditions in an SQL statement's WHERE clause. For the record to be selected, all the conditions separated by AND must be true.

OR: Combines multiple conditions in an SQL statement's WHERE clause, but unlike AND, if any of the conditions separated by OR is true, the record will be selected.

NOT: Negates a condition, returning true if the condition is false. It can be used with other conditions to invert their logic.

BETWEEN:  Selects values within a range.

LIKE:  Searches for a specified pattern.

IN:  Matches any value in a list of values.

Commands to Group Data in SQL

In SQL, grouping data is primarily accomplished using the ‘GROUP BY’ statement, often in conjunction with aggregate functions to summarize or aggregate values across rows that share a common attribute. Here’s a few examples:

GROUP BY:  Groups rows with the same values in specified columns.

HAVING:  Filters groups based on aggregate functions.

Subqueries and Nested Queries in SQL

A subquery or nested query involves embedding a query within another query. These constructs enhance the flexibility and power of SQL, allowing for complex data retrieval, comparison, and manipulation within a single query execution. Some examples include:

Date and Time Functions

NOW():  Returns the current date and time.

CURDATE():  Returns the current date.

DATE_FORMAT():  Formats the date value.

Data Manipulation Functions

INSERT INTO:  Adds new rows to a table.

UPDATE:  Modifies existing data.

DELETE:  Removes data from a table.

Miscellaneous Functions

DISTINCT:  Selects unique values from a column.

AS:  Renames a column or table for the query.

LIMIT:  Restricts the number of rows returned.

Sample SQL Query

Now that we’ve introduced the basics, let’s see a query in action! Consider a scenario where you have a database table named ‘sales’ with the following columns: ‘sales_amount’, ‘sale_date’, and ‘salesman_id’. If you wanted to know the total sales amount from each salesperson in January 2024, sorted with the highest sales amount first, your query should look like this: 

Say Hello to Moby: Chat With Data

You could become a SQL master, or you could have someone write queries for you using just plain English. 

You don’t have to be a SQL master to harness the power of SQL to query your own data. Use our Prompts list to nail down the data you want, whether it’s Shopify data, Blended Ads metrics, or detailed information about shipping or custom expenses, there are thousands of questions you can ask Moby about your data. Learn more here.

‍