I need Help answering these SQL Server Database Questions

Here is the Codes:

CREATE DATABASE SQLTestDB GO

USE SQLTestDB GO

CREATE TABLE Diagnosis ( DiagnosisID INT NOT NULL, DiagnosisDescription NVARCHAR(100) NOT NULL, PRIMARY KEY (DiagnosisID) );

INSERT INTO dbo.Diagnosis ( DiagnosisID , DiagnosisDescription ) VALUES ( 1 , 'Test Diagnosis 1' );

INSERT INTO dbo.Diagnosis ( DiagnosisID , DiagnosisDescription ) VALUES ( 2 , 'Test Diagnosis 2' );

INSERT INTO dbo.Diagnosis ( DiagnosisID , DiagnosisDescription ) VALUES ( 3 , 'Test Diagnosis 3' );

INSERT INTO dbo.Diagnosis ( DiagnosisID , DiagnosisDescription ) VALUES ( 4 , 'Test Diagnosis 4' );

INSERT INTO dbo.Diagnosis ( DiagnosisID , DiagnosisDescription ) VALUES ( 5 , 'Test Diagnosis 5' );

CREATE TABLE DiagnosisCategory ( DiagnosisCategoryID INT NOT NULL, CategoryDescription NVARCHAR(100) NOT NULL, CategoryScore INT NOT NULL, PRIMARY KEY (DiagnosisCategoryID) );

INSERT INTO dbo.DiagnosisCategory ( DiagnosisCategoryID , CategoryDescription , CategoryScore ) VALUES ( 1 , 'Category A', 10);

INSERT INTO dbo.DiagnosisCategory ( DiagnosisCategoryID , CategoryDescription , CategoryScore ) VALUES ( 2 , 'Category B', 20);

INSERT INTO dbo.DiagnosisCategory ( DiagnosisCategoryID , CategoryDescription , CategoryScore ) VALUES ( 3 , 'Category C', 30);

CREATE TABLE DiagnosisCategoryMap ( DiagnosisCategoryID INT NOT NULL, DiagnosisID INT NOT NULL );

INSERT INTO dbo.DiagnosisCategoryMap ( DiagnosisCategoryID ,DiagnosisID ) VALUES ( 1 , 1 );

INSERT INTO dbo.DiagnosisCategoryMap ( DiagnosisCategoryID ,DiagnosisID ) VALUES ( 2 , 2 );

INSERT INTO dbo.DiagnosisCategoryMap ( DiagnosisCategoryID ,DiagnosisID ) VALUES ( 3 , 3 );

INSERT INTO dbo.DiagnosisCategoryMap ( DiagnosisCategoryID ,DiagnosisID ) VALUES ( 3 , 4 );

CREATE TABLE dbo.Member ( MemberID INT NOT NULL, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, PRIMARY KEY (MemberID) );

INSERT INTO dbo.Member ( MemberID, FirstName, LastName ) VALUES ( 1, 'John', 'Smith');

INSERT INTO dbo.Member ( MemberID, FirstName, LastName ) VALUES ( 2, 'Jack', 'Smith');

INSERT INTO dbo.Member ( MemberID, FirstName, LastName ) VALUES ( 3, 'Will', 'Smyth');

CREATE TABLE MemberDiagnosis ( MemberID INT NOT NULL, DiagnosisID INT NOT NULL );

INSERT INTO dbo.MemberDiagnosis ( MemberID, DiagnosisID ) VALUES ( 1, 2);

INSERT INTO dbo.MemberDiagnosis ( MemberID, DiagnosisID ) VALUES ( 1, 4);

INSERT INTO dbo.MemberDiagnosis ( MemberID, DiagnosisID ) VALUES ( 3, 3);

INSERT INTO dbo.MemberDiagnosis ( MemberID, DiagnosisID ) VALUES ( 3, 4);

Here are the Questions:

Instructions: 1) Rename SQLTestDBSetup.sql.txt to SQLTestDBSetup.sql. 2) Please run SQLTestDBSetup.sql to setup the test database. 3) Write a query to return a list of all members and all of their corresponding categories: a. Please include the following fields: Member ID, First Name, Last Name, Most Severe Diagnosis ID, Most Severe Diagnosis Description, Category ID, Category Description, Category Score and Is Most Severe Category. b. Most Severe Diagnosis ID and Description should be the diagnosis with the lowest Diagnosis ID present for each Members Category. c. Is Most Severe Category 0 or 1 should be set to 1 for the lowest Category ID present for each Member. Severity is not based on category score. Please also set this to 1 for Members without corresponding Categories. d. Members without Diagnosis or Categories should be included in the result set. 4) Extra Credit: Write a C# Console Application that prompts for a Member ID and displays the results of query #3 for that Member.