Question

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

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.

Homework Answers

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');
Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
Use the following tables to answer Q5 and Q6. Tourist (tno, Name, phone, age,loc) Vehicle (Vno,...
Use the following tables to answer Q5 and Q6. Tourist (tno, Name, phone, age,loc) Vehicle (Vno, model, mileage, owner_name, owner_ph)) Rented (tno, Vno) 5. (i)Write an SQL query, to create the table vehicle with following constraints: Vno should begin with second letter ‘v’; Models are one among Thar, X3. Mileage should be above 18 and below 25. No two records can have same phone (ii) Write an SQL query to retrieve the name of the owner who does owns X3...
Question 1: Group by and Aggregates: Write SQL statements to answer the following questions using Assignment...
Question 1: Group by and Aggregates: Write SQL statements to answer the following questions using Assignment 4’s schema (Customer-Invoice-Line-Product-Vendor). Make sure that your SQL script runs without any errors. Submit your answers in a .SQL file. 1 (2 Points) - Find the count of distinctvendors thatsupplied products that are priced lowerthan 185? 2 (2 Points) - For each vendor, find their product that has the lowest product quantity. Your output should include vendor code, vendor name, product description and product...
SQL A manufacturing company’s data warehouse contains the following tables. Region region_id (p) region_name super_region_id (f)...
SQL A manufacturing company’s data warehouse contains the following tables. Region region_id (p) region_name super_region_id (f) 101 North America 102 USA 101 103 Canada 101 104 USA-Northeast 102 105 USA-Southeast 102 106 USA-West 102 107 Mexico 101 Note: (p) = "primary key" and (f) = "foreign key". They are not part of the column names. Product product_id (p) product_name 1256 Gear - Large 4437 Gear - Small 5567 Crankshaft 7684 Sprocket Sales_Totals product_id (p)(f) region_id (p)(f) year (p) month (p)...
The relational schema for the Academics database is as follows: DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode)...
The relational schema for the Academics database is as follows: DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode) ACADEMIC(acnum, deptnum*, famname, givename, initials, title) PAPER(panum, title) AUTHOR(panum*, acnum*) FIELD(fieldnum, id, title) INTEREST(fieldnum*, acnum*, descrip) Some notes on the Academics database: An academic department belongs to one institution (instname) and often has many academics. An academic only works for one department. Research papers (PAPER) are often authored by several academics, and of course an academic often writes several papers (AUTHOR). A research field...
Practice using EXCEL – Part of your Orientation Assignment to prepare for class on the first...
Practice using EXCEL – Part of your Orientation Assignment to prepare for class on the first day. Step by Step instructions on completing PR1-5B. BEFORE STARTING TO WORK THE PROBLEM YOU NEED TO WRITE ALL BALANCE FORMULAS. To do so do the following in order. Click on Cell D39. In D39 you will write a formula to add rows D37 and D38. To do so do the following: a)    Start in Cell D39 and press = sign b)    Highlight cell...
We will take another Web Field Trip this week for your Assignment. Visit the following site:...
We will take another Web Field Trip this week for your Assignment. Visit the following site: bloomberg   .com /markets/rates You will find an amazing amount of information about GLOBAL interest rates. It will open to the United States. NOTE: For this link to operate properly, you may need to open it outside the classroom page, by opening a new browser window. Copy/paste the above address to a new page and go there outside of class. Explore, learn and really study...
For this assignment you need to write a parallel program in C++ using OpenMP for vector...
For this assignment you need to write a parallel program in C++ using OpenMP for vector addition. Assume A, B, C are three vectors of equal length. The program will add the corresponding elements of vectors A and B and will store the sum in the corresponding elements in vector C (in other words C[i] = A[i] + B[i]). Every thread should execute approximately equal number of loop iterations. The only OpenMP directive you are allowed to use is: #pragma...
Assume that the City Council in Prescott, Arizona is considering implementing price ceilings on rental units...
Assume that the City Council in Prescott, Arizona is considering implementing price ceilings on rental units based on the number of bedrooms in the unit. The demand function for rental units (on a single bedroom equivalent basis) is given by QD = 120 – 4P and the supply function is given by QS = 2P, where P is price and Q is quantity. The Council is considering imposing a ceiling price on rental units of Pmax = 16. 1. Using...
(Use the case of “Global Treps”) You have been selected as the project manager for the...
(Use the case of “Global Treps”) You have been selected as the project manager for the Global Treps project. You helped to run a local shark tank like event at your college last year as part of a class project, so you have a general idea of what is involved. The schedule goal is six months, and the budget is $120,000. Your favorite professor, Dr. K., and a few of her associates have agreed to fund the project. Your strengths...
You are a database consultant with Ace Software, Inc., and have been assigned to develop a...
You are a database consultant with Ace Software, Inc., and have been assigned to develop a database for the Mom and Pop Johnson video store in town. Mom and Pop have been keeping their records of videos and DVDs purchased from distributors and rented to customers in stacks of invoices and piles of rental forms for years. They have finally decided to automate their record keeping with a relational database. You sit down with Mom and Pop to discuss their...
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT