JULY 1. Log on to your Oracle ApEx account. Navigate to SQL Workshop à SQL Scrip

Question

JULY

1. Log on to your Oracle ApEx account. Navigate to SQL Workshop à SQL Scripts

Create a SQL script called update_demo_orders that contains these update statements:

update demo_orders set order_total = 200 where customer_id = 1;

update demo_orders set order_total = 15 where customer_id = 2;

update demo_orders set order_total = 12 where customer_id = 3;

update demo_orders set order_total = 22 where customer_id = 4;

update demo_orders set order_total = 32 where customer_id = 5;

update demo_orders set order_total = 2 where customer_id = 6;

update demo_orders set order_total = 9 where customer_id = 7;

run the script to update the demo_orders table.

2. Create a SQL query that will show the count(*), the sum of the order_total column and the month for each of the three months in the demo_orders table, group by month so that the user can see the month-to-month order count and total. To get the month, you will have to use the to_char function on the order_timestamp column.

Copy and paste your SQL code into this assignment.

3. Modify the previous query and further break each group from months to months and customer_id. So we want to know what each customer spent each month. ORDER BY the month. This will require you to use the same to_char() code you used to get the month in the GROUP BY clause.

Copy and paste your SQL code into this assignment.

4. Create a SQL query that will show the MIN sum of all the unit_price * quantity in the demo_order_items_table. GROUP BY product_id. So we are looking for the products that generated the lowest overall profit. Don’t worry if you can’t get the product_id to show in your result set.

Copy and paste your SQL code into this assignment.

5. Modify the previous SQL query to include a column that will show the MAX sum of all the unit_price * quantity in the demo_order_items_table. GROUP BY product_id. So we are looking for the products that generated the greatest overall profit. Don’t worry if you can’t get the product_id to show in your result set.

Copy and paste your SQL code into this assignment.

JULY

Explanation / Answer

1. Step1:Click SQL Workshop and then SQL Script
Step2: Click Create
Step3: enter below statements
update demo_orders set order_total=200 wherecustomer_id=1;
update demo_orders set order_total=15 wherecustomer_id=2;
update demo_orders set order_total=12 wherecustomer_id=3;
update demo_orders set order_total=22 wherecustomer_id=4;
update demo_orders set order_total=32 wherecustomer_id=5;
update demo_orders set order_total=2 wherecustomer_id=6;
update demo_orders set order_total=9 wherecustomer_id=7;
Step4: save the file with update_demo_orders.sql
Step5: Upload the script
Step6; Open the script you want to run and then select Run

2.select count(*) as order_total, to_char(order_timestamp) from demo_orders group by to_char(order_timestamp);

Related Questions

Navigate

• Browse (All)
• Browse J
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.