USE master; GO DROP DATABASE IF EXISTS Family; GO --*/ CREATE DATABASE Family; GO USE Family; GO CREATE TABLE dbo.Person ( PersonID INT IDENTITY NOT NULL, SIN CHAR(9) NOT NULL, FatherPersonID INT NULL, MotherPersonID INT NULL, FirstName NVARCHAR(60) NULL, LastName NVARCHAR(70) NULL, DateOfBirth DATE NULL, DateOfDeath DATE NULL, NetWorth MONEY, CONSTRAINT PK_Person PRIMARY KEY CLUSTERED ( PersonID ), CONSTRAINT AK_Person_SIN UNIQUE ( SIN ) ); GO SET IDENTITY_INSERT dbo.Person ON; GO INSERT INTO dbo.Person ( PersonID, SIN, FatherPersonID, MotherPersonID, FirstName, LastName, DateOfBirth, DateOfDeath, NetWorth) VALUES -- GENERATION 1 ( 1, '152240116', NULL, NULL, 'Harry', 'Martin', '1912-02-16', '1963-04-15', NULL), ( 2, '152240213', NULL, NULL, 'Marie', 'LeFleur', '1916-04-13', '1963-04-15' , NULL), ( 3, '152240321', NULL, NULL, 'Ron', 'Jones', '1917-05-21', '2002-11-30' , NULL), ( 4, '152240426', NULL, NULL, 'Sarah', 'Kim', '1917-03-26', '2006-10-09', NULL ), ( 5, '152240522', NULL, NULL, 'Fred', 'Cooper', '1915-11-22', '2000-12-11' , NULL), ( 6, '152240630', NULL, NULL, 'Audrey', 'Maclean', '1920-06-30', '2007-03-31' , NULL), ( 7, '152240721', NULL, NULL, 'Leo', 'Sanchez', '1916-08-21', '2004-02-29' , NULL), ( 8, '152240804', NULL, NULL, 'Sarah', 'Black', '1917-08-04', '2005-08-07' , NULL), ( 9, '152240912', NULL, NULL, 'Hui', 'Peng', '1919-11-12', '1971-06-15' , NULL), ( 10, '152241002', NULL, NULL, 'Rita', 'Yang', '1921-08-02', '1990-12-03' , NULL), ( 11, '152241109', NULL, NULL, 'Jerry', 'Gold', '1919-07-09', '1998-05-04' , NULL), ( 12, '152241106', NULL, NULL, 'Kate', 'Lyall', '1920-04-06', NULL , 2000000), -- GENERATION 2 ( 13, '351250142', 1, 2, 'Peter', 'Martin', '1942-01-02', NULL, 1100000 ), ( 14, '351250241', 3, 4, 'Erin', 'Jones', '1941-02-14', '1968-07-02', 1400000 ), ( 15, '351250340', 5, 6, 'Ben', 'Cooper', '1940-10-31', '2017-09-21' , NULL), ( 16, '351250444', 7, 8, 'Tina', 'Sanchez', '1944-12-25', NULL , 1300000), ( 17, '351250541', 9, 10, 'Jeff', 'Peng', '1941-09-23', '2018-02-12', 1200000 ), ( 18, '351250643', 11, 12, 'Anne', 'Gold', '1943-01-17', NULL, 1500000 ), -- GENERATION 3 ( 19, '457280107', 13, 14, 'Ari', 'Martin', '1968-07-02', NULL, 600000 ), ( 20, '457280211', 15, 16, 'Judy', 'Cooper', '1970-11-11', NULL , 600000), ( 21, '457280310', 15, 16, 'Daniel', 'Cooper', '1968-10-04', NULL, 800000 ), ( 22, '457280403', 17, 18, 'Kate', 'Peng', '1969-03-07', NULL, 300000 ), -- GENERATION 4 ( 23, '551250100', 19, 20, 'Devon', 'Martin', '1996-07-11', NULL , 50000), ( 24, '551250201', 19, 20, 'Jillian', 'Martin', '1997-09-15', NULL , 60000), ( 25, '551250399', 19, 20, 'Brie', 'Martin', '1999-01-19', NULL , 80000), ( 26, '551250499', 21, 22, 'Stan', 'Cooper', '1998-05-21', NULL, 90000 ), ( 27, '551250505', 21, 22, 'Ian', 'Cooper', '1999-08-25', NULL , 200000); SET IDENTITY_INSERT dbo.Person OFF; GO /* REQUIREMENT 1*/ CREATE FUNCTION dbo.GetPersonID ( @SIN CHAR(9) ) RETURNS INT AS BEGIN; DECLARE @PersonID INT; SELECT @PersonID = PersonID FROM Person WHERE SIN = @SIN; RETURN @PersonID; END; GO /* REQUIREMENT 2*/ CREATE OR ALTER PROCEDURE dbo.CreatePerson ( @SIN CHAR(9), @FirstName NVARCHAR(60), @LastName NVARCHAR(70), @FatherSIN CHAR(9), @MotherSIN CHAR(9), @NetWorth MONEY = 75000, @DateOfBirth DATE = NULL, @DateOfDeath DATE = NULL, @PersonID INT OUTPUT ) AS BEGIN; SET NOCOUNT ON; SET XACT_ABORT ON; BEGIN TRANSACTION; DECLARE @FatherPersonID INT = dbo.GetPersonID (@FatherSIN); DECLARE @MotherPersonID INT = dbo.GetPersonID (@MotherSIN); IF(@FatherPersonID IS NULL) BEGIN; INSERT INTO dbo.Person (SIN) VALUES (@FatherSIN); SET @FatherPersonID = SCOPE_IDENTITY(); END; IF(@MotherPersonID IS NULL) BEGIN; INSERT INTO dbo.Person (SIN) VALUES (@MotherSIN); SET @MotherPersonID = SCOPE_IDENTITY(); END; INSERT INTO dbo.Person (SIN, FatherPersonID, MotherPersonID, FirstName, LastName, DateOfBirth, DateOfDeath, NetWorth) VALUES (@SIN, @FatherPersonID, @MotherPersonID, @FirstName, @LastName, @DateOfBirth, @DateOfDeath, @NetWorth); SET @PersonID = SCOPE_IDENTITY(); COMMIT TRANSACTION; END; GO /* REQUIREMENT 3*/ DECLARE @PersonID INT; EXECUTE dbo.CreatePerson @SIN = '623456777', @FirstName = 'Garret', @LastName = 'Martin', @FatherSIN = '551250100', @MotherSIN = '634561001', @DateOfBirth = '2020-12-18', @PersonID = @PersonID OUTPUT; SELECT @PersonID; GO DECLARE @PersonID INT; EXECUTE dbo.CreatePerson @SIN = '633444677', @FirstName = 'Essam', @LastName = 'Fahmy', @FatherSIN = '787888111', @MotherSIN = '457280211', @DateOfBirth = '2002-12-19', @PersonID = @PersonID OUTPUT; SELECT @PersonID; GO /* REQUIREMENT 4*/ CREATE TYPE dbo.NetWorth FROM MONEY NULL; GO CREATE FUNCTION dbo.GetPersonNetWorth ( @NetWorth dbo.NetWorth ) RETURNS @retPerson TABLE( PersonID INT NOT NULL, SIN CHAR(9) NOT NULL, FatherPersonID INT NULL, MotherPersonID INT NULL, FirstName NVARCHAR(60) NULL, LastName NVARCHAR(70) NULL, DateOfBirth DATE NULL, DateOfDeath DATE NULL, NetWorth MONEY ) AS BEGIN IF @NetWorth >= 0 BEGIN; INSERT INTO @retPerson SELECT PersonID, SIN, FatherPersonID, MotherPersonID, FirstName, LastName, DateOfBirth, DateOfDeath, NetWorth FROM dbo.Person WHERE NetWorth > @NetWorth ORDER BY NetWorth DESC END; RETURN; END; GO DECLARE @NetWorth dbo.NetWorth = 75000; SELECT * FROM dbo.GetPersonNetWorth(@NetWorth); GO DECLARE @NetWorth dbo.NetWorth = 700000; SELECT * FROM dbo.GetPersonNetWorth(@NetWorth); GO /* REQUIREMENT 5*/ WITH PersonRank AS ( SELECT LastName, AVG(ISNULL(NetWorth, 0)) As FamilyAverage FROM Person GROUP BY LastName) SELECT FirstName, Person.LastName, FamilyAverage, RANK() OVER( ORDER BY FamilyAverage DESC ) AS RankNum FROM PersonRank ORDER BY FamilyAverage DESC;