Starting from:
$25
Execute the script file LargeCo_oralceScript.sql Solution
Execute the script file LargeCo_oralceScript.sql to create the tables with data for the schema depicted by the ERD above.
You can create a new Oracle instance for these tables or add these tables to an existing instance. You won't be
submitting the database. You will only be submitting SQL statements.
A: List the product sku, description, type, and price for each product that has the same description for the interior product
type and exterior product type. List the output by description (A to Z).
B: List all employees who work in the department with the most employees. List the employee name, title, and department. List the output by last name (A to Z).
(Do not hard code the department. Use a subquery to find the department with the most employees as this might change over time.)
C: List the highest paid employee in each department. List the employee name, department code, and salary. List the output by the department code (lowest to highest).
D: List all the products purchased that are the highest priced item for a brand. List the invoice number, the SKU, the quantity, the price, and the brand.
List the output by the SKU (A to Z).
E: List the name of all customers who do not have an invoice. List the customer code, the customer name, and customer address.
List the output by customer code with the largest customer code listed first.
F: List the name and invoice date of the customer (or customers) who have the oldest invoice. There may be one or more customer with the date.
List the customer's name and the invoice date. List the output by customer last name (A to Z).
G: List the average salary of employees. Make sure you average the current salaries only.
H: Calculate the current salary of all employees, the average of current salaries and display the difference between the two for each employee.
List the employee code, the employee name, the employee's current salary, the average of all current salaries, and the difference between
the current salary and the average salaray. List the output by current salary with the largest salary listed first.
I: Display the total quantity ordered of each product supplied by each vendor. List the output by vendor name (A to Z).
J: Find the largest line item on each invoice. Largest in this case means the largest total amount (quantity x price).
Display the SKU number, the invoice number, and the amount. List the output in order by invoice number with the lowest
invoice number first. Note: It is possible to have more than one "largest" line item on an invoice. This could occur if
two (or more) line items have the same total amount. For example, invoice 907 has items 7532-PY3 and 3754-MAK and both of
these have a total amount of 65.97. Therefore they both will appear in the query results. It is also possible to have
the same item more than once on the same invoice. These duplicate items may (like invoice 415) or may not
(like invoice 162) cause both items to appear in the results.
You can create a new Oracle instance for these tables or add these tables to an existing instance. You won't be
submitting the database. You will only be submitting SQL statements.
A: List the product sku, description, type, and price for each product that has the same description for the interior product
type and exterior product type. List the output by description (A to Z).
B: List all employees who work in the department with the most employees. List the employee name, title, and department. List the output by last name (A to Z).
(Do not hard code the department. Use a subquery to find the department with the most employees as this might change over time.)
C: List the highest paid employee in each department. List the employee name, department code, and salary. List the output by the department code (lowest to highest).
D: List all the products purchased that are the highest priced item for a brand. List the invoice number, the SKU, the quantity, the price, and the brand.
List the output by the SKU (A to Z).
E: List the name of all customers who do not have an invoice. List the customer code, the customer name, and customer address.
List the output by customer code with the largest customer code listed first.
F: List the name and invoice date of the customer (or customers) who have the oldest invoice. There may be one or more customer with the date.
List the customer's name and the invoice date. List the output by customer last name (A to Z).
G: List the average salary of employees. Make sure you average the current salaries only.
H: Calculate the current salary of all employees, the average of current salaries and display the difference between the two for each employee.
List the employee code, the employee name, the employee's current salary, the average of all current salaries, and the difference between
the current salary and the average salaray. List the output by current salary with the largest salary listed first.
I: Display the total quantity ordered of each product supplied by each vendor. List the output by vendor name (A to Z).
J: Find the largest line item on each invoice. Largest in this case means the largest total amount (quantity x price).
Display the SKU number, the invoice number, and the amount. List the output in order by invoice number with the lowest
invoice number first. Note: It is possible to have more than one "largest" line item on an invoice. This could occur if
two (or more) line items have the same total amount. For example, invoice 907 has items 7532-PY3 and 3754-MAK and both of
these have a total amount of 65.97. Therefore they both will appear in the query results. It is also possible to have
the same item more than once on the same invoice. These duplicate items may (like invoice 415) or may not
(like invoice 162) cause both items to appear in the results.
1 file (323.6KB)
