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.

 

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. 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

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme