LSBU Developer Technical Test

This is a practical interview test that will ask you to write some TSQL, C# and HTML

Please email your answers to us. When submitting yours answers please ensure that all code provided is in a word document attached to the email.

Please submit the answers to this test by your advised date

This test uses the WideWorldImporters database and a copy of this can be download from GitHub

Question 1

Given the starting query 

                Use WideWorldImporters
                SET NOCOUNT ON
                SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

                SELECT
                cust.CustomerID,
                cust.CustomerName
                FROM Website.Customers cust

Extend this query so it will include the Category Name and the City for the customer and ordered by the city.

Provide a copy of this query


Question 2

Extend the query you have written to return all the current columns and include a count of the number of customers in each city.

Provide a copy of this query


Question 3

Using the Warehouse.StockItems and Warehouse.StockItemHoldings tables write a query that will return

  • StockItemId
  • StockItemName
  • TargetStockLevel
  • LastStockTakeQuantity
  • StockLevelWarning
  • RunningStockValueExcVAT

Field Definitions
  • StockLevelWarning - This should show a warning of Under 40% or Under 20% where the LastStockTakeQuantity is below that percentage of the TargetStockLevel
  • RunningStockValueExcVAT - This should show the running total of the unit price of the current total items held in stock, excluding VAT

Where the LastStockTakeQuantity is below the TargetStockLevel
Order By StockItemI

Provide a copy of this query


Question 4

Using your query in Question 3 create a stored procedure that will take a parameter 

            DECLARE @StockLevel INT 
            SET @StockLevel = 10

Use this parameter instead of the TargetStockLevel

Provide a copy of this Procedure


Question 5

Create a C# Console App that will call your stored procedure with a stock level of 100 and display the results in the format of Console format output screen

(the data doesn’t need to match)

Provide a copy of this console app code in the word document


Question 6

Write a HTML page that will display the data like HTML Format Output screen

You can hardcode the data in the html, and add any styling you want to

Provide a copy of this html in the word document


Question 7

Create a C# Console App that will post to a webservice

Within this app create a person class with the string attributes Firstname, Lastname, FavouriteProgrammingLanguage

Create an instance of this class and populate with your details.

Post a JSON string of this instance to the webservice at https://lsbuinterviewtest.azurewebsites.net/DevOpsTest

Provide a copy of this console app code in the word document