Here are the Questions:

Instructions: 1) Write a C# Console Application that prompts for a Member ID and displays the results of query #2 for that Member. 2) 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.

Here is the Codes:using SQL SERVER

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);