Need help with SQL homework, questions start under the problems section. This document is intended to relate

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)