Alright, great job for coming this far. It’s time for some challenges to finalize our social media database.
Challenges overview
- Query all posts and order them based on number of likes.
- Query a users followers profiles and the posts they have liked.
- Add comments to the posts.
Here is the tables we have created so far and the inserted data.
CREATE TABLE Users
(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
Username VARCHAR(255) UNIQUE NOT NULL,
Password VARCHAR(255) NOT NULL,
CHECK (LENGTH(Password) > 5)
);
-- one to one field user and profile
CREATE TABLE Profiles
(
UserID INTEGER NOT NULL PRIMARY KEY,
Img VARCHAR(1),
Bio TEXT,
FOREIGN KEY (UserID) REFERENCES Users(ID)
);
-- one to many field user and post
CREATE TABLE Posts
(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
UserID INTEGER NOT NULL,
Title VARCHAR(255) NOT NULL,
Content TEXT NOT NULL,
FOREIGN KEY (UserID) REFERENCES Users(ID)
);
-- many to many field user likes posts
CREATE TABLE Likes
(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
PostID INTEGER,
UserID INTEGER,
FOREIGN KEY (PostID) REFERENCES Posts(ID),
FOREIGN KEY (UserID) REFERENCES Users(ID)
);
-- many to many field user follows users
CREATE TABLE Follows
(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
OwnerID INTEGER,
FollowingID INTEGER,
FOREIGN KEY (OwnerID) REFERENCES Users(ID),
FOREIGN KEY (FollowingID) REFERENCES Users(ID)
);
INSERT INTO Users (Username, Password) VALUES
('Ben', 'secret'),
('Jim', 'secret'),
('Luk', 'secret');
INSERT INTO Profiles (UserID, Img, BIO) VALUES
(1, '😎', 'I am a cool guy'), --Ben
(2, '🥳', 'I love party'), --Jim
(3, '🤠', 'I am a cowboy'); --Luk
INSERT INTO Posts (UserID, Title, Content) VALUES
(1, 'sql', 'sql content'),
(1, 'java', 'java content'),
(2, 'NLP', 'NLP Content'),
(2, 'rust', 'rust content');
INSERT INTO Likes (UserID, PostID) VALUES
(1, 1), -- Ben likes sql
(2, 1), -- Jim likes sql
(3, 1), -- Luk likes sql
(1, 2), -- Ben likes Java
(3, 3); -- Luk likes NLP
INSERT INTO Follows (OwnerID, FollowingID) VALUES
(1, 2), -- ben follow jim
(2, 1), -- jim folliw ben
(2, 3), -- jim follow luk
(3, 1), -- luk follow ben
(1, 3); -- ben follow luk
Challenge 1
- Query all posts and order them after number of likes.
- The posts with the most likes should come first.
- The tricky part is to include posts that have 0 likes.
Tip: Use a right join.
Expected result:
A table of post title, post content and number of likes.
Challenge 2
In this challenge you will get some starter code.
This code return all user profiles, Jim follows.
SELECT
followsUser.Username,
followsProfile.Img,
followsProfile.Bio
FROM Follows f
JOIN Users u ON f.OwnerID = u.ID
JOIN Users followsUser ON f.FollowingID = followsUser.ID
JOIN Profiles followsProfile ON followsProfile.UserID = followsUser.ID
WHERE u.Username = 'Jim';
Your challenge is to add on to this. So that it returns the profile and the posts the followed users have liked
.
Expected result:
A table of, username, profile image, profile bio, the user’s post with title and content.
Challenge 3
We want a feature where users can comment on posts.
Each comment is associated with one post and one user.
Share your solutions in comments. Thanks for reading and happy coding!