![IBM Coding Exam 2024](https://onlinestudy4u.in/wp-content/uploads/2024/07/CC57FF06-9F78-42C0-B744-99877DCC0615_4_5005_c.jpeg)
IBM Backend Developer Test 2024 | Previous Year Questions & Answers
In This Article We Are Solving IBM Backend Developer Test 2024 Questions From Previous Year Examination Along With That You Will Get Solutions And Explanation.
![IBM Backend Developer Test 2024](https://www.onlinestudy4u.in/wp-content/uploads/2024/07/CC57FF06-9F78-42C0-B744-99877DCC0615_4_5005_c-1024x287.jpeg)
Question #1 – IBM Backend Developer Test 2024
Write a query to select the names of the customers who live in New York and have ordered a product with a total cost of more than $100 (Total cost = Price + Shipping).
Table Schema:
Orders
Productid | Customerid | Price | Shipping |
---|---|---|---|
1001 | 101 | 70 | 50 |
1002 | 102 | 60 | 30 |
1003 | 103 | 180 | 40 |
Products
Productid | Weight | Customerid |
---|---|---|
1001 | 100 | 101 |
1002 | 200 | 102 |
1003 | 120 | 103 |
Customers
Customerid | CustomerName | City |
---|---|---|
101 | Jack | New York |
102 | Remi | Washington |
103 | Jones | Miami |
Sample Output:
Customer |
---|
Jack |
Answer In My SQL
SELECT c.CustomerName AS Customer
FROM Customers c
JOIN Orders o ON c.Customerid = o.Customerid
WHERE c.City = 'New York' AND (o.Price + o.Shipping) > 100;
Explanation – IBM Backend Developer Test 2024
Join Tables:
- We need information from both the
Customers
andOrders
tables. We use anINNER JOIN
to combine these tables on theCustomerid
column, which is common to both tables.
Filter by City:
- We filter the combined results to include only those customers who live in New York using the condition
c.City = 'New York'
.
Filter by Total Cost:
- We further filter the results to include only those orders where the total cost (Price + Shipping) is greater than $100 using the condition
(o.Price + o.Shipping) > 100
.
Select the Required Column:
- Finally, we select the
CustomerName
from theCustomers
table.
Given the provided data, only Jack (Customerid 101) meets the criteria of living in New York and having an order with a total cost greater than $100 (70 + 50 = 120). Therefore, the output will be:
Output:
Customer |
---|
Jack |
Problem: IBM Backend Developer Test 2024
![](https://www.onlinestudy4u.in/wp-content/uploads/2024/07/CC57FF06-9F78-42C0-B744-99877DCC0615_4_5005_c-1024x287.jpeg)
Objective: Write a SQL query to fetch the number of wins for the years 2021, 2022, and 2023, for teams that:
- Achieved a minimum of 2 wins in both 2021 and 2022.
- Failed to secure at least 2 wins in 2023.
Expected output schema:
team
: Name of the team.year
: Year of the win count (2021, 2022, or 2023).no_of_wins
: Number of wins in that year.
Database structure:
- Table name:
team_match_results
- Columns:
team
: VARCHAR(20) – Name of the team.match_id
: INTEGER – Unique identifier for each match.year
: INTEGER – Year the match was held.winning_status
: VARCHAR(20) – Status showing whether the team “Won” or “Lost” the match.
Solution In My SQL:
Here is the SQL query to achieve this:
WITH yearly_wins AS (
SELECT
team,
year,
COUNT(*) AS no_of_wins
FROM
team_match_results
WHERE
winning_status = 'Won' AND year IN (2021, 2022, 2023)
GROUP BY
team, year
),
teams_with_min_wins AS (
SELECT
team
FROM
yearly_wins
GROUP BY
team
HAVING
SUM(CASE WHEN year = 2021 AND no_of_wins >= 2 THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN year = 2022 AND no_of_wins >= 2 THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN year = 2023 AND no_of_wins >= 2 THEN 1 ELSE 0 END) = 0
)
SELECT
y.team,
y.year,
y.no_of_wins
FROM
yearly_wins y
JOIN
teams_with_min_wins t ON y.team = t.team
ORDER BY
y.team, y.year;
Explanation:
CTE yearly_wins
: This Common Table Expression calculates the number of wins for each team for the years 2021, 2022, and 2023.
- It filters the rows where
winning_status
is ‘Won’ and theyear
is one of 2021, 2022, or 2023. - It groups the results by
team
andyear
and counts the number of wins (no_of_wins
).
CTE teams_with_min_wins
: This Common Table Expression filters the teams that meet the specific win criteria:
- Ensures the team has at least 2 wins in both 2021 and 2022.
- Ensures the team has less than 2 wins in 2023.
- Uses
HAVING
to apply these conditions withSUM
andCASE
statements.
Final Select: This part joins the yearly_wins
CTE with the teams_with_min_wins
CTE to get the relevant teams and their win counts.
- It orders the results by
team
andyear
in ascending order.
Sample Data and Expected Output: IBM Backend Developer Test 2024
Assume the team_match_results
table has the following data:
team match_id year winning_status
------ -------- ---- --------------
Team A 1 2021 Won
Team A 2 2021 Won
Team A 3 2021 Lost
Team A 4 2022 Won
Team A 5 2022 Won
Team A 6 2023 Won
Team A 7 2023 Lost
Team B 8 2021 Won
Team B 9 2021 Won
Team B 10 2022 Won
Team B 11 2022 Won
Team B 12 2023 Lost
Team B 13 2023 Lost
Expected output:
team year no_of_wins
------ ---- ----------
Team A 2021 2
Team A 2022 2
Team A 2023 1
Team B 2021 2
Team B 2022 2
Team B 2023 0
In this example, both Team A
and Team B
have at least 2 wins in 2021 and 2022, and fewer than 2 wins in 2023, thus meeting the criteria specified in the problem.
Problem Understanding: IBM Backend Developer Test 2024
![](https://www.onlinestudy4u.in/wp-content/uploads/2024/07/CC57FF06-9F78-42C0-B744-99877DCC0615_4_5005_c-1024x287.jpeg)
An ISP needs a query to generate a report for billing purposes. The report should list each client’s MAC address along with their total traffic and the corresponding cost for the current billing period, which is defined as May 2022. Each client has a unique tariff rate per megabyte of data consumed. The result should be sorted in descending order based on the total cost.
Schema: IBM Backend Developer Test 2024
clients table:
id
(SMALLINT): Client IDmac
(VARCHAR(17)): MAC address of the clienttariff
(DECIMAL(6,5)): Tariff rate per MB for the client
traffic table:
client_id
(SMALLINT): Client ID (foreign key referencingclients.id
)dt
(VARCHAR(19)): Date of the traffic recordamount
(INT): Amount of traffic in megabytes
Sample Data: IBM Backend Developer Test 2024
clients table:
| id | mac | tariff |
|----|-------------------|---------|
| 1 | A2-53-FC-0C-3E-B4 | 0.00007 |
| 2 | DC-80-42-E9-AE-FC | 0.00003 |
| 3 | 3F-9B-A9-2A-B1-7B | 0.00007 |
| 4 | D4-6F-E4-AF-47-D5 | 0.00004 |
| 5 | B9-65-FC-8E-F0-15 | 0.00007 |
traffic table:
| client_id | dt | amount |
|-----------|------------|--------|
| 1 | 2022-05-22 | 9127 |
| 1 | 2022-06-07 | 62203 |
| 1 | 2022-06-10 | 88227 |
| 2 | 2022-05-31 | 99874 |
Expected Output: IBM Backend Developer Test 2024
| mac | traffic | cost |
|-------------------|---------|-------|
| B9-65-FC-8E-F0-15 | 147927 | 10.35 |
| D4-6F-E4-AF-47-D5 | 93743 | 3.75 |
| DC-80-42-E9-AE-FC | 99874 | 3.00 |
| A2-53-FC-0C-3E-B4 | 9127 | 0.64 |
| 3F-9B-A9-2A-B1-7B | 8386 | 0.59 |
SQL Query Explanation: IBM Backend Developer Test 2024
SELECT
c.mac,
SUM(t.amount) AS traffic,
ROUND(SUM(t.amount) * c.tariff, 2) AS cost
FROM
clients c
JOIN
traffic t ON c.id = t.client_id
WHERE
t.dt LIKE '2022-05%'
GROUP BY
c.mac
ORDER BY
cost DESC;
SELECT Clause:
c.mac
: Selects the MAC address from theclients
table.SUM(t.amount) AS traffic
: Calculates the total traffic (sum ofamount
fromtraffic
table) for each client.ROUND(SUM(t.amount) * c.tariff, 2) AS cost
: Computes the total cost by multiplying the total traffic by the client’s tariff rate (tariff
column inclients
table), rounded to two decimal places.
FROM Clause:
clients c
: Aliases theclients
table asc
.JOIN traffic t ON c.id = t.client_id
: Joinsclients
andtraffic
tables usingclient_id
fromtraffic
andid
fromclients
.
WHERE Clause:
t.dt LIKE '2022-05%'
: Filters records fromtraffic
table for May 2022 (dt
column starts with ‘2022-05’).
GROUP BY Clause:
c.mac
: Groups the results bymac
address to aggregate traffic and cost calculations per client.
ORDER BY Clause:
cost DESC
: Sorts the result set in descending order based on the calculatedcost
.
Summary: IBM Backend Developer Test 2024
This SQL query effectively retrieves the required information from the database, calculates the total traffic and billing cost for each client in May 2022 based on their tariff rate, and presents the results sorted by cost in descending order. It efficiently uses SQL aggregation functions (SUM
), arithmetic operations (ROUND
), joins (JOIN
), and filtering (WHERE
) to achieve the desired outcome.
Problem Statement: IBM Backend Developer Test 2024
A shipping company needs a query to generate a report listing all customer packages that are in the process of being delivered. The report should include:
- Customer email address (
email
) - Total number of packages (
total_packages
) for each customer - Total weight of all packages (
total_weight
) for each customer
The result should be sorted in descending order by the total weight of packages.
Schema:
Customers Table:
CREATE TABLE customers (
id INT PRIMARY KEY,
email VARCHAR(255)
);
Packages Table:
CREATE TABLE packages (
id INT PRIMARY KEY,
status VARCHAR(50),
weight DECIMAL(10, 2)
);
Customer_Packages Table:
CREATE TABLE customer_packages (
customer_id INT,
package_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(id),
FOREIGN KEY (package_id) REFERENCES packages(id)
);
Sample Data: IBM Backend Developer Test 2024
Customers Table Data:
INSERT INTO customers (id, email) VALUES
(1, 'mail@gmail.com'),
(2, 'another@email.com');
Packages Table Data:
INSERT INTO packages (id, status, weight) VALUES
(1, 'delivered', 93.37),
(2, 'created', 86.16),
(3, 'delivered', 66.24),
(4, 'delivered', 78.63),
(5, 'onhold', 78.63),
(6, 'cancelled', 75.63),
(7, 'created', 74.63),
(8, 'shipped', 76.63);
Customer_Packages Table Data (Associations):
INSERT INTO customer_packages (customer_id, package_id) VALUES
(1, 1), (1, 2), (1, 3), (1, 4), (1, 5),
(2, 6), (2, 7), (2, 8);
SQL Solution: IBM Backend Developer Test 2024
SELECT
c.email AS email,
COUNT(cp.package_id) AS total_packages,
SUM(p.weight) AS total_weight
FROM
customers c
JOIN
customer_packages cp ON c.id = cp.customer_id
JOIN
packages p ON cp.package_id = p.id
WHERE
p.status IN ('created', 'shipped', 'onhold')
GROUP BY
c.email
ORDER BY
total_weight DESC;
Explanation: IBM Backend Developer Test 2024
Joining Tables:
- We start by joining the
customers
table (c
) with thecustomer_packages
table (cp
) usingcustomer_id
.
Further Join:
- We then join the
packages
table (p
) usingpackage_id
fromcustomer_packages
.
Filtering Data:
- The
WHERE
clause filters packages based on their status (created
,shipped
,onhold
), ensuring we only include packages that are in the process of being delivered.
Aggregation:
- Using
COUNT(cp.package_id)
, we count the number of packages (total_packages
) associated with each customer. - Using
SUM(p.weight)
, we calculate the total weight (total_weight
) of all packages associated with each customer.
Grouping:
- We group the results by
c.email
to ensure each row in the result set corresponds to a unique customer email address.
Sorting:
- Finally, we sort the result set in descending order (
ORDER BY total_weight DESC
) based on the total weight of packages.
This SQL query will generate a report listing each customer’s email, the total number of packages they have, and the total weight of those packages, sorted by the total weight in descending order.
Question: IBM Backend Developer Test 2024
While developing a dashboard for a large cloud hosting company, you need to generate a report listing Cloud Virtual Machine (CVM) configurations along with their total number of deployments in the year 2021. Write an SQL query to achieve this.
Answer:
SELECT
c.name AS configuration,
COUNT(d.configuration_id) AS deployments
FROM
configurations c
JOIN
deployments d ON c.id = d.configuration_id
WHERE
d.dt >= '2021-01-01' AND d.dt < '2022-01-01'
GROUP BY
c.name
ORDER BY
deployments DESC;
Explanation:
SELECT Statement:
c.name AS configuration
: Selects the name of the configuration from theconfigurations
table, aliased asconfiguration
.COUNT(d.configuration_id) AS deployments
: Counts the number of deployments by counting occurrences ofconfiguration_id
in thedeployments
table, aliased asdeployments
.
FROM Clause:
configurations c
: Specifies theconfigurations
table with aliasc
to be used in the query.JOIN deployments d ON c.id = d.configuration_id
: Joins theconfigurations
anddeployments
tables based onconfiguration_id
, ensuring that only matching rows are included.
WHERE Clause:
d.dt >= '2021-01-01' AND d.dt < '2022-01-01'
: Filters deployments to include only those that occurred in the year 2021. This range ensures that deployments from January 1, 2021, to December 31, 2021, are considered.
GROUP BY Clause:
c.name
: Groups the result set by thename
column ofconfigurations
. This ensures that each unique configuration is represented only once in the result set.
ORDER BY Clause:
deployments DESC
: Orders the result set in descending order based on thedeployments
count. Configurations with more deployments appear first.
This query efficiently retrieves the desired information by joining relevant tables, applying appropriate filters, aggregating data using COUNT
, and sorting the results as per the specified requirements.