For each question below: read carefully what is being asked and create an SQL Query which answers the question.
others
Description
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. You must not use LIKE in this query.
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: Europe & Switzerland
= Eurland
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.
