Create schema and insert dummy data into Stack Overflow clone

StackOverflowCloneInsertData.sql

-- Create StackOverflow-like Database
CREATE DATABASE StackOverflowClone

GO

USE StackOverflowClone

GO

-- Users table
CREATE TABLE Users (
    UserId INT PRIMARY KEY IDENTITY(1,1),
    UserName NVARCHAR(100),
    Email NVARCHAR(255),
    DateJoined DATETIME DEFAULT GETDATE(),
    Reputation INT DEFAULT 0
);

-- Categories table
CREATE TABLE Categories (
    CategoryId INT PRIMARY KEY IDENTITY(1,1),
    CategoryName NVARCHAR(255)
);

-- Questions table
CREATE TABLE Questions (
    QuestionId INT PRIMARY KEY IDENTITY(1,1),
    Title NVARCHAR(255),
    Body TEXT,
    PostedBy INT FOREIGN KEY REFERENCES Users(UserId),
    CategoryId INT FOREIGN KEY REFERENCES Categories(CategoryId),
    DatePosted DATETIME DEFAULT GETDATE()
);

-- Answers table
CREATE TABLE Answers (
    AnswerId INT PRIMARY KEY IDENTITY(1,1),
    Body TEXT,
    PostedBy INT FOREIGN KEY REFERENCES Users(UserId),
    QuestionId INT FOREIGN KEY REFERENCES Questions(QuestionId),
    DatePosted DATETIME DEFAULT GETDATE(),
    IsAccepted BIT DEFAULT 0
);

-- Comments table
CREATE TABLE Comments (
    CommentId INT PRIMARY KEY IDENTITY(1,1),
    Body NVARCHAR(1000),
    PostedBy INT FOREIGN KEY REFERENCES Users(UserId),
    QuestionId INT NULL FOREIGN KEY REFERENCES Questions(QuestionId),
    AnswerId INT NULL FOREIGN KEY REFERENCES Answers(AnswerId),
    DatePosted DATETIME DEFAULT GETDATE()
);

-- Votes table
CREATE TABLE Votes (
    VoteId INT PRIMARY KEY IDENTITY(1,1),
    Type NVARCHAR(50), -- Up or Down
    UserId INT FOREIGN KEY REFERENCES Users(UserId),
    QuestionId INT NULL FOREIGN KEY REFERENCES Questions(QuestionId),
    AnswerId INT NULL FOREIGN KEY REFERENCES Answers(AnswerId),
    DateVoted DATETIME DEFAULT GETDATE()
);

-- Tags table
CREATE TABLE Tags (
    TagId INT PRIMARY KEY IDENTITY(1,1),
    TagName NVARCHAR(100)
);

-- QuestionTags table
CREATE TABLE QuestionTags (
    QuestionId INT FOREIGN KEY REFERENCES Questions(QuestionId),
    TagId INT FOREIGN KEY REFERENCES Tags(TagId),
    PRIMARY KEY (QuestionId, TagId)
);

-- UserBadges table
CREATE TABLE Badges (
    BadgeId INT PRIMARY KEY IDENTITY(1,1),
    BadgeName NVARCHAR(100),
    Description NVARCHAR(1000)
);

-- UserBadges association table
CREATE TABLE UserBadges (
    UserId INT FOREIGN KEY REFERENCES Users(UserId),
    BadgeId INT FOREIGN KEY REFERENCES Badges(BadgeId),
    DateAwarded DATETIME DEFAULT GETDATE(),
    PRIMARY KEY (UserId, BadgeId)
);

GO 

-- Users
DECLARE @i INT = 0;
WHILE @i < 20
BEGIN
    INSERT INTO Users (UserName, Email) VALUES ('User' + CAST(@i AS NVARCHAR), 'user' + CAST(@i AS NVARCHAR) + '@example.com');
    SET @i = @i + 1;
END

-- Categories
INSERT INTO Categories (CategoryName) VALUES ('Programming'), ('Web Development'), ('Databases'), ('Data Science');

-- Questions
DECLARE @j INT = 0;
WHILE @j < 200
BEGIN
    INSERT INTO Questions (Title, Body, PostedBy, CategoryId) 
    VALUES ('How to fix issue ' + CAST(@j AS NVARCHAR), 'I am having trouble with...', 1 + (@j % 20), 1 + (@j % 4));
    SET @j = @j + 1;
END

-- Answers
DECLARE @k INT = 0;
WHILE @k < 600
BEGIN
    INSERT INTO Answers (Body, PostedBy, QuestionId) 
    VALUES ('You should try this solution...', 1 + (@k % 20), 1 + (@k % 200));
    SET @k = @k + 1;
END

-- Comments
DECLARE @l INT = 0;
WHILE @l < 1000
BEGIN
    INSERT INTO Comments (Body, PostedBy, QuestionId) 
    VALUES ('I agree with this', 1 + (@l % 20), 1 + (@l % 200));
    SET @l = @l + 1;
END

-- Votes
DECLARE @m INT = 0;
WHILE @m < 2000
BEGIN
    INSERT INTO Votes (Type, UserId, QuestionId) 
    VALUES (CASE WHEN (@m % 2) = 0 THEN 'Up' ELSE 'Down' END, 1 + (@m % 20), 1 + (@m % 200));
    SET @m = @m + 1;
END

-- Tags
INSERT INTO Tags (TagName) VALUES ('JavaScript'), ('Python'), ('SQL'), ('HTML'), ('CSS'), ('React'), ('Angular'), ('C#'), ('Java'), ('PHP');

-- QuestionTags
DECLARE @n INT = 0;
WHILE @n < 200
BEGIN
    INSERT INTO QuestionTags (QuestionId, TagId) 
    VALUES (1 + (@n % 200), 1 + (@n % 10));
    SET @n = @n + 1;
END

-- Badges
INSERT INTO Badges (BadgeName, Description) VALUES ('Beginner', 'For new members'), ('Expert', 'For experts in a topic'), ('Moderator', 'For site moderators'), ('Enthusiast', 'For active members');

-- UserBadges
DECLARE @o INT = 0;
DECLARE @userId INT = 1;
DECLARE @badgeId INT = 1;

WHILE @o < 40
BEGIN
    INSERT INTO UserBadges (UserId, BadgeId) 
    VALUES (@userId, @badgeId);
    
    SET @badgeId = @badgeId + 1;
    IF @badgeId > 4
    BEGIN
        SET @userId = @userId + 1;
        SET @badgeId = 1;
    END

    SET @o = @o + 1;
END