SQL Tutorial: Project - Social Media Database 🎯

Welcome to our SQL tutorial! Today, we're going to build a Social Media Database from scratch. By the end of this project, you'll have a solid understanding of SQL and how to use it to manage data in a real-world application.

Getting Started 📝

Before we dive in, let's make sure you have everything you need:

A SQL client (e.g., MySQL Workbench, pgAdmin, or SQLite Database Browser)
A database management system installed (e.g., MySQL, PostgreSQL, or SQLite)

Creating the Database 💡

First things first, let's create our database.

sql

CREATE DATABASE social_media;

Now, select the database we just created:

sql

USE social_media;

Creating Tables 💡

Now that we have our database, it's time to create some tables. In our Social Media Database, we'll have tables for Users, Posts, Comments, and Likes.

Users Table

sql

CREATE TABLE Users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Posts Table

sql

CREATE TABLE Posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES Users(id)
);

Comments Table

sql

CREATE TABLE Comments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  post_id INT NOT NULL,
  user_id INT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (post_id) REFERENCES Posts(id),
  FOREIGN KEY (user_id) REFERENCES Users(id)
);

Likes Table

sql

CREATE TABLE Likes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  post_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES Users(id),
  FOREIGN KEY (post_id) REFERENCES Posts(id)
);

Quick Quiz

Question 1 of 1

What is the primary key for the Users table?

Inserting Data 💡

Now that we have our tables set up, let's insert some data into them.

sql

INSERT INTO Users (username, email, password_hash)
VALUES ('john_doe', '[john_doe@example.com](mailto:john_doe@example.com)', 'password123');

Now, let's insert a post for John Doe.

sql

INSERT INTO Posts (user_id, content)
VALUES (1, 'Hello, world!');

Quick Quiz

Question 1 of 1

What SQL command do we use to insert data into a table?

Querying Data 💡

Now that we have some data, let's see how to query it.

Fetching User Data

sql

SELECT * FROM Users WHERE id = 1;

Fetching Post Data

sql

SELECT * FROM Posts WHERE user_id = 1;

Fetching Comments

sql

SELECT * FROM Comments WHERE post_id = (SELECT id FROM Posts WHERE user_id = 1);

Counting Likes

sql

SELECT COUNT(*) FROM Likes WHERE post_id = (SELECT id FROM Posts WHERE user_id = 1);

Quick Quiz

Question 1 of 1

What SQL command do we use to fetch data from a table?

Wrapping Up 💡

Congratulations! You've built a Social Media Database from scratch. Now that you've got the basics down, you can continue to explore SQL and build more complex applications.

Remember, the key to mastering SQL is practice. Keep experimenting, and don't hesitate to ask questions if you're stuck. Happy coding! 💪