Answers – Problem Scenarios for Practice

Please refer to the SQL practice questions here.

Answers to those problem scenarios are given below.

Answer to Problem Scenario # 1
UPDATE tbOrdersTickets SET TicketID = CONCAT(10,TicketID) FROM tbOrdersTickets;

SELECT OrderID, TicketID FROM tbOrdersTickets;

Answer to Problem Scenario # 2
SELECT * FROM tbOrdersTickets WHERE OrderID <> 1013;

Answer to Problem Scenario # 3
SELECT * FROM tbOrdersInfo WHERE DeliveredDate > DATEADD(Day,6,OrderedDate);

Answer to Problem Scenario # 4
SELECT DATEPART(wk, OrderedDate),DATENAME(dw, OrderedDate), OrderQty FROM tbOrdersInfo
WHERE DATENAME(dw, OrderedDate) = ‘Friday’;

Answer to Problem Scenario # 5
SELECT WeekOfYear, DealDay, OrderQty,
ISNULL(OrderQty-LAG(OrderQty) OVER (ORDER BY WeekOfYear),0) AS Growth
FROM(
SELECT DATEPART(wk, OrderedDate) WeekOfYear,DATENAME(dw, OrderedDate) DealDay, OrderQty
FROM tbOrdersInfo
WHERE DATENAME(dw, OrderedDate) = ‘Friday’) A;

Answer to Problem Scenario # 6
WITH CTE(Qtr, TotalSales) AS(
SELECT Qtr, MAX(TotalSales) FROM(
SELECT NTILE(4) OVER(ORDER BY YearMonth) Qtr, * FROM tbSalesSummary)
Results GROUP BY Qtr)

Answer to Problem Scenario # 7
SELECT
DATENAME(mm, PaidDate) Month,
YEAR(PaidDate) Year,
COUNT(*) TrxnCount,
SUM(PaidAmount) Amount
FROM Trxns
GROUP BY DATENAME(mm, PaidDate), Year(PaidDate), Month(PaidDate)
ORDER BY Month(PaidDate)

Answer to Problem Scenario # 8
CREATE PROCEDURE uspGetMonthlySummary @ipvMonth INT
AS
BEGIN
SELECT
DATENAME(mm, PaidDate) Month,
YEAR(PaidDate) Year,
COUNT(*) TrxnCount,
SUM(PaidAmount) Amount
FROM Trxns
WHERE MONTH(PaidDate) = @ipvMonth
GROUP BY
DATENAME(mm, PaidDate),
Year(PaidDate),
Month(PaidDate)
END;