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.
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:
SQL1SELECT 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.
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:
SQL1SELECT * FROM movies
2LIMIT 4;This code outputs:
Plain text1 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.
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:
SQL1SELECT movie_name, release_date 2FROM movies;
- The
SELECTstatement now specifiesmovie_nameandrelease_dateexplicitly, meaning only these columns will be retrieved. - The
FROMclause still indicates themoviestable as the source.
Plain text1 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 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.
SQL1SELECT movie_name AS "Name", release_date AS "Date" 2FROM movies;
movie_name AS "Name": In the output, themovie_namecolumn will be called "Name".release_date AS "Date": In the output, therelease_datecolumn will be named "Date".
The output of the query is:
Plain text1 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.
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
SELECTstatement. - 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!