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

  • 22m
  • 22 questions
The SQL Competency benchmark measures whether a learner has had exposure to data analysis using SQL including tasks such as retrieving data, sorting and ordering the retrieved data, fetching data from multiple tables, grouping and aggregating data, applying functions and conditional expressions, and writing complex queries using subqueries. A learner who scores high on this benchmark demonstrates that they have essential data management and analysis skills.

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