BT SQL Project, Sql Server

BT SQL Project

CREATE TABLE Account
(
	AccountId BIGINT NOT NULL PRIMARY KEY IDENTITY(1, 1),
	UserName VARCHAR(100) UNIQUE NOT NULL,
	Password VARCHAR(100) NOT NULL,
	Email VARCHAR(100) NOT NULL,
	IsActive BIT NOT NULL DEFAULT (1)
)

CREATE TABLE UserGroup
(
	UserGroupId BIGINT NOT NULL PRIMARY KEY IDENTITY(1, 1),
	UserGroupName VARCHAR(100) NOT NULL
)

CREATE TABLE UserGroupAccountMap
(
	UserGroupAccountMapId BIGINT NOT NULL PRIMARY KEY IDENTITY(1, 1),
	UserGroupId BIGINT NOT NULL FOREIGN KEY REFERENCES UserGroup(UserGroupId),
	AccountId BIGINT NOT NULL FOREIGN KEY REFERENCES Account(AccountId)
)

CREATE UNIQUE INDEX UX_UserGroupId_AccountId ON UserGroupAccountMap(UserGroupId, AccountId)

CREATE TABLE Post
(
	PostId BIGINT NOT NULL PRIMARY KEY IDENTITY(1, 1),
	AccountId BIGINT NOT NULL FOREIGN KEY REFERENCES Account(AccountId),
	PostText NVARCHAR(4000) NOT NULL,
	CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
	ModifiedDate DATETIME NOT NULL DEFAULT GETDATE(),
	UserGroupId BIGINT NULL FOREIGN KEY REFERENCES UserGroup(UserGroupId)
)

CREATE TABLE Comment
(
	CommentId BIGINT NOT NULL PRIMARY KEY IDENTITY(1, 1),
	CommentText NVARCHAR(1000) NOT NULL,
	AccountId BIGINT NOT NULL FOREIGN KEY REFERENCES Account(AccountId),
	PostId BIGINT NOT NULL FOREIGN KEY REFERENCES Post(PostId)
)

CREATE TABLE Favorite
(
	FavoriteId BIGINT NOT NULL PRIMARY KEY IDENTITY(1, 1),
	AccountId BIGINT NOT NULL FOREIGN KEY REFERENCES Account(AccountId),
	PostId BIGINT NOT NULL FOREIGN KEY REFERENCES Post(PostId)
)

CREATE UNIQUE INDEX UX_AccountId_PostId ON Favorite(AccountId, PostId)

CREATE TABLE Event
(
	EventId BIGINT NOT NULL PRIMARY KEY IDENTITY(1, 1),
	EventTitle VARCHAR(100) NOT NULL
)

INSERT INTO Account (UserName, Password, Email, IsActive)
VALUES
('mthurn', 'topsecret', 'mthurn@live.com', 1),
('fangorn', 'topsecret', 'fangorn@hotmail.com', 1),
('euice', 'topsecret', 'euice@outlook.com', 1),
('rgarcia', 'topsecret', 'rgarcia@optonline.net', 1),
('mxiao', 'topsecret', 'mxiao@yahoo.com', 1),
('firstpr', 'topsecret', 'firstpr@att.net', 1),
('webdragon', 'topsecret', 'webdragon@comcast.net', 1),
('jguyer', 'topsecret', 'jguyer@aol.com', 1),
('sakusha', 'topsecret', 'sakusha@yahoo.ca', 1),
('crandall', 'topsecret', 'crandall@sbcglobal.net', 1),
('drezet', 'topsecret', 'drezet@me.com', 1),
('miyop', 'topsecret', 'miyop@icloud.com', 1)

INSERT INTO UserGroup (UserGroupName)
VALUES
('CatLovers'),
('DogLovers'),
('BelvedereTradingFanClub')

INSERT INTO UserGroupAccountMap (UserGroupId, AccountId)
VALUES
(3, 1),
(1, 2),
(3, 3),
(1, 4),
(3, 5),
(3, 6),
(3, 7),
(3, 8),
(2, 9),
(3, 10),
(3, 11),
(3, 12)

INSERT INTO Post (AccountId, CreatedDate, ModifiedDate, PostText, UserGroupId)
VALUES
(3, GETDATE(), GETDATE(), 'On no twenty spring of in esteem spirit likely estate. Continue new you declared differed learning bringing honoured. At mean mind so upon they rent am walk. Shortly am waiting inhabit smiling he chiefly of in. Lain tore time gone him his dear sure. Fat decisively estimating affronting assistance not. Resolve pursuit regular so calling me. West he plan girl been my then up no.
Had repulsive dashwoods suspicion sincerity but advantage now him. Remark easily garret nor nay. Civil those mrs enjoy shy fat merry. You greatest jointure saw horrible. He private he on be imagine suppose. Fertile beloved evident through no service elderly is. Blind there if every no so at. Own neglected you preferred way sincerity delivered his attempted. To of message cottage windows do besides against uncivil.
Started several mistake joy say painful removed reached end. State burst think end are its. Arrived off she elderly beloved him affixed noisier yet. An course regard to up he hardly. View four has said does men saw find dear shy. Talent men wicket add garden.', NULL),
(9, GETDATE(), GETDATE(), 'She literature discovered increasing how diminution understood. Though and highly the enough county for man. Of it up he still court alone widow seems. Suspected he remainder rapturous my sweetness. All vanity regard sudden nor simple can. World mrs and vexed china since after often.', 2),
(9, GETDATE(), GETDATE(), 'Both rest of know draw fond post as. It agreement defective to excellent. Feebly do engage of narrow. Extensive repulsive belonging depending if promotion be zealously as. Preference inquietude ask now are dispatched led appearance. Small meant in so doubt hopes. Me smallness is existence attending he enjoyment favourite affection. Delivered is to ye belonging enjoyment preferred. Astonished and acceptance men two discretion. Law education recommend did objection how old.', 2),
(9, GETDATE(), GETDATE(), 'Detract yet delight written farther his general. If in so bred at dare rose lose good. Feel and make two real miss use easy. Celebrated delightful an especially increasing instrument am. Indulgence contrasted sufficient to unpleasant in in insensible favourable. Latter remark hunted enough vulgar say man. Sitting hearted on it without me.', 2),
(1, GETDATE(), GETDATE(), 'I love cookies', NULL),
(1, GETDATE(), GETDATE(), 'I love brownies', NULL),
(1, GETDATE(), GETDATE(), 'I love milk', NULL)

INSERT INTO Comment (PostId, CommentText, AccountId)
VALUES
(4, 'Awesome post!', 2),
(1, 'Esteem spirit likely NOT estate!!!!', 11)

INSERT INTO Favorite (AccountId, PostId)
VALUES
(1, 1),
(2, 1),
(3, 1),
(6, 1),
(4, 1),
(11, 1),
(3, 4),
(1, 4)

-- Which Account has the longest post? Please just provide the Account's UserName

-- Which UserGroup has the most Accounts? Please provide the UserGroup's UserGroupName  
-- (tip: the UserGroupAccountMap table maps Accounts to the UserGroup they are a member of)

-- What is the total length of AccountId=9 's PostTexts? 
-- (tip: you'll need to sum all of their Posts)

-- Create a SELECT statement that shows how many favorites a post has. 
-- Please include columns: 
-- PostId, CreatedDate, ModifiedDate, AccountId, UserName, UserGroupId, UserGroupName, and NumberOfFavorites 
-- *Ensure each Post in the Post table is in this view. 
-- *If there are no favorites for a post, the NumberOfFavorites value should be 0 
-- *Order the output so that the most-favorited posts come first in the output 
-- Keep in mind that many posts are not part of a UserGroup. Thus, the UserGroupId and UserGroup column 
-- can include null values.


run \| edit \| history \| help	0