Introduction to SQL (Part 2)

A beginner guide for learning SQL from scratch

Photo by Myke Simon on Unsplash

Why is there a cinema photograph in SQL tutorial? You might have this question on your mind. Today we’re going to solve several SQL exercises about movie from SQL Bolt. Let’s get started.

In SQL, you can use the WHERE clause to filter specific results so that only records that meet specific conditions are returned. The WHERE clause is applied to each record of data by checking specific column values to determine whether it should be included in the results or not.

Filtering in SQL can be divided into filtering on numeric columns (INTEGER and REAL or FLOAT) and filtering on text columns (TEXT or STRING). In this section we will learn how to filter on numeric columns, text columns, multiple columns and missing value.

1. Query for filtering on numeric columns

-- Filtering on numeric columns
SELECT 
    *
FROM
    table_1
WHERE
    value = 100;

These are several operators for filtering on numeric columns.

Figure 1. Operator for filtering on numeric columns (Image by author)

The difference for operators BETWEEN ... AND ... and IN (...):

• BETWEEN ... AND ... is used for numeric ranges and allows you to specify a range of values, for example this query would return records where the price column value is between 10 and 50, including both 10 and 50.

-- Filtering on numeric columns using BETWEEN ... AND ..
SELECT 
    *
FROM
    products
WHERE
    price BETWEEN 10 AND 50;

• IN (...) is used for specifying a list of specific values, and it can be used for both numeric and non-numeric columns, for example this query would return records where employee_id column value matches any of the values in the list (1001, 1002, or 1003).

-- Filtering on numeric columns using IN (...)
SELECT 
    *
FROM
    employees
WHERE
    employee_id IN (1001, 1002, 1003);

2. Query for filtering on text columns

-- Filtering on text columns
SELECT 
    *
FROM
    table_2
WHERE
    name LIKE "%af%";

These are several operators for filtering on text columns and the explanation about how to use them.

Figure 2. Operator for filtering on text columns (Image by author)

Here’s how the implementation of these operators in SQL:

• The equal operator, =, is used for exact matching. It checks if two values are exactly the same, including their case (in a case-sensitive comparison), for example to find product with exactly type as Watch in its name.

-- Filtering on text columns using =
SELECT 
    product_name
FROM
    products
WHERE
    product_name = "Watch";

• The not equal operator, !=, is the negation of = operator.
• LIKE operator is used for pattern matching and case-insensitive comparison ( RED matches with red when you use LIKE operator). For example to find all SKU with blue somewhere in their name, this query would return records where product_name contains the substring blue in any case. The result will be Blue Jacket, Blue Skirt, and Blue T-shirt.

-- Filtering on text columns using LIKE
SELECT 
    product_name
FROM
    products
WHERE
    product_name LIKE "%blue%";

• The NOT LIKE operator is the negation of LIKE operator.
• Wildcard character % is used to represent any number of characters (including zero characters), for example to find city with karta in the last part of their name. This query would return records where city contains the substring karta in any case. The result will beYogyakarta and Jakarta.

-- Filtering on text columns using LIKE and %
SELECT 
    city_name
FROM
    city
WHERE
    city_name LIKE "%karta";

• Wildcard character _ is used to represent a single character, for example to find products with a two letters preceding one, which is phone.

-- Filtering on text columns using LIKE and _
SELECT 
    product_name
FROM
    products
WHERE
    product_name LIKE "__one";

• IN (...) operator is used to filter records where a value in text column matches any value in a specified list, for example this query would return employees in the marketing or sales departments.

-- Filtering on numeric column using IN (...)
SELECT 
    *
FROM
    employees
WHERE
    department IN ("Marketing", "Sales");

• The NOT IN (...) operator is the negation of IN (...) operator.

3. Query for filtering on multiple columns

To filter records based on multiple columns or multiple conditions, you can add AND and OR operators. The AND operator in WHERE clause will return values that satisfy all of the specified conditions, on the other hand the OR operator in WHERE clause will return values that satisfy at least one of several specified conditions. You can also combine AND and OR operators to filter records based on more complex conditions. For example, the following query would return employees who work in the HR department and join the company after September 15, 2021.

-- Filtering based on multiple conditions or multiple columns
SELECT 
    *
FROM
    employees
WHERE
    department LIKE "HR"
  AND 
    join_date > "2021-09-15";

4. Query for filtering on missing value

In addition to the operators commonly used to filter records for numeric columns, text columns, and multiple columns, The IS NULL and IS NOT NULL operators will come in handy when you have a dataset with missing values. The IS NULL operator will return all records with missing values and the IS NOT NULL method will return all records without any missing values. For example, if you want to select all products with no missing values in the product_code, you can use this query.

-- Filtering on missing value
SELECT 
    *
FROM
    products
WHERE
    product_code IS NOT NULL;

Let’s solve several exercises about movie from SQL Bolt.

Figure 3. Exercise 2 in SQL Bolt

To answer the 1st and 2nd questions in Exercise 2 of SQL Bolt you need to do these queries:

-- 1st Question
SELECT 
    id,
    title 
FROM 
    movies
WHERE
    id = 6;

-- 2nd Question
SELECT 
    id,
    title,
    year
FROM 
    movies
WHERE
    year BETWEEN 2000 AND 2010;

Now you can solve 3rd and 4th questions by yourself. After that, we can continue to solve these questions from Exercise 3 of SQL Bolt.

Figure 4. Exercise 3 in SQL Bolt

To answer the 1st and 2nd questions in Exercise 3 of SQL Bolt you need to do these queries:

-- 1st Question
SELECT 
    id,
    title
FROM 
    movies
WHERE
    title LIKE "%Toy Story%";

-- 2nd Question
SELECT 
    id,
    title,
    director
FROM 
    movies
WHERE
    director = "John Lasseter";

You can continue to solve the 3rd and 4th questions in Exercise 4 by yourself. If you found any difficulties or obstacles around these exercises, please leave a comment and I will do my best to assist you.

So far, we’ve already learned how to filter on numeric columns, text columns, multiple columns and missing value using the SELECT, FROM, and WHERE clauses, as well as several operators in WHERE clause. See you in the next section of a beginner guide for learning SQL from scratch.

This is part of the Introduction to SQL series, here is the list of Introduction to SQL series.

Introduction to SQL (Part 1)

Introduction to SQL (Part 3)

References

1. SQL Basics Cheat Sheet from DataCamp

https://www.datacamp.com/cheat-sheet/sql-basics-cheat-sheet

SQL Basics Cheat Sheet

2. Exercises from SQL Bolt

SQLBolt - Learn SQL - SQL Lesson 2: Queries with constraints (Pt. 1)