Week 1: PostgreSQL Basics for Beginners – Getting Started

Week 1: PostgreSQL Basics for Beginners (Days 1–7)

In Week 1, you’ll focus on understanding the fundamentals of PostgreSQL. This includes installation, database concepts, creating tables, writing simple SQL queries, and practicing basic database management skills. By the end of this week, you’ll be ready to dive deeper into relational database management with PostgreSQL.

---

Day 1: Introduction to PostgreSQL and Setup

• What is PostgreSQL?
• PostgreSQL is an open-source, relational database management system (RDBMS) that uses and extends the SQL language. It’s known for its scalability, flexibility, and support for advanced data types.
• Install PostgreSQL:
• Download and install PostgreSQL from the official website.
• Set up PostgreSQL on your system (Windows, macOS, Linux).
• Learn how to interact with PostgreSQL using psql, the command-line interface.

Example:

# Verify PostgreSQL installation
psql --version

---

Day 2: Understanding Database Concepts

• Databases and Tables:
• Understand the structure of a relational database: databases, schemas, tables, and rows.
• Learn about primary keys, foreign keys, and indexes.
• Creating a Database:
• Learn how to create and drop databases.

Example:

-- Create a new database
CREATE DATABASE my_database;

-- List all databases
\l

---

Day 3: PostgreSQL Data Types

• Common Data Types:
• Learn about common PostgreSQL data types such as INTEGER, VARCHAR, TEXT, DATE, BOOLEAN, NUMERIC, and FLOAT.
• Understand how to define fields with appropriate data types when creating tables.

Example:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    join_date DATE
);

---

Day 4: Basic SQL Commands – Inserting and Selecting Data

• Inserting Data:
• Learn how to insert data into tables using the INSERT INTO statement.

Example:

-- Insert data into users table
INSERT INTO users (name, age, join_date)
VALUES ('John Doe', 28, '2024-01-01');

• Selecting Data:
• Practice querying data using SELECT, WHERE, ORDER BY, and LIMIT.

Example:

-- Retrieve all records from the users table
SELECT * FROM users;

-- Retrieve specific records with conditions
SELECT * FROM users WHERE age > 25;

---

Day 5: Updating and Deleting Data

• Updating Data:
• Learn how to update existing records with the UPDATE statement.

Example:

-- Update user age
UPDATE users SET age = 29 WHERE name = 'John Doe';

• Deleting Data:
• Learn how to delete records using the DELETE FROM statement.

Example:

-- Delete a record from the users table
DELETE FROM users WHERE name = 'John Doe';

---

Day 6: Filtering and Sorting Data

• Filtering Data with WHERE:
• Learn how to filter results using logical operators such as AND, OR, and NOT.

Example:

-- Retrieve users aged over 25 and with name 'John Doe'
SELECT * FROM users WHERE age > 25 AND name = 'John Doe';

• Sorting Data with ORDER BY:
• Learn how to sort query results using ORDER BY to organize data in ascending or descending order.

Example:

-- Sort users by age in descending order
SELECT * FROM users ORDER BY age DESC;

---

Day 7: Basic Joins and Relationships

• Understanding Joins:
• Learn how to join two or more tables using the JOIN keyword. Practice with INNER JOIN, LEFT JOIN, and RIGHT JOIN.

Example:

-- Retrieve data from two tables using INNER JOIN
SELECT users.name, orders.product
FROM users
INNER JOIN orders ON users.id = orders.user_id;

• Foreign Keys:
• Understand how foreign keys work to establish relationships between tables.

Example:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    product VARCHAR(100)
);

---

Conclusion

In Week 1, you’ve laid the foundation for working with PostgreSQL. You’ve learned how to install and set up PostgreSQL, understand key concepts, and write basic SQL queries. You’ve also practiced inserting, updating, deleting, and selecting data, as well as working with joins and foreign keys.

Next Steps:

As you move into Week 2, you’ll explore more advanced SQL techniques, including subqueries, aggregate functions, and data constraints. This will strengthen your ability to manage complex databases and relationships in PostgreSQL. Keep practicing and stay tuned for the upcoming lessons!