SQL Tutorial: Project - Forum Database 🎯

Welcome to the SQL Tutorial for beginners and intermediates! Today, we'll be building a Forum Database, a practical project that will help you understand SQL concepts from the ground up. 📝

What is SQL?

SQL (Structured Query Language) is a language used to communicate with databases. It allows you to create, manipulate, and query databases. In this project, we'll be using SQL to build a database for a forum website. 💡

Setting Up the Database

Before we start, let's create a new database called forum.

sql

CREATE DATABASE forum;

Now, we'll select the database we just created.

sql

USE forum;

Creating Tables

Our forum will have three main tables: users, topics, and posts.

Users Table

The users table will store user information. Let's create it.

sql

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL
);

Topics Table

The topics table will store the discussion topics.

sql

CREATE TABLE topics (
    id INT PRIMARY KEY,
    user_id INT,
    title VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Posts Table

The posts table will store the actual posts under each topic.

sql

CREATE TABLE posts (
    id INT PRIMARY KEY,
    topic_id INT,
    user_id INT,
    content TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (topic_id) REFERENCES topics(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Inserting Data

Now that our tables are set up, let's insert some data.

sql

-- Insert user data
INSERT INTO users (id, username, email, password) VALUES
(1, 'admin', 'admin@example.com', 'password123'),
(2, 'user1', 'user1@example.com', 'password456');

-- Insert topic data
INSERT INTO topics (id, user_id, title) VALUES
(1, 1, 'Welcome to our forum!');

-- Insert post data
INSERT INTO posts (id, topic_id, user_id, content) VALUES
(1, 1, 1, 'Hello, welcome to our forum!'),
(2, 1, 2, 'Nice forum, keep it up!');

Querying Data

Now, let's see how to query data from our tables.

sql

-- Select all users
SELECT * FROM users;

-- Select all topics
SELECT * FROM topics;

-- Select all posts
SELECT * FROM posts;

-- Select all posts by user1
SELECT * FROM posts WHERE user_id = 2;

Quiz

With this, you've learned the basics of creating a database and tables, inserting data, and querying data using SQL. Happy coding! 🚀