Lab 1 Week 1 (Getting Started)

Getting Started

Your submission will be a single text-based SQL file with your solutions included.

Your submission needs to include a comment header, follow the course style guide, and be adequately commented to clearly indicate the question and solution. Your file should be coded in a way that it is entirely executable at once.

Before you Start

Before you start this lab, we want to get the data up to date. So run the following 3 statements to update the data to a more current timeline:

UPDATE employees SET hire_date = hire_date + (20*365); UPDATE job_history SET end_date = end_date + (20*365); UPDATE job_history SET start_date = start_date + (20*365);

Tasks

After having downloaded the course sample database creation script, Write SQL to answer the following questions.

1. If the following SELECT statement does NOT execute successfully, how would you fix it (Answer in commented text) and then write the corrected statement (not commented)

SELECT last_name LName, job_id Job Title, Hire Date Job Start FROM employees;

2. Display the employee_id, last name and salary of employees earning in the range of $8,000 to $11,000. Sort the output by top salaries first and then by last name. Output the salary such that it looks like money (i.e. with a $ and 2 decimal places).

3. Write the solution for question 2 again with the salary being in a format appropriate to send to another software application.

4. Display the job titles and full names of employees whose first name contains an e or E anywhere. The output should look like: (BONUS MARK FOR NOT using the OR keyword in the solution but obtaining the same results) Job Title Full name --------------------------------------

AD_VP Neena Kochhar

more rows

5. Create a query to display the address of the various locations where offices are located. Add a parameter to the query such that the user can enter all, or part of, the city name and all locations from the resultant cities will be shown.

6. Write a query to display the tomorrows 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.

7. For each employee in departments 20, 50 and 60 display last name, first name, department name, salary, and salary increased by 4% and expressed as a whole number. Label the increased salary 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". Note: Salary in this database is stored as Monthly Salary see column descriptions in the database columns tab

8. For each employee hired before 2014, display the employees last name, hire date and calculate the number of YEARS between TODAY (not hard coded) and the date the employee was hired.

a. Label the column Years worked.

b. Order your results by the number of years employed. Round the number of years employed DOWN to the closest whole number.

9. 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!

10. Display each employees 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.

a. Label the column REVIEW DAY.

b. Format the dates to appear in the format like: WEDNESDAY, SEPTEMBER the Eighteenth of year 2019 Sort by review date