Question

You need to provide a list of the first and last names of all employees who work in the Sales department who earned a bonus and had sales over $50,000. The company president would like the sales listed starting with the highest amount first. The EMPLOYEES table and the SALES_DEPT table contain the following columns:

EMPLOYEES
EMP_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPTARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(10)
SALES_DEPT
SALES_ID NUMBER(10) PRIMARY KEY
SALES NUMBER(20)
QUOTA NUMBER(20)
MANAGER VARCHAR2(30)
BONUS NUMBER(10)
EMPLOYEE_ID NUMBER(10) FOREIGN KEY
Which SELECT statement will accomplish this task?
a.SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, , s. sales
WHERE e.employee_id = s.employee_id
FROM employees e, sales_dept s AND IS NOT NULL AND sales > 50000
ORDER BY sales DESC;
b.SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, ,
FROM employees e, sales_dept s
ORDER BY sales DESC
WHERE e.employee_id = s.employee_id AND sales > 50000 AND IS NOT NULL;
c.SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, , s. sales
FROM employees e, sales_dept s
WHERE e.employee_id = s.employee_id AND IS NOT NULL AND sales > 50000
ORDER BY sales DESC;
d.SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, , s. sales
ORDER BY sales DESC
FROM employees e, sales_dept s
WHERE e.employee_id = s.employee_id AND IS NOT NULL AND sales > 50000;

Answer 1

The correct SELECT statement is option C, which selects the required columns from the employees and sales_dept tables, matches the employee IDs, filters based on sales and bonus requirements, and orders the results in descending order of sales.

Step-by-step explanation:

The correct SELECT statement to accomplish this task is option C:

SELECT e.employee_id, e.last_name, e.first_name, s.sales

FROM employees e, sales_dept s

WHERE e.employee_id = s.employee_id AND sales > 50000 AND bonus IS NOT NULL

ORDER BY sales DESC;

This query selects the required columns from the employees and sales_dept tables, matches the employee IDs, filters based on sales and bonus requirements, and orders the results in descending order of sales.