UUS Practice Tests⌕ Search
IT Certifications · 1Z0-171

Oracle AI Database SQL Associate Exam Practice Test

The Oracle AI Database SQL Associate Exam (1Z0-171) practice page focuses on reading and writing SQL for Oracle Database. The question bank samples relational query tasks such as filtering rows, sorting results, joining tables, combining query outputs, and using aggregate and string functions. It also touches on common schema changes, sequences, column definitions, and the WITH clause.

Exam overview

Exam providerOracle
Exam code1Z0-171
CategoryIT Certifications

About the Oracle AI Database SQL Associate Exam

SQL work requires matching a query’s structure to the result needed. Practice interpreting join behavior, including retaining rows from one side when no match exists, and distinguishing set operators that preserve duplicate rows. Build accuracy with WHERE conditions, ascending order, aggregate counts, string matching, and character-length expressions. For data-definition tasks, connect column types such as NUMBER precision and scale or VARCHAR2 length to the values a table can store, and recognize how a sequence supplies generated numeric values. The question set also samples common table-expression syntax through WITH. These skills support writing application queries, checking result sets, and making straightforward schema changes in Oracle environments. Pay attention to clause purpose and row behavior rather than memorizing isolated syntax, since small differences in join type, filtering, or set operation can change the returned data.

Sample Practice Questions

Review a selection from this exam's published US Practice Tests practice bank.

Question 1

Which query lists departments even when they have no matching employees, showing NULL for employee columns in those cases?

  1. ASELECT d.department_id, e.employee_id FROM departments d INNER JOIN employees e ON e.department_id = d.department_id
  2. BSELECT d.department_id, e.employee_id FROM departments d LEFT OUTER JOIN employees e ON e.department_id = d.department_id
  3. CSELECT d.department_id, e.employee_id FROM departments d CROSS JOIN employees e
  4. DSELECT d.department_id, e.employee_id FROM departments d JOIN employees e USING (employee_id)
Show answerCorrect answer: B

A left outer join preserves every row from the left table, DEPARTMENTS. When no employee matches a department, the selected columns from EMPLOYEES are NULL.

Question 2

In Oracle Database, which clause sorts query results by LAST_NAME in ascending order?

  1. AGROUP BY LAST_NAME
  2. BORDER BY LAST_NAME ASC
  3. CSORT BY LAST_NAME
  4. DHAVING LAST_NAME ASC
Show answerCorrect answer: B

ORDER BY specifies result ordering. ASC requests ascending order and is the default direction when omitted.

Question 3

Which query combines rows from ORDERS and RETURNS while retaining duplicate rows that occur in both results?

  1. ASELECT order_id FROM orders UNION SELECT order_id FROM returns
  2. BSELECT order_id FROM orders UNION ALL SELECT order_id FROM returns
  3. CSELECT order_id FROM orders MINUS SELECT order_id FROM returns
  4. DSELECT order_id FROM orders INTERSECT SELECT order_id FROM returns
Show answerCorrect answer: B

UNION ALL appends the results and preserves duplicates. UNION removes duplicates, while MINUS and INTERSECT return rows based on set differences or overlap.

Question 4

In Oracle Database, which statement creates a sequence named ORDER_SEQ that starts at 1 and increases by 1?

  1. ACREATE SEQUENCE ORDER_SEQ START WITH 1 INCREMENT BY 1;
  2. BCREATE COUNTER ORDER_SEQ BEGIN 1 STEP 1;
  3. CCREATE SEQUENCE ORDER_SEQ FIRST 1 NEXT 1;
  4. DCREATE TABLE ORDER_SEQ AS SEQUENCE START 1;
Show answerCorrect answer: A

CREATE SEQUENCE defines a sequence generator. START WITH sets its initial value, and INCREMENT BY specifies the change between generated values.

Question 5

Which Oracle SQL query returns the names of employees whose names begin with the letters 'Al'?

  1. ASELECT NAME FROM EMPLOYEES WHERE NAME LIKE 'Al%';
  2. BSELECT NAME FROM EMPLOYEES WHERE NAME = 'Al%';
  3. CSELECT NAME FROM EMPLOYEES WHERE NAME STARTS 'Al';
  4. DSELECT NAME FROM EMPLOYEES WHERE NAME LIKE '%Al';
Show answerCorrect answer: A

LIKE supports pattern matching; % matches any sequence of characters, including an empty sequence. Placing it after Al matches names beginning with Al.

Question 6

In Oracle SQL, what does COUNT(*) return for a query over a set of rows?

  1. AThe number of rows, including rows containing NULL values
  2. BThe number of distinct rows only
  3. CThe number of columns in the result
  4. DThe number of rows only when every column is non-NULL
Show answerCorrect answer: A

COUNT(*) counts every row in the selected set. Unlike COUNT(column), it does not exclude rows because a particular column is NULL.

Question 7

In Oracle SQL, which expression returns the number of characters in the string 'CAT'?

  1. ALENGTH('CAT')
  2. BCOUNT('CAT')
  3. CSIZE('CAT')
  4. DCHARCOUNT('CAT')
Show answerCorrect answer: A

LENGTH returns the number of characters in a character value, so LENGTH('CAT') returns 3. COUNT is an aggregate function, and SIZE and CHARCOUNT are not the relevant Oracle SQL functions here.

How to use this practice test

Access the free practice set to answer up to 20 randomly selected questions from the 50-question bank in a session. After submitting, review the results and revisit any missed SQL behavior, such as join row preservation, duplicate handling, or column-definition meaning.

Preparation tips

  • Write small sample queries to compare inner and outer joins, especially when one department has no matching employees; note which result columns become NULL.
  • Test UNION ALL against other set operations using overlapping result rows, and verify whether duplicates remain.
  • Practice SELECT clauses in combination: filter with WHERE, sort with ORDER BY, and check COUNT(*) and string expressions on sample data.
  • Review Oracle DDL examples for creating a sequence and adding a VARCHAR2 column; explain the role of NUMBER precision and scale in each table definition.
  • Rewrite a multi-step query using a WITH clause, then identify how its named query block is referenced by the main SELECT.
Official sourceOfficial exam page ↗