Can you please answer these 1 and 2 , 3 questions by using oracle live sql now Schema used for assigmentent: OE Use the

[answerhappygod]

Can you please answer these 1 and 2 , 3 questions by usingoracle live sql now
Schema used for assigmentent: OE
Use the script below to explore the schema’s tables. Ensuremaximum row preference is set to 5000 to obtain a fullerunderstanding of each table.
SELECT * FROM oe.categories_tab;
SELECT * FROM oe.customers;
SELECT * FROM oe.orders;
SELECT * FROM oe.order_items;
SELECT * FROM oe.product_information;
SELECT all_cons_columns.owner AS schema_name, all_cons_columns.table_name, all_cons_columns.column_name, all_constraints.constraint_type, all_cons_columns.position, all_constraints.statusFROM all_constraints, all_cons_columns WHERE all_constraints.constraint_type IN ('P', 'R') AND all_cons_columns.owner = 'OE' AND all_constraints.constraint_name =all_cons_columns.constraint_name AND all_constraints.owner =all_cons_columns.ownerORDER BY all_cons_columns.owner, all_cons_columns.table_name, all_cons_columns.position;
For each question, write a query that solves for the specifictask. The columns in the output generated by your query should beordered in the same order observed in the screenshot accompanyingeach prompt. The screenshots are of result sets generated bycorrect, proposed solutions to each problem.
1. All sales representatives for the companyreport to Mary. She has recently mentioned that she would like tosee a report containing the number of orders placed by customersassisted by a sales representative in years 2007 and 2008. Thistask has been assigned to you. Ensure that the report is sorted byorder year (ascending), order count (ascending), and salesrepresentative ID (ascending).
SALES_REP_ID
ORDER_YEAR
ORDER_COUNT
156
2007
2
155
2007
3
158
2007
3
2. The company’s only supplier of operatingsystems is going out of business, leaving the company only a fewweeks to find a new supplier before stock levels hit zero. Thecompany wishes to replace the former supplier with a supplier theyalready do business with for closely related products. Here,closely related is defined as supplying products in any softwarecategory. Management wishes to receive a report containing allsuppliers currently supplying software products to the company. Inaddition to the list of suppliers, include the number of softwareproducts each supplier currently supplies. Do not include thesupplier going out of business, supplier ID 103092. Sort thesupplier list by the number of software products supplied,descending. HINT: category names starting with the word“software” satisfy the requirement of the supplier being closelyrelated.
SUPPLIER_ID
PRODUCT_COUNT
103089
10
103080
7
103093
6
103088
4
103086
3
103082
3
103094
2
103096
1
103087
1
103083
1
3. The company distributes rewards tocustomers based on the amount spent each year. Only the top 5customers are rewarded. Management requires a report containingcustomer IDs, customer names, order totals across all orders, andcustomer’s rank for the top 5 customers. Customer rank is based onthe sum of all order totals for the customer compared to all othercustomers’ sums. The customer with the largest amount spent for theyear is rank 1, second largest spent for the year is rank 2, and soon. Ties in customer yearly spending will receive the same rank.Use consecutive integers (e.g., 1, 1, 2, 3, 4, 4, 5). Only considerorders placed in year 2008 and format the customer’s name as “lastname, first name.” Alias the derived columns with meaningful names.Sort the result set by customer’s sum of order totals, de