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!
Top comments (0)