Question

You want to create a report that includes each employee's last name, employee identification number, date of hire, and salary. The report should include only those employees who have been with the company for more than one year and whose salary exceeds $40,000. Which of the following SELECT statements will accomplish this task?

A. SELECT last_name, employee_id, hire_date, salary FROM employees WHERE (SYSDATE - hire_date) > 365 AND salary > 40000;
B. SELECT last_name, employee_id, hire_date, salary FROM employees WHERE (CURRENT_DATE - hire_date) > 365 AND salary > 40000;
C. SELECT last_name, employee_id, hire_date, salary FROM employees WHERE (DATEDIFF(CURRENT_DATE, hire_date)) > 365 AND salary > 40000;
D. SELECT last_name, employee_id, hire_date, salary FROM employees WHERE (TO_DATE(SYSDATE) - hire_date) > 365 AND salary > 40000;

Answer 1

The correct SQL query to generate a report for employees with more than one year at a company and a salary over $40,000 is Option C, which uses DATEDIFF to calculate the time difference and includes a salary check.

Step-by-step explanation:

The student is asking for assistance in constructing an SQL query to generate a report from a database. The report must include each employee's last name, employee identification number, hire date, and salary, but only for those employees who have been with the company for more than one year and have a salary exceeding $40,000.

To create this report correctly, an understanding of SQL syntax and date functions specific to the SQL dialect in use is necessary.The correct SQL statement for this scenario is:

C. SELECT last_name, employee_id, hire_date, salary FROM employees WHERE (DATEDIFF(CURRENT_DATE, hire_date)) > 365 AND salary > 40000;

This statement uses the DATEDIFF function to calculate the difference in days between the current date and the hire date. If the difference is greater than 365 days, it indicates that the employee has been with the company for more than one year. The condition salary > 40000 ensures that only those employees with a salary exceeding $40,000 are included in the report.