MattCASmith

I recently dipped my toe into the world of SQL for the first time and got my head around the basic operators and statements. Here’s a summary of how to construct basic queries.

I’d had brushes with SQL before. Even when I was a journalist, I would do the occasional WordPress web development, which would involve setting up SQL databases to store sites’ posts and making sure the content management system could query and write to them. But somehow I always avoided sitting down and actually getting to grips with SQL itself.

With this in mind, I was almost starting from scratch recently when I needed to query a database manually. I’d used similar tools like Splunk before, but all I had was this limited experience and whatever I could piece together from Google. The good news, then, is that basic SQL database queries aren’t actually that difficult to put together.

SELECT and FROM

The two most basic elements of an SQL query are SELECT and FROM. SELECT tells the system which columns we want to see, and FROM tells it which table we want them from.

Let’s imagine we have a database full of employee information, with fields like firstname, lastname, jobtitle, address, country, emailaddress, phonenumber, and salary. However, we only want to extract a list of people to send personalised emails to, so we’re just interested in returning the firstname and emailaddress fields to give us the data we need to achieve this.

SELECT firstname, emailaddress
FROM employeedata

The SQL query above would return the columns firstname and email address from the table employeedata. It’s really that simple! Now let’s complicate things a bit…

WHERE

Let’s say our email is UK-specific, and therefore we only want to send it to employees in the UK. There are a couple of ways that we can do this, and both of them involve a WHERE statement, which specifies criteria for the records that are returned.

The simplest way to achieve this, assuming our data is nice and standardised, is to add a WHERE condition that filters the data based on the country field.

SELECT firstname, emailaddress
FROM employeedata
WHERE country = "United Kingdom"

Another potential solution is to use a wildcard (more on those later) to filter the data for email addresses ending in .co.uk – assuming all UK employees’ addresses match this format.

SELECT firstname, emailaddress
FROM employeedata
WHERE emailaddress LIKE "*.co.uk"

But the marketing department have changed their minds again, and now they only want to send emails to managers and assistant managers in the UK.

AND and OR conditions

To achieve this, we can use an AND statement (which specifies additional conditions that must all be true) and an OR statement (which specifies that one of a set of conditions must be true). Note the brackets that specify which conditions are part of the OR statement.

SELECT firstname, emailaddress
FROM employeedata
WHERE country = "United Kingdom"
AND (jobtitle = "Manager" OR jobtitle = "Assistant Manager")

A simpler way to do this would be with the IN operator, which specifies that a field must match one of a series of values listed between brackets.

SELECT firstname, emailaddress
FROM employeedata
WHERE country = "United Kingdom"
AND jobtitle IN ("Manager", "Assistant Manager")

But wait a minute – this is an email about payments that only affects managers and assistant managers earning between £30,000 and £50,000. Let’s add another condition.

BETWEEN

If we want to search for records with a field value that sits between two numbers, then we can use (you guessed it) the BETWEEN function. In our example, it looks like this…

SELECT firstname, emailaddress
FROM employeedata
WHERE country = "United Kingdom"
AND jobtitle IN ("Manager", "Assistant Manager")
AND salary BETWEEN 30000 AND 50000

However, on reviewing the output it seems that a large number of French employees have accidentally been entered with “United Kingdom” as their country. How can we fix this?

NOT

The solution is a NOT statement, which excludes records that match the condition it precedes. In this case, let’s use another wildcard to exclude any staff with .fr email addresses.

SELECT firstname, emailaddress
FROM employeedata
WHERE country = "United Kingdom"
AND jobtitle IN ("Manager", "Assistant Manager")
AND salary BETWEEN 30000 AND 50000
AND emailaddress NOT LIKE "*.fr"

This will refine our search by excluding any record with a value in the emailaddress field ending in “.fr”, which means we now have an accurate list of email addresses.

ORDER BY

Finally, let’s arrange our results nicely, first sorting by the employee’s first name and secondly by email address if two names are the same. We can use ORDER BY for this.

SELECT firstname, emailaddress
FROM employeedata
WHERE country = "United Kingdom"
AND jobtitle IN ("Manager", "Assistant Manager")
AND salary BETWEEN 30000 AND 50000
AND emailaddress NOT LIKE "*.fr"
ORDER BY firstname ASC, emailaddress ASC

The multiple levels of sorting are separated by commas, and the ASC means the sorting is ascending. We could also use DESC to sort the records returned in descending order.

And there we go – a nice, neat list of first names and email addresses for marketing to use. Before we finish, though, let’s take a closer look at how wildcards work.

LIKE and wildcards

As we’ve seen, there are characters that can be used to match against fields based on only part of the values they contain. The two main ones are the asterisk, which is a placeholder for one or more characters, and the underscore, which is a placeholder for only one character.

For example, the “*.fr” in the last query would match “email@email.fr” or “a.fr”, while “_.fr” would match only “a.fr”. If we were looking for any email address, we could use multiple wildcards to search for “*@*.*”, which would match any potential email address format, including “email@email.fr” or “example@example.co.uk”.

SELECT *
FROM employeedata
WHERE emailaddress LIKE "*@*.*"

Note the LIKE, which is used instead of the equals symbol to use wildcards. Also note the wildcard asterisk in the SELECT statement, which will return all fields in the table.

There’s much more to SQL and I’ve got plenty to learn, but these basics should be enough to get started, and hopefully they’ve helped to demystify basic queries for someone else, too!

Photo by Edu Grande on Unsplash