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 first_name, last_name, annual_salary * bonus_pct
FROM employees, bonus NATURAL JOIN;
b.SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct
FROM employees, bonus
WHERE e.employee_id = b.employee_id;
c.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;
d.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;

Answer 1

The correct query to calculate each employee's bonus based on salary times bonus percentage is option d, which correctly joins the EMPLOYEES and BONUS tables on EMPLOYEE_ID and calculates the bonus by multiplying annual_salary by bonus_pct.

Step-by-step explanation:

To determine the amount of each employee's bonus as a calculation of salary times bonus percentage, the correct SQL query out of the provided options is:

d. 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 utilizes an explicit JOIN condition to combine rows from the EMPLOYEES and BONUS tables where the EMPLOYEE_ID matches in both tables. The product of annual_salary and bonus_pct for each employee is calculated using the multiplication operator (*).

Option a is incorrect because NATURAL JOIN does not require the specification of a joining condition, but it assumes the columns with the same name are used for joining, which is not true in this case since EMPLOYEE_ID is a VARCHAR2 in the BONUS table and NUMBER in the EMPLOYEES table.

Option b is incorrect as they only select the annual_salary and bonus_pct without computing their product.

Option c is similar to option b, and it doesn't calculate the actual bonus either.