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.
Tasks
- Write a query to display the tomorrow’s date in the following format:September 15th of year 2019the 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!
- 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_CLERKOnly 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 2012, 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:WEDNESDAY, SEPTEMBER the Eighteenth of year 2019
Sort by review date
Reviews
There are no reviews yet.