Oracle Database Sql Certified Associate · Free Practice Question Medium
Question 26
View and examine the following scenario.
Given: There are employees who receive more salary than employee with employee number 6.
Required: Create a report which will display the name ( first name and last name ) for those employees who receive more salary than the employee with employee number 6.
View and examine the following available responses. Identify the SQL queries which would give you the required output. (Select two.)
-
A
- SELECT first_name, last_name
- FROM employees
- WHERE salary > (
- SELECT salary
- FROM employees
- WHERE employee_id = IN (6)
- );
-
B
- SELECT first_name, last_name
- FROM employees
- WHERE salary > (
- SELECT salary
- WHERE employee_id = (6)
- );
-
C
- SELECT first_name, last_name
- FROM employees
- WHERE salary >
- (SELECT salary
- FROM employees
- WHERE employee_id = 6);
-
D
- SELECT first_name, last_name
- FROM employees
- WHERE salary > (
- SELECT salary
- FROM employees
- WHERE employee_id IN (6)
- );
-
E
- SELECT first_name, last_name
- FROM employees
- WHERE salary > (
- SELECT salary
- WHERE employee_id = 6
- );
Reveal correct answers
Correct answers: C, D
Explanation
The two correct SQL queries that will return the required output are:
- SELECT first_name, last_name
- FROM employees
- WHERE salary >
- (SELECT salary
- FROM employees
- WHERE employee_id = 6);
- SELECT first_name, last_name
- FROM employees
- WHERE salary > (
- SELECT salary
- FROM employees
- WHERE employee_id IN (6)
- );
Why these work:
Both use a subquery to retrieve the salary of the employee with
employee_id = 6.The outer query then compares each employee’s salary to that value.
employee_id IN (6)is functionally equivalent to= 6in this context, thoughINis more flexible for multiple values.
Why the others fail:
employee_id = IN (6)is invalid syntax.SELECT salary WHERE employee_id = 6is missing theFROMclause — also invalid.employee_id = (6)is syntactically incorrect unless used in a specific context with a subquery.
The Oracle WHERE clause is used to filter the results from a SELECT, INSERT, UPDATE, or DELETE statement.
Syntax
The syntax for the WHERE clause in Oracle/PLSQL is:
- WHERE conditions;
Parameters or Arguments
conditions - The conditions that must be met for records to be selected.
The Oracle IN condition is used to help reduce the need to use multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.
Syntax
The syntax for the IN condition in Oracle/PLSQL is:
- expression IN (value1, value2, ... value_n);
OR
- expression IN (subquery);
Parameters or Arguments
expression - The value to test.
value1, value2, ... value_n - The values to test against expression. subquery This is a SELECT statement whose result set will be tested against expression. If any of these values matches expression, then the IN condition will evaluate to true.
Note
The Oracle IN condition will return the records where expression is value1, value2..., or value_n.
The Oracle IN condition is also called the Oracle IN operator.
A. This query has a syntax error as the IN operator is used incorrectly in the subquery. It should be used with a list of values, not with a single value like in this case.
B. This query has a syntax error as the IN operator is used incorrectly in the subquery. It should be used with a list of values, not with a single value like in this case. Additionally, the parentheses around the value 6 are unnecessary.
C. This query correctly retrieves the first name and last name of employees who receive a salary greater than the employee with employee number 6 by using a subquery to compare the salary values.
D.
This query correctly retrieves the first name and last name of employees who receive a salary greater than the employee with employee number 6 by using a subquery with the IN operator to compare the salary values.
E. This query has a syntax error as the SELECT statement within the subquery is missing the FROM clause. It should specify the table from which to select the salary value.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
