That concentration changes how you should prepare. Candidates who spread their time evenly across eleven domains end up underprepared for the one that carries 25% of the marks, while over-studying domains worth 5% each. This guide covers the full weighting, why functions dominate, and how to prepare for an exam whose blueprint is far more lopsided than its topic list suggests.
What Is the Oracle 1Z0-171 Certification?
Oracle 1Z0-171 is the Oracle AI Database SQL Associate certification. It validates practical SQL skills against Oracle Database — retrieving and sorting data, applying functions, joining tables, writing subqueries, managing objects with DDL and DML, and controlling user access. It is an associate-level credential aimed at developers, analysts, and administrators who work with Oracle SQL.
Despite the AI in the title, this is a SQL exam. The naming reflects Oracle’s current branding of its database platform rather than a syllabus full of machine learning content — the domains are conventional SQL topics, and you will not be asked to build a model.
| Attribute | Detail |
|---|---|
| Exam code | 1Z0-171 |
| Exam name | Oracle AI Database SQL Associate |
| Number of questions | 60 (multiple choice) |
| Duration | 120 minutes |
| Passing score | 65% |
| Price | $245 USD |
Two hundred minutes would be generous; 120 minutes for 60 questions gives exactly two minutes each, which is comfortable for SQL questions where you often need to read a statement carefully before answering. The 65% threshold means 39 correct out of 60, leaving 21 you can miss — genuinely forgiving compared with most vendor exams.
What Does the 1Z0-171 Exam Cover?
The exam spans eleven domains covering relational concepts, data retrieval, functions, joins, subqueries, set operators, DML, schema objects, DDL, user access, and data dictionary views. The weighting is highly uneven — three domains carry 55% between them and the remaining eight share the rest.
| Domain | Weight |
|---|---|
| Using Functions | 25% |
| Retrieving Data using SELECT & Sorting | 15% |
| DDL for Tables & Relationships | 15% |
| Displaying Data from Multiple Tables (joins) | 10% |
| Relational Database Concepts | 5% |
| Using Subqueries | 5% |
| Using SET Operators | 5% |
| Managing Tables using DML | 5% |
| Managing Indexes, Synonyms & Sequences | 5% |
| Controlling User Access | 5% |
| Data Dictionary Views & Time Zones | 5% |
Functions, SELECT and sorting, and DDL together account for 55% of the exam. Everything else — including subqueries and joins, which candidates often assume dominate a SQL exam — sits at 10% or below.
That distribution rewards a deliberately uneven study plan. Six of the eleven domains are worth 5% each, meaning roughly three questions apiece. Spending equal time on each of those as on the 25% functions domain is the most common way candidates misallocate their preparation here.
Why Do Functions Carry a Quarter of the Exam?
Using Functions is the largest 1Z0-171 domain at 25% — roughly 15 of the 60 questions — covering character, arithmetic, and date functions, ROUND, TRUNC and MOD, the NULL-handling family, data type conversion, group functions, and nested functions. It dominates because functions are where SQL stops being declarative and starts requiring precision.
The NULL-handling functions deserve first attention. NVL, NULLIF, and COALESCE each behave differently, and questions frequently hinge on knowing exactly which returns what when a NULL is involved. NULL semantics are the classic source of SQL errors in production and a reliable source of exam questions.
Conversion functions are the second concentration. TO_CHAR, TO_NUMBER, and TO_DATE with their format models appear repeatedly, and date formatting in particular generates questions because the format masks are easy to misread. Implicit versus explicit conversion is worth understanding rather than assuming.
Group functions bring their own trap. Aggregates ignore NULLs, GROUP BY has strict rules about which columns may appear in the SELECT list, and HAVING filters groups where WHERE filters rows. Confusing WHERE and HAVING is among the most common mistakes on this exam. The Oracle SQL language reference is the authoritative source for exact function behaviour, and it is worth checking edge cases there rather than relying on memory.
What SELECT and Sorting Knowledge Is Tested?
Retrieving Data using SELECT and Sorting is 15% of the exam, covering column aliases, concatenation, DISTINCT, arithmetic expressions, NULL handling, operator precedence, row limiting, substitution variables, and ORDER BY. It is foundational content, but the questions target precision rather than recognition.
Operator precedence is the standout topic. When an expression mixes arithmetic and comparison operators, or combines AND with OR, the evaluation order determines the result — and questions are frequently constructed so that a plausible-looking answer is wrong precisely because someone assumed left-to-right evaluation.
NULL behaviour appears here too, and it is worth internalising early because it recurs across domains. Arithmetic involving NULL yields NULL, concatenation treats it differently, and comparisons against NULL never return true. Those three facts resolve a surprising proportion of questions.
Row limiting is the modern addition worth knowing explicitly. Oracle’s FETCH FIRST syntax is the current approach to limiting result sets, and candidates who learned Oracle SQL years ago may reach for ROWNUM out of habit. Both appear in the wild; know which the exam expects.
Why Is DDL Worth 15%?
DDL for Tables and Relationships is 15% of the exam, covering CREATE TABLE, column management, TRUNCATE, temporary and external tables, constraints, and views. It is the third-largest domain, and it surprises candidates who assume an associate-level SQL exam concentrates on querying.
Constraints carry much of this domain. Primary keys, foreign keys, unique, check, and NOT NULL constraints each have distinct behaviour, and questions often describe an attempted operation and ask whether a constraint would block it. Understanding referential integrity — what happens to child rows when a parent is deleted — is core examinable content.
Views appear alongside tables and are more nuanced than they first look. Whether a view is updatable depends on how it was defined, and questions probe that boundary. The distinction between a simple view over one table and a complex view involving joins or aggregation matters here.
TRUNCATE versus DELETE is a perennial favourite. They appear similar and differ substantially: DELETE is DML and can be rolled back and filtered with WHERE, while TRUNCATE is DDL, removes all rows, and cannot be rolled back in the same way. Knowing which is appropriate — and what each does to storage — is reliably tested.
How Are Joins, Subqueries, and Set Operators Tested?
Joins are 10% of the exam, with subqueries and set operators at 5% each — 20% between them. That is less than many candidates expect, but these domains still reward precision, and the questions tend to be conceptually harder than their weighting suggests.
On joins, know the full range and their behaviour with unmatched rows. Inner joins, left, right, and full outer joins, self-joins, non-equijoins, and Cartesian products all appear explicitly. The examinable skill is usually predicting how many rows a described join returns, particularly when NULLs or unmatched rows are involved.
Subqueries split into single-row, multiple-row, and correlated forms. The correlated case is the conceptually demanding one, because the inner query references the outer and executes per row. Understanding when a correlated subquery is necessary — and recognising one from its structure — is what this 5% is really testing.
Set operators are narrow and highly learnable. UNION removes duplicates while UNION ALL does not, INTERSECT returns common rows, and MINUS returns the difference. The rules about column compatibility and where ORDER BY may appear in a set operation are the details questions target. At 5% these are cheap marks for an hour’s focused study, and the Oracle SQL quick reference is a useful summary for exactly this kind of syntax detail.
Who Should Take the 1Z0-171 Exam?
This certification suits application developers, data analysts, database administrators, and anyone whose work involves querying Oracle databases. There are no formal prerequisites, and the associate level is genuinely reachable for someone with a few months of practical SQL experience.
Developers are the largest audience. If you write SQL inside applications, this exam formalises knowledge you mostly hold and fills the gaps — typically in DDL, constraints, and the less familiar function families that day-to-day application queries never require.
Analysts arriving from other SQL dialects face a specific challenge worth naming. SQL is standardised in principle and divergent in practice, and Oracle-specific behaviour around date handling, conversion functions, and NULL semantics differs enough from other platforms to generate wrong answers based on habits from elsewhere.
The third group is professionals building toward broader data credentials, for whom SQL competence is foundational. Anyone who has worked through platform-level data certifications — the Oracle certification guide covers a comparable fundamentals credential — will find this a natural complement, since SQL underpins nearly all of them.
How Should You Prepare for 1Z0-171?
Effective 1Z0-171 preparation is deliberately uneven: give a quarter of your time to functions, because functions are a quarter of the exam. Most candidates need four to eight weeks. Writing queries against a real Oracle database matters far more than reading about syntax.
| Phase | Focus | Goal |
|---|---|---|
| 1 | SELECT, sorting, operator precedence, NULL behaviour | Predict a result set without running the query |
| 2 | Functions — character, date, conversion, NULL family, groups | The 25% domain; know exact behaviour, not approximate |
| 3 | DDL — CREATE TABLE, constraints, views, TRUNCATE vs DELETE | Predict whether a constraint blocks an operation |
| 4 | Joins, subqueries, set operators | Predict row counts including unmatched rows |
| 5 | DML, indexes, sequences, privileges, dictionary views | Bank the six 5% domains cheaply |
| 6 | Timed practice across all eleven domains | Consistent accuracy at two minutes per question |
Phase two is where the exam is won or lost. Fifteen questions ride on functions, and the difference between knowing that NVL exists and knowing exactly what it returns for each argument combination is the difference between a comfortable pass and a marginal one.
Run the queries rather than reading them. SQL is unusually well suited to hands-on learning because you get immediate, unambiguous feedback — write a query that should return five rows, and the database tells you instantly whether your understanding was right. Grounding this in the Oracle database concepts guide helps connect the syntax to what the engine is actually doing.
Then test under time. Working through a realistic 1Z0-171 practice exam at two minutes per question builds the habit of reading a statement carefully but efficiently. Pay particular attention to any question you answered from another SQL dialect’s behaviour — that is exactly where Oracle-specific detail catches people out.
Is the Oracle SQL Associate Worth Earning?
1Z0-171 is worth most to professionals working in Oracle environments and to those who want a recognised, vendor-backed credential proving SQL competence. Its value comes from formalising a skill that is widely claimed and unevenly held.
The practical argument is that SQL competence is assumed on far more job descriptions than it is verified. A vendor certification provides evidence rather than assertion, which matters most for candidates whose experience is self-taught or whose CV lacks obvious database-heavy roles.
It also functions as a foundation. Oracle’s database certification track builds upward from SQL fundamentals toward administration and development specialisations, so passing this establishes a base for deeper credentials. The current structure is published on Oracle’s certification path overview.
The honest limitation is that SQL knowledge is portable while the credential is Oracle-branded. If you work across PostgreSQL, SQL Server, and Oracle, much of what you learn transfers even though the certificate names one vendor. That is a reasonable trade — the skills outlast the branding — but it is worth being clear-eyed that this certifies Oracle SQL specifically.
Frequently Asked Questions
How many questions are on the Oracle 1Z0-171 exam?
The exam contains 60 multiple-choice questions with a 120-minute limit, giving exactly two minutes per question. That pacing is comfortable for SQL questions, which often require reading a statement carefully before the answer becomes clear.
What is the passing score for 1Z0-171?
The passing score is 65%, meaning 39 correct answers out of 60. That leaves 21 questions you can miss, which is more forgiving than most vendor certifications. The uneven domain weighting still means functions cannot be neglected.
How much does the exam cost?
The exam costs $245 USD. Third-party practice test products are priced separately and cost considerably less, so do not confuse the two when budgeting for the certification.
Does 1Z0-171 involve artificial intelligence content?
No. Despite “AI Database” in the title, the syllabus covers conventional SQL topics — SELECT statements, functions, joins, subqueries, DDL, and DML. The naming reflects Oracle’s current database branding rather than machine learning content in the exam.
Which domain is most heavily weighted?
Using Functions at 25% is by far the largest, worth roughly 15 of the 60 questions. It covers character, date, and conversion functions, the NULL-handling family, and group functions. Six other domains are worth only 5% each.
Do you need prior Oracle experience?
Not necessarily, but Oracle-specific behaviour matters. Candidates coming from other SQL dialects should pay attention to date handling, conversion functions, and NULL semantics, where habits from PostgreSQL or SQL Server can produce wrong answers.
What is the difference between TRUNCATE and DELETE?
DELETE is DML, can be filtered with WHERE, and participates in transactions so it can be rolled back. TRUNCATE is DDL, removes all rows, and cannot be rolled back the same way. The distinction and its storage implications are reliably examinable.
How important are joins on this exam?
Less than most candidates expect. Joins are 10% of the exam, with subqueries and set operators at 5% each. They still require precision — particularly predicting row counts with unmatched rows and NULLs — but functions and DDL carry considerably more weight.
How long should you study for 1Z0-171?
Four to eight weeks suits most candidates with some SQL exposure. Weight your time toward functions, SELECT and sorting, and DDL, which together are 55% of the exam, and practise by running queries rather than only reading syntax.
Conclusion
1Z0-171 looks like an evenly spread SQL exam and is nothing of the sort. Functions alone are 25%, and with SELECT/sorting and DDL they account for 55% of the marks, while six separate domains are worth 5% apiece. Study in those proportions rather than working through the syllabus list at equal pace.
Learn NULL behaviour and conversion functions to genuine precision, understand constraints well enough to predict whether an operation will fail, and run every query rather than reading it. Sixty questions, 120 minutes, 65% to pass — a forgiving threshold on an exam whose blueprint tells you exactly where to concentrate.
