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.
Big Data and SQL 
One comment