Which two statements will return the names of the three employees with the lowest salaries?
A: This statement is correct. It orders the employees by salary and fetches the first 3 rows.
B: This statement has a typo with 'FETCE' and 'RONS' which should be 'FETCH' and 'ROWS'. Hence, it will not execute successfully.
C: This statement will not execute successfully due to syntactical errors and incorrect use of the ORDER BY clause.
D: This statement is correct. It uses a subquery to order employees by salary and then limits the results to the first 3 using the ROWNUM pseudo-column.
E: This statement will not execute successfully because ROWNUM is evaluated before the ORDER BY clause, so it would return the first 3 rows based on the table's natural order, not the lowest salaries.
The behavior of ROWNUM and the FETCH FIRST syntax are explained in the Oracle Database SQL Language Reference 12c.
Which three queries execute successfully?
In Oracle SQL, date arithmetic can be performed directly, subtracting one date from another to get the number of days between them:
Option A:
SELECT (SYSDATE - DATE '2019-01-01') / 1 FROM DUAL; This query successfully calculates the difference between the current date and a specific date.
Option D:
SELECT SYSDATE - DATE '2019-01-01' - 1 FROM DUAL; This query subtracts a specific date from the current date and then subtracts 1 more day, which will execute successfully.
Option F:
SELECT SYSDATE - 1 - DATE '2019-01-01' FROM DUAL; Similar to D, this will successfully compute the date difference minus one day.
Options B, C, and E are incorrect because:
Option B and Option C: You cannot divide by a date or divide a date by a number.
Option E: You cannot subtract a date from a number.
Which three statements are true about inner and outer joins?
A: True. A full outer join does indeed return both matched and unmatched rows from both tables involved in the join. It combines the results of both left and right outer joins.
E: True. An inner join, by definition, returns rows that have matching values in both tables. Rows from both tables that do not match are not returned in an inner join result set.
Inner joins match rows from the joined tables based on the join condition, while outer joins include all rows from one or both tables regardless of whether a matching row exists in the other table.
Reference: The Oracle SQL documentation explains different types of joins, including inner joins, left and right outer joins, and full outer joins, clarifying how they differ in the result sets they produce.
Examine the description of the ORDER_ITEMS table:
Examine this incomplete query:
SELECT DISTINCT quantity * unit_price total_paid FROM order_items ORDER BY
Which two can replace
In a SELECT statement with DISTINCT, the ORDER BY clause can only order by expressions that are part of the SELECT list.
A . quantity alone is not sufficient to replace <clause> as it is not included in the SELECT list after DISTINCT.
B . This option can successfully replace <clause> because both quantity and unit_price are used in the SELECT expression, and thus their individual values are valid for the ORDER BY clause.
C . total_paid is an alias for the expression quantity * unit_price, but it cannot be used in the ORDER BY clause because Oracle does not allow aliases of expressions in DISTINCT queries to be used in ORDER BY.
D . product_id is not included in the SELECT list after DISTINCT and thus cannot be used in ORDER BY.
E . The expression quantity * unit_price is exactly what is selected, so it can replace <clause> and the query will complete successfully.
Oracle Database SQL Language Reference, 12c Release 1 (12.1): 'ORDER BY Clause'
Which four statements are true about constraints on Oracle tables?
C: True. A UNIQUE constraint in Oracle SQL allows for the inclusion of NULL values; specifically, it permits multiple NULLs in a column or set of columns but ensures that all non-NULL values are unique.
D: True. A PRIMARY KEY constraint can indeed be added to a table after it has been created and even after it has been populated, as long as the existing data does not violate the primary key constraint rules (i.e., all values must be unique and not NULL).
F: True. A UNIQUE constraint can utilize a pre-existing index on the columns it covers. If a suitable index already exists, Oracle can use this index to enforce the constraint, optimizing performance and resource utilization.
G: True. Columns that are part of a FOREIGN KEY constraint can contain NULL values. This is permissible under SQL standards and Oracle implementation, as a NULL foreign key value is considered to not refer to any row in the referenced table and thus does not violate referential integrity.
