Oracle SQL study guide
Oracle SQL · Work through your exam, one topic at a time.
The Oracle Database SQL Certified Associate credential - commonly referenced by its exam code, 1Z0-071 - is an entry-level certification that validates a candidate's ability to write and reason about SQL against an Oracle Database. It is aimed at developers, junior DBAs, data analysts, report writers, and students who need to prove they can retrieve, filter, transform, aggregate, and modify data using Oracle's SQL dialect rather than a generic one. The Oracle SQL study guide has 10 sections and about 3,057 words.
01Relational Concepts and Basic SELECT Retrieval
A relational table holds rows over named columns. A primary key enforces uniqueness and non-nullness; a foreign key references a parent key and enforces referential integrity.
321 words, about 2 min02Restricting and Sorting Rows
The WHERE clause keeps rows that satisfy a predicate. Comparison operators include = , < / != , , = , < , <= .
308 words, about 2 min03Single-Row Functions
Single-row functions run once per row and return one value per row. Learn the families, not every variant.
302 words, about 2 min04Conversion Functions and Conditional Expressions
Explicit conversion is safer and is what questions reward. TO CHAR(value, 'format') turns numbers or dates into strings; TO DATE(string, 'format') parses; TO NUMBER(string) parses numerics.
301 words, about 2 min05Aggregating with Group Functions
Group functions ( COUNT , SUM , AVG , MIN , MAX , also STDDEV , VARIANCE ) collapse many rows to one value. Every one of them ignores NULLs - except COUNT( ) , which counts rows regardless of content.
292 words, about 1 min06Displaying Data from Multiple Tables
An inner join returns only matching rows. Outer joins preserve unmatched rows from one side ( LEFT , RIGHT ) or both ( FULL ), filling the other side with NULLs.
310 words, about 2 min07Using Subqueries to Solve Queries
A subquery is a SELECT nested inside another statement. Single-row subqueries return one row and use = , < , .
314 words, about 2 min08Using Set Operators
UNION combines two result sets and removes duplicates. UNION ALL keeps every row including duplicates and is the cheapest option.
313 words, about 2 min09Managing Data with DML
INSERT adds rows, either with a VALUES list, with & substitution variables, or by copying from a subquery (the column count must match). UPDATE changes existing rows and may take a subquery in SET or WHERE.
288 words, about 1 min10DDL and Schema Objects
CREATE TABLE defines columns with data types ( VARCHAR2 , NUMBER , DATE , CHAR , TIMESTAMP ) and constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY with ON DELETE CASCADE or SET NULL , and CHECK. ALTER TABLE adds, modifies, or drops columns and constraints.
308 words, about 2 min