0

SQL Server Developer Assessment Test and Solution

Emeka OkekeDec 28, 2019 | edited Dec 28, 2019 - by @sqldibia

Here is a SQL Server Developer Assessment test I took from Cornerstone Automation Systems for a SQL Server database developer position last year. I thought that I should share it along with my solution/answers.

There might be some technical recruiters looking for ideas on how to set up SQL Server developer assessment test. This may also be useful to applicants or potential applicants seeking for SQL Server developer positions.

The Test

So let's get right on to it. In this exercise, we will be setting up a simple database application for a pharmacy. We will be updating and creating tables to store Patients, Pharmaceutical Drugs, and Prescriptions for Patients.

We will then develop codes to populate these tables with test data. NOTE: Remember to save all scripts created during this exercise as you will be required to return these to the recruiter.

Preparation

  1. Using Microsoft SQL Server Express edition create a blank SQL Database named DeveloperAssessment.
  2. Run the script provided with the assessment to create the sample tables.
  3. Add a Primary Key and a DateUpdated field to the following tables created in step 2
    1. Drug
    2. Patient
    3. Prescription
  4. Add a Foreign Key relationship on the Prescription table to Drug and Patient tables.
  5. INSERT 100 Records into the Drug table.
  6. You do not need to use real drug names Drug1, Drug2, etc is perfectly acceptable.
  7. Provide scripts or a brief explanation in how you accomplished this.
  8. Avoid writing out 100 INSERT statements
  9. INSERT 100 Records into the Patient table.
    1. You do not need to use real patient names Patient1, Patient2, etc is perfectly acceptable.
    2. Provide scripts or a brief explanation in how you accomplished this.
    3. Avoid writing out 100 INSERT statements
  10. INSERT 100 Records into the Prescription table.
    1. Create one record per patient
    2. Please use 1 Drug for each patient (No Repeats)
    3. Enter an initial 0 in the Quantity column
    4. Avoid writing out 100 INSERT statements
  11. Write a script to UPDATE the Quantity column in the Prescription table. UPDATE records with odd primary key values to Quantity=30 and UPDATE records with even primary key values to Quantity=60
  12. Create a one to many Child Table to Prescription. You can choose the name. This table must include the following
    1. PrimaryKey
    2. ForeginKey to Prescription
    3. BottleNumber
    4. FillQuantity
    5. DateUpdated
  13. Create a stored procedure that will process the Prescription table. Each Prescription record will need 1 or more entries in the table created in step 7. If the Prescription Quantity is 30 create one record with a quantity of 30 (1 bottle of 30). If the Prescription quantity is 60 create 2 records with a quantity of 30 each (2 bottles of 30). Be sure to set the bottle number accordingly. Once the procedure has executed you must be able to execute the stored procedure repeatedly but it should NOT reprocess the record set.
  14. Create a database View that joins the Patient, Drug, and Prescription table. This view must have the following columns.
    1. PrescriptionId
    2. PatientName
    3. DrugName
    4. FillQuantity
    5. BottleNumber
  15. Take a backup of the DeveloperAssessment database. Zip up the database backup and all scripts created during this exercise and email back this zip file to the recruiter.

The Solution/Answers

We will start with creating the sample database as follows:



CREATE DATABASE [DeveloperAssessment]
GO

Next we create the tables in the newly created sample database as required by this SQL Server Developer Assessment excercise:



USE [DeveloperAssessment]
GO
/****** Object:  Table [dbo].[Drug]    Script Date: 4/15/2017 12:15:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Drug](
	[DrugName] [varchar](50) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Patient]    Script Date: 4/15/2017 12:15:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Patient](
	[PatientName] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Prescription]    Script Date: 4/15/2017 12:15:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Prescription](
	[Quantity] [int] NOT NULL
) ON [PRIMARY]

GO

Next we add the columns and constraints as required by this excercise:



ALTER TABLE [dbo].[Drug] ADD [DateUpdated] DATETIME
ALTER TABLE [dbo].[Patient] ADD [DateUpdated] DATETIME
ALTER TABLE [dbo].[Prescription] ADD [DateUpdated] DATETIME
ALTER TABLE [dbo].[Drug] ADD [DrugID] INT IDENTITY(1,1) PRIMARY KEY
ALTER TABLE [dbo].[Patient] ADD [PatientID] INT IDENTITY(1,1) PRIMARY KEY
ALTER TABLE [dbo].[Prescription] ADD [PrescriptionID] INT IDENTITY(1,1) PRIMARY KEY
ALTER TABLE [dbo].[Prescription] ADD [DrugID] INT
ALTER TABLE [dbo].[Prescription] ADD [PatientID] INT
GO
ALTER TABLE [dbo].[Prescription] ADD CONSTRAINT FK_DrugID_Prescription FOREIGN KEY ([DrugID]) REFERENCES [dbo].[Drug]([DrugID])
ALTER TABLE [dbo].[Prescription] ADD CONSTRAINT FK_PatientID_Prescription FOREIGN KEY ([PatientID]) REFERENCES [dbo].[Patient]([PatientID])
GO

Here we now populate the tables while ensuring no constraint violation as stated in this test:



DECLARE @CNT INT=1,@PID INT,@DID INT,@STR VARCHAR(4)

WHILE @CNT<=100
    BEGIN	    		
		SET @STR=CAST(@CNT AS VARCHAR(4))
	    INSERT [dbo].[Patient]([PatientName],[DateUpdated])
		SELECT 'Patient' +  @STR,GETDATE()
		SET @PID=SCOPE_IDENTITY()

		INSERT [dbo].[Drug]([DrugName],[DateUpdated])
		SELECT 'Drug' + @STR,GETDATE()
		SET @DID=SCOPE_IDENTITY()

		INSERT [dbo].[Prescription]([Quantity],[DrugID],[PatientID],[DateUpdated])
		SELECT 0,@DID,@PID,GETDATE()

		SET @CNT+=1
	END
GO

Next we create the odd numbered and even numbered primary key as required by this SQL Server Developer Assessment test using TSQL modulo % operator:



--Odd numbered primary keys
UPDATE [dbo].[Prescription]
SET [Quantity]=30,[DateUpdated]=GETDATE()
WHERE [PrescriptionID]%2!=0

--Even numbered primary keys
UPDATE [dbo].[Prescription]
SET [Quantity]=60,[DateUpdated]=GETDATE()
WHERE [PrescriptionID]%2=0
GO

Next we create the cough syrup table. Please note that the questions are answered in the order they are listed here:



CREATE TABLE [dbo].[CoughSyrup](CoughSyrupID INT IDENTITY(1,1) PRIMARY KEY,
[PrescriptionID] INT CONSTRAINT FK_PrescriptionID_CoughSyrupID FOREIGN KEY REFERENCES [dbo].[Prescription]([PrescriptionID]),
[BottleNumber] INT,[FillQuantity] INT,[DateUpdated] DATETIME)
GO

Next we create the drug prescription processing stored procedure as follows:



CREATE PROC [dbo].[processPrescription]
/************************************************************************************
       Author               : Ricky Okoli
       Create Date          : 07/13/2019
        Reviewed by         : N/A
       Description          : Each Prescription record will need 1 or more entries in the table created in step 7. If the Prescription Quantity is 30 create 
	                      one record with a quantity of 30 (1 bottle of 30). If the Prescription quantity is 60 create 2 records with a quantity of 30 each 
			      (2 bottles of 30). Be sure to set the bottle number.Once the procedure has executed you must be able to execute the stored procedure 
			      repeatedly but it should NOT reprocess the record set
       Dependency           : 
       Returns              : 
       Modification History: 
       Updated By        Updated On      Comments
       ------        ----------      --------      
		  
************************************************************************************/
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY

		DECLARE @Prescription30 TABLE([PrescriptionID] INT,[Quantity] INT)
		DECLARE @Prescription60 TABLE([PrescriptionID] INT,[Quantity] INT)

		INSERT @Prescription30([PrescriptionID],[Quantity])
		SELECT P.[PrescriptionID],[Quantity]  FROM [dbo].[Prescription] P
		LEFT JOIN [dbo].[CoughSyrup] C ON P.[PrescriptionID]=C.[PrescriptionID]
		WHERE C.[PrescriptionID] IS NULL AND P.[Quantity]=30

		INSERT @Prescription60([PrescriptionID],[Quantity])
		SELECT P.[PrescriptionID],30  FROM [dbo].[Prescription] P
		LEFT JOIN [dbo].[CoughSyrup] C ON P.[PrescriptionID]=C.[PrescriptionID]
		WHERE C.[PrescriptionID] IS NULL AND P.[Quantity]=60


		INSERT [dbo].[CoughSyrup]([PrescriptionID],[FillQuantity],[BottleNumber],[DateUpdated])
		SELECT [PrescriptionID],[Quantity],RIGHT(CONVERT(BIGINT,CONVERT(VARBINARY,NEWID(),1),1),4),GETDATE()		
		FROM @Prescription30

		INSERT [dbo].[CoughSyrup]([PrescriptionID],[FillQuantity],[BottleNumber],[DateUpdated])
		SELECT [PrescriptionID],[Quantity],RIGHT(CONVERT(BIGINT,CONVERT(VARBINARY,NEWID(),1),1),4),GETDATE()		
		FROM @Prescription60

		INSERT [dbo].[CoughSyrup]([PrescriptionID],[FillQuantity],[BottleNumber],[DateUpdated])
		SELECT [PrescriptionID],[Quantity],RIGHT(CONVERT(BIGINT,CONVERT(VARBINARY,NEWID(),1),1),4),GETDATE()		
		FROM @Prescription60
	
    END TRY
    BEGIN CATCH
        DECLARE @error INT, @message VARCHAR(4000), @xstate INT;
        SELECT @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();

        RAISERROR ('processPrescription: %d: %s', 16, 1, @error, @message) ;
    END CATCH   
END
GO

Finally we create the view that will get the drug prescription details as required by this excercise:



CREATE VIEW [dbo].[getPrescriptiondetails]
/************************************************************************************
       Author               : Ricky Okoli
       Create Date          : 07/13/2019
        Reviewed by         : N/A
       Description          : 
       Dependency           : 
	   Returns              : PrescriptionId,PatientName,DrugName,FillQuantity,BottleNumber
       Modification History: 
       Updated By        Updated On      Comments
       ------        ----------      --------      
		  
************************************************************************************/
AS
SELECT A.[PrescriptionID],B.[PatientName],C.[DrugName],A.[Quantity] AS FillQuantity,D.BottleNumber
FROM [dbo].[Prescription] A INNER JOIN [dbo].[Patient] B ON A.PatientID=B.PatientID
INNER JOIN [dbo].[Drug] C ON A.DrugID=C.DrugID
LEFT JOIN [dbo].[CoughSyrup] D ON A.PrescriptionID=D.PrescriptionID

For the full SQL Server Assessment Test and Solution, download here.

Emeka Okeke@sqldibia+ Follow
0
Emeka Okeke+ Follow
locationPennsburg, PennsylvaniajoinedDec 11, 2019

More from @sqldibia