Question

You have the following EMPLOYEES table:

EMPLOYEE_ID NUMBER(5) NOT NULL PRIMARY KEY
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
ADDRESS VARCHAR2(35)
CITY VARCHAR2(25)
STATE VARCHAR2(2)
ZIP NUMBER(9)
TELEPHONE NUMBER(10)
DEPARTMENT_ID NUMBER(5) NOT NULL FOREIGN KEY
The BONUS table includes the following columns:
BONUS_ID NUMBER(5) NOT NULL PRIMARY KEY
ANNUAL_SALARY NUMBER(10)
BONUS_PCT NUMBER(3, 2)
EMPLOYEE_ID VARCHAR2(5) NOT NULL FOREIGN KEY
You want to determine the amount of each employee's bonus as a calculation of salary times bonus. Which of the following queries should you issue?
a.SELECT e.first_name, e.last_name, b.annual_salary * b. bonus_pct
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id;
b.SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id;
c.SELECT first_name, last_name, annual_salary * bonus_pct
FROM employees, bonus NATURAL JOIN;
d.SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct
FROM employees, bonus
WHERE e.employee_id = b.employee_id;

Answer 1

The correct SQL query to calculate and select each employee's bonus amount is the one that multiplies the annual salary by the bonus percentage and filters the joined tables based on matching employee IDs.

Step-by-step explanation:

To determine the amount of each employee's bonus, which is the calculation of salary times bonus percentage, the correct SQL query must select the employee's name and the bonus calculation. Therefore, the appropriate SQL query is:

SELECT e.first_name, e.last_name, b.annual_salary * b.bonus_pct
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id;

This query will produce a result set with the first name, last name, and the calculated bonus amount for each employee.