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

Question

PS8

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.

Explanation / Answer

CREATE TABLE IF NOT EXISTS `lead_activity2` ( `lead_activity_id` int(11) NOT NULL AUTO_INCREMENT, `sp_id` int(11) NOT NULL, `act_date` datetime NOT NULL, `act_name` varchar(255) NOT NULL, PRIMARY KEY (`lead_activity_id`), KEY `act_date` (`act_date`), KEY `act_name` (`act_name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ; INSERT INTO `lead_activity2` (`lead_activity_id`, `sp_id`, `act_date`, `act_name`) VALUES (1, 5, '2012-10-16 16:05:29', 'sale'), (2, 5, '2012-10-16 16:05:29', 'search'), (3, 5, '2012-10-16 16:05:29', 'sale'), (4, 5, '2012-10-17 16:05:29', 'DNC'), (5, 5, '2012-10-17 16:05:29', 'sale'), (6, 5, '2012-09-16 16:05:30', 'SCB'), (7, 5, '2012-09-16 16:05:30', 'sale'), (8, 5, '2012-08-16 16:05:30', 'sale'), (9, 5,'2012-08-16 16:05:30', 'sale'), (10, 5, '2012-07-16 16:05:30', 'sale');

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

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