Skip to main content
Ace Skills Consulting
Oracle logoBeginner

1Z0-071: Oracle Database SQL

Write accurate, efficient SQL on Oracle Database and prepare thoroughly for the 1Z0-071 exam.

  • 40 hours of live, trainer-led sessions
  • Hands-on practice in a live Oracle Database
  • Joins, subqueries, set operators and analytic basics
  • DDL, DML, transactions and schema objects
  • Practice questions with explained answers
Aligned to Oracle exam 1Z0-071
Duration
5 Days | 40 Hrs
Next batch
Batches on request
Delivery
Live Online · Onsite · Corporate
Level
Beginner
Exam
1Z0-071
Language
English

Why learn 1Z0-071 with Ace Skills

  • SQL done properly

    Learn exactly how Oracle evaluates queries so results are never a surprise.

  • Constant practice

    Write and run queries throughout every session on a live database.

  • Exam precision

    Practise the edge cases — NULLs, conversions, grouping — that the exam tests.

  • Career foundation

    Build SQL skills that apply to analytics, development and database administration.

Course overview

SQL is the most durable skill in data work, and Oracle Database remains central to banking, telecom, government and ERP systems across India and the Gulf. This five-day course teaches SQL thoroughly on Oracle, with the precision the 1Z0-071 exam demands.

Days one and two cover retrieving data: the SELECT statement, filtering and sorting, substitution variables, single-row functions for characters, numbers and dates, conversion functions and conditional expressions, followed by group functions, GROUP BY and HAVING. Every topic is practised immediately on a sample schema.

Day three focuses on combining data with joins of every type, subqueries — single-row, multi-row and correlated — and set operators. Day four turns to changing data and structures: INSERT, UPDATE, DELETE and MERGE, transaction control, creating and altering tables, constraints, views, sequences, synonyms and indexes, and the data dictionary.

Day five covers user access and privileges, managing data across time zones, an introduction to analytic functions, and an exam readiness session with timed practice and a review of the question styles that catch candidates out.

This course is aligned to the Oracle Database SQL exam, 1Z0-071. Ace Skills is not an Oracle partner and does not use official courseware. Please confirm the current exam details on Oracle University before booking.

Key features

  • Live Oracle Database environment
  • Hundreds of graded practice queries
  • Clear coverage of NULL handling and implicit conversion
  • Joins, subqueries and set operators in depth
  • Schema objects and data dictionary labs
  • Timed practice questions with explanations
  • Course completion certificate from Ace Skills Consulting
  • Post-training doubt support

Who should attend

  • Aspiring database developers and administrators
  • Data and business analysts
  • Application developers working with Oracle
  • ERP and reporting professionals
  • Graduates starting a career in data

Prerequisites

  • Basic computer literacy
  • Familiarity with tables and spreadsheets
  • No prior SQL experience required

Curriculum

5 Days · 40 Hours · 15 Modules · 10 Labs

  1. Relational database concepts

    • Tables, rows and keys
    • Entity relationships
    • SQL statement categories
    • Tools: SQL Developer and SQL*Plus

    SELECT basics

    • Selecting columns and expressions
    • Column aliases and concatenation
    • DISTINCT
    • NULL handling
    • Lab: Query the sample HR schema · 40 min

    Restricting and sorting

    • WHERE conditions
    • Comparison and logical operators
    • LIKE, IN, BETWEEN
    • ORDER BY
    • Row limiting with FETCH
    • Substitution variables
    • Lab: Filter and sort employee data · 45 min

  2. Single-row functions

    • Character functions
    • Number functions
    • Date functions and arithmetic
    • Nesting functions
    • Lab: Format and calculate with single-row functions · 40 min

    Conversion and conditional expressions

    • Implicit and explicit conversion
    • TO_CHAR, TO_DATE, TO_NUMBER
    • NVL, NVL2, NULLIF, COALESCE
    • CASE and DECODE

    Aggregating data

    • Group functions
    • GROUP BY
    • HAVING
    • Nesting group functions
    • Lab: Departmental summary report with GROUP BY and HAVING · 45 min

  3. Joins

    • Equijoins and non-equijoins
    • Self-joins
    • Outer joins
    • Natural joins and USING
    • Cartesian products
    • Lab: Combine data from four tables · 50 min

    Subqueries

    • Single-row subqueries
    • Multi-row subqueries
    • Correlated subqueries
    • EXISTS and NOT EXISTS
    • NULLs in subqueries
    • Lab: Answer business questions with subqueries · 50 min

    Set operators

    • UNION and UNION ALL
    • INTERSECT and MINUS
    • Matching columns and ordering results

  4. Data manipulation

    • INSERT, UPDATE and DELETE
    • MERGE
    • Multi-table inserts
    • Transactions: COMMIT, ROLLBACK, SAVEPOINT
    • Read consistency
    • Lab: Apply and roll back data changes · 40 min

    Tables and constraints

    • Data types
    • CREATE and ALTER TABLE
    • Constraints
    • Dropping, truncating and flashback basics
    • Lab: Build a schema with constraints · 45 min

    Other schema objects

    • Views
    • Sequences
    • Synonyms
    • Indexes
    • Data dictionary views
    • Lab: Create views, sequences and indexes · 35 min

  5. Controlling access

    • System and object privileges
    • Roles
    • GRANT and REVOKE

    Advanced topics

    • Time zones and TIMESTAMP types
    • Interval data types
    • Introduction to analytic functions
    • Hierarchical query overview

    Exam readiness

    • Exam topic review
    • Common traps
    • Timed practice questions with review
    • Lab: Timed practice set with explanations · 90 min
Download full syllabus (PDF)

Skills you will gain

  • SQL
  • Oracle Database
  • SELECT queries
  • Single-row functions
  • Aggregation
  • Joins
  • Subqueries
  • Set operators
  • DML
  • DDL
  • Constraints
  • Views & indexes
  • Privileges

Tools & technologies

  • Oracle Database
  • Oracle SQL Developer
  • SQL*Plus
  • Oracle Live SQL

Certification exam

Exam details
Exam nameOracle Database SQL Certified Associate
Exam code1Z0-071
Voucher includedNo — book directly with the vendor

Skill areas measured

  1. Relational database concepts
  2. Retrieving data using SELECT
  3. Restricting and sorting data
  4. Using single-row functions
  5. Using conversion functions and conditional expressions
  6. Reporting aggregated data using group functions
  7. Displaying data from multiple tables
  8. Using subqueries
  9. Using set operators
  10. Managing tables using DML statements
  11. Managing schema objects and indexes
  12. Controlling user access

Exam details are pending verification. Vendors change exams; confirm on the official vendor page before booking. Official exam page (opens in a new tab)

Schedule & Pricing

Live Online

Individual

Price on request

  • 40 hours of live, trainer-led sessions
  • 10 hands-on labs
  • Ace Skills course completion certificate

Onsite

At your office or our centre

Pricing depends on location, group size and dates.

  • Delivered at your premises or an Ace centre
  • Same curriculum and labs as live online
  • Dates planned around your team

Corporate / Team

Private batch for your organisation

A private batch with content adapted to your environment and goals.

  • Customised curriculum
  • Private batch, online or onsite
  • Progress and assessment reports
  • GST invoice and PO-based billing

No public batches scheduled — Talk to an Advisor for a private batch.

Projects & labs you will build

  • Day 2 · 45 min

    Departmental summary report with GROUP BY and HAVING

  • Day 3 · 50 min

    Answer business questions with subqueries

Your certificate

Certificate of Completion — Ace Skills Consulting

Awarded on completing the course. It records the course title and training hours, and you can share it on your LinkedIn profile.

The Oracle certification itself is awarded by Oracle when you pass exam 1Z0-071.

Frequently asked questions

Do I need any SQL experience?

No. The course starts from relational basics, but the pace increases quickly, so daily practice helps.

Is the exam only about Oracle?

It uses Oracle Database syntax and behaviour, but most of what you learn applies to other SQL databases.

Will I have a database to practise on?

Yes. You get access to a live Oracle Database during the course and guidance for free practice options afterwards.

Is this an official Oracle course?

No. It is an independent Ace Skills course aligned to the 1Z0-071 exam topics.

Related courses

Explore more

All Oracle, Java, Salesforce & ServiceNow courses Oracle certifications

Oracle and Java are registered trademarks of Oracle and/or its affiliates.

Train your team on 1Z0-071

Private batches online or at your office in India and the GCC, with a curriculum adapted to your environment.