Exploring SELECT Statements

Lesson 2

Welcome back! In the previous lesson, you learned the basics of databases and SQL, focusing on using PostgreSQL. You also wrote some initial SQL queries to get familiar with listing tables and retrieving data. In this lesson, you will learn how to retrieve specific data using the SELECT statement.

Understanding the SELECT Statement

The SELECT statement is one of the fundamental building blocks of SQL. Its primary purpose is to retrieve data from a database table. Here's a breakdown of a basic SELECT statement:

SQL
1SELECT column1, column2, ...
2FROM table_name;

SELECT: This keyword initiates the retrieval operation.
column1, column2, ...: These are the columns you want to retrieve from the table.
FROM: This keyword specifies the table from which to retrieve the data.
table_name: The name of the table to retrieve data from.

Basic SELECT Statement

In the last lesson, we learned how to use * to select all columns from a table. The asterisk (*) is a wildcard character that tells SQL to select all columns from the movies table. To review:

SQL
1SELECT * FROM movies
2LIMIT 4;

This code outputs:

Plain text
1 movie_id |     movie_name      | release_date | phase 
2----------+---------------------+--------------+--------
3        1 | Iron Man            | 2008-05-02   |     1
4        2 | The Incredible Hulk | 2008-06-13   |     1
5        3 | Iron Man 2          | 2010-05-07   |     1
6        4 | Thor                | 2011-05-06   |     1

Notice that the output contains all 4 column names.

Selecting Specific Columns

Now, let's say we only want to retrieve the movie_name and release_date columns from the movies table. Here's how you can do it:

SQL
1SELECT movie_name, release_date 
2FROM movies;

The SELECT statement now specifies movie_name and release_date explicitly, meaning only these columns will be retrieved.
The FROM clause still indicates the movies table as the source.

Plain text
1
2                 movie_name                  | release_date 
3---------------------------------------------+--------------
4 Iron Man                                    | 2008-05-02
5 The Incredible Hulk                         | 2008-06-13
6 Iron Man 2                                  | 2010-05-07
7 Thor                                        | 2011-05-06
8 Captain America: The First Avenger          | 2011-07-22
9 The Avengers                                | 2012-05-04
10 Iron Man 3                                  | 2013-05-03
11 Thor: The Dark World                        | 2013-11-08
12 Captain America: The Winter Soldier         | 2014-04-04
13 Guardians of the Galaxy                     | 2014-08-01
14 Avengers: Age of Ultron                     | 2015-05-01
15 Ant-Man                                     | 2015-07-17
16 Captain America: Civil War                  | 2016-05-06
17 Doctor Strange                              | 2016-11-04
18 Guardians of the Galaxy Vol. 2              | 2017-05-05
19 Spider-Man: Homecoming                      | 2017-07-07
20 Thor: Ragnarok                              | 2017-11-03
21 Black Panther                               | 2018-02-16
22 Avengers: Infinity War                      | 2018-04-27
23 Ant-Man and The Wasp                        | 2018-07-06
24 Captain Marvel                              | 2019-03-08
25 Avengers: Endgame                           | 2019-04-26
26 Spider-Man: Far From Home                   | 2019-07-02
27 Black Widow                                 | 2021-07-09
28 Shang-Chi and the Legend of the Ten Rings   | 2021-09-03
29 Eternals                                    | 2021-11-05
30 Spider-Man: No Way Home                     | 2021-12-17
31 Doctor Strange in the Multiverse of Madness | 2022-05-06
32 Thor: Love and Thunder                      | 2022-07-08
33 Black Panther: Wakanda Forever              | 2022-11-11
34 Ant-Man and The Wasp: Quantumania           | 2023-02-17
35 Guardians of the Galaxy Vol. 3              | 2023-05-05
36 The Marvels                                 | 2023-11-10
37(33 rows)

Using Aliases in SELECT Statements

Using aliases can make your output more readable and meaningful. Aliases are temporary names assigned to columns or tables for the duration of the query. You achieve this with the AS keyword.

SQL
1SELECT movie_name AS "Name", release_date AS "Date" 
2FROM movies;

movie_name AS "Name": In the output, the movie_name column will be called "Name".
release_date AS "Date": In the output, the release_date column will be named "Date".

The output of the query is:

Plain text
1                    Name                     |    Date    
2---------------------------------------------+------------
3 Iron Man                                    | 2008-05-02
4 The Incredible Hulk                         | 2008-06-13
5 Iron Man 2                                  | 2010-05-07
6 Thor                                        | 2011-05-06
7 Captain America: The First Avenger          | 2011-07-22
8 The Avengers                                | 2012-05-04
9 Iron Man 3                                  | 2013-05-03
10 Thor: The Dark World                        | 2013-11-08
11 Captain America: The Winter Soldier         | 2014-04-04
12 Guardians of the Galaxy                     | 2014-08-01
13 Avengers: Age of Ultron                     | 2015-05-01
14 Ant-Man                                     | 2015-07-17
15 Captain America: Civil War                  | 2016-05-06
16 Doctor Strange                              | 2016-11-04
17 Guardians of the Galaxy Vol. 2              | 2017-05-05
18 Spider-Man: Homecoming                      | 2017-07-07
19 Thor: Ragnarok                              | 2017-11-03
20 Black Panther                               | 2018-02-16
21 Avengers: Infinity War                      | 2018-04-27
22 Ant-Man and The Wasp                        | 2018-07-06
23 Captain Marvel                              | 2019-03-08
24 Avengers: Endgame                           | 2019-04-26
25 Spider-Man: Far From Home                   | 2019-07-02
26 Black Widow                                 | 2021-07-09
27 Shang-Chi and the Legend of the Ten Rings   | 2021-09-03
28 Eternals                                    | 2021-11-05
29 Spider-Man: No Way Home                     | 2021-12-17
30 Doctor Strange in the Multiverse of Madness | 2022-05-06
31 Thor: Love and Thunder                      | 2022-07-08
32 Black Panther: Wakanda Forever              | 2022-11-11
33 Ant-Man and The Wasp: Quantumania           | 2023-02-17
34 Guardians of the Galaxy Vol. 3              | 2023-05-05
35 The Marvels                                 | 2023-11-10
36(33 rows)

Notice how the column names have changed in the output.

Summary

In this lesson, you learned how to write SQL queries using the SELECT statement to retrieve data from a database. We covered:

The structure of a basic SELECT statement.
Selecting all columns from a table.
Selecting specific columns.
Using aliases to rename columns in the output.

Remember, practice is key to mastering SQL. In the next section, you'll find exercises to reinforce what you've learned. Take your time to work through them, and feel free to refer back to this lesson for guidance.

Good luck, and happy querying!

Enjoy this lesson? Now it's time to practice with Cosmo!

Practice is how you turn knowledge into actual skills.