A Log of My Works: SQL Reports (11/16/2011)

This was one of my later SQL projects at Phoenix. All things considered, I liked working with SQL. The syntax is flexible and uncomplicated... even in Microsoft's implementation of it. The project instructions were as follows:

Sort all employees by payroll, in descending order.

Calculate the total payroll of exempt and non-exempt employees (before & after the adjustments are made).

Choose a specific job_title and decrease the salary of all individuals in this category by 3.5%.

Increase the remaining employee’s salaries by 5%.

Add a ‘Senior Accountant’ position to the Job_title table.

You discovered that the newly created position should not have been added to your database. As a result, this information needs to be deleted from the table.

The HR Department recently submitted a change request. They want the following information extracted from the database.

Calculate the number of exempt and non-exempt employees there are, by job title. The results should be sorted in descending order.

Determine if you have any employees whose salary is below the minimum salary for their job classification. If so, who are they?

Calculate the average, minimum and maximum salary for exempt employees. Using the UNION command also display the same information for the non-exempt employees.

Calculate the difference between the maximum and minimum salaries for the non-exempt employees.

Create a query that shows all employees whose name has the second letter of either an ‘a’,’e’,’i’,’r’ or ‘u’.

Using a CASE statement, illustrate:

Assuming that today’s date is 12/16/2020, create a temporary field that calculates the total service time (in years) of each employee. In the same query, use a CASE statement to identify employees who are over 52 years old and have over 20 years of service time with the company. Identify them as “Potential Retirees”.

1-A. Sorting all employees by payroll went as expected.

2-A. Total payroll of exempt and non-exempt employees was calculated without issue.

3. The decrease in pay for one title and increase for all others were handled as one query using a CASE statement. Initially using the CASE statement in the update did not work. I forgot to add the END statement. After doing so it ran without issues.

1-B. The same query as 1-A but after the update.

2-B. The same query as 2-A, with updated data.

4. The Senior accountant position was easy to add.

5. And just as easy to delete.

6. When I initially wrote this query, it was only grouped by job title. For reasons I did not understand, it did not work until the exempt-nonexempt status was included with the GROUP BY clause. According to the error message, it could not be included in the SELECT statement without doing so.

7. There are no employees whose salary is below the minimum.

8. The first time around when using AVG, MIN and MAX I mixed up which part of the statement goes in the parentheses. After rearranging everything, it worked.