Understanding SQL Queries, with Examples

Key Points

• SQL is a simple programming language which is used for managing and communicating with relational databases.
• SQL queries can be used to retrieve, filter, and manipulate data in a variety of industries.
• The primary types of SQL queries include Select, Join, Subqueries, Aggregation, and Modification queries.

Love it or hate it, there’s no escaping SQL these days. Every industry uses it, and almost everyone has at least some exposure to SQL queries, regardless of their line of work. In fact, people who never touch SQL at their job make up an increasingly thin minority. 

If you’re in the tech field, you should probably know what SQL is and how to use it. Fortunately, it’s an easy tool to get comfortable with. So easy that on day one of learning SQL, most people will be able to swiftly call whole columns of data from a table, and will probably even be able to filter for certain criteria to make their search more precise. 

The main trick to getting competent with SQL is understanding the different kinds of SQL queries available to you, and what makes them useful. That’s what we’ll cover here. For total novices, we’ll also explain what SQL is and how it’s used to make data analytics far more effective. 

At the end, we’ll even throw in some pointers on resources you can use to upskill your SQL or troubleshoot any issues you encounter on your analytics journey. 

Let’s get querying! 

What Is SQL?

Before we get into SQL queries, we’ll need to break down what SQL actually is. Short for Structured Query Language, SQL (usually pronounced “sequel”) is a simple programming language used for managing and “communicating” with relational databases.

A relational database stores data as tables rather than as key-value pairs. Therefore, we use SQL to access structured data, not unstructured data. 

When most people think of using SQL, they often imagine pulling small slices of data from a huge database. While reading data is probably SQL’s most commonly used function, you can also use SQL to create, update, and delete databases and tables. 

Since data is everywhere, SQL can be used in a variety of instances, but you’ll most often see it being used by data analytics teams, or in business intelligence, marketing, healthcare, and web development. 

The main benefits of using SQL lie in its simplicity as well as its ability to easily retrieve relevant information from vast sets of data. You’ll see how simple it is once we show you some examples of what the “code” looks like, and you’ll recognize how much data you can harness with SQL once you start querying databases yourself. 

While SQL has a few major disadvantages, it’s important to keep in mind its limitations. For one thing, querying data is usually fairly costly. Many services, like Snowflake, charge by the query, so if you get carried away with your querying, you might end up with a disgruntled manager.

On top of the cost, we also want to emphasize that SQL only works with tabular data. Dealing with unstructured data is a whole different ball game. 

What Are the Primary Types of SQL Queries?

Now that you’ve been properly introduced to SQL, let’s consider some of the main types of queries, when to use them, and how to use them.

Of course, these are not all of the SQL queries out there. In fact, there are many types and variations that you can learn to increase your knowledge with more advanced queries. But for now, we’re going to look at the primary SQL queries you should be familiar with starting out.

SQL query types can be categorized as follows: 

SELECT Queries

In the most basic and fundamental of queries, you use SELECT to call columns from a table. 

JOIN Queries 

As the name suggests, JOINs allow you to join separate tables into a single one. 

Subqueries

Subqueries, or queries within a query, allow you to filter and manipulate data in more creative ways. 

Aggregation Queries 

Aggregate functions allow you to perform calculations on the data you’re querying. If you’ve ever used Excel, you probably already know what an aggregate function is. 

Modification Queries

Actions like INSERT, UPDATE, and DELETE allow you to modify tables or databases with SQL. Please be careful with these if you’re working in a production environment.

Let’s go over each of these queries in more detail, and look at some examples of each. 

SQL Queries: SELECT

SELECT statements are the most critical of the SQL queries available. In fact, you pretty much can’t run a query without SELECT, since the whole point of SQL is to call sets of data from a table. 

The absolute most basic query you can run is: 

SELECT * 

FROM [table_name_goes_here] 

By using that asterisk, you’re telling the tool that you want to fetch ALL the information from that table. 

When beginners are first getting comfortable with SQL, this is a perfectly fine command to run. In the real world, though, it’s not very practical unless you’re working with a relatively small set of data. If it’s a big table, you’re probably going to be waiting a long time for that query to finish executing—and possibly spending a lot of money, too. 

Fortunately, you can qualify your SELECT statement with filters, and even specify which columns you want to call. 

Let’s say you have a “Customers” table, like in this free online query editor, which shows customer id, first and last names, the age of those customers, and which country they live in. 

If you only wanted to know the first and last names of those customers, you could write this: 

Since we specified which columns we want to view, SQL only returned part of the table, rather than the whole thing. 

This only shows you a broad view of data within your table, though. What if you wanted to get a little more specific?

Using SELECT WHERE

We could also call all the columns, but use the WHERE clause to specify certain criteria. If we only wanted to see customers from the USA, we could do this: 

Pay attention to the syntax here. SQL requires single quotes around non-numeric data. Otherwise, you will get an error. 

You can also use ORDER BY and GROUP BY at the end of the SELECT statement to either arrange the data by an ascending or descending value or arrange it by specific groups of values, such as country. 

SQL Queries: JOIN

JOINs are a bit tricky to get right, but they’re an important command, as they allow you to bring together data from different tables into a single query. The thing that makes JOINs complicated is that you often need to identify a common column between two tables. Here’s a breakdown: 

INNER JOIN: returns all rows where there is a match in the join column. 

LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table. 

RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table. 

FULL JOIN: returns all rows from both tables, even if there are no matches in the other table. 

Say we want to examine data from our “Customers” table, and also data from an “Orders” table: 

With our SQL wizardry, we can now see which customers have placed orders, and how frequently. As you can see, one customer placed an order twice.

Perhaps not a mind-blowing insight, but hopefully this gives you a good sense of how JOINs can help you start to see patterns or relationships between different tables in a relational database. 

Subqueries

If you know how to use subqueries to further filter and manipulate your data, you’re well on your way to becoming a SQL expert. The truly neat thing about these so-called “nested queries” is that, like JOINs, they allow you to analyze data from across different tables. 

This nested query pulls from the Orders table and the Shippings table. It asks SQL to identify any customers that have placed an order and have not yet received their shipment. 

Like JOINs, the challenging thing about subqueries is that you need to look for some kind of common relationship between different tables. Here, we were able to land on “customer” or “customer_id” as a point of similarity between the distinct sets of data.  

Aggregation SQL Queries 

For math lovers out there, aggregations will quickly become your favorite set of SQL queries, as these allow you to perform calculations on your data.

The main aggregate functions include COUNT, SUM, AVG, MIN, and MAX. 

COUNT returns the number of rows from a table that meets your specified criteria: 

What if we wanted to know the average age of our customers? 

Or the youngest customer in our database? 

Pop quiz: Can you figure out which query to run to identify the oldest customer in our database? 

Modification SQL Queries 

Modification statements are used to update information in your database. You should be careful when using these commands, so as not to upset your database administrator by obstructing company data. Unless it is your own database, then have fun!

The common modification commands include INSERT, UPDATE, and DELETE. 

Let’s say your company’s credo is never to trust anyone over 30, and so you want to delete any customers from the database older than 30: 

Here’s another scenario in which you need to adjust a record, rather than delete it entirely. Betty Doe just got married, and her last name is now Robinson. Use the UPDATE command to modify her record in your Customers table like so: 

Here, we used Betty’s customer id to locate her instead of her former last name, in order to ensure we were updating the correct customer. 

Notice too, that, unlike other queries we’ve dealt with here, we don’t need the SELECT statement for these. That makes sense since you’re not actually querying data at all, but rather changing certain data. 

How to Learn More About SQL Queries

We’ve only just scratched the surface here when it comes to what can be accomplished with SQL. The types of queries we’ve introduced you to are the most commonly used ones. But in the real world, you’ll usually see much longer and more complex SQL queries pulling from truly vast sets of data. 

Don’t be scared, though. There’s plenty of help available to you online for getting a handle on SQL. With enough practice, you can master this. After all, it’s a programming language designed to be simple and intuitive. 

One of the best free resources out there for SQL beginners is the course offered by W3 Schools. This tutorial is short and to the point, and very hands-on—even providing you with a built-in query tool so that you don’t have to worry about installing SQL packages to your computer.

There are plenty of resources to learn SQL queries for free on the internet, such as quick lessons on YouTube.

You can also turn to paid courses. Sure, it’s never fun to pay for things, but see it as an investment in something that could lead to a higher-paying job. Coursera, Udemy, and Datacamp are all good places to go for expert, and usually hands-on, instruction in SQL. 

In addition to these learning resources, there are also places you can go online to get community support on SQL-related projects. Stackoverflow is a developer forum where users can ask other developers or data scientists for help de-bugging certain issues or, in our case, finding the optimal way to execute a more complicated SQL query. 

The image featured at the top of this post is ©Maria Vonotna/Shutterstock.com.