[SQL Basic] How to rename Columns and see Unique Values? — SQL AS/DISTINCT/IS NULL

#7. Marketing/Data Analysis for Beginners

Today, we will cover AS, DISTINCT, IS NULL. They’re pretty simple and very commonly used, so let’s get started. Here is our handy Glossary:

Lesson 3:
📌SELECT: you’re asking the database to show you something
📌FROM: you’re giving the database a location to search
📌WHERE: you’re narrowing down/specifying the location

Lesson 4:
📌ORDER BY: you’re asking for a specific order for the list to be displayed

Lesson 5:
📌AND: find information that matches 2 specific categories
📌OR: find all information that matches at least 1 specified category
📌IN: find information that matches any of the following categories

Lesson 6:
📌LIKE: used to find a specific pattern in a column

📌AS: used to rename columns
📌DISTINCT: used to see unique values
📌IS NULL: used to see null values

# Table of Contents

Query 1. Finding films based in China and (1) changing the column name to ‘China_Movie’, (2) sorting by rental rate in ascending order

SELECT film_id, title AS China_Movie, description, rental_rate FROM film WHERE description LIKE ‘%china%’ ORDER BY rental_rate ASC;

Query 2. What are the distinct (different) values in the rating column?

SELECT DISTINCT rating FROM film;

Query 3. Finding the null value of address2 in the address table

SELECT * FROM address WHERE address2 IS NULL;

# AS

Q1. Finding films based in China and changing the column name to ‘China_Movie’, then sorting them by rental rate in ascending order

SELECT (original column name) AS (new column name)

The first query that we are going to use is AS. This is used with SELECT to rename columns. We want to give columns distinct names since we export data by writing specific conditions. All we need to do is write the original column name after select and add the new column name after AS.

In this example, we will use LIKE to search for a pattern that matches information about films based on China. Before that, let’s identify the information we want to see. We want the film ID, title, description, price (rental rate) and we want to search in the ‘film’ table. Oh, let’s also order it by price in ascending order. Try this:

SELECT film_id, title, description, rental_rate FROM film WHERE description LIKE’%china%’ ORDER BY rental_rate ASC;

Looking good! Here are all movies with the word ‘China’ in the description. Now we want to rename the column ‘title’ to ‘China Movie’. Let’s do this by using our new query AS. We’re only renaming one thing here, so we only need to use AS once in the area we want changed. Plug in AS after title in the SELECT query, like this:

SELECT film_id, title AS China_Movie, description, rental_rate ….

The rest of your query will remain the same, the only thing you’ve changed is the query of renaming that one column. It should look like this:

🔑 You can go one step further and rename all the columns!
Here is our example:

SELECT film_id, title AS China_Movie, description AS Summary,
rental_rate AS Price
FROM film
WHERE description LIKE’%china%’
ORDER BY rental_rate ASC;

How is it? Looks much more manageable now, doesn’t it? 😄

# DISTINCT

Q2. What are the distinct (different) values in the rating column?

SELECT DISTINCT column

If you want to see film ratings like PG, G, NC-17 in the film table, how can you write your query? Try this:

SELECT film_id, title, description, rating FROM film;

Notice how there are many different movies with the same ratings.
What if we just want to see how many ratings are available, without the film information? We can write it like this:

SELECT rating FROM film;

While this gives us the information we want, it shows multiple results of the same rating. It’ll be easier to sort through this table if we have a list of ratings without the duplicates. This is where DISTINCT comes in. This query works when partnered with SELECT, so let’s put it into practice to see exactly what ratings are available:

SELECT DISTINCT rating FROM film ORDER by rating;

🔑 We added ORDER BY here just to get a clearer view of the information.

There we have it! We can see all 5 ratings available in the movies we have in our tables, without any duplicate information.

# IS NULL

Q3. Finding the null value of address2 in the address table

WHERE column IS NULL

Finally, let’s learn how to find the null value in SQL.

📌 The term NULL refers to a value of zero. This means there is no information at all.

Let’s check out the address table for a second. Run this query:

SELECT * FROM address;

In the address2 column, you’ll see a few results with “(null)” and the rest are just blank. Even though in our eyes the blanks are also ‘null’, SQL is reading them as having value. The value is just a space! So let’s check out only the columns with blanks by putting a space between our single quotations.

SELECT * FROM address WHERE address2=‘ ’;

Here’s the list of address2 results with a blank. Next let’s check out the results where the information is (null). Can we just type WHERE address2=‘(null)’?
Give it a try!

⚠️ As you can see, we won’t get any results. That’s because we need to use a different query here. Try this one:

SELECT * FROM address WHERE address2 IS NULL;

There we go! Here are all the addresses where their address2 information is null, meaning absolutely nothing was entered (not even a space!) So if you ever want to find columns that have (null) in it, you need to use the IS NULL query.

# Practice Time

📰 Your challenge this time is to find the unique value of rental duration in the film table, and sort it in ascending order. 👍 Take a moment to figure it out on your own before you check out our tutorial below.

🔑 Use the queries SELECT, DISTINCT, FROM, and ORDER BY

🔑 Start with: SELECT DISTINCT rental_duration ….

Did you get it? Compare your query to the one we’ve provided below!

SELECT DISTINCT rental_duration FROM film ORDER BY rental_duration ASC;

In this example, we have to use DISTINCT because we want to see the unique values without any duplicates. Without DISTINCT, we will get rows and rows of information that we don’t need.

What did you think? How was it? If you’re feeling lost, follow along in our video tutorial linked below:

# Tutorial Video

See you next time! 👋

# [SQL Basic Series] — Marketing/Data Analysis for Beginners

1. What is SQL? — Database Introduction
2. Downloading SQLGate and Connecting to a Database
3. What kind of data is in the ‘film’ table? — SELECT/FROM/WHERE
4. How to sort film lists by price (rental rate)? — ORDER BY
5. Can I rent a 3 hour movie for $0.99? — AND/OR/IN
6. How can I find a movie that’s thrilling? — LIKE
7. How to rename columns and see unique values? — AS/DISTINCT/IS NULL
8. Let’s see Sample Database tables!
9. What is the total length of all the films added together? — COUNT/SUM
10. What is the average film length? — AVG/MIN/MAX
11. How many movies have the same rating? — GROUP BY/HAVING
12. Let’s designate a value based on the rental duration. — CASE
13. Let’s find out the movie genres by joining tables! — INNER JOIN
14. Let’s check movie rentals by the inventory list! — LEFT/RIGHT JOIN
15. Let’s find customers that have rented movies priced $9.99! — Subquery
16. How to work with Date Functions in SQL — DATE_FORMAT, DATEDIFF
17. How to work with String Functions in SQL — CONCAT, LENGTH, SUBSTR
18. Let’s use some Numeric SQL functions — TRUNCATE, ROUND, MOD
19. Let’s learn some advanced CASE queries!
20. Let’s learn some advanced JOIN queries!