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:

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.

\dt is a meta-command used in psql to list all tables in the current database.

The output is:

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:

• SELECT is the SQL keyword used to retrieve data.
• * means "all columns."
• FROM movies specifies the table from which you want to retrieve data.

The output of the query is:

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:

• Multi-line comments are enclosed in /* and */, ignoring everything in between. These are useful for longer explanations or notes. Example:

• Case Insensitivity: In SQL, keywords such as SELECT and FROM are 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.

This command selects all columns from the characters table, and LIMIT 10 restricts the number of rows returned to 10.

The output is:

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.