TwitterPrototype.sql

USE [Master];

IF DB_ID('Prototype') IS NOT NULL 
	DROP DATABASE Prototype;

CREATE DATABASE Prototype;

USE Prototype;

CREATE TABLE dbo.Users
(
	userid   INT IDENTITY(1, 1) PRIMARY KEY,
	username NVARCHAR(100) NOT NULL
);

CREATE TABLE dbo.Tweets 
(
	tweetid INT IDENTITY(1, 1) PRIMARY KEY,
	[text]  NVARCHAR(140) NOT NULL,
	userid  INT			  NOT NULL
	CONSTRAINT FK_Tweets_Users FOREIGN KEY(userid)
		REFERENCES dbo.Users(userid)
);

CREATE TABLE dbo.Follows (
	followerid INT NOT NULL,
	followeeid INT NOT NULL,
	CONSTRAINT FK_Follows_FollowerId FOREIGN KEY(followerid)
		REFERENCES dbo.Users(userid),
	CONSTRAINT FK_Follows_FolloweeId FOREIGN KEY(followeeid)
		REFERENCES dbo.Users(userid),
	CONSTRAINT Pk_FollowsPk PRIMARY KEY (followerid, followeeid)
);

INSERT INTO dbo.Users
	VALUES ('Alex')
INSERT INTO dbo.Users
	VALUES ('Jon')
INSERT INTO dbo.Users
	VALUES ('Ignace')

INSERT INTO dbo.Tweets
	VALUES ('This is my second Tweet.', 1),
		   ('Noda Time is cool.', 2),
		   ('C# 6.0. Woot.', 2),
		   ('Material = Success.', 3),

-- Feature 1: Make 'Alex' follow 'Jon'
INSERT INTO dbo.Follows
	VALUES (1, 2);
-- Make 'Ignace' follow 'Jon;
INSERT INTO dbo.Follows
	VALUES (3, 2);
-- Make 'Ignace' follow 'Alex' also
INSERT INTO dbo.Follows
	VALUES (3, 1);

-- Feature 2: List followers of 'Jon'
SELECT username
FROM dbo.Follows f
JOIN dbo.Users u ON u.userid = f.followerid
WHERE followeeid = 2

-- Feature 3: Calculate follower count of 'Jon'
SELECT COUNT(*) As FollowerCount
FROM dbo.Follows f
JOIN dbo.Users u ON u.userid = f.followeeid
WHERE u.userid = 2

-- Feature 4: List people who 'Ignace' follows
SELECT u.username
FROM dbo.Follows f 
JOIN dbo.Users u on u.userid = f.followeeid
WHERE f.followerid = 3

-- Feature 5: Get home page list for user 'Ignace'
SELECT 
	t.[text],
	u.username
FROM dbo.Tweets t
JOIN dbo.Users u ON u.userid = t.tweetid
WHERE u.userid IN 
	(SELECT u.userid
	 FROM dbo.Follows f 
	 JOIN dbo.Users u on u.userid = f.followeeid
	 WHERE f.followerid = 3
	 UNION
	 SELECT 3)