Make a query that displays all employees whose phone number starts with “ 515 ”. Display the result as a single column, format the results as follows: “ Steven King can be contacted at 515.123.4567 ” You cannot use the LIKE keyword in this query. Question 2) Make a query that tells us the difference between the average minimum salary and average maximum salary in the Jobs table. Note: You do not have to format the number to show less decimal places. Example: If the average minimum salary was 5000 and the average maximum salary is 13000, then the result to display would be 8000. Question 3) Show all location’s whose postal code is exactly 5 characters long. Display the postal code and city name. When displaying the city name, remove all space characters. Question 4) Display all country names in the new format: The first 3 letters of the region name combined with the last 4 letters of the country name. Order by the new country name in alphabetical order. Example: Eur ope & Switzer land = Eur land 1 Question 5) Find all jobs where there is only 1 employee with that job. Display the job titles in ascending order. Note: You do not need the job_history table. Question 6) (3 marks) Show the count of employees who have the same job id. Include the average pay for that job and the last date someone was hired with that job. Order the result by the largest number of employees, then order by highest average pay. Note the row ordering below and the formatting of average pay. Make your report look the same. Question 7) (2 Marks) Display a count of all employees for each department. We only need to include employees with the Job ID of “IT_PROG” and “SA_REP”. You must show both the department id and department name beside the employee count. Filter out any results where the number of employees in a department was less than 3. 2 Assessment You will be assessed based on the rubric attached to your DropBox. Hand-in In this assignment you will create a new .SQL file using Oracle SQL Developer. Please rename your file to follow the format: assignment#_firstname_lastname.sql For Example: assignment5_scott_wachal.sql Use the appropriate LEARN DropBox to upload your file. If you need to upload a newer version, you may do so before the deadline, but only the newest copy will be graded by your instructor. 3

[embeddoc url=”https://www.assignmentguruh.com/wp-content/uploads/2020/12/20201207203558dbms_1002___assignment_5.pdf”]

DBMS-1002
Assignment 5
Instructions
For each question below: read carefully what is being asked and create an SQL Query which
answers the question. Note that it is sometimes possible to misinterpret the meaning of a
question. When in doubt, please clarify with your instructor before handing in your assignment.
Questions
Question 1)
Make a query that displays all employees whose phone number starts with “ 515 ”.
Display the result as a single column, format the results as follows:
“ Steven King can be contacted at 515.123.4567 ”
You cannot use the LIKE keyword in this query.
Question 2)
Make a query that tells us the difference between the average minimum salary and average
maximum salary in the Jobs table. Note: You do not have to format the number to show less
decimal places.
Example: If the average minimum salary was 5000 and the average maximum salary is 13000,
then the result to display would be 8000.
Question 3)
Show all location’s whose postal code is exactly 5 characters long.
Display the postal code and city name. When displaying the city name, remove all space
characters.
Question 4)
Display all country names in the new format: The first 3 letters of the region name combined with
the last 4 letters of the country name. Order by the new country name in alphabetical order.
Example: Eur ope & Switzer land = Eur land
1
Question 5)
Find all jobs where there is only 1 employee with that job. Display the job titles in ascending
order. Note: You do not need the job_history table.
Question 6) (3 marks)
Show the count of employees who have the same job id. Include the average pay for that job
and the last date someone was hired with that job.
Order the result by the largest number of employees, then order by highest average pay.
Note the row ordering below and the formatting of average pay. Make your report look the same.
Question 7) (2 Marks)
Display a count of all employees for each department. We only need to include employees with
the Job ID of “IT_PROG” and “SA_REP”. You must show both the department id and department
name beside the employee count. Filter out any results where the number of employees in a
department was less than 3.
2
Assessment
You will be assessed based on the rubric attached to your DropBox.
Hand-in
In this assignment you will create a new .SQL file using Oracle SQL Developer. Please rename
your file to follow the format: assignment#_firstname_lastname.sql
For Example: assignment5_scott_wachal.sql
Use the appropriate LEARN DropBox to upload your file. If you need to upload a newer version,
you may do so before the deadline, but only the newest copy will be graded by your instructor.
3