SQL Query Examples and Tutorial

The structure of a database can feel very similar to Excel. However, databases, in general, are more powerful than Excel spreadsheets. Here are three reasons why you could say so: 

1. Databases can interact with many other programming languages - giving a programmer more power to manage and retrieve information from data.
2. Databases can process more data than spreadsheets in Excel. Excel can handle up to 1 million rows of data, whereas a database can handle over a billion rows.
3. Databases can more easily be connected to the internet, allowing hundreds of users and applications to access them simultaneously.

Excited to get started with SQL? DataCamp has some amazing material to get you started; learn SQL highlights the tracks you can follow to hone your SQL skills!

Lets dive deeper into SQL Queries now… 

An SQL query is a statement built by putting together various SQL commands. These SQL commands together perform a specific task to access, manage, modify, update, control, and organize your data stored in a database and managed via a DBMS. 

Why use SQL?

SQL is incredibly versatile, and used by everyone from data scientists and engineers to marketers and human resource teams - basically, anyone who simply wants to be more data-literate!

It's not just used in tech industries or limited to big companies. Multiple industries of all sizes directly or indirectly use it. For example, professionals working in finance and healthcare industries often use SQL, given the large amount of data they produce. 

Data is the new commodity, and SQL is a very powerful tool when it comes to handling and working with big data. Wondering how you can leverage SQL to the maximum? Check out DataCamp’s What can you do with SQL tutorial to help you answer the question.

How to write SQL queries

Before we get into the query formulation itself, let's consider the question: “Where do I write and execute my SQL query?” 

DataCamp’s Learn SQL hub can be a good start point to enable working with SQL on a Mac or Windows machine and getting started.

But if you simply want to get started with learning the native languages of databases - SQL, you can start with DataCamp’s Introduction to SQL course. This course teaches syntax in SQL shared by many types of databases, such as PostgreSQL, MySQL, SQL Server, and Oracle. You will work with DataCamp’s inbuilt SQL interface and will not have to deal with the initial setup hassles of an RDBMS. 

DataCamp also has an awesome notebook tool where you can create your own notebooks: DataLab. DataLab is a collaborative cloud-based notebook that allows you to analyze data, collaborate with your team and share insights. DataLab is designed to take you from learning data science to doing data science. With built-in datasets, you can start analyzing data within minutes.

Now let's have a look at the dataset we will work with in this tutorial. 

We will begin with ‘films’ -  a SQL table holding data of 10 movies. This data is only a small subset of data from the IMDb Film dataset used in DataCamp’s Introduction to SQL course we mentioned earlier.

The ‘films’ table has ten records - the movie names stored across 10 rows. The fields are the five columns in the table, holding the information related to the movie. 

• ‘title’ is the name of the movie
• ‘release_year’ is the year when the movie was released
• ‘budget’ is the production cost of the movie in American dollars
• ‘gross’ is how much the movie earned in American dollars. 
• ‘imdb_score’  shows the ratings produced from votes submitted by IMDb users, not movie critics. 

Let's start the exercise with some basic SQL commands, using them to explore the ‘films’ table above. We do not expect you to execute the solution queries right away… but rather use it as a base to learn some key concepts. So don’t worry if you haven’t yet decided on a platform you want to run the queries from.

Basic SQL queries

Keep in mind that SQL keywords or commands are not case sensitive; writing ‘select’ is the same as writing ‘SELECT’. 

We will use a semicolon at the end of each SQL statement we write. This is the norm with some database systems, and is done to separate each SQL statement so that they can be executed in the same call to the server. 

Let’s start with a question. Q1: What are the movies stored in the table?

SELECT - FROM

The SELECT statement is used to choose the fields that you want to see from the database table. For Q1, you want the ‘title’ field.

FROM command/keyword specifies the specific table in the database from where you want to pull the data. Remember, a database can consist of multiple tables.

Syntax: SELECT column1, column2, … FROM table_name;

SQL query: SELECT title FROM films;

Result: 

You might be wondering, what if you've never seen the table above so you don’t know the names of the columns to specify within the SELECT statement. Good point! We will learn about the magical ‘*’ (asterisk) later on in the tutorial. 

WHERE

Q2: What are the movies to be released in 2010?

For this question, you want to “filter out” the movies so that you have movie names that fulfill the condition: ‘release_year’ is 2010. 

You can use the ‘WHERE’ clause to extract only those records that fulfill a specified condition.

Syntax: SELECT column1, column2, … FROM table_name WHERE condition; 

SQL query: SELECT title, release_year FROM films WHERE release_year = 2010;

Result: 

Operators in The WHERE Clause

The following operators can be used within the WHERE clause:

Arithmetic in SQL

You can perform simple arithmetic in SQL using the mathematical symbols: +, -, *, /. However, you can only perform operations across columns using these arithmetic symbols. You can also use parentheses to manage the order of operations.

But fear not, for more complicated operations - SQL has aggregate functions that allow you to perform operations within the same row. We cover this topic later on in the tutorial.

Q3: Calculate the profit made by each movie in the ‘films’ table. 

Hint: Perform Gross - Budget and save the result in a column called ‘movie_profit’ using AS

SQL query: SELECT title, (gross - budget) AS movie_profit FROM films

Result: 

Disclaimer: The film buffs out there might be bothered by the negative profits for Fight Club. The formula used ((gross - budget) AS movie_profit) is a very simple one and indeed, given the formula and the data, it is the correct result. 

But the example above is to remind you to be mindful of the data collection process! Even simple data exploration can reveal data errors that can be caused because of an error in the data collection or data storage process (data type change, etc) - such might have been the case when crafting the dataset in use. 

Always pay attention to the data you are working with!

AND, OR, NOT

The WHERE clause can be combined with other conditional clauses using AND, OR, and NOT operators. The AND and OR operators are used to filter records based on more than one condition, and the NOT operator is used to negate a condition. 

Let’s see their usage with examples:

Q4: What are the movies to be released in 2010 and have a rating higher than 9?

Syntax: SELECT column1, column2, … FROM table_name WHERE condition1 AND condition2 AND condition3 AND condition4…..;

SQL query: SELECT title, release_year, imdb_score FROM films WHERE release_year = 2010 AND imdb_score >= 9;

The AND operator displays a record if all the conditions separated by AND are true.

In the ‘films’ table, we have the movie ‘Inception’. It is the only value that is true for release_year = 2010, but it is false for imdb_score >= 9 and hence does not meet both the set AND condition. Thus the query returns an empty result set.

Q5: What are the movies to be released in 2010 or have a rating higher than 9?

Syntax: SELECT column1, column2, … FROM table_name WHERE condition1 OR condition2 OR condition3 OR condition4…..;

SQL query: SELECT title, release_year, imdb_score FROM films WHERE release_year = 2010 OR imdb_score >= 9;

Result: 

OR operator displays a record if any of the conditions separated by OR is TRUE.

The movies: The Godfather, The Dark Knight, The Godfather: Part II, The Shawshank Redemption are true for imdb_score >= 9, whereas Inception is true for release_year = 2010, and hence they make the cut for the OR condition.

Q6: What are the movies that have a release year other than 2010?

Syntax: SELECT column1, column2, … FROM table_name WHERE NOT condition1;

SQL query: SELECT title, release_year FROM films WHERE NOT release_year = 2010;

Result: 

You could also write: SELECT title, release_year FROM films WHERE release_year != 2010;

This would also give the same result as above.There can be multiple ways to achieve the same result in SQL. However, some queries may be more performant than others. The performance may depend on multiple factors - the amount of data, commands used within the query, how the database is structured, data type, etc. This is a slightly more complicated and advanced topic that we will not cover in this SQL query tutorial. 

But if you are interested, DataCamp’s Database Design course dives deeper into these topics.

ORDER BY

The ORDER BY keyword is used to sort the result in either ascending (using ASC keyword) or descending order (using DESC keyword). Sorting is done in ascending order by default.

Q7: What are the movies stored in the table? Sort it in descending order of release_year.

Syntax: SELECT column1, column2, … FROM table_name ORDER BY column1, column2, ... ASC|DESC;

SQL query: SELECT title, release_year FROM films ORDER BY release_year DESC;

Result: 

LIMIT

Sometimes it may take a long time for the database to run your query; especially if the amount of data stored in the database is very large. 

An easy, quick way to test a query or sample the kind of result you get back is using the LIMIT function. LIMIT lets you limit the number of results you get.

Q8: What are the top 5 latest movies stored in the table? 

Hint: Sort the result set in descending order wrt release_year and then use LIMIT.

Syntax: SELECT column1, column2, … FROM table_name LIMIT x;

SQL query: SELECT title, release_year FROM films ORDER BY release_year DESC LIMIT 5;

Result: 

INSERT INTO

INSERT INTO is used to add new records into a database table. You can write an INSERT INTO statement in two ways: 

• Specify both column names along with the corresponding values

Syntax: INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

• If you are adding values for ALL the columns of the table, you don't need to specify the column names in the query. Be careful when using this method, and make sure the order of the values is in the same order as the columns in the table. 

Syntax: INSERT INTO table_name VALUES (value1, value2, value3, ...);

Q9: Add an entry for ‘The Godfather: Part III’ into the table with the details: (‘The Godfather: Part III’, 1990, 54000000, 136900000, 7.6)

Syntax: SELECT column1, column2, … FROM table_name ORDER BY column1, column2,... ASC|DESC;

SQL query: INSERT INTO films (title, release_year, budget, gross, imdb_score)

VALUES (‘The Godfather: Part III’, 1970, 54000000, 136900000, 7.6);

Resultant table: 

UPDATE

We use the UPDATE statement to modify the existing records in a table. 

Q10: Correct the release_year for ‘The Godfather: Part III’ to be 1990 instead of 1970

Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ...WHERE condition;

SQL query: UPDATE films SET release_year = 1990 WHERE title = ‘The Godfather: Part III’;

Resultant table: 

DELETE

The DELETE statement is used to delete an existing record in a table. 

Q11: Delete the entry for ‘The Godfather: Part III’ from the ‘films’ table.

Syntax: DELETE FROM table_name WHERE condition;

SQL query: DELETE FROM films WHERE title = ‘The Godfather: Part III’;

Resultant table: Same as the original table that we started with a total of 10 records.

Advanced SQL queries

Remember we put the magical ‘*’ for a later discussion at the end of Q1? The * is an asterisk. It is an overarching character that we could read as ‘all’. 

Bringing back Q1: What are the movies stored in the table?

We used SELECT - FROM with the Syntax: SELECT column1, column2, … FROM table_name;

We used the query: SELECT title FROM films;

But what if you didn’t know ‘title’ was a field within the table ‘films’? Or the name of any field within the table? Or you simply want all the fields in the table to get a feel of the data? Well, this is where * comes in handy.

We could rewrite the query as: SELECT * FROM films; 

This would get us all the fields available in the table.

SQL Wildcard Characters

LIKE

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. 

Q12: Retrieve all the names of the movie starting with ‘The’

Syntax: SELECT column1, column2, … FROM table_name WHERE column1 LIKE patternToMatch;

SQL query: Select * FROM films WHERE title LIKE ‘The%’;

Resultant table:

What’s great is that we can use wildcards in combinations! So you can create pretty complex match statements using wildcard characters along with LIKE.

Aggregate Functions

SQL is excellent at aggregating data like you would in a pivot table in Excel. Aggregate functions can perform calculations not between two columns but within a column, so working with all or some rows in the same column. 

These are the aggregate functions within SQL: 

Q13: What is the latest movie stored in the table?

SQL query: SELECT title AS latest_movie, MAX(release_year) AS released_in FROM films;

Result: 

GROUP BY

SQL aggregate functions aggregate across an entire column. But what if you want to aggregate only part of a column? For example, you might want to count the number of movies released in a year.

This is where you’d need a GROUP BY clause. GROUP BY allows you to segregate data into groups, which can then be aggregated independently of one another.

Q13: Count the number of movies released in a year.

SQL query: SELECT release_year, COUNT(*) AS number_of_movies FROM films GROUP BY release_year;

Result: 

SQL Query Examples from multiple tables

So far, we have been working with only one table at a time. But the real power of databases and SQL comes from the ability to work with data from multiple tables. 

The term "relational database" in RDBMS comes from the fact that the tables within the database relate to one another. They have common identifiers that allow data from multiple tables to be combined easily. This is where we use SQL table JOINS.

Let’s see a simple example to get ourselves familiar with the JOIN syntax and also learn a little about the different kinds of JOINS possible.

Let us consider another table called ‘movieDirectors’ that holds the director’s name for some of the movies we have in the ‘films’ table. 

We could combine the data in these two tables using a common identifier: ‘title’.

Q14: Add a column to the ‘films’ table, populating it with the director’s name from the ‘movieDirectors’ table.

Syntax: SELECT leftTable.column1, rightTable.column1, leftTable.column2…. FROM leftTable INNER JOIN rightTable ON leftTable.commonIdentifier = rightTable.commonIdentifier;

SQL Query: Select films.title, films.release_year, movieDirectors.director FROM films INNER JOIN movieDirectors ON films.title = movieDirectors.title

Result: 

Different types of SQL Joins

In the example query above, ‘films’ is the left table and ‘movieDirectors’ is the right table. So with the INNER JOIN we performed, we only got back movies that existed in both the tables. The result set was an intersection of the two tables.

SQL UNION

SQL JOINS allow you to combine two datasets side-by-side. But with SQL UNION you can stack one dataset on top of the other. The UNION operator is used to combine the result-set of two or more SELECT statements when the following “rules” are met: 

1. Every SELECT statement must have the same number of columns.
2. The columns must also have similar data types.
3. The columns used in the SELECT statements must be in the same order.

Syntax: SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;

Let us consider the table ‘movies_2000s’:

Now if we were to combine the two tables…

SQL Query: SELECT title FROM films UNION SELECT title FROM movies_2000s;

Result: 

You might have noticed that ‘Inception’ appeared only once in the result set. This is because UNION selects only distinct values; if you want all the values, you can use UNION ALL.

NESTED SQL Queries

SQL is capable of performing wonders with data. Nested queries are yet another tool in SQL that makes it a language worth knowing. A nested query is nothing but a query within a query (Kind of like dreams in Inception!)

With nested queries, you can perform very complicated operations but in multiple steps keeping the readability and understandability of code intact.

Q15: Determine the average profitability of the movies in the table ‘films’.

Hint: You calculated profitability earlier, with Q3. Now you have to take the result from this query and apply the AVG function to it.

SQL Nested Query: 

SELECT AVG(*) AS average_profit FROM 

(SELECT title, (gross - budget) AS movie_profit FROM films where gross > budget);

We removed the negative value from ‘Fight Club’ in the calculation within the inner SQL query by adding gross > budget. 

In the nested query above, first the inner query is executed and then this result set is used as a temporary table for the outer query to query from and get data. 

We will not dive too much into the nested SQL query topic. But we highly recommend you to follow DataCamp’s Intermediate SQL course to master SQL queries. You will be playing with European Soccer Database while learning more about nested queries. You will also learn about CASE statements and window functions - topics we couldn’t cover in this SQL query tutorial. 

Final thoughts

You have learned a lot about SQL queries and are in a great position to start your journey with some real-world problem solving using SQL. In this SQL query tutorial, you learned what a SQL query is and how to write SQL queries. 

You saw the query syntax and answered some questions along the way. We started with simple SQL query examples using SELECT - FROM - WHERE statements and made our way to slightly more complex SQL queries using joins, union and nested queries. 

We covered a lot in the tutorial, however this is NOT an exhaustive list of all possible simple and advanced SQL queries - there are more. And we highly recommend following DataCamp’s SQL Fundamentals track to get a deeper and more comprehensive SQL understanding. 

Remember, practice is the key to mastering any skill and SQL is no exception! Practise will take your SQL skills from beginner to advanced.

So, go get started and happy querying!