Oracle代写 | OPIM 5272 – Business Process Modeling and Data Management

这个作业是使用Oracle SQL Developer开发和创建查询
UNIVERSITY OF CONNECTICUT
School of Business
OPIM 5272 – Business Process Modeling and Data Management
Spring 2020 – Homework 3
Instructions:
Please submit individually. Please use Oracle SQL Developer to develop and create queries. Use tables with C_ tables.
1. Write a SQL query that outputs the manager id, manager’s full name (First name + Last name), and the number
of employees managed within a department by each manager from the C_Employees table. Label the columns
appropriately. Also, order by Manager’s Name. Note, you DO NOT WANT TO SHOW a row with a null
department or manager information. Output should look like this: (10 points)
2. Analyze Products through Product Description and Order Items table, and display minimum and maximum unit
price of products in shown format, which have ‘spnix’ or ‘screws’ in their produce names. Also, order by
products with minimum price in descending order. Analyze the result and identify Product name with minimum
unit price and a product name with maximum unit price. (10 points)
3. Write a SQL query that output showing first name, last name, department id, department name of employees
that are earning highest salaries in that department. Also, display employees from lowest to highest salary (15
points)
4. Write a SQL query that will output the department name and the maximum salary of the employee in that
department. Consider only those departments whose salary is at least equal to the average salary of the “IT”
department. Also, order by maximum rounded salary. (20 points) (Hint: First write inner query by joining
employees and departments table and calculate average salary of IT department. Main query by joining
employees and departments table)
5. Display a report to executives with the country name, city, state province and number of departments that has
at least 2 working employees. (10 points) (Hint: First write a subquery to find departments that has at least 2
working employees and use that in main query to find country, city and state province)
6. Write a SQL query that will display only one value: the number of distinct managers that appear in the
employees table. The count should not include a NULL manager. (5 points)
7. Write a SQL query to report employee name (first name + last name), job title, start date, and end date of past
jobs of all employees with commission percentage null. (10 points) (Hint: You will have to use C_JOB_HISTORY
table for evaluating employees previous jobs)
8. Write a SQL statement which copies all of the data in the ORDERS table into a new table that is called
orders_copy. But, only copy those rows with orders that has been placed between 2004 and 2007. Also alter the
table by writing the SQL statement that will add a primary key constraint which will be called order_pk_2, which
adds the Order ID column as the primary key for the newly created table.
Next, Update the orders_copy table and set the PROMOTIOIN_ID to 1 for all the orders having the order that has
placed through ‘online’ mode. In addition, update the sales_rep_id on the orders_copy table with sales_rep_id of
order 2454 for the orders that has placed through ‘online’ mode and sales_rep_id doesn’t exists. (Write the query
using a subquery). (20 points)