Social Media CASE STUDY using SQL

 DROP TABLE IF EXISTS users;

DROP TABLE IF EXISTS posts;


CREATE TABLE users (

    user_id INT PRIMARY KEY,

    first_name VARCHAR(50),

    last_name VARCHAR(50),

    join_date DATE

);


CREATE TABLE posts (

    post_id INT PRIMARY KEY,

    user_id INT,

    content TEXT,

    creation_time DATETIME 

);


INSERT INTO users (user_id, first_name, last_name, join_date) VALUES

(1, 'Alice', 'Smith', '2021-01-10'),

(2, 'Bob', 'Johnson', '2021-02-15'),

(3, 'Carol', 'Williams', '2021-03-20'),

(4, 'David', 'Brown', '2021-04-25'),

(5, 'Eve', 'Davis', '2021-05-30'),

(6, 'Frank', 'Miller', '2021-06-15'),

(7, 'Rob', 'David', '2021-08-15');



INSERT INTO posts (post_id, user_id, content, creation_time) VALUES

(1, 1, 'Excited to join SocialSphere!', '2021-01-11 08:30:00'),

(2, 2, 'Beautiful day for a walk!', '2021-02-16 09:45:00'),

(3, 1, 'Loving the new features!', '2021-01-12 10:00:00'),

(4, 3, 'Check out my new blog post!', '2021-03-21 14:20:00'),

(5, 2, 'Had a great time at the concert last night!', '2021-02-20 23:00:00'),

(6, 5, 'Just finished a 10k run!', '2021-05-31 07:30:00'),

(7, 6, 'Anyone up for a movie night?', '2021-06-16 19:45:00'),

(8, 4, 'Happy to be part of this community!', '2021-04-26 12:00:00');



 SELECT * from posts

  SELECT * from users

Comments

Post a Comment

Popular posts from this blog

50 Essential SQL Questions to Land Your Dream Job

 CREATE TABLE worker (     first_name VARCHAR(50),     last_name VARCHAR(50),     department VARCHAR(50),     salary DECIMAL(10, 2),     joining_date DATE ); INSERT INTO worker (first_name, last_name, department, salary, joining_date) VALUES  ('John', 'Doe', 'HR', 55000, '2020-01-15'), ('Jane', 'Smith', 'Finance', 67000, '2019-03-22'), ('Robert', 'Johnson', 'IT', 72000, '2018-06-10'), ('Emily', 'Davis', 'Marketing', 59000, '2021-07-01'), ('Michael', 'Wilson', 'Sales', 63000, '2020-09-15'), ('Sarah', 'Brown', 'HR', 56000, '2022-02-19'), ('David', 'Miller', 'Finance', 68000, '2019-10-05'), ('Laura', 'Garcia', 'IT', 75000, '2020-11-22'), ('James', 'Martinez', 'Marketing', 60000, '2021-03-18'), ('Olivia...
Read more

How to find all the customers who placed orders on three consecutive days | SQL Scenario questions

  DML Script: DROP TABLE IF EXISTS Orders CREATE TABLE Orders ( customer_name VARCHAR(255), order_date DATETIME ); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-01'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-01'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-02'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-02'); INSERT INTO Orders (customer_name, order_date) VALUES ('Charlie', '2024-04-03'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-03'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-04'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-04'); INSERT INTO Orders (customer_name, order_date) VALUES ('Charlie', '2024-04-05'); INSER...
Read more

ACCENTURE SQL INTERVIEW QUESTION | Change the ProductIDs

 DROP TABLE IF EXISTS Products; CREATE TABLE Products (     ProductID INT PRIMARY KEY,     Product VARCHAR(255),     Category VARCHAR(100) ); INSERT INTO Products (ProductID, Product, Category) VALUES     (1, 'Laptop', 'Electronics'),     (2, 'Smartphone', 'Electronics'),     (3, 'Tablet', 'Electronics'),     (4, 'Headphones', 'Accessories'),     (5, 'Smartwatch', 'Accessories'),     (6, 'Keyboard', 'Accessories'),     (7, 'Mouse', 'Accessories'),     (8, 'Monitor', 'Accessories'),     (9, 'Printer', 'Electronics');
Read more