Databases and SQL

Introduction

Databases are the backbone of nearly every web application. In this course, you'll learn SQL (Structured Query Language) and how to work with PostgreSQL, one of the most powerful open-source databases.

SQL basics

-- Creating a table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Inserting data
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- Querying data
SELECT * FROM users;
SELECT name, email FROM users WHERE id = 1;
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- Updating data
UPDATE users SET name = 'Alice Smith' WHERE id = 1;

-- Deleting data
DELETE FROM users WHERE id = 1;

Joins

-- INNER JOIN
SELECT posts.title, users.name
FROM posts
INNER JOIN users ON posts.author_id = users.id;

-- LEFT JOIN (includes users with no posts)
SELECT users.name, COUNT(posts.id) as post_count
FROM users
LEFT JOIN posts ON users.id = posts.author_id
GROUP BY users.name;

Assignment