For the DEMO_CUSTOMER Databse in Oracle XE
1. Retrieve all records from the DEMO_CUSTOMERS table.
select * from DEMO_CUSTOMERS
2. Retrieve the first name, last name, and email of all customers.
select CUST_FIRST_NAME, CUST_LAST_NAME, CUST_EMAIL from DEMO_CUSTOMERS
3. Retrieve all distinct states from the DEMO_CUSTOMERS table.
select distinct CUST_STATE from DEMO_CUSTOMERS
4. Retrieve the order ID, customer ID, and order total for all orders sorted by order total in descending order.
select ORDER_ID, CUSTOMER_ID, ORDER_TOTAL from DEMO_ORDERS order by ORDER_TOTAL DESC;
5. Find the total number of orders placed by each customer.
select CUST_FIRST_NAME, CUST_LAST_NAME, CUST_EMAIL from DEMO_CUSTOMERS6. 1. Calculate the average order total for each customer.
select CUSTOMER_ID, AVG(ORDER_TOTAL) as AVG_ODER_TOTAL from DEMO_ORDERS group by CUSTOMER_ID7.1. Retrieve all product names and their total quantities sold.
select PRODUCT_NAME, SUM(QUANTITY) TOTAL_SALE
from DEMO_PRODUCT_INFO PI, DEMO_ORDER_ITEMS OI
WHERE PI.PRODUCT_ID = OI.PRODUCT_ID
GROUP BY PRODUCT_NAME
8 1. Find the highest order total in the DEMO_ORDERS table.
select MAX(ORDER_TOTAL) from DEMO_ORDERS
9.1. Retrieve the name and price of all products that cost more than $50.
select PRODUCT_NAME, LIST_PRICE from DEMO_PRODUCT_INFO where LIST_PRICE>50
10. 1. Find the total sales amount for each product (unit price multiplied by quantity).
select PRODUCT_ID, sum(UNIT_PRICE * QUANTITY)
TOTAL_SALES_AMOUNT from DEMO_ORDER_ITEMS group by PRODUCT_ID
11. 1. Retrieve the user name and the total number of orders processed by each user.
select USER_NAME, COUNT(ORDER_ID) ORDERS_PROCESSED
from DEMO_USERS, DEMO_ORDERS
where DEMO_USERS.USER_ID= DEMO_ORDERS.USER_ID
group by USER_NAME
12. 1. Create a backup table CUSTOMER_BACKUP containing all records from the DEMO_CUSTOMERS table.
create table CUSTOMER_BACKUP as
select * from DEMO_CUSTOMERS13. 1. Insert data from the DEMO_CUSTOMERS table into the CUSTOMER_BACKUP table.
insert into CUASTOMER_BACKUP(CUSTOMER_ID, CUST_CITY, CUST_STATE)
SELECT CUSTOMER_ID, CUST_CITY, CUST_STATE FROM DEMO_CUSTOMERS“
14. 1. Retrieve the total number of unique customers from the DEMO_ORDERS table.
select count(DISTINCT CUSTOMER_ID) as UNI_CUS from DEMO_ORDERS
15. 1. Retrieve all orders placed in the year 2023.
select * from DEMO_ORDERS where extract(YEAR from ORDER_TIMESTAMP)=2024
Conclusion
We have tried new commands like inserting data from other tables, referring to other tables and using them. Creating comparison statements to get the desired data from the db.