Welcome to the first lesson of our course "Intro to SQL with PostgreSQL." This lesson will introduce you to some fundamental concepts that form the backbone of how we interact with data.
A database is like an organized digital filing cabinet where you can store and retrieve data efficiently. In this course, we'll focus on a specific type of database known as a relational database. A relational database stores data in tables, much like how an Excel file has different sheets for different sets of data.
SQL (Structured Query Language) is the language used to communicate with databases. It allows you to create, read, update, and delete data stored in a database. By learning SQL, you will have the skills to interact with databases and unlock valuable insights from the stored data.
PostgreSQL is an advanced, open-source relational database management system (RDBMS). It's known for its robustness, flexibility, and comprehensive feature set. We'll be using PostgreSQL in this course due to its capabilities and widespread use in the industry.
Throughout this course, we'll be working with a sample dataset that provides details for various Marvel movies. This dataset contains 3 different tables, but for this lesson, we'll focus on the movies table.
Below is a sample representation of what the movies table looks like:
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
This table shows data for the first 4 Marvel movies. The table has columns for movie_id, movie_name, release_date, and phase. Each row of the database (also called an entry) holds the data for each of these columns for a single movie.
To display all tables in the current database, use the following command in the PostgreSQL.
SQL1\dt
\dt is a meta-command used in psql to list all tables in the current database.
The output is:
Plain text1 List of relations 2 Schema | Name | Type | Owner 3--------+---------------+-------+---------- 4 public | characters | table | postgres 5 public | movie_details | table | postgres 6 public | movies | table | postgres 7(3 rows)
For this course, we will focus on the Name column. The table names in our database are "characters", "movie_details", and "movies".
Let's learn how to write some basic SQL queries to retrieve information from the movies table.
To select all the records from the movies table, use the following SQL query:
SQL1SELECT * FROM movies;SELECTis the SQL keyword used to retrieve data.*means "all columns."FROM moviesspecifies the table from which you want to retrieve data.
The output of the query is:
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 7 5 | Captain America: The First Avenger | 2011-07-22 | 1 8 6 | The Avengers | 2012-05-04 | 1 9 7 | Iron Man 3 | 2013-05-03 | 2 10 8 | Thor: The Dark World | 2013-11-08 | 2 11 9 | Captain America: The Winter Soldier | 2014-04-04 | 2 12 10 | Guardians of the Galaxy | 2014-08-01 | 2 13 11 | Avengers: Age of Ultron | 2015-05-01 | 2 14 12 | Ant-Man | 2015-07-17 | 2 15 13 | Captain America: Civil War | 2016-05-06 | 3 16 14 | Doctor Strange | 2016-11-04 | 3 17 15 | Guardians of the Galaxy Vol. 2 | 2017-05-05 | 3 18 16 | Spider-Man: Homecoming | 2017-07-07 | 3 19 17 | Thor: Ragnarok | 2017-11-03 | 3 20 18 | Black Panther | 2018-02-16 | 3 21 19 | Avengers: Infinity War | 2018-04-27 | 3 22 20 | Ant-Man and The Wasp | 2018-07-06 | 3 23 21 | Captain Marvel | 2019-03-08 | 3 24 22 | Avengers: Endgame | 2019-04-26 | 3 25 23 | Spider-Man: Far From Home | 2019-07-02 | 3 26 24 | Black Widow | 2021-07-09 | 4 27 25 | Shang-Chi and the Legend of the Ten Rings | 2021-09-03 | 4 28 26 | Eternals | 2021-11-05 | 4 29 27 | Spider-Man: No Way Home | 2021-12-17 | 4 30 28 | Doctor Strange in the Multiverse of Madness | 2022-05-06 | 4 31 29 | Thor: Love and Thunder | 2022-07-08 | 4 32 30 | Black Panther: Wakanda Forever | 2022-11-11 | 4 33 31 | Ant-Man and The Wasp: Quantumania | 2023-02-17 | 5 34 32 | Guardians of the Galaxy Vol. 3 | 2023-05-05 | 5 35 33 | The Marvels | 2023-11-10 | 5 36(33 rows)
In the output, we see 33 Marvel movies along with their release date and phase number. Note at the bottom, we can see (33 rows) indicating how many entries are in the table.
Now that you have learned your first SQL query, let's dive into the syntax of SQL commands. Here are some fundamental syntax components to know:
-
Semicolon (
;): The semicolon serves as the end of a statement, similar to a period in a sentence. Correct usage is crucial for clear command separation. -
Comments: Comments are used for adding notes or explanations within your SQL code, which are ignored during execution. PostgreSQL supports both single-line and multi-line comments.
-
Single-line comments start with
--. The rest of the line after--is ignored by PostgreSQL. For example:SQL1-- This command selects all records from the movies table 2SELECT * FROM movies; -
Multi-line comments are enclosed in
/*and*/, ignoring everything in between. These are useful for longer explanations or notes. Example:SQL1/* 2The following command is used 3to select all records from the movies table 4*/ 5SELECT * FROM movies; -
Case Insensitivity: In SQL, keywords such as
SELECTandFROMare case-insensitive, which means they can be written in uppercase, lowercase, or any combination thereof without affecting the query's functionality. However, it is a common convention to write SQL keywords in uppercase to distinguish them easily from table names, column names, and other identifiers. This practice enhances readability and clarity.
Our characters table contains 243 entries. If we want to limit the number of entries in the output, we use the LIMIT clause.
SQL1SELECT * FROM characters
2LIMIT 10;This command selects all columns from the characters table, and LIMIT 10 restricts the number of rows returned to 10.
The output is:
Plain text1 character_id | movie_id | character_name | actor | screen_time_minutes 2--------------+----------+-------------------+-------------------+--------------------- 3 1 | 1 | Tony Stark | Robert Downey Jr. | 120 4 2 | 1 | Pepper Potts | Gwyneth Paltrow | 40 5 3 | 1 | James Rhodes | Terrence Howard | 30 6 4 | 1 | Obadiah Stane | Jeff Bridges | 25 7 5 | 1 | Happy Hogan | Jon Favreau | 20 8 6 | 1 | Agent Coulson | Clark Gregg | 15 9 7 | 1 | Raza | Faran Tahir | 10 10 8 | 1 | Yinsen | Shaun Toub | 10 11 9 | 2 | Bruce Banner/Hulk | Edward Norton | 110 12 10 | 2 | Betty Ross | Liv Tyler | 35 13(10 rows)
Great job! In this lesson, you learned about databases, SQL, and PostgreSQL. We also introduced the movies table from our course dataset and practiced writing basic SQL queries to retrieve data.
You learned how to:
- List all the tables in the database using
\dt - List all the entries in a table using
SELECT * FROM movies; - Limit the number of entries using
LIMIT
Now, it’s time to solidify your understanding with some hands-on practice exercises. Try writing additional queries using the examples provided in this lesson. The practice exercises will help you build confidence and prepare you for more advanced SQL topics in upcoming lessons.