Be prepared to identify the correct usage the optional ESCAPE parameter when you want Oracle to interpret literally a wildcard character within the expression. In the expression, specify the escape character (for example, \) followed by the wildcard character that you want Oracle to interpret literally. Then specify the escape character in the ESCAPE parameter itself. For example, the following query returns the job IDs and job titles for jobs that have a job ID that begins with the characters “FI_”.

SELECT job_id, job_title
FROM jobs
WHERE job_id LIKE ‘FI\_%’ ESCAPE ‘\’;

The following example explains how to use the ROUND and TRUNC functions to round or truncate DATE values to the nearest day. Notice that the ROUND and TRUNC functions accept a format model element (for example, DAY) as their second argument; if you omit this argument, these functions round and truncate dates to the nearest day, respectively. Note that you can use the SQL functions ROUND and TRUNC with DATE values only; you cannot use these functions with TIMESTAMP, TSTZ, or TSLTZ values.

SELECT
ROUND(TO_DATE(’23-AUG-99:12:12′,’DD-MON-RR:HH24:MI’), ‘DAY’) AS result,
TRUNC(TO_DATE(’23-AUG-99:12:12′,’DD-MON-RR:HH24:MI’), ‘DAY’) AS result
FROM dual;

The only valid logical operators in an outer join condition are = and AND.

The exam will most likely include two or more questions that test your knowledge of less frequently used non-equijoins. A non-equijoin is useful when no corresponding columns exist between related tables, but rather a relationship exists between two columns having compatible datatypes. A non-equijoin is a query that uses a join condition with something other than the equality operator, including the BETWEEN, <, <=, >, or >= operators. More specifically, the result set of a non-equijoin of two tables A and B includes rows in which the join attribute from A falls within a specified range of values that originate from B. Be familiar with using both older and newer non-equijoin syntax. The course Join Related Data includes an exercise with the following query that demonstrates how to build a non-equijoin using the older syntax.

SELECT e.last_name, e.hire_date
FROM hr.employees e, hr.job_history h
WHERE e.hire_date BETWEEN h.start_date AND h.end_date
AND h.employee_id = 122;

The following query uses the new join syntax

SELECT e.last_name, e.hire_date
FROM hr.employees e JOIN hr.job_history h
ON (e.hire_date BETWEEN h.start_date AND h.end_date)
AND h.employee_id = 122;

SELECT e.last_name, e.hire_date
FROM hr.employees e JOIN hr.job_history h
ON (e.hire_date >= h.start_date AND e.hire_date <= h.end_date)
AND h.employee_id = 122;
A natural join of two tables that do not have matching columns is equivalent to a cross join of the tables – in other words, it produces a Cartesian product.

Beware that questions on the OCP exams do not classify a join that uses the USING clause as a natural join – they only consider a natural join as a query that uses the NATURAL keyword.

The exam will likely include many scenario-based questions that ask you to identify joins that meet specific criteria. Be prepared to identify equivalent joins that use both older or newer syntax.
 For example, the following four queries produce equivalent result sets.

SELECT i.order_id,
i.line_item_id AS item_id,
p.product_name AS product,
i.quantity AS quantity
FROM order_items i, product_information p
WHERE p.product_id = i.product_id;

SELECT order_id,
line_item_id AS item_id,
product_name AS product,
quantity
FROM order_items i
INNER JOIN product_information p
ON p.product_id = i.product_id;

SELECT order_id,
line_item_id AS item_id,
product_name AS product,
quantity
FROM order_items
INNER JOIN product_information
USING (product_id);
SELECT order_id,
line_item_id AS item_id,
product_name AS product,
quantity
FROM order_items
NATURAL JOIN product_information;

Some of the joins that are test question answers are long and complicated queries that can consume a lot of valuable time to analyze line-by-line. To save time, you can often rule out certain answers quickly by looking for joins with invalid syntax. For example, look for joins that use a condition in the USING clause [for example, USING (p.product_id = l.product_id)], a column list in the ON clause [for example, ON (product_id)], qualified column names in the USING clause [for example, USING(p.product_id)], invalid combinations of the ON and USING clauses, invalid combinations of the NATURAL keyword and an ON or USING clause, etc.

Be careful when analyzing natural joins of two tables that have two or more matching columns. As our course Join Related Data discusses, if you want to join such tables on a subset of matching columns, you must use the USING clause rather than the NATURAL keyword.