0% completed
Problem
Table: Customer
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| customer_id | int |
| customer_name | varchar |
+---------------+---------+
customer_id is the column with unique values for this table.
Each row of this table contains the information of each customer in the WebStore.
Table: Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| sale_date | date |
| order_cost | int |
| customer_id | int |
| seller_id | int |
+---------------+---------+
order_id is the column with unique values for this table.
Each row of this table contains all orders made in the webstore.
sale_date is the date when the transaction was made between the customer (customer_id) and the seller (seller_id).
Table: Seller
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| seller_id | int |
| seller_name | text |
+---------------+---------+
seller_id is the column with unique values for this table.
Each row of this table contains details about the seller.
Problem Definition
Write a solution to report the names of all sellers who did not make any sales in 2020.
Return the result table ordered by seller_name in ascending order.
Example
Output
Try It Yourself
Solution
To solve this problem, the solution involves using SQL queries to identify sellers who did not make any sales in the year 2020. The information is spread across the Customer and Orders tables, with the Orders table containing details about sales transactions, including sale dates, order costs, customer IDs, and seller IDs.
The solution combines the LEFT JOIN, ON, WHERE, and ORDER BY clauses. The LEFT JOIN is employed to join the Seller table (S) with the Orders table (O) based on the condition that the seller IDs match. The ON clause further refines the join by including only those sales that occurred in the year 2020, determined using the Year function on the sale_date column.
The WHERE clause filters the results only to include rows where there is no corresponding seller ID in the Orders table, effectively isolating sellers who did not make any sales in 2020. This is achieved by checking for a NULL value in the O.seller_id column.
The final step involves ordering the result table by seller_name in ascending order using the ORDER BY clause.
SELECT S.seller_name FROM Seller AS S LEFT JOIN Orders AS O ON S.seller_id = O.seller_id AND Year(sale_date) = '2020' WHERE O.seller_id IS NULL ORDER BY seller_name
Let's break down the query step by step:
Step 1: Joining Seller and Orders tables
We need to connect the Seller and Orders tables based on the seller_id to get information about each sale and the associated seller.
SELECT S.seller_name FROM Seller AS S LEFT JOIN Orders AS O ON S.seller_id = O.seller_id
Output After Step 1:
+-------------+ | seller_name | +-------------+ | Daniel | | Elizabeth | | Elizabeth | | Elizabeth | | Frank | +-------------+
Step 2: Filtering sales in 2020
Now, we want to include only the sales made in the year 2020.
AND YEAR(sale_date) = '2020'
Output After Step 2:
+-------------+ | seller_name | +-------------+ | Daniel | | Elizabeth | | Elizabeth | | Frank | +-------------+
Step 3: Filtering sellers with no sales in 2020
To find sellers who did not make any sales in 2020, we use the WHERE clause to filter out those who had sales (O.seller_id IS NULL).
WHERE O.seller_id IS NULL
Output After Step 3:
+-------------+ | seller_name | +-------------+ | Frank | +-------------+
Step 4: Ordering the result
Finally, we order the result by seller_name in ascending order.
ORDER BY seller_name
Final Output:
+-------------+ | seller_name | +-------------+ | Frank | +-------------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible