MarTech

Introduction to SQL

Structured Query Language (SQL)

Structured Query Language (SQL) was developed by IBM researchers in the 1970s. SQL—sometimes pronounced “sequel”—is used to store, retrieve, and manipulate data in databases.

Many data management systems have user-friendly ways to export entire sets of data, or sometimes just the transactions from a date range, for example, but what if you need a list of records with very specific criteria?

Let’s say you need a list of every person in your database who has been an email subscriber for the past six months, hasn’t purchased anything, lives in California, and has a birthday next month. You could use a spreadsheet to filter the list down, but that’s very time-consuming, especially if you need this list regularly. That’s where SQL can help.

You could build a SQL query for this and re-use it every time that specific list is needed. Depending on your marketing software, you could even automate this task, scheduling the SQL to run hourly, daily, or weekly to update your list.

SQL is also used to display data on a webpage. WordPress uses MySQL to query data and display it on the website, and a custom query written by a developer can display very specific data for users.

Getting started with SQL queries

A SQL query should start with the SELECT keyword, followed by the columns to be queried, the FROM keyword, and the name of the table to query.

SELECT
	Id,
	Email,
	FirstName,
	LastName
FROM contacts
WHERE EmailOptIn = 1
	AND EmailOptOut = 0
	AND Email IS NOT NULL

Notice in the example that the columns (Id, Email, FirstName, LastName) are separated by commas. You do not need to separate columns (or even new clauses) with line breaks, however, but it’s a good idea as a way to keep your SQL human-readable.

The FROM keyword designates the table, contacts, from which the columns are queried. Everything after FROM contacts in the above example is part of a WHERE clause, which we will explore later in this article. This query simply searches the table called contacts for the data in the specified columns and filters the query to only select contacts who are opted into email communication, are not opted out, and who have a value in the Email column (not null).

Note: In SQL, there is a difference between null and an empty string (‘’). It’s usually a good idea to check for both when validating data.

Aliases

In SQL, an alias is a column name that is defined within the query. By default, columns in the results table are titled based on the field name. But you can change the title of a column by using an alias.

If the field name is email_address_contact, you can rename it in the results to Email by placing the alias immediately after the actual column name. You can optionally use the AS keyword for readability.

The following examples will both change the column title to Email.

SELECT email_address_contact Email FROM people
SELECT email_address_contact AS Email FROM people

Aliases become really helpful when using functions. A SQL function can cause problems if it shows up in the results table when working with a programming language like PHP to display results on a webpage.

One last note on aliases: It’s possible to create column aliases with spaces by enclosing the alias in single quotes. This can improve readability, but again, some programming languages may have difficulty handling column titles with spaces.

Filtering a query

In order to filter a query for specific criteria, use the WHERE clause, followed by one or more conditions. A condition should have a field, a comparison operator of some kind, and a value or range of values to check for. If using multiple conditions, separate them with the AND or OR keywords.

SELECT
	Id,
	FirstName AS First,
	LastName AS Last,
	Dob AS Birthdate,
	CustomerSince AS 'Customer Anniversary'
FROM people
WHERE (Birthdate > '1980-12-31' AND LEFT(FirstName, 1) = 'B')
	OR Last LIKE '_a%'
	OR (LastName = 'Caples' AND CustomerSince >= '2020-01-01')
	AND Birthdate IS NOT NULL

Notice the use of parentheses to distinguish between condition groups. The example is searching the database for records matching any of the following conditions:

If a record meets any of those conditions, it will be returned in the results table if it also meets the condition that the Birthdate field is not null. (To find records that are null, use IS NULL in the condition.)

Data types

There are three primary data types in SQL: string, numeric, and date/time. Each of these data types has multiple subtypes within it. A good resource for information about each data type can be found at W3Schools.

Conclusion

Let’s take a look at our earlier example querying people who meet the following conditions:

SELECT Id, Email, FirstName, LastName
FROM contacts
WHERE DateSubscribed <= DATEADD(month, -6, GETDATE())
AND (LastPurchaseDate = ‘’ OR LastPurchaseDate IS NULL)
AND AddressState = ‘CA’
AND DATEPART(month, Dob) = MONTH(DATEADD(month, 1, GETDATE()))
AND EmailOptIn = 1
AND EmailOptOut = 0
AND Email IS NOT NULL

Using this query, we can capture this list of people and send an email inviting them to a product event near them with a free birthday gift offer.

Notice that we don’t actually need to count the number of purchases on the customer’s record—only whether there’s a value for LastPurchaseDate. You can get creative with your SQL queries to prevent unnecessary clauses. And be sure to look up all of the functions and keywords used in these examples, since I haven’t explained them all.

It’s important to remember that a SQL query depends on the data available in the database. If we don’t have a column for the date a person subscribes to emails, or if a contact has missing birthdate data, for example, our query will be limited and less reliable. Clean data systems are very important.

There are many more functions and features within SQL, but this is just an introduction. There are a lot of good websites for learning SQL, including Codecademy, LearnSQL, StackSkills, and W3Schools. (Here’s a free SQL cheatsheet!) You can also find good tutorials on YouTube and solutions on StackOverflow. There are plenty of resources to help you learn the basics (and more) of Structured Query Language.