SQL Project: EcommerceDB
Team 1:
Abdallah Ramadan
Ahmed Nafea
Hagar Mohamed
Rania Ossama
Merehan Ibrahim
Youssef Wael
All Database Tables
Run to view results
Question 1:
List all products whose price is between 50 and 200, ordered by price descending
SELECT
ProductID AS `Product ID`,
ProductName AS `Product Name`,
Description AS `Product Description`,
Price AS `Product Price`
FROM
ECommerceDB.products
WHERE
Price BETWEEN LEAST({{ Q1_min_price }}, {{ Q1_max_price }})
AND GREATEST({{ Q1_min_price }}, {{ Q1_max_price }})
ORDER BY
Price DESC
{% if Q1_limit_n %}
LIMIT
{{ Q1_limit_n | int }}
{% endif %};
Minimum Price
/ 2000
Maximum Price
/ 2000
Number of rows (LIMIT)
Run to view results
Question 2:
Show all customers who registered in 2024 and are from "USA".
SELECT
CONCAT(c.FirstName, ' ', c.LastName) AS `Full Name`,
c.Email AS `Email`,
c.Phone AS `Phone`,
cal.DATE AS `Registration Date`,
ad.Country AS `Country`
FROM
customers c
JOIN address ad ON c.AddressID = ad.AddressID
JOIN calendar cal ON c.RegistrationDateID = cal.DateID
WHERE
1=1
{% if Q2_registration_year %}
AND cal.YEAR = {{ Q2_registration_year }}
{% endif %}
{% if Q2_country %}
AND ad.Country IN (
{% for c in Q2_country %}
{{ c }}{% if not loop.last %}, {% endif %}
{% endfor %}
)
{% endif %}
{% if Q2_limit_n %}
LIMIT {{ Q2_limit_n | int }}
{% endif %};
Registration Year
2024
Country
USA
Number of rows (LIMIT)
Run to view results
Question 3:
UPDATE (currently commented out to avoid real update)
UPDATE products
SET
-- Price = Price * (1 + ({{ Q3_increase_percentage }}/100))
Price = Price -- No need to UPDATE right now.
WHERE
CategoryID IN (
SELECT
CategoryID
FROM
categories c
WHERE
1=1
{% if Q3_category %}
AND c.CategoryName IN (
{% for c in Q3_category %}
{{ c }}{% if not loop.last %}, {% endif %}
{% endfor %}
)
{% endif %}
)
;
SELECT (Preview the change)
SELECT
p.ProductID AS "Product ID",
p.ProductName AS "Product Name",
c.CategoryName AS "Category",
p.Price AS "Old Price",
(p.Price * (1 + ({{ Q3_increase_percentage }}/100))) AS "New Price",
((p.Price * (1 + ({{ Q3_increase_percentage }}/100))) - p.Price) AS "Difference"
FROM
products p
JOIN categories c ON p.CategoryID = c.CategoryID
WHERE
{% if Q3_category %}
c.CategoryName IN (
{% for c in Q3_category %}
{{ c }}{% if not loop.last %}, {% endif %}
{% endfor %}
)
{% endif %}
{% if Q3_limit_n %}
LIMIT {{ Q3_limit_n | int }}
{% endif %}
;
Category
Electronics
Price Increase Percentage
/ 100
Number of rows (LIMIT)
Run to view results
Question 4:
DELETE (currently commented out to avoid real update)
DELETE FROM orders
WHERE
1 = 0
-- {% if Q4_include_zero %}
-- OR TotalAmount = 0
-- {% endif %}
-- {% if Q4_include_null %}
-- OR TotalAmount IS NULL
-- {% endif %}
;
SELECT (Preview the changes)
SELECT
OrderID AS "Order ID",
TotalAmount AS "Total Amount"
FROM
orders
WHERE
1=1
{% if Q4_include_zero %}
AND TotalAmount = 0
{% endif %}
{% if Q4_include_null %}
AND TotalAmount IS NULL
{% endif %}
{% if Q4_limit_n %}
LIMIT {{ Q4_limit_n | int }}
{% endif %}
;
Include TotalAmount = 0?
Include TotalAmount IS NULL?
Number of rows (LIMIT)
Run to view results
Question 5:
CREATE OR REPLACE VIEW Top_Customers_2024 AS
SELECT
CONCAT(c.FirstName, ' ', c.LastName) AS "Customer Name",
c.Email AS "Email",
COUNT(o.OrderID) AS "Total Orders",
SUM(o.TotalAmount) AS "Total Sales"
FROM
customers c
JOIN orders o ON c.CustomerID = o.CustomerID
JOIN calendar cal ON o.OrderDateID = cal.DateID
WHERE
1 = 1
{% if Q5_year %}
AND cal.YEAR = {{ Q5_year }}
{% endif %}
GROUP BY
"Customer Name",
"Email"
HAVING
1 = 1
{% if Q5_min_sales %}
AND "Total Sales" > {{ Q5_min_sales | int}}
{% endif %}
ORDER BY
"Total Sales" DESC,
"Total Orders" DESC
{% if Q5_limit_n %}
LIMIT {{ Q5_limit_n | int }}
{% endif %};
Year
2024
Minimum Sales Amount
Number of rows (LIMIT)
Run to view results
Question 6:
SELECT
ProductName AS "Product Name",
Description AS "Product Description",
Price AS "Product Price"
FROM
products
WHERE
Price > (
SELECT AVG(Price)
FROM products
)
ORDER BY
"Product Price" DESC
{% if Q6_limit_n %}
LIMIT {{ Q6_limit_n | int }}
{% endif %};
Number of rows (LIMIT)
Run to view results
Question 7:
SELECT DISTINCT
c.CustomerID AS "Customer ID",
CONCAT(c.FirstName, ' ', c.LastName) AS "Customer Name",
c.Email AS "Email",
c.Phone AS "Phone"
FROM
customers c
JOIN orders o ON c.CustomerID = o.CustomerID
JOIN orderdetails od ON o.OrderID = od.OrderID
JOIN products p ON od.ProductID = p.ProductID
WHERE
p.Price = (
SELECT MAX(Price)
FROM products
)
ORDER BY
c.CustomerID
{% if Q7_limit_n %}
LIMIT {{ Q7_limit_n | int }}
{% endif %};
Number of rows (LIMIT)
Run to view results
Question 8:
Procedure already created. no need to run it.
-- CREATE PROCEDURE GetOrdersByCustomer (IN Customer_ID INT) BEGIN
-- SELECT
-- 'Customer ID' AS 'Customer',
-- 'Full Name' AS '&',
-- 'Email' AS 'Orders',
-- 'Phone' AS 'Report'
-- UNION
-- SELECT
-- c.CustomerID,
-- CONCAT (c.FirstName, ' ', c.LastName),
-- c.Email,
-- c.Phone
-- FROM
-- customers c
-- WHERE
-- c.CustomerID = Customer_ID
-- UNION
-- SELECT
-- '',
-- '',
-- '',
-- ''
-- UNION
-- SELECT
-- 'Order ID',
-- 'Order Date ID',
-- 'Delivery Date ID',
-- 'Total Amount'
-- UNION
-- SELECT
-- o.OrderID,
-- o.OrderDateID,
-- o.DeliveryDateID,
-- o.TotalAmount
-- FROM
-- orders o
-- WHERE
-- o.CustomerID = Customer_ID;
-- END
Customer ID
Number of rows (LIMIT)
CALL GetOrdersByCustomer({{ Q8_customer_id | int }})
{% if Q8_limit_n %}
LIMIT {{ Q8_limit_n | int }} + 4
{% endif %};
Run to view results
Question 9:
WITH
top_products_by_sales AS (
SELECT
p.ProductName AS "Product Name",
p.CategoryID AS Product_Category,
COUNT(o.OrderID) AS Total_Orders,
SUM(od.UnitPrice * od.Quantity) AS Total_Sales,
RANK() OVER (
PARTITION BY p.CategoryID
ORDER BY SUM(od.UnitPrice * od.Quantity) DESC
) AS Product_Rank
FROM
products p
JOIN orderdetails od ON p.ProductID = od.ProductID
JOIN orders o ON od.OrderID = o.OrderID
GROUP BY
"Product Name",
Product_Category
),
top_categories_by_sales AS (
SELECT
ca.CategoryID AS Category_ID,
ca.CategoryName AS "Category Name",
COUNT(o.OrderID) AS Total_Orders,
SUM(od.UnitPrice * od.Quantity) AS Total_Sales,
RANK() OVER (
ORDER BY SUM(od.UnitPrice * od.Quantity) DESC
) AS Category_Rank
FROM
categories ca
JOIN products p ON ca.CategoryID = p.CategoryID
JOIN orderdetails od ON p.ProductID = od.ProductID
JOIN orders o ON od.OrderID = o.OrderID
GROUP BY
Category_ID,
"Category Name"
ORDER BY
Category_Rank ASC
)
SELECT
tc."Category Name" AS "Category",
tp."Product Name" AS "Product Name",
tp.Total_Orders AS "Total Orders",
tp.Total_Sales AS "Total Sales"
FROM
top_categories_by_sales tc
JOIN top_products_by_sales tp ON tc.Category_ID = tp.Product_Category
WHERE
{% if Q9_top_n %}
tp.Product_Rank <= {{ Q9_top_n | int }}
{% else %}
tp.Product_Rank < 4
{% endif %}
{% if Q9_category %}
AND tc."Category Name" IN (
{% for c in Q9_category %}
{{ c }}{% if not loop.last %}, {% endif %}
{% endfor %}
)
{% endif %}
ORDER BY
tc.Category_Rank ASC,
tp.Product_Rank ASC
{% if Q9_limit_n %}
LIMIT {{ Q9_limit_n | int }}
{% endif %};
Select Categories
Top N Products per Category
Number of rows (LIMIT)
Run to view results
Question 10:
SELECT
cal.MONTH AS "Month",
MONTHNAME(cal.DATE) AS "Month Name",
SUM(o.TotalAmount) AS "Total Sales"
FROM
calendar cal
JOIN orders o ON o.OrderDateID = cal.DateID
WHERE
cal.YEAR = 2024
{% if Q10_start_month and Q10_end_month %}
AND cal.MONTH BETWEEN
LEAST({{ Q10_start_month | int }}, {{ Q10_end_month | int }})
AND
GREATEST({{ Q10_start_month | int }}, {{ Q10_end_month | int }})
{% endif %}
GROUP BY
"Month", "Month Name"
ORDER BY
"Month" ASC;
Start Month
/ 12
End Month
/ 12
Run to view results
Question 11:
SELECT
o.OrderID AS "Order ID",
o.TotalAmount AS "Total Amount",
cal.DAYOFWEEK AS "Day"
FROM
orders o
JOIN calendar cal ON o.OrderDateID = cal.DateID
WHERE
1=1
{% if Q11_days %}
AND cal.DAYOFWEEK IN (
{% for d in Q11_days %}
{{ d }}{% if not loop.last %}, {% endif %}
{% endfor %}
)
{% else %}
AND cal.DAYOFWEEK IN ('Saturday', 'Sunday')
{% endif %}
ORDER BY
"Total Amount" DESC
{% if Q11_limit_n %}
LIMIT {{ Q11_limit_n | int }}
{% endif %};
Days of Week
SaturdaySunday
Number of rows (LIMIT)
Run to view results
Question 12:
Overall Average Delivery time for all Orders:
SELECT
AVG(datediff (c_delivery.DATE, c_order.DATE)) AS 'Average Delivery Time in Days'
FROM
orders o
JOIN calendar c_order ON o.OrderDateID = c_order.DateID
JOIN calendar c_delivery ON o.DeliveryDateID = c_delivery.DateID
WHERE
o.DeliveryDateID IS NOT NULL
;
Run to view results
Delivery time for each Order:
SELECT
o.OrderID AS "Order ID",
DATEDIFF(
(
SELECT cal.DATE
FROM calendar cal
JOIN orders o2 ON cal.DateID = o2.DeliveryDateID
WHERE o2.OrderID = o.OrderID
),
(
SELECT cal.DATE
FROM calendar cal
JOIN orders o2 ON cal.DateID = o2.OrderDateID
WHERE o2.OrderID = o.OrderID
)
) AS "Delivery Time (Days)"
FROM
orders o
{% if Q12_limit_n %}
LIMIT {{ Q12_limit_n | int }}
{% endif %};
Number of rows (LIMIT)
Run to view results
Question 13:
SELECT
ca.CategoryName AS "Category",
SUM(od.Quantity) AS "Total Quantity Sold"
FROM
categories ca
JOIN products p ON ca.CategoryID = p.CategoryID
JOIN orderdetails od ON p.ProductID = od.ProductID
JOIN orders o ON od.OrderID = o.OrderID
GROUP BY
"Category"
ORDER BY
SUM(od.Quantity) DESC
{% if Q13_limit_n %}
LIMIT {{ Q13_limit_n | int }}
{% endif %};
Top N Categories
Run to view results
Question 14:
SELECT
cu.CustomerID AS "Customer ID",
CONCAT(cu.FirstName, ' ', cu.LastName) AS "Full Name",
COUNT(o.OrderID) AS "Number of Orders"
FROM
customers cu
JOIN orders o ON cu.CustomerID = o.CustomerID
JOIN calendar ca ON o.OrderDateID = ca.DateID
WHERE
ca.DATE >= CURDATE() - INTERVAL {% if Q14_days_interval %}{{ Q14_days_interval | int }}{% else %}90{% endif %} DAY
GROUP BY
"Customer ID",
"Full Name"
ORDER BY
"Number of Orders" DESC
{% if Q14_limit_n %}
LIMIT {{ Q14_limit_n | int }}
{% endif %};
Number of days to look back
Top N Customers
Run to view results
Question 15:
SELECT
CONCAT(c.FirstName, ' ', c.LastName) AS "Customer Name",
c.Email AS "Email",
IFNULL(
FORMAT(
SUM(
CASE
WHEN 1 = 1
{% if Q15_year %} AND cal.YEAR = {{ Q15_year | int }}{% endif %} THEN o.TotalAmount
ELSE 0
END
),
2
),
0
) AS "Total Sales",
RANK() OVER (
ORDER BY
SUM(
CASE
WHEN 1 = 1 {% if Q15_year %} AND cal.YEAR = {{ Q15_year | int }}{% endif %} THEN o.TotalAmount
ELSE 0
END
) DESC
) AS "Rank"
FROM
customers c
LEFT JOIN orders o ON c.CustomerID = o.CustomerID
LEFT JOIN calendar cal ON o.OrderDateID = cal.DateID
GROUP BY
"Customer Name",
"Email"
ORDER BY
"Rank" ASC
{% if Q15_limit_n %}
LIMIT {{ Q15_limit_n | int }}
{% endif %};
Year to rank customers
2024
Number of rows (LIMIT)
Run to view results