Home

 › 

Articles

 › 

Understanding SQL Queries, with Examples

SQL language programming data

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: 

SELECT SQL Queries
SELECT SQL queries in action, showing the first_name and age of records in the Customers table.

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: 

SQL Queries SELECT WHERE
The SELECT WHERE SQL query is

in action, showing records with

certain criteria.

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 SELECT order by
ORDER BY being used to sort our records by age.

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: 

SQL Queries JOIN
Showing data from the Customers and Orders table using JOIN.

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. 

SQL Queries subqueries
Subqueries are basically just nested queries.

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: 

SQL Queries count
COUNT is good if you want to know the number of rows in a table.

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

SQL Queries AVG
AVG is used for finding the averages of numbers, such as the age of customers in your database.

Or the youngest customer in our database? 

SQL Queries MIN
This use of SELECT MIN shows records from our Customers table with the minimum age.

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: 

SQL Queries DELETE
Modifying a table by using the DELETE FROM query.

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: 

SQL Queries UPDATE
Changing the name of one of our customers using the UPDATE and SET queries.

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. 

Summary Table

Type of SQL QueryDescription
SELECT QueriesUsed to call columns from a table.
JOIN QueriesAllow you to join separate tables into a single one.
SubqueriesQueries within a query, used to filter and manipulate data in more creative ways.
Aggregation QueriesPerform calculations on the data you’re querying.
Modification QueriesUsed to modify tables or databases with SQL (INSERT, UPDATE, DELETE).

Frequently Asked Questions

What are the basic SQL queries?

There are many types of SQL queries for querying and modifying databases, but the primary SQL queries you should be familiar with are SELECT, UPDATE, DELETE, and INSERT.

How to use an SQL query?

To use an SQL query, you first need to connect to a database. Once connected, you can execute SQL commands. For example, to select all data from a table named “Employees” you would use the SELECT command like this: SELECT * FROM Employees;.

The * means “all columns”, and the semicolon at the end signifies the end of the command.

Can I teach myself SQL?

You don’t need to know much about coding to start learning SQL, and you can even teach yourself. The barrier to entry is low, but mastering SQL can be difficult. The best way is to build a project using a SQL database or use a free online SQL tool to practice your queries.

What is a primary key in SQL?

A primary key in SQL is a unique identifier for each record in a table. Think back to our example earlier which specified customer_ID. It helps to quickly find a specific record when querying your database.

What is SQL?

Structured Query Language, or “SQL” for short, is a language for talking to databases Its main function is to let you create, retrieve, update, and delete data in a database.

Why is SQL important?

SQL is important because it’s the main way we interact with databases. If you’ve got a lot of data stored and you need to find specific information, SQL is the tool you’d use to ask your database questions. As you can imagine, almost every company keeps vast data records about their customers and business, so learning SQL will be useful in many areas of tech.

To top