View the Exhibit and examine the structure of the EMP table. You want to display the names and salaries of only those employees who earn the highest salaries in their departments. Which two SQL statements give the required output? (Choose two.)
A. SELECT ename, sal
FROM emp e
WHERE sal = (SELECT MAX(sal)
FROM emp
WHERE deptno = e.deptno);
B. SELECT ename, sal
FROM emp
WHERE sal = ALL (SELECT MAX(sal)
FROM emp
GROUP BY deptno);
C. SELECT ename, sal
FROM emp e
WHERE EXISTS (SELECT MAX(sal)
FROM emp WHERE deptno = e.deptno);
D. SELECT ename, sal
FROM emp
NATURAL JOIN (SELECT deptno, MAX(sal) sal
FROM emp
GROUP BY deptno);
Exhibits:
EMP table:
Theory:
Since all the queries are SELECT queries with subqueries, EXISTS and MAX functions, ALL operator and NATURAL join, the information needed to answer the question can be found as follows:
SELECT,
JOINS,
COMPARISON OPERATORS,
MAX and
EXISTS.
A
SUBQUERY or
INNER QUERY is a
SELECT statement that exists within another SQL statement (
QUTER QUERY). A subquery can be used within SELECT, INSERT, UPDATE and DELETE statements. A subquery is
non-correlated when it can be executed successfully on its own, whereas a
correlated subquery uses values from the outer query. Any valid SELECT statement can qualify as a subquery include those that retrieve data from one or more tables or views, those that use complex expressions or aggregate functions, and those that include the WHERE and GROUP BY clauses, HAVING or ORDER BY clauses, or any other other features available to SELECT statements.
EXISTS or IN operators tests for existence of rows in a subquery, creating a semi-join condition. A semi-join between two tables returns rows from the first table where one or more matches are found in the second table. The difference between a semi-join and a conventional join is that rows in the first table will be returned at most once. A semijoin returns rows that match an
EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery. Note that
EXISTS will not scan all rows in the subquery, only one row is required to determine whether the outcome is TRUE or FALSE. However, NOT EXISTS must scan all rows, which may cause performance problems.
MAX function returns maximum value of expression not taking NULL values in consideration.
MAX function returns
NULL value if there is no maximum value of the expression, which is different than
COUNT function that never returns NULL value as it returns a value of 0 if it encounters no values. The
NATURAL JOIN joins two tables which contain a column or multiple columns with the same name and data-type. Natural join uses also table reference as other regular inner or outer joins do, hence a table can be joined with a select query (inline view).
GROUP COMPARISON OPERATORS (
ANY,
SOME,
ALL) - specifie a comparison with any or all members in a list or subquery.
- ANY = SOME - compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Can be followed by any expression or subquery that returns one or more values. It evaluates to TRUE if at least value meets the comparison criteria, and evaluates to FALSE if the query returns no rows.
- ALL - compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Can be followed by any expression or subquery that returns one or more values. It evaluates to TRUE only if all values meet the comparison criteria, and evaluates to TRUE if the query returns no rows.
Answer:
A - Correct
B - Wrong - it would require a salary to be equal to the amount of maximum salary in each department at the same time, which is not true unless that amount would be identical for each department, but that's not stated in the question
C - Wrong - it would only check if there is any employee with salary in the department of the employee and would display all employees no matter if their salary is the highest or not.
D - Correct
0 comments: