DBS301 – Assignment 1 Solved

$ 29.99
Category:

Description

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.
Create a new Worksheet in SQL Developer. Save the file as A1_ID1#_ID2#.sql
Only one submission per group please.
Group Work
This assignment is to be completed in groups of 3 or 4. Please only one submission per group. The comment header MUST have ALL names and student numbers.
VERY IMPORTANT:
Being part of a group is the same as being a part of a team for these assignments. When you submitted your work as part of a group you are saying that:
• you understood what was submitted and that you fully participated with ALL the group members.
• It does not mean letting others do your work for you.
• It does not mean watching the others do the work.
• For your full participation, you get a mark equal to all the others in the group.
• If on the test, which is very much like the assignment, you cannot answer it strongly indicates that you didn’t participate and understand the assignment but depended on others for the mark you received. That is very much like submitting their work and claiming it is your work.
Tasks
• Full name should be in the form “Lastname, Firstname” with an alias called “Full Name”.
• <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 (4 columns):

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,000 – $11,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 (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 $6000 – $17000. 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 25 characters.
• The output lines should look like this sample line (4 columns):
Jonathon Taylor SA_REP ======= $8,600 80

8. “Tricky One”
• 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 24 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: #########
— 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

Reviews

There are no reviews yet.

Be the first to review “DBS301 – Assignment 1 Solved”

Your email address will not be published. Required fields are marked *