Order Entry Database — SQL Queries
This time, I will try to use SQL Queries in the database available on the Oracle Live SQL website. For those who don’t know what Oracle Live SQL is, I recommend reading my previous article.
This project starts with building an Entity Relation (ER) Diagram for Order Entry Database. Next, I will create some case studies that this company might face.
Entity Relation (ER) Diagram for Order Entry Database
ER Diagrams are designed to help us understand the database. The relationship between tables can be known primary key and foreign key to combine columns in different tables if needed. This database consists of 5 tables, which consist of:
- ORDERS : About all the bookings that are made
- CUSTOMERS : For registered customer data
- ORDER_ITEMS : Data for the number of products sold and their types
- PRODUCT_INFORMATION : Specific information related to the existing product
- CATEGORIES_TAB : Product categories and their information
1. Basic Queries for Analyzing Data
General information related to the data that we are using can be searched using the query below:
-- Date Range of ORDERS Data
SELECT MIN(ORDER_DATE) AS MIN_DATE, MAX(ORDER_DATE) AS MAX_DATE FROM OE.ORDERS;
-- Total Orders Done
SELECT COUNT(ORDER_ID) AS TOTAL_ORDER FROM OE.ORDERS;
-- Total Revenue yang Didapat
SELECT SUM(ORDER_TOTAL) AS TOTAL_REVENUE FROM OE.ORDERS;
-- Total Revenue Earned
SELECT COUNT(CUSTOMER_ID) AS TOTAL_CUST FROM OE.CUSTOMERS;
-- Total Customers (Transacted)
SELECT COUNT(DISTINCT(CUSTOMER_ID)) AS TOTAL_CUST_TRANS FROM OE.ORDERS;
-- Number of Product Types Available
SELECT COUNT(PRODUCT_ID) AS TOTAL_PRODUCT_ID FROM OE.PRODUCT_INFORMATION;
-- BNumber of Product Types Purchased
SELECT COUNT(DISTINCT(PRODUCT_ID)) AS TOTAL_PRODUCT_ID_SOLD FROM OE.ORDER_ITEMS;
-- Number of Categories
SELECT COUNT(CATEGORY_ID) AS TOTAL_CATEGORY FROM OE.CATEGORIES_TAB;
From the output above we can have a better understanding of the data. The ORDERS table which contains all orders has a date range of March 20, 2004 to August 01, 2008. The total orders recorded are 105 with a total revenue (gross) of $3,668,054.7. There are 319 customers listed in the CUSTOMERS table. However, there are only 47 customers who transacted in the ORDERS table. The number of product types registered in the store is 288 types with 22 categories. The total types of products sold are 185.
2. Case Study — 1: Customers
The marketing team wants to know who are the customers with the highest amount of spending and transactions. To offer benefits in the form of membership, the marketing team needs a list of customers and their emails sorted by the highest spending.
SELECT co.CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_EMAIL, SUM(ORDER_TOTAL) AS Total_Revenue, COUNT(ORDER_STATUS) AS Total_Trans
FROM OE.CUSTOMERS co
JOIN OE.ORDERS oi ON co.CUSTOMER_ID = oi.CUSTOMER_ID
GROUP BY co.CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_EMAIL
ORDER BY Total_Revenue DESC;
The data in the CUSTOMERS table is joined (JOIN) with the ORDERS table to get the output as shown above. From the output, we can find out that the customer with the highest spending does not certainly have the most transactions. For example, Goldie Slater (id:150) has the 3rd highest total spend even though he/she only transacted once.
3. Case Study — 2: Products
The sales team wants to know what is the best-selling product (the one with the highest number of units sold). In addition, the sales team also wants to know all the products with the highest total units sold.
SELECT pi.PRODUCT_ID, PRODUCT_NAME, SUM(QUANTITY) AS Total_Quantity
FROM OE.PRODUCT_INFORMATION pi
JOIN OE.ORDER_ITEMS oi ON pi.PRODUCT_ID = oi.PRODUCT_ID
GROUP BY pi.PRODUCT_ID, PRODUCT_NAME
ORDER BY Total_Quantity DESC;
The data in the PRODUCT_ITEMS table is joined (JOIN) with the ORDER_ITEMS table to get a list of best-selling products and their quantities.
4. Case Study — 3: Categories
It was known that there were 22 categories of products available. The sales team wants to know the number of products sold and the average price of products in each category sorted by the number of products sold.
SELECT ct.CATEGORY_NAME, SUM(oi.QUANTITY) AS TOTAL_SOLD, ROUND(AVG(oi.UNIT_PRICE),2) AS avg_price
FROM OE.ORDER_ITEMS oi
INNER JOIN OE.PRODUCT_INFORMATION pi ON oi.PRODUCT_ID = pi.PRODUCT_ID
INNER JOIN OE.CATEGORIES_TAB ct ON pi.CATEGORY_ID = ct.CATEGORY_ID
GROUP BY ct.CATEGORY_NAME
ORDER BY TOTAL_SOLD DESC;
Data pada tabel ORDER_ITEMS digunakan untuk menghitung total produk yang terjual beserta harga setiap produknya. Dilakukan JOIN sebanyak 2 kali yaitu dengan tabel PRODUCT_INFORMATION dan CATEGORIES_TAB sehingga diperoleh output seperti pada gambar di atas.
5. Case Study — 4: Transaction
It was found that there are 10 order status categories in the ORDER_STATUS column in the ORDERS table:
0: Not fully entered
1: Entered
2: Canceled — bad credit
3: Canceled — by customer
4: Shipped — whole order
5: Shipped — replacement items
6: Shipped — backlog on items
7: Shipped — special delivery
8: Shipped — billed
9: Shipped — payment plan
10: Shipped — paid
The sales team wants to know the total number of transactions based on the status of the order.
Queries:
SELECT DISTINCT(ORDER_STATUS), COUNT(ORDER_ID) AS TOTAL
FROM OE.ORDERS
GROUP BY ORDER_STATUS
ORDER BY ORDER_STATUS;
DISTINCT is used to calculate the number of orders in each category (order status) which results in the output as shown above.
After analyzing the list, it turned out that out of 105 transactions, not all of them were completed/paid. The sales team wants to know the total incomplete bookings.
SELECT COUNT(ORDER_ID) AS TOTAL_UNPAID
FROM OE.ORDERS
WHERE ORDER_STATUS IN (0,1,2,3);
Based on the output above, we can conclude that out of a total of 105 bookings, there are 34 bookings that have not been completed or canceled.
That wraps up the SQL project using Order Entry (OE) database using Oracle Live SQL. If you are interested in this project, feel free to contact me!
Thank you!
