Introduction to Databases, SQL, and Writing First Query

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:

 movie_id |     movie_name      | release_date | phase 
----------+---------------------+--------------+--------
        1 | Iron Man            | 2008-05-02   |     1
        2 | The Incredible Hulk | 2008-06-13   |     1
        3 | Iron Man 2          | 2010-05-07   |     1
        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.

\dt

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

The output is:

             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | characters    | table | postgres
 public | movie_details | table | postgres
 public | movies        | table | postgres
(3 rows)

For this course, we will focus on the Name column. The table names in our database are "characters", "movie_details", and "movies".