Simple Social Media Database: SQL Solution

In our previous article, we introduced a practice scenario for building a simple social media application database.

Now, let's walk through the solutions for each task, explaining the SQL commands in detail. We'll keep things simple, focusing on the basics you've just learned.

1. Creating the Users Table

Let's start by creating our Users table (we gave you this code previously too):

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50),
    Email VARCHAR(100),
    DateJoined DATE,
    IsActive BOOLEAN
);

Let's break this down:

CREATE TABLE Users: This tells SQL to make a new table named "Users".
UserID INT PRIMARY KEY: This creates a column called UserID. It's an integer (whole number) and it's our primary key. Remember, a primary key uniquely identifies each row in the table.
Username VARCHAR(50): This creates a column for usernames. VARCHAR means it can hold text, and (50) means it can be up to 50 characters long.
Email VARCHAR(100): Similar to Username, but can hold up to 100 characters.
DateJoined DATE: This column will hold the date when a user joined.
IsActive BOOLEAN: This can be TRUE or FALSE, indicating if the user's account is active.

2. Creating the Posts Table

Now, let's create the Posts table:

CREATE TABLE Posts (
    PostID INT PRIMARY KEY,
    UserID INT,
    Content VARCHAR(500),
    PostDate DATE,
    Likes INT
);

Here's what each line means:

PostID INT PRIMARY KEY: Like in the Users table, this is our unique identifier for each post.
UserID INT: This will hold the UserID of the person who made the post. It connects this table to the Users table.
Content VARCHAR(500): This will hold the text of the post, up to 500 characters.
PostDate DATE: This will store the date when the post was made.
Likes INT: This will store the number of likes as a whole number.

3. Inserting Users

Now let's add some users to our Users table:

INSERT INTO Users (UserID, Username, Email, DateJoined, IsActive)
VALUES (1, 'john_doe', 'john@example.com', '2023-01-01', TRUE);

INSERT INTO Users (UserID, Username, Email, DateJoined, IsActive)
VALUES (2, 'jane_smith', 'jane@example.com', '2023-02-15', TRUE);

Here's what we're doing:

INSERT INTO Users: This tells SQL we want to add data to the Users table.
The first set of parentheses lists the columns we're providing data for.
VALUES introduces the actual data we're inserting.
The second set of parentheses contains the values for each column, in the same order we listed the columns.

We've added two users: John Doe and Jane Smith.

4. Inserting Posts

Let's add some posts:

INSERT INTO Posts (PostID, UserID, Content, PostDate, Likes)
VALUES (1, 1, 'Hello, world!', '2023-03-01', 5);

INSERT INTO Posts (PostID, UserID, Content, PostDate, Likes)
VALUES (2, 2, 'I love SQL!', '2023-03-02', 10);

This is similar to inserting users, but now we're adding data to the Posts table. Notice how we use the UserID to connect each post to a user.

5. Retrieving Posts from a Specific User

To get all posts from John Doe (UserID 1), we use:

SELECT * FROM Posts WHERE UserID = 1;

This tells SQL:

SELECT *: Get all columns
FROM Posts: From the Posts table
WHERE UserID = 1: But only for the user with UserID 1

6. Updating Likes on a Post

Let's add a like to Jane's post:

UPDATE Posts SET Likes = 11 WHERE PostID = 2;

This says:

UPDATE Posts: We're changing data in the Posts table
SET Likes = 11: Set the number of Likes to 11
WHERE PostID = 2: For the post with PostID 2 (Jane's post)

7. Finding Recent Users

To find users who joined this year, we could use:

SELECT * FROM Users WHERE DateJoined >= '2023-01-01';

This gets all users whose DateJoined is January 1, 2023 or later.

8. Deleting a Specific Post

To delete John's post:

DELETE FROM Posts WHERE PostID = 1;

This removes the row from the Posts table where PostID is 1.

Remember, these are basic examples to help you practice SQL commands. As you continue learning, you'll discover more advanced SQL features and best practices for database design.

Keep practicing these commands. Try changing the values, writing your INSERT statements, or retrieving data differently. The more you experiment, the more comfortable you'll become with SQL!

We used the WHERE clause here only to help us delete. It is part of a set of tools to filter data in our SQL commands. In the next article, we will learn about filtering our data to grab exactly what we need.