1. What is an SQL query?

SQL (Structured Query Language, often pronounced "sequel") is a programming language used to communicate with databases that allows you to store, organize, and retrieve data efficiently by using simple commands. SQL is expressed as a series of statements, the most useful of which (and the subject of this entire course) is the select statement.

select Statements

A select statement is a command in SQL used to query a database and retrieve structured information from it. The result of a SQL select statement will be a table - a series of columns and rows - organized as specified in the query itself. select statements are comprised of one or more clauses. I'm going to introduce the three primary clauses now, and we'll learn about some others later:

• select clause - lists the columns that should be present in the output of the query
• from clause - tells the database which tables contain the data to be searched
• where clause - tells the database which criteria must be met for data to be present in the output

Technically, the only required clause in a select statement is the select clause (some databases also require the from clause). Your database will always expect these clauses to appear in this order: select, from, where. You cannot switch the order and put, for example, the where clause first.

A well-formed select statement might look like this:

select account_id
from accounts
where first_name = 'Jean'

This query says to the database, "find all rows in the accounts table where the first_name value is Jean and return the account_id from each of those rows". We'll talk a lot more about how this works in the coming chapters.

SQL Dialects

Like natural languages, SQL has several dialects – variants on the core language that mean something to certain types of databases but not to other types of databases. A full treatment of SQL dialects is beyond the scope of this course; I'm just going to cover some basics that you'll need to know as you get started.

Most dialects of SQL are identical in 95% of their usage so you generally do not need to worry about which database will be executing your code. However, one very big exception is in the treatment of dates across databases. Most SQL dialects expect dates to be in the format YYYY-MM-DD, for example 2025-06-01 for June 1, 2025. However, some databases (notably Oracle, used by ROI CRM) require dates in a different format (e.g., DD-MON-YYYY or 01-JUN-2025 for Oracle systems).

This website uses DuckDB to execute the queries you write. In all contexts relevant to this course, DuckDB closely adheres to ANSI SQL (the standard dialect) and the code you write here should be directly portable to your actual CRM or data warehouse. I will do my best to note instances where this might not be the case and provide alternate syntax.