Need help with SQL homework, questions start under the problems section. This document is intended to relate
Fantastic news! We've Found the answer you've been seeking!
Question:
Need help with SQL homework, questions start under the problems section.
Transcribed Image Text:
This document is intended to relate the concepts we discuss in class to the Oracle SQL Language Reference or to the Oracle PL/SQL Language Reference as applies. In today's class we will (hope to) discuss: 1. Multiple Table Joins (SQL Language Reference: Page 812 - ; See 2075 for NATURAL JOIN & USING) 2. Subqueries (SQL Language Reference: Page 816 - ) a. Set Operators (SQL Language Reference: Page 202) 3. Analytic Functions (SQL Language Reference: Page 295 - ) 4. Hierarchical Queries (SQL Language Reference: Page 803 - ) Problems (Homework 1) 1. Which employees earn more than their department's average earnings? 2. For every product in the product information table, list the number of times it has been sold as well as the employee responsible for selling the largest quantity of it. We want to know if a product has never been sold, so please return all products, regardless of whether they have been sold. 3. Show the hierarchy of employees in the Sales department only. Explicitly show the chain of command using / to depict paths. 4. For each product that has been sold, who is the top salesperson? 5. What is the average salary of all middle-managers? (i.e. folks who manage others but themselves have managers). 6. We want to reward growth. For each month in which a salesperson made sales, determine whether that month's sales are greater than those of the prior month. Generate a column whose values are 'growth' when sales are greater than the prior month and 'no growth' otherwise. 7. Generate a cumulative sum of monthly sales. 8. Do 7 but this time generate the cumulative sum of monthly sales for each salesperson 9. For each month where sales were made give the name or product_description for the for the product sold in the largest quantity. 10. Demonstrate how a hierarchical query can be used to generate the date corresponding the first day of each month in 2022. There should be one column and 12 rows in the result. (do this using the ANSI method, and again using Oracle's CONNECT BY) This document is intended to relate the concepts we discuss in class to the Oracle SQL Language Reference or to the Oracle PL/SQL Language Reference as applies. In today's class we will (hope to) discuss: 1. Multiple Table Joins (SQL Language Reference: Page 812 - ; See 2075 for NATURAL JOIN & USING) 2. Subqueries (SQL Language Reference: Page 816 - ) a. Set Operators (SQL Language Reference: Page 202) 3. Analytic Functions (SQL Language Reference: Page 295 - ) 4. Hierarchical Queries (SQL Language Reference: Page 803 - ) Problems (Homework 1) 1. Which employees earn more than their department's average earnings? 2. For every product in the product information table, list the number of times it has been sold as well as the employee responsible for selling the largest quantity of it. We want to know if a product has never been sold, so please return all products, regardless of whether they have been sold. 3. Show the hierarchy of employees in the Sales department only. Explicitly show the chain of command using / to depict paths. 4. For each product that has been sold, who is the top salesperson? 5. What is the average salary of all middle-managers? (i.e. folks who manage others but themselves have managers). 6. We want to reward growth. For each month in which a salesperson made sales, determine whether that month's sales are greater than those of the prior month. Generate a column whose values are 'growth' when sales are greater than the prior month and 'no growth' otherwise. 7. Generate a cumulative sum of monthly sales. 8. Do 7 but this time generate the cumulative sum of monthly sales for each salesperson 9. For each month where sales were made give the name or product_description for the for the product sold in the largest quantity. 10. Demonstrate how a hierarchical query can be used to generate the date corresponding the first day of each month in 2022. There should be one column and 12 rows in the result. (do this using the ANSI method, and again using Oracle's CONNECT BY)
Expert Answer:
Related Book For
Posted Date:
Students also viewed these databases questions
-
In a survey of a sample of kindergarten students, a researcher had to read survey items to students and record their responses. While reading and recording the results from one student, the...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Portray in words what transforms you would have to make to your execution to some degree (a) to accomplish this and remark on the benefits and detriments of this thought.You are approached to compose...
-
Asset allocation explains a large portion of a portfolio return. However, the implementation issues involved inthe asset allocation process may reduce the efficiency of the asset allocation strategy,...
-
Liza needs to buy a textbook for the next economics class. The price at the college bookstore is $65. One online site offers it for $55 and another site, for $57. All prices include sales tax. The...
-
Gardenia Co. and Lantana Co. both operate in the same industry. Gardenia began its operations in 2014 with a $20 million initial investment in plant and equipment with an expected life of 10 years....
-
What is a budget committee? What purpose does it serve? R-4
-
Colin Davis Machine Company maintains a general ledger account for each class of inventory, debiting such accounts for increases during the period and crediting them for decreases. The transactions...
-
Ecker Company reports $ 2 , 7 0 0 , 0 0 0 of net income and declares $ 3 8 8 , 0 2 0 of cash dividends on its preferred stock for the year. At year - end, the company had 6 7 8 , 0 0 0 weighted -...
-
Consider the following relations for a mini world of credit). COMPANY. Answer all queries in the language asked for (alternate language gets no EMPLOYEE (FName, LName, SSN, Bdate, Address, Sex,...
-
Question 04. Data Case for Chapter 12: Estimating the Cost of Capital You work in Walt Disney Company's corporate finance and treasury department and have just been assigned to the team estimating...
-
Q1 The company adopted the following depreciation policy for its non-current assets: Information: Useful Life Residual Value Building 40 Nil Equipment 10 10% of cost price On 1 December 20X2, the...
-
1. A debt of $7000 is to be amortized with 6 equal semiannual payments. If the interest rate is 11%, compounded semiannually, what is the size of each payment? (Round your answer to the nearest...
-
To complete this key resonance structure showing the effect OCH3 on OH Draw key resonance structure. Which symbol / word is needed? 1 OH 2 6 H3C 8- 8+ no .. -bond stable unstable ne A B C D E F G H I...
-
Read/Review the following article. Use the AD-AS model to explain the current macroeconomics issue we are facing today: inflation. Your graph can be hand or computer drawn. Make sure to label the...
-
A company makes a single product and has hired you as a consultant to help management select which alternative course of action they should take. What is the purpose of the company hiring you as a...
-
Chapter: Wireless Networking Using any diagram software , design A WELL DESIGNED AND DETAILED computer lab layout for todays university. The lab must be perfect, sophisticated and equipped with...
-
When is the indirect pattern appropriate, and what are the benefits of using it?
-
Calculate profitability and asset management ratios using sales and receivables data.} - Because sales revenue is such a key component of a company's success, analysts are interested in a large...
-
When is revenue generally considered earned?LO6
-
Explain the criteria for revenue recognition.LO3
Study smarter with the SolutionInn App