Welcome to the ultimate resource for preparing for the Oracle Database SQL Exam (1Z0-071)! Whether you are gearing up for the certification exam or aiming to master SQL fundamentals, this repository is your comprehensive guide covering all exam topics and SQL concepts required for the Oracle Database SQL Associate certification.
This repository contains structured learning materials, practice exercises, examples, and resources to help you master Oracle SQL and successfully pass the 1Z0-071 certification exam. The content is organized to align perfectly with the official exam topics and provides in-depth coverage of all required skills.
| Aspect | Details |
|---|---|
| Exam Name | Oracle Database SQL |
| Exam Code | 1Z0-071 |
| Format | Multiple Choice Questions (MCQ) |
| Duration | 120 minutes |
| Number of Questions | 63 |
| Passing Score | 63% |
| Validated For | Oracle Database 11.2.0.1.0 and up to 19c |
| Certification Title | Oracle Database SQL Certified Associate |
This repository is structured to cover all exam topics in a logical progression:
- Explaining the theoretical and physical aspects of a relational database
- Relating clauses in SQL Select Statement to Components of an ERD
- Explaining the relationship between a database and SQL
- Using Column aliases
- Using The SQL SELECT statement
- Using concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
- Using Arithmetic expressions and NULL values in the SELECT statement
- Applying Rules of precedence for operators in an expression
- Limiting Rows Returned in a SQL Statement
- Using Substitution Variables
- Using the DEFINE and VERIFY commands
- Sorting Data
- Manipulating strings with character functions in SQL SELECT and WHERE clauses
- Performing arithmetic with date data
- Manipulating numbers with the ROUND, TRUNC and MOD functions
- Manipulating dates with the date function
- Applying the NVL, NULLIF, and COALESCE functions to data
- Understanding implicit and explicit data type conversion
- Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
- Nesting multiple functions
- Restricting Group Results
- Creating Groups of Data
- Using Group Functions
- Using Self-joins
- Using Various Types of Joins
- Using Non equijoins
- Using OUTER joins
- Understanding and Using Cartesian Products
- Using Single Row Subqueries
- Using Multiple Row Subqueries
- Update and delete rows using correlated subqueries
- Matching the SELECT statements
- Using the ORDER BY clause in set operations
- Using The INTERSECT operator
- Using The MINUS operator
- Using The UNION and UNION ALL operators
- Managing Database Transactions
- Controlling transactions
- Perform Insert, Update and Delete operations
- Performing multi table Inserts
- Performing Merge statements
- Managing Indexes
- Managing Synonyms
- Managing Sequences
- Describing and Working with Tables
- Describing and Working with Columns and Data Types
- Creating tables
- Dropping columns and setting column UNUSED
- Truncating tables
- Creating and using Temporary Tables
- Creating and using external tables
- Managing Constraints
The repository also includes several key reference documents:
- concepts.md: Comprehensive explanation of relational database concepts
- database_schema.md: Details about the sample database schema used in examples
- learning_path.md: Suggested learning path for exam preparation
- select-basics.md: Fundamental concepts of SQL SELECT statements
1Z0-071_Oracle_Database_SQL_Associate/
├── 001 - Relational Database Concepts/
│ └── All Topics Covered
├── 002 - Retrieving Data using the SQL SELECT Statement/
│ └── All Topics Covered
├── 003 - Restricting and Sorting Data/
│ └── All Topics Covered
├── 004 - Using Single-Row Functions to Customize Output/
│ └── All Topics Covered
├── 005 - Using Conversion Functions and Conditional Expressions/
│ └── All Topics Covered
├── 006 - Reporting Aggregated Data Using Group Functions/
│ └── All Topics Covered
├── 007 - Displaying Data from Multiple Tables/
│ └── All Topics Covered
├── 008 - Using Subqueries to Solve Queries/
│ └── All Topics Covered
├── 009 - Using SET Operators/
│ └── All Topics Covered
├── 010 - Managing Tables using DML statements/
│ └── All Topics Covered
├── 011 - Managing Indexes Synonyms and Sequences/
│ └── All Topics Covered
├── 012 - Use DDL to manage tables and their relationships/
│ └── All Topics Covered
├── CONTRIBUTING.md
├── LICENSE
├── concepts.md
├── database_schema.md
├── learning_path.md
└── select-basics.md
- Start with the Fundamentals: Begin with the "001 - Relational Database Concepts" folder to build a solid foundation.
- Follow the Learning Path: Use the learning_path.md file as a guide for a structured learning approach.
- Practice Regularly: Each topic folder contains examples and practice exercises. Complete them to reinforce your understanding.
- Review Key Concepts: Refer to the supplementary markdown files (concepts.md, database_schema.md, etc.) for deeper understanding.
- Test Your Knowledge: After studying each section, test yourself with the practice questions and exercises.
To practice the SQL examples in this repository, you'll need:
- Oracle Database: Install Oracle Database (11g or later) or use Oracle Live SQL (https://livesql.oracle.com/)
- SQL Developer or SQL*Plus: For executing SQL commands
- Sample Schema: Import the sample schema provided in the database_schema.md file
Contributions to improve this repository are welcome! Please see the CONTRIBUTING.md file for guidelines on how to contribute.
Ways you can contribute:
- Add detailed explanations for complex topics
- Create new examples that demonstrate SQL concepts
- Develop practice exercises with solutions
- Design visual aids like diagrams or flowcharts
- Fix bugs in SQL scripts or example code
- Improve documentation clarity
This repository is released under the Unlicense. This means you are free to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the content without any restrictions.
- Oracle Database SQL Certified Associate Certification
- Oracle Database Documentation
- Oracle Live SQL
- Oracle Learning Library
Special thanks to all contributors who have helped make this repository a valuable resource for Oracle SQL certification candidates.
Good luck with your Oracle Database SQL Associate certification journey! 🎓
Last updated: June 2025