Learning SQL as a Beginner in 2022 (Tutorials and Exercises)

Related Articles

Razer Raiju Mobile Controller Review

October 5, 2022

How to Build a Laptop (Step-by-step Guide)

July 3, 2022

The Best Ear-Hook Headphones in 2022

June 26, 2022

SQL, pronounced “sequel” (or SQL), is the standard tool for every data scientist and programmer who has to juggle data on a daily basis.

It could be said that SQL is one of the most important programming languages nowadays if you are aiming for a job as a data scientist (as well as data analyst/analyst).

With SQL, it is possible to answer any question about stored data.

Two possible scenarios from practice:

1. You have a music school, and a student wants to know when his teacher has the next appointment.
2. You want to know how many customers of your online shop are older than 25

In this tutorial, we’re going to dive into SQL basics from a total beginner’s perspective to get you comfortable with this crucial skill.

Let’s start by answering the most important question:

What is SQL?

SQL stands for Structured Query Language.

A query language is a type of programming language designed to make it easier to retrieve specific information from databases, and that’s what SQL does.

To make the definition even easier: SQL is the language of databases.

This is important because most companies store their data in databases. And while there are many types of databases (like MySQL, PostgreSQL, and Microsoft SQL Server), most of them speak SQL. Once you master the basics of SQL, you can work with any of these database management systems (DMBS).

Even if you plan to do your data analysis using another language, such as Python or R, most companies will most likely require you to use SQL to query the relevant data from the database.

Now that we’ve defined the basics of SQL, let’s take a quick look at the functionality.

What can you do with SQL?

You can do the following with SQL:

• create databases
• Create tables in databases
• Retrieve data from one or more databases
• Insert new data into the database
• Change or update entries in your database
• Delete entries from a database
• Delete databases
• Set access restrictions for your database
• etc.

Before we learn about SQL and databases, we should understand what data is.

What is data?

Put simply, data is “information” that relates to something. For example, your name, age, height, weight, etc. are some data related to you. An image, an illustration, an Excel file, a PDF file, etc. can also be regarded as data.

We store this data in the so-called database.

What is a database?

A database is a systematic collection of data. It supports the electronic storage and manipulation of data. Databases thus make data management easy.

Here are some examples:

• Your ISP uses a database to store personal information, phone numbers, and other contact information.
• Your electric utility uses a database to manage annual billing, resolve customer-related questions, process outage data, etc.
• Let’s also look at social media platforms like Instagram or Facebook. The database needs to store, manipulate, and display data related to members, their friends, member activity, news, advertising, and more.
• We can give countless examples of how databases are used.

Now that we’ve got the vocabulary and basics covered, let’s get started with SQL. In the following sections, you will get to know the most important basics of SQL and the manipulation of databases step-by-step.

We will first create a table for your fictitious database and then query, change and delete data from it.

1. Create a table

When creating new tables in SQL, the CREATE TABLE statement is called. It takes as arguments all the columns we want in the table and their data types.

Here we create a simple table called Months. It consists of 3 columns:

• id – The number of the month in the calendar year (integer).
• name – The name of the month (string, maximum 10 characters).
• day – The number of days in this month (integer).

And this is what the respective SQL looks like:

CREATE TABLE months (id int, name varchar(10), day int);

Also when creating tables it is good practice to add a primary key to one of the columns. This helps to keep the entries unique and speeds up the selection of the queries. We won’t cover them in this lesson, but you can read about them in our SQL Quickstart courses or just google the term.

2. Insert data into rows

Now we want to fill the months with a few lines of information. Adding entries to a table is done using the INSERT statement. There are two different ways to use it:

The first way doesn’t specify the column names where the data will be inserted, it just expects the values and leaves it up to the developer to specify all the data in the right order.

INSERT INTO months VALUES (1,’January’,15) ;

The above example is nice and short, but there’s a big problem. If we add more columns in the future, the query will break. The preferred way to work around this is the following notation:

INSERT INTO months (id,name,day) VALUES (2,’February’,11);

3. SELECT query

So-called SELECT queries are your best friend when you want to extract data from a database. The select statement is used all the time. Therefore, we will cover it in detail below.

3.1 Select all data in a table

The simplest SELECT example is the following query, which returns all columns and rows from the Products table:

SELECT * FROM products;

The asterisk (*) or also called wildcard means that we want to select all columns without exception.

Since SQL databases usually consist of more than one table, the FROM keyword is needed to specify which table we want to search.

3.2 Select specific columns in a table

Sometimes we don’t want to have all the columns in a table. With SQL, we can select and query only those columns that we need:

Instead of putting the asterisk (*), we write the name(s) of the desired column(s). See the following example:

SELECT name, price FROM products;

In the example above, we select the Name and Price columns from the Products table.

3.3. Sort data in ascending or descending order

Also, in many cases we want the results of the query to be sorted. In SQL we do this with ORDER BY. The command takes an optional modifier:

• ASC (standard sorting, ascending) or
• DESC (descending)

This is what the command looks like in SQL:

SELECT name, price FROM products ORDER BY price DESC;

4. WHERE

You’ve already learned how to select only specific columns. But what happens if you only want to display rows that meet a certain condition (e.g. products with a turnover of at least €5 million)?

The solution to the problem of filtering data according to conditions is called the WHERE statement.

In the following query, we only select the products that make at least €5 million in sales.

SELECT * FROM products WHERE sales >= 5;

5. Logical operators: AND / OR

WHERE conditions can be made as specific as you want your condition to be using the logical AND (AND) and OR (OR) operators and mathematical comparisons (=,<,>,<=,>=,<>).

If you want to select all albums in the “Metal” genre that have made at least €10 million in sales, write the following SQL statement:

SELECT * FROM albums WHERE genre = ‘metal’ AND revenue >= 10;

6. In, Between, and Like

WHERE statements also support special commands that allow quick checking of frequently used queries.

They are divided as follows:

• IN – compares the column to multiple possible values. Returns true if at least one condition is true
• BETWEEN – checks if a value is within a range
• LIKE – searches for a specific pattern

For example, if we create a query to select all metal and rock albums, we can use IN(“value1”, “value2”).

SELECT * FROM albums WHERE genre IN (‘rock’,’metal’);

If we want to get all albums released between 1999 and 2010, we write the SQL statement as follows:

SELECT * FROM albums WHERE released BETWEEN 1999 AND 2010;

7. Features

SQL comes with ready-made functions to help you evaluate data.

Here are some of the useful SQL functions:

• COUNT() – returns the number of rows
• SUM() – returns the grand total of a numeric column
• AVG() – returns the average of multiple values
• MIN() / MAX() – returns the minimum/maximum value of a column

8. Nested selection (SELECT nesting)

In the previous section, we learned how to perform simple calculations on data. If we actually want to use the result of these calculations, a nested query (aka “sub-select”) is often necessary.

Let’s say we want to query the artist, album, and release year for the oldest album in a table.

We already know how to get the right columns:

SELECT artist, album, released FROM Album;

We also know how to find out the chronologically oldest year:

SELECT MIN(published) FROM Album;

Now all you have to do is combine the two SELECT statements using WHERE :

SELECT artist, album, released FROM album WHERE released = ( SELECT MIN(released) FROM album) ;

9. Joins (connect tables)

In more complex databases, there are usually multiple tables that are related in some way.

Imagine you have two tables:

• video games
• video game developer

There is a developer column in the videogames table, but it contains an integer (eg 5) instead of the developer’s name. This number represents the ID of each developer from the video game developer table, which logically links the two sheets together. This allows us to use the information stored in both tables at the same time.

If we want to create a query that returns everything we need to know about our games, we can use something called INNER JOIN to get the columns from both tables.

This is the simplest form of JOIN and the one most frequently used in practice. There are other JOINS (eg Left Join) but these are mostly used in very specific cases and will be skipped in this short SQL tutorial.

10. Aliases

If you look at the previous example, you’ll see that there are two columns called “Name”. That sounds confusing. So let’s make a change to make the column names more descriptive. For this we use aliases:

• Column name without alias: my_sales
• With alias: u

In SQL it looks like this:

• SELECT my_sales FROM table_name AS u;
• Likewise, we can shorten query building by putting aliases on table names.

11. Update

You often have to change or update data in individual rows. In SQL, this update is done using the UPDATE statement.

The use of UPDATE consists of:

• UPDATE: Select the table that contains the record we want to change.
• SET: Set the new value(s) for the desired column(s).
• WHERE: Use WHERE to select which row(s) we want to update. Important: If the WHERE statement is forgotten, all rows in the table will change.

An example of an update statement in SQL looks like this:

UPDATE tablename SET firstname = ‘Daniel’ WHERE id = 1;

12. Delete rows

Deleting a table row with SQL is quick and easy. All you need is the right table and the specific row to be deleted.

SQL example to delete a row:

DELETE tablename WHERE id = 20;

Note: Please be careful when deleting rows. When you write your DELETE statement, make sure you also include the WHERE statement. Without “WHERE” all table rows are deleted. Urgh!

13. Delete tables

If we want to delete all rows without deleting the table, use the TRUNCATE command. Here is a SQL example of the query:

TRUNCATE TABLE table name;

If you want to delete all data and the associated table (irrevocably), use the DROP command:

DROP TABLE table name;

Note: Please be careful with DROP TABLE command because after executing it all data will disappear in nirvana.

Summary of the SQL tutorial

Congratulation! If you’ve read this “Learn SQL” article to the end, you should now be able to query a database and its tables to get the data you’re interested in. You are also able to edit and delete this data.

The topic of SQL is much more in-depth, but the above basics and SQL commands are sufficient for beginners to gain their first practical experience. With a little practice in SQL, you will lay an important foundation for becoming a data scientist and web developer.