Question 001:
1 - CREATE AN USER NAMED PRAC001 IN SQLPLUS AND GRANT DBA PREVILEGES.
2 - CREATE AN WORKSPACE NAMED PRAC001 AND CREATE AN APPLICATION NAMED "DEMO WEB APPLICATION".
3 - CREATE THE TABLES AS PER SCRIPT AT APEX : SQL WORKSHOPS > SQL SCRIPTS.
OR AT "SQL_WORKSHOP>SQL COMMANDS".
https://drive.google.com/file/d/1UW3B2IqDWrSIjCtTPjaIBR5neEEUnKmg/view?usp=sharing
4 - UPLOAD THE CSV(s) PROVIDED :
https://drive.google.com/file/d/1Asxs3GbYSq0mGNf-HD727wt7iryvF1S6/view?usp=sharing
5 - CREATE TWO PAGES WITH FORM AND REPORTS SIDE BY SIDE OF THE FOLLOWING TABLES:
A) DEMO_PRODUCT_INFO
B) DEMO_CUSTOMERS
6 - CREATE MASTER DETAILS PAGE OF THE FOLLOWING TABLES :
A) DEMO_ORDERS
B) DEMO_ORDER_ITEMS.
7 - CREATE 6 Charts in DASHBOARD at Main page WITH the FOLLOWING QUERIES:
A) HORIZONTAL BAR CHART NAMED : "Top Orders by Date" WITH THE FOLLOWING QUERY:
select to_char(o.order_timestamp,'Mon DD, YYYY') order_day,
SUM(o.order_total) sales
from demo_orders o
group by to_char(o.order_timestamp,'Mon DD, YYYY'),
order_timestamp order by 2 desc nulls last
fetch first 5 rows only
B) CREATE CLASSIC REPORT NAMED : "Sales for This Month" WITH THE TEMPLATE OF BADGE LIST WITH THE FOLLOWING Query:
select sum(o.order_total) total_sales, count(distinct o.order_id) total_orders,
count(distinct o.customer_id) total_customers
from demo_orders o;
C) CREATE PIE CHART WITH THE FOLLOWING QUERY NAMED : "Sales by Product"
SELECT p.product_name||' [$'||p.list_price||']' product,
SUM(oi.quantity * oi.unit_price) sales
FROM demo_order_items oi,
demo_product_info p
WHERE oi.product_id = p.product_id
GROUP BY p.product_id, p.product_name,
p.list_price
ORDER BY p.product_name desc
D) CREATE VERTICAL BAR CHART "Sales by Category" with the following query :
SELECT p.category Category, sum(o.order_total) Sales
FROM demo_orders o, demo_order_items oi, demo_product_info p
WHERE o.order_id = oi.order_id
AND oi.product_id = p.product_id
GROUP BY category ORDER BY 2 desc
E) Create Classic Report : "Top Customers" with the following query :
SELECT b.cust_last_name || ', ' || b.cust_first_name || ' - '|| count(a.order_id) ||' Order(s)' customer_name, SUM(a.ORDER_TOTAL) order_total, b.customer_id id
FROM demo_orders a, DEMO_CUSTOMERS b
WHERE a.customer_id = b.customer_id
GROUP BY b.customer_id, b.cust_last_name || ', ' || b.cust_first_name
ORDER BY NVL(SUM(a.ORDER_TOTAL),0) DESC
F) Create Classic Report : "Top Products" with the following query :
SELECT p.product_name||' - '||SUM(oi.quantity)||' x' ||to_char(p.list_price,'L999G99')||'' product,
SUM(oi.quantity * oi.unit_price) sales, p.product_id
FROM demo_order_items oi, demo_product_info p
WHERE oi.product_id = p.product_id
GROUP BY p.Product_id, p.product_name, p.list_price
ORDER BY 2 desc
