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.

Answer 1

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');