Oracle Database 12c Administrator Certified Associate: SQL Competency (Intermediate Level)

Topics covered

• add a comment to a table and query the data dictionary view for comment information
• create a new table and insert rows by combining CREATE TABLE and AS in a SQL statement
• define constraints such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK
• demonstrate how the expressions in the SELECT lists of queries must match in number in a SQL statement
• demonstrate illegal queries when using the GROUP BY function in a SQL statement
• demonstrate when to use the FOR UPDATE and LOCK table statements
• describe and use the arithmetic expressions in SQL
• describe a statement level rollback
• eliminate duplicate rows from SQL statement output using the DISTINCT qualifier
• identify how NULL values are defined in SQL and how they affect arithmetic expression outcomes
• provide an overview of data dictionary views
• use a left, right, and full outer join in a SQL statement
• use self-joins using the ON clause
• use the CASE expression to facilitate a conditional inquiry in a SQL statement
• use the COMMIT and ROLLBACK statements
• use the IN, ANY, and ALL operators in multiple-row subqueries in a SQL statement
• use the NULLIF and COALESCE functions when working with NULL values in a SQL statement
• use the ROW_LIMITING_CLAUSE to limit the number of rows returned by a SQL statement
• use the TO_CHAR function to convert a number to a character data type in a SQL statement
• use UNION and UNION ALL operators to return all rows in a SQL statement
• using group functions and the HAVING clause in a subquery
• work with NULL values when grouping a set of rows in a SQL statement