Assignment

Submission

Your submission will be a single text-based SQL file with appropriate header and commenting. Please ensure your file runs when the entire file is executed in SQL Developer.

Tasks

1. Display the employee number, full employee name, job and hire date of all employees hired in May or November of any year, with the most recently hired employees displayed first.

Also, exclude people hired in 2015 and 2016.

Full name should be in the form "Lastname, Firstname" with an alias called "FullName".

Hire date should point to the last day in May or November of that year (NOT to the exact day) and be in the form of [May 31 of 2016] with the heading Start Date. Do NOT use LIKE operator.

means days that end in a 1, should have "st", days that end in a 2 should have "nd", days that end in a 3 should have "rd" and all others should have "th"

You should display ONE row per output line by limiting the width of the Full Name to 25 characters. The output lines should look like this line (4 columns):

174

Abel, Ellen

SA_REP

[May 31st of 2016]

2. List the employee number, full name, job and the modified salary for all employees whose monthly earning (without this increase) is outside the range $6,500 - $11,500 and who are employed as Vice Presidents or Managers (President is not counted here).

You should use Wild Card characters for this.

VP's will get 25% and managers 18% salary increase.

Sort the output by the top salaries (before this increase) firstly.

Heading will be like Employees with increased Pay

The output lines should look like this sample line (note: 1 column):

Emp# 124 named Kevin Mourgos who is ST_MAN will have a new salary of $6960

3. Display the employee last name, salary, job title and manager# of all employees not earning a commission OR if they work in the SALES department, but only if their total monthly salary with $1000 included bonus and commission (if earned) is greater than $15,000.

Let's assume that all employees receive this bonus.

If an employee does not have a manager, then display the word NONE

instead. This column should have an alias Manager#.

Display the Total annual salary as well in the form of $135,600.00 with the

heading Total Income. Sort the result so that best paid employees are shown first.

The output lines should look like this sample line (5 columns):

De Haan

17000

AD_VP

100

$216,000.00

4. Display Department_id, Job_id and the Lowest salary for this combination under the alias Lowest Dept/Job Pay, but only if that Lowest Pay falls in the range $6500 - $16800. Exclude people who work as some kind of Representative job from this query and departments IT and SALES as well.

Sort the output according to the Department_id and then by Job_id.

You MUST NOT use the Subquery method.

5. Display last_name, salary and job for all employees who earn more than all lowest paid employees per department outside the US locations.

Exclude President and Vice Presidents from this query.

Sort the output by job title ascending.

You need to use a Subquery and Joining.

6. Who are the employees (show last_name, salary and job) who work either in IT or MARKETING department and earn more than the worst paid person in the ACCOUNTING department.

Sort the output by the last name alphabetically.

You need to use ONLY the Subquery method (NO joins allowed).

7. Display alphabetically the full name, job, salary (formatted as a currency amount incl. thousand separator, but no decimals) and department number for each employee who earns less than the best paid unionized employee (i.e. not the president nor any manager nor any VP), and who work in either SALES or MARKETING department.

Full name should be displayed as Firstname Lastname and should have the heading Employee.

Salary should be left-padded with the = symbol till the width of 15 characters. It should have an alias Salary.

You should display ONE row per output line by limiting the width of the Employee to 24 characters.

The output lines should look like this sample line (4 columns):

Jonathon Taylor

SA_REP

======= $8,600

80

8. "Tricky One" Display department name, city and number of different jobs in each department. If city is null, you should print Not Assigned Yet.

This column should have alias City.

Column that shows # of different jobs in a department should have the heading # of Jobs

You should display ONE row per output line by limiting the width of the City to 22 characters.

You need to show complete situation from the EMPLOYEE point of view, meaning include also employees who work for NO department (but do NOT display empty departments) and from the CITY point of view meaning you need to display all cities without departments as well.

Example Submission

-- *********************** -- Name: Your Name -- ID: ######### -- Date: The current date -- Purpose: Assignment 1 - DBS301 -- *********************** -- Question 1 - write a brief note about what the question is asking -- Q1 SOLUTION -- SELECT * FROM TABLE; -- Question 2 - blah blah blah

REFERENCE - https://drive.google.com/drive/folders/1qPZUYN6fdFfO3OwqKQ9TU5gFTTN-4dcR?usp=sharing