[SOLVED] DBS301 - LAB3

Description

Single-Line Functions

This week’s lab continues using the SELECT command and learning the interfaces for both SQL Developer and introduces the use of single-line functions.

Getting Started

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 L03_ID#_LASTNAME.sql

Your submission needs to be commented and include the question, the solutions, and the results.  An example is provided!

Tasks

1. Write a query to display the tomorrow’s date in the following format:
   September 15th of year 2019
   the result will depend on the day when you RUN/EXECUTE this query.  Label the column “Tomorrow”.

   Advanced Option (BONUS):  Define an SQL variable called “tomorrow”, assign it a value of tomorrow’s date, use it in an SQL statement.  Don’t forget to undefine it!

2. For each employee in departments 20, 50 and 60 display last name, first name, salary, and salary increased by 4% and expressed as a whole number. Label the column “Good Salary”.
   Also add a column that subtracts the old salary from the new salary and multiplies by 12. Label the column “Annual Pay Increase”.
3. Write a query that displays the employee’s Full Name and Job Title in the following format:
   DAVIES, CURTIS is ST_CLERK
   Only employees whose last name ends with S and first name starts with C or K.  Give this column an appropriate label like Person and Job.  Sort the result by the employees’ last names.
4. For each employee hired before 2012, display the employee’s last name, hire date and calculate the number of YEARS between TODAY and the date the employee was hired.
   1. Label the column Years worked.
   2. Order your results by the number of years employed. Round the number of years employed up to the closest whole number.
5. Create a query that displays the city names, country codes and state province names, but only for those cities that starts with S and has at least 8 characters in their name. If city does not have a province name assigned, then put Unknown Province. Be cautious of case sensitivity!
6. Display each employee’s last name, hire date, and salary review date, which is the first Thursday after a year of service, but only for those hired after 2017.
   1. Label the column REVIEW DAY.
   2. Format the dates to appear in the format like:
      WEDNESDAY, SEPTEMBER the Eighteenth of year 2019
   3. Sort by review date