SQL Course: Challanges
Tags: sql, challenge
Published: 2024-07-02 10:25:44

Alright, great job for coming this far. It’s time for some challenges to finalize our social media database. 

## Challenges overview

1. Query all posts and order them based on number of likes.
2. Query a users followers profiles and the posts they have liked.
3. 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!