SQL & PostgreSQL Tutoriel complet pour Débutants - WEWANTCODE.COM

Introduction

This tutorial provides a comprehensive guide for beginners wanting to learn SQL and PostgreSQL. It covers the fundamental concepts of databases, installation procedures, and basic operations, ensuring a strong start in database management.

Step 1: Understanding Databases

• What is a Database?

  • A database is an organized collection of data that can be easily accessed, managed, and updated.

• What is a Relational Database?

  • A relational database uses a structure that allows us to identify and access data in relation to another piece of data in the database. SQL (Structured Query Language) is used to communicate with these databases.

Step 2: Installing PostgreSQL

• Installation on Mac

  • Visit the PostgreSQL download page.
  • Follow the instructions specific to macOS to download and install PostgreSQL.

• Installation on Windows

  • Go to the PostgreSQL download page.
  • Download the installer and follow the prompts to install PostgreSQL on your system.

Step 3: Working with Tables in Databases

• Understanding Tables

  • Tables are the fundamental building blocks in databases, where data is stored in rows and columns.

• Data Types

  • Familiarize yourself with common data types used in PostgreSQL such as:
    • INTEGER
    • VARCHAR
    • DATE
    • BOOLEAN

Step 4: Keys and Constraints

• Primary and Foreign Keys

  • Primary keys uniquely identify each record in a table.
  • Foreign keys create a link between two tables.

• Constraints

  • Constraints ensure the accuracy and reliability of the data:
    • UNIQUE: ensures all values in a column are different.
    • NOT NULL: ensures a column cannot have a NULL value.
    • CHECK: ensures all values in a column satisfy a specific condition.

Step 5: Creating Your First Table

• Use the following SQL command to create a table:

CREATE TABLE actors (
    actor_id SERIAL PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    birth_date DATE
);

Step 6: Inserting Data into Your Table

• Insert data using the following SQL command:

INSERT INTO actors (first_name, last_name, birth_date)
VALUES ('Robert', 'Downey Jr.', '1965-04-04');

Step 7: Modifying and Deleting Data

• To update data in the table, use:

UPDATE actors
SET birth_date = '1965-04-05'
WHERE first_name = 'Robert' AND last_name = 'Downey Jr.';

• To delete data from the table, use:

DELETE FROM actors
WHERE actor_id = 1;

Conclusion

This tutorial provides a foundational understanding of SQL and PostgreSQL, covering essential concepts from database definitions to basic SQL commands. As you progress, consider exploring more advanced topics like CRUD operations, data manipulation, and complex queries. For further learning, check out the full playlist provided in the video description.