Description
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
• Write a query to display the tomorrow’s date in the following format:
January 10th of year 2019
the result will depend on the day when you RUN/EXECUTE this query. Label the column
“Tomorrow”.
Advanced Option: 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!
• 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”.
• 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.
• For each employee hired before 1997, display the employee’s last name, hire date and
calculate the number of YEARS between TODAY and the date the employee was hired.
• Label the column Years worked.
• Order your results by the number of years employed. Round the number of
years employed up to the closest whole number.
• 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!
• 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.
• Label the column REVIEW DAY.
• Format the dates to appear in the format like:
THURSDAY, August the Thirty-First of year 2018
• Sort by review date
Example Submission
— ***********************
— Name: Your Name
— ID: #########
— Date: The current date
— Purpose: Lab 3 DBS301
— ***********************
— Question 1 – write a brief note about what the
question is asking
— Q1 SOLUTION —
SELECT * FROM TABLE;
— Question 2 – blah blah blah