Description
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 2014 and 2015.
• Full name should be in the form Lastname, Firstname with an alias called Full
Name.
• 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<st,nd,rd,th> of 2016] with the
heading Start Date. Do NOT use LIKE operator.
DBS301 – Database Design II and SQL using Oracle Assignment 1
2 | P a g e
• <st,nd,rd,th> 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:
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 $5,000 – $10,000
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:
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:
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 $6000 – $18000.
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.
DBS301 – Database Design II and SQL using Oracle Assignment 1
3 | P a g e
• 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 25 characters.
• The output lines should look like this sample line:
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
25 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.
DBS301 – Database Design II and SQL using Oracle Assignment 1
4 | P a g e
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