Write a SQL query using a SET operator to show those job ids and titles that employees have not had as positions in the past

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

OPIM 5272 – Business Process Modeling and Data Management

Fall 2019

Homework 3 - SQL

1. Write a query to display the following report:

(the average salary is for all departments found in all countries. The country average salary is the average of salaries for each country, and the last column is the difference between the country average salary and the departmental average salary.) You can use any constructs or functions that apply.

 

2. Write a SQL query using a SET operator to show those job ids and titles that employees have not had as positions in the past. Your output should look as follows:

 

3. Write a SQL query to display what percentage of the employees salary for that job grade is from the total sum salaries that are on the employees table. Output should look as follows:

 

(if you run this against the newly populated rows in the c_customers table it will look like this, otherwise…)

(this will be the output if you are using the original employees table)
 

  

4. Please write a SQL query that show on average which job promotes people the fastest? Give the full name of the job and the average days employees spend in it. We assume this database only stores current employees. We will also assume the shortest average duration between start and end date on the job_history table is the job that promotes the quickest. You can display all 8 records, or add the where clause and display that one specific job.

Adding this where clause (WHERE rownum = 1) will show you the first row, like so:

** I did not cover this in class, but good to know and this is why I’m incorporating this. Rownum is a pseudocolumn which indicates the row number in a result set retrieved by a SQL query. We can use this to show the top or very first row.

 

5. Write a sql query to show the location ids and the counts of all departments that are within that location id. In addition, show the list of the department ids as an aggregated list. See output that I am expecting:

 

6. Do you think it is advisable to automate processes “as is” using workflow technology? Why or why not? In what cases might be useful, and in what cases might this be a waste of time. Give an example of each.

 

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of