SQL Server – Problem Scenarios for Practice

There is no denying that practice is a key factor in students developing subject-matter knowledge. Really, practice is essential to maximize the impact of training.

For both beginning and experienced students, we have prepared a selection of SQL exercises that we have previously shared in this blog. The solutions are provided in a separate post.

Problem Scenario:1

Every order in a corporation has a ticket number associated with it. However, the order ID and the ticket number appear to be identical. The decision was made to keep the current ticket numbers the same while switching to a 6-digit long number for the tickets.

Hence you need to add 100,000 to the ticket Or add 10 before the current number.

For instance, ticket 1024 should be modified to 101024. Below is a list of the dataset, schema setup, and required output.

Please take the appropriate steps to amend the ticket numbers.

Dataset:

 OrderID  TicketID
1001 1024
1002 1025
1003 1026
1004 1027
1005 1028
1006 1029
1007 1030
1008 1031
1009 1032
1010 1033
1011 1034
1012 1035
1013 1036
1014 1037
1015 1038
1016 1039

Schema Build:
CREATE TABLE tbOrdersTickets(
OrderID INT,
TicketID INT
);

INSERT INTO tbOrdersTickets VALUES(1001, 1024);
INSERT INTO tbOrdersTickets VALUES(1002, 1025);
INSERT INTO tbOrdersTickets VALUES(1003, 1026);
INSERT INTO tbOrdersTickets VALUES(1004, 1027);
INSERT INTO tbOrdersTickets VALUES(1005, 1028);
INSERT INTO tbOrdersTickets VALUES(1006, 1029);
INSERT INTO tbOrdersTickets VALUES(1007, 1030);
INSERT INTO tbOrdersTickets VALUES(1008, 1031);
INSERT INTO tbOrdersTickets VALUES(1009, 1032);
INSERT INTO tbOrdersTickets VALUES(1010, 1033);
INSERT INTO tbOrdersTickets VALUES(1011, 1034);
INSERT INTO tbOrdersTickets VALUES(1012, 1035);
INSERT INTO tbOrdersTickets VALUES(1013, 1036);
INSERT INTO tbOrdersTickets VALUES(1014, 1037);
INSERT INTO tbOrdersTickets VALUES(1015, 1038);
INSERT INTO tbOrdersTickets VALUES(1016, 1039);

Required output:

 OrderID  TicketID
1001 101024
1002 101025
1003 101026
1004 101027
1005 101028
1006 101029
1007 101030
1008 101031
1009 101032
1010 101033
1011 101034
1012 101035
1013 101036
1014 101037
1015 101038
1016 101039

Problem Scenario:2

To solve the example below, use the same dataset of PS-1.
Create a report with all the details on the orders. Except for 1013, all order IDs BETWEEN 1001 TO 1016 should be retrieved.


Problem Scenario:3

A business has a rule that all orders must be delivered within six days of the order date. Since there are no default restrictions in place, the Expected Delivery Date field has NULL entries which you need to take care of while you generate the reports. You are now required to get the record of the deliveries that were in violation of the policy. This means – a report that lists orders whose delivery dates are later than expected.

Dataset:

OrderID OrderedDate ExpectedDeliveryDate DeliveredDate OrderStatus
1011 2023-01-01 2023-01-07 2023-01-04 DELIVERED
1011 2023-01-01 2023-01-07 CANCELLED
1011 2023-01-01 IN SHIPPING
1011 2023-01-01 2023-01-07 2023-01-16 DELIVERED
1011 2023-01-01 2023-01-07 2023-01-08 DELIVERED
1011 2023-01-01 2023-01-07 IN SHIPPING
1011 2023-01-01 2023-01-04 DELIVERED
1011 2023-01-01 2023-01-07 2023-01-07 DELIVERED

Build Schema:
CREATE TABLE tbOrdersInfo(
OrderID INT,
OrderedDate VARCHAR(10),
ExpectedDeliveryDate VARCHAR(10),
DeliveredDate VARCHAR(10),
OrderStatus VARCHAR(20)
);

INSERT INTO tbOrdersInfo VALUES(1011, ‘2023-01-01′,’2023-01-07′,’2023-01-04′,’DELIVERED’);
INSERT INTO tbOrdersInfo VALUES(1011, ‘2023-01-01′,’2023-01-07′,”,’CANCELLED’);
INSERT INTO tbOrdersInfo VALUES(1011, ‘2023-01-01′,”,”,’IN SHIPPING’);
INSERT INTO tbOrdersInfo VALUES(1011, ‘2023-01-01′,’2023-01-07′,’2023-01-16′,’DELIVERED’);
INSERT INTO tbOrdersInfo VALUES(1011, ‘2023-01-01′,’2023-01-07′,’2023-01-08′,’DELIVERED’);
INSERT INTO tbOrdersInfo VALUES(1011, ‘2023-01-01′,’2023-01-07′,”,’IN SHIPPING’);
INSERT INTO tbOrdersInfo VALUES(1011, ‘2023-01-01′,”,’2023-01-04′,’DELIVERED’);
INSERT INTO tbOrdersInfo VALUES(1011, ‘2023-01-01′,’2023-01-07′,’2023-01-07′,’DELIVERED’);

Problem Scenario:4

Consider that today is January 21st, 2023.

Data on sales orders from the first of the year through January 20th was given to you. Every Friday, the company offers a price reduction on certain products, and they wanted to examine how this affected sales.

You are asked to extract the information from the following data, which shows the year’s week, the day of the week, and the total quantity ordered. Note that the week starts on Sunday for this company and ends on Saturday, unlike some GCC nations.

Dataset:

SummaryID OrderedDate OrderQty OrderAmount
1 2023-01-01 6 8400
2 2023-01-02 10 11100
3 2023-01-03 13 12300
4 2023-01-04 14 16600
5 2023-01-05 9 10200
6 2023-01-06 29 22375
7 2023-01-07 7 9654
8 2023-01-08 8 9701
9 2023-01-09 3 2200
10 2023-01-10 5 3499
11 2023-01-11 11 11900
12 2023-01-12 16 14985
13 2023-01-13 42 43201
14 2023-01-14 15 14000
15 2023-01-15 18 15000
16 2023-01-16 9 10255
17 2023-01-17 7 9654
18 2023-01-18 11 11920
19 2023-01-19 13 12400
20 2023-01-20 45 45989

Build Schema:
CREATE TABLE tbOrdersInfo(
SummaryID INT,
OrderedDate VARCHAR(10),
OrderQty INT,
OrderAmount DECIMAL(9,2)
);

INSERT INTO tbOrdersInfo VALUES (1, ‘2023-01-01’, 6, 8400);
INSERT INTO tbOrdersInfo VALUES (2, ‘2023-01-02’, 10, 11100);
INSERT INTO tbOrdersInfo VALUES (3, ‘2023-01-03’, 13, 12300);
INSERT INTO tbOrdersInfo VALUES (4, ‘2023-01-04’, 14, 16600);
INSERT INTO tbOrdersInfo VALUES (5, ‘2023-01-05’, 9, 10200);
INSERT INTO tbOrdersInfo VALUES (6, ‘2023-01-06’, 29, 22375);
INSERT INTO tbOrdersInfo VALUES (7, ‘2023-01-07’, 7, 9654);
INSERT INTO tbOrdersInfo VALUES (8, ‘2023-01-08’, 8, 9701);
INSERT INTO tbOrdersInfo VALUES (9, ‘2023-01-09’, 3, 2200);
INSERT INTO tbOrdersInfo VALUES (10, ‘2023-01-10’, 5, 3499);
INSERT INTO tbOrdersInfo VALUES (11, ‘2023-01-11’, 11, 11900);
INSERT INTO tbOrdersInfo VALUES (12, ‘2023-01-12’, 16, 14985);
INSERT INTO tbOrdersInfo VALUES (13, ‘2023-01-13’, 42, 43201);
INSERT INTO tbOrdersInfo VALUES (14, ‘2023-01-14’, 15, 14000);
INSERT INTO tbOrdersInfo VALUES (15, ‘2023-01-15’, 18, 15000);
INSERT INTO tbOrdersInfo VALUES (16, ‘2023-01-16’, 9, 10255);
INSERT INTO tbOrdersInfo VALUES (17, ‘2023-01-17’, 7, 9654);
INSERT INTO tbOrdersInfo VALUES (18, ‘2023-01-18’, 11, 11920);
INSERT INTO tbOrdersInfo VALUES (19, ‘2023-01-19’, 13, 12400);
INSERT INTO tbOrdersInfo VALUES (20, ‘2023-01-20’, 45, 45989);

Problem Scenario:5

Consider the same set of information as was previously mentioned. One more request is made to create a report with the same output as before with an additional column showing sales increase. Simply take the order quantity from each Friday to calculate the growth. The growth should demonstrate the difference from Friday to Friday.

Dataset:

SummaryID OrderedDate OrderQty OrderAmount
1 2023-01-01 6 8400
2 2023-01-02 10 11100
3 2023-01-03 13 12300
4 2023-01-04 14 16600
5 2023-01-05 9 10200
6 2023-01-06 29 22375
7 2023-01-07 7 9654
8 2023-01-08 8 9701
9 2023-01-09 3 2200
10 2023-01-10 5 3499
11 2023-01-11 11 11900
12 2023-01-12 16 14985
13 2023-01-13 42 43201
14 2023-01-14 15 14000
15 2023-01-15 18 15000
16 2023-01-16 9 10255
17 2023-01-17 7 9654
18 2023-01-18 11 11920
19 2023-01-19 13 12400
20 2023-01-20 45 45989

Build Schema:
CREATE TABLE tbOrdersInfo(
OrderSummaryID INT,
OrderedDate VARCHAR(10),
OrderQty INT,
OrderAmount DECIMAL(9,2)
);

INSERT INTO tbOrdersInfo VALUES (1, ‘2023-01-01’, 6, 8400);
INSERT INTO tbOrdersInfo VALUES (2, ‘2023-01-02’, 10, 11100);
INSERT INTO tbOrdersInfo VALUES (3, ‘2023-01-03’, 13, 12300);
INSERT INTO tbOrdersInfo VALUES (4, ‘2023-01-04’, 14, 16600);
INSERT INTO tbOrdersInfo VALUES (5, ‘2023-01-05’, 9, 10200);
INSERT INTO tbOrdersInfo VALUES (6, ‘2023-01-06’, 29, 22375);
INSERT INTO tbOrdersInfo VALUES (7, ‘2023-01-07’, 7, 9654);
INSERT INTO tbOrdersInfo VALUES (8, ‘2023-01-08’, 8, 9701);
INSERT INTO tbOrdersInfo VALUES (9, ‘2023-01-09’, 3, 2200);
INSERT INTO tbOrdersInfo VALUES (10, ‘2023-01-10’, 5, 3499);
INSERT INTO tbOrdersInfo VALUES (11, ‘2023-01-11’, 11, 11900);
INSERT INTO tbOrdersInfo VALUES (12, ‘2023-01-12’, 16, 14985);
INSERT INTO tbOrdersInfo VALUES (13, ‘2023-01-13’, 42, 43201);
INSERT INTO tbOrdersInfo VALUES (14, ‘2023-01-14’, 15, 14000);
INSERT INTO tbOrdersInfo VALUES (15, ‘2023-01-15’, 18, 15000);
INSERT INTO tbOrdersInfo VALUES (16, ‘2023-01-16’, 9, 10255);
INSERT INTO tbOrdersInfo VALUES (17, ‘2023-01-17’, 7, 9654);
INSERT INTO tbOrdersInfo VALUES (18, ‘2023-01-18’, 11, 11920);
INSERT INTO tbOrdersInfo VALUES (19, ‘2023-01-19’, 13, 12400);
INSERT INTO tbOrdersInfo VALUES (20, ‘2023-01-20’, 45, 45989);

The output requested:

 WeekOfYear   DealDay   OrderQty   Growth 
1  Friday 29                          0
2  Friday 42 13
3  Friday 45 3

Problem Scenario:6

You are requested to generate a report that displays the quarterly sales summary. The monthly sales summary data and the amount has been given below.

Dataset:

 Id  YearMonth  SummaryMonth  TotalSales 
1 201901  January 339000
2 201902  February 449000
3 201903  March 393000
4 201904  April 320000
5 201905  May 343430
6 201906  June 444000
7 201907  July 303000
8 201908  August 200990
9 201909  September 299900
10 201910  October 393000
11 201911  November 420000
12 201912  December 415000

Build Schema:

CREATE TABLE tbSalesSummary(
Id INT IDENTITY(1,1),
YearMonth INT,
SummaryMonth VARCHAR(20),
TotalSales DECIMAL(20,2));

INSERT INTO tbSalesSummary(YearMonth,SummaryMonth,TotalSales) VALUES
(201901, ‘January’, 339000),
(201902,’February’,449000),
(201903,’March’,393000),
(201904,’April’,320000),
(201905,’May’,343430),
(201906,’June’,444000),
(201907,’July’,303000),
(201908,’August’,200990),
(201909,’September’,299900),
(201910,’October’,393000),
(201911,’November’,420000),
(201912,’December’,415000);

The output requested:

Quarter  Total Sales 
1 449000
2 444000
3 303000
4 420000

Problem Scenario:7

You are requested to generate a report that displays the monthly transactions summary. The data, SQL script, and expected output is provided below.

Dataset:

AccountID  AccountNumber  PaidAmount PaidDate
1  A1020101  2000  2020-01-16 13:10:000
2  A1020102  3300  2020-01-17 13:33:000
3  A1020103  1100  2020-01-21 15:01:000
4  A1020104  5400  2020-01-22 15:49:000
5  A1020105  5200  2020-01-23 16:30:000
6  A1020106  3400  2020-01-23 16:59:000
7  A1020107  1200  2020-01-24 17:10:000
8  A1020108  4200  2020-01-25 18:22:000
9  A1020109  2100  2020-01-28 18:43:000
10  A1020110  3400  2020-01-30 19:12:000
11  A1020111  5500  2020-01-31 22:19:000
1  A1020101  2000  2020-02-01 13:10:000
2  A1020102  3300  2020-02-03 13:33:000
3  A1020103  1100  2020-02-04 15:01:000
4  A1020104  5400  2020-02-06 15:49:000
5  A1020105  5200  2020-02-08 16:30:000
6  A1020106  3400  2020-02-12 16:59:000
7  A1020107  1200  2020-02-13 17:10:000
8  A1020108  4200  2020-02-18 18:22:000
9  A1020109  2100  2020-02-20 18:43:000
10  A1020110  3400  2020-02-22 19:12:000
11  A1020111  5500  2020-02-27 22:19:000


Build Schema:

CREATE TABLE Trxns(
AccountID INT,
AccountNumber VARCHAR(20),
PaidAmount DECIMAL,
PaidDate SMALLDATETIME
)

INSERT INTO Trxns VALUES(221001, ‘1’, 2000.00, ‘2020-01-16 13:10:00:000’)
INSERT INTO Trxns VALUES(221002, ‘2’, 3300.00, ‘2020-01-17 13:33:00:000’)
INSERT INTO Trxns VALUES(221002, ‘3’, 1100.00, ‘2020-01-21 15:01:00:000’)
INSERT INTO Trxns VALUES(221002, ‘4’, 5400.00, ‘2020-01-22 15:49:00:000’)
INSERT INTO Trxns VALUES(221002, ‘5’, 5200.00, ‘2020-01-23 16:30:00:000’)
INSERT INTO Trxns VALUES(221002, ‘6’, 3400.00, ‘2020-01-23 16:59:00:000’)
INSERT INTO Trxns VALUES(221002, ‘7’, 1200.00, ‘2020-01-24 17:10:00:000’)
INSERT INTO Trxns VALUES(221002, ‘8’, 4200.00, ‘2020-01-25 18:22:00:000’)
INSERT INTO Trxns VALUES(221002, ‘9’, 2100.00, ‘2020-01-28 18:43:00:000’)
INSERT INTO Trxns VALUES(221002, ’10’, 3400.00, ‘2020-01-30 19:12:00:000’)
INSERT INTO Trxns VALUES(221002, ’11’, 5500.00, ‘2020-01-31 22:19:00:000’)

INSERT INTO Trxns VALUES(221001, ‘1’, 2000.00, ‘2020-02-01 13:10:00:000’)
INSERT INTO Trxns VALUES(221002, ‘2’, 3300.00, ‘2020-02-03 13:33:00:000’)
INSERT INTO Trxns VALUES(221002, ‘3’, 1100.00, ‘2020-02-04 15:01:00:000’)
INSERT INTO Trxns VALUES(221002, ‘4’, 5400.00, ‘2020-02-06 15:49:00:000’)
INSERT INTO Trxns VALUES(221002, ‘5’, 5200.00, ‘2020-02-08 16:30:00:000’)
INSERT INTO Trxns VALUES(221002, ‘6’, 3400.00, ‘2020-02-12 16:59:00:000’)
INSERT INTO Trxns VALUES(221002, ‘7’, 1200.00, ‘2020-02-13 17:10:00:000’)
INSERT INTO Trxns VALUES(221002, ‘8’, 4200.00, ‘2020-02-18 18:22:00:000’)
INSERT INTO Trxns VALUES(221002, ‘9’, 2100.00, ‘2020-02-20 18:43:00:000’)
INSERT INTO Trxns VALUES(221002, ’10’, 3400.00, ‘2020-02-22 19:12:00:000’)
INSERT INTO Trxns VALUES(221002, ’11’, 5500.00, ‘2020-02-27 22:19:00:000’);

The output requested:

Month  Year TrxnCount  Amount
 January 2020 11 36800
 February 2020 11 36800

Problem Scenario:8

You must develop a stored procedure (uspGetMonthlySummary) that generates the aforementioned report.

Please use the same dataset as specified in PS-7.

The stored procedure needs to take the value of the month parameter (@ipvMonth – in number format) and produce the monthly summary report for that particular month. The output must match the previous output.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s