Oracle SQL Certification 1Z0-071 Course | Section 3: Relational Database concepts

Before writing SQL queries, it is essential to understand how data is structured, stored, and related inside a relational database. Many Oracle SQL 1Z0-071 exam questions assume this knowledge and test your ability to reason about data models, relationships, and database objects rather than just syntax.

This section introduces the theoretical foundation behind relational databases and explains how Oracle implements these concepts in practice.

Video 1: Relational Databases and ERDs

A relational database stores data in tables made up of rows and columns. Each table represents an entity, such as employees or departments, and each column represents an attribute of that entity.

Key concepts covered include:

• What a relational database is
• How tables represent entities
• The difference between rows and columns
• Attributes versus entities
• The purpose of primary keys and foreign keys
• How keys enforce data integrity

Relationships between tables are explained using real world examples:

• One to One, such as country and capital
• One to Many, such as departments and employees
• Many to Many, such as authors and books

Entity Relationship Diagrams, or ERDs, are introduced as visual representations of these relationships. ERDs are commonly used during database design and may appear in certification exam questions.

A practical walkthrough shows how to generate an ERD directly from the HR schema using Oracle SQL Developer. This demonstrates how theoretical concepts map to real database structures, including how multiple employees can share the same job and how foreign keys link tables together.

Video 2: SQL and SQL Statement Types

SQL, or Structured Query Language, is the language used to communicate with relational databases. It is considered a fourth generation language because it focuses on what data is needed rather than how to retrieve it.

This lesson introduces the main categories of SQL statements used in Oracle and tested in the exam.

Data Definition Language, or DDL, is used to define and modify database structures. Common commands include CREATE, ALTER, DROP, TRUNCATE, RENAME, and COMMENT. DDL statements perform an implicit commit, meaning changes are saved immediately and cannot be rolled back.

Data Manipulation Language, or DML, is used to work with data inside tables. This includes SELECT, INSERT, UPDATE, DELETE, and MERGE. DML changes are not committed automatically. They remain visible only in the current session until a COMMIT is issued, or undone using ROLLBACK.

Data Control Language, or DCL, is used to control access to database objects by granting or managing privileges.

Understanding the difference between implicit and explicit commits is critical for the exam. Many questions test transaction behavior and data visibility across sessions.

Video 3: Oracle SQL vs ANSI SQL

ANSI SQL is the industry standard specification for SQL. All major database systems support it, including Oracle, MySQL, PostgreSQL, and SQL Server.

Oracle SQL fully supports ANSI SQL but also includes Oracle specific extensions and legacy syntax. One example is the old outer join syntax using the plus sign, which appears in the WHERE clause instead of the FROM clause.

For the exam, the key points are:

• Differences between Oracle SQL and ANSI SQL are not tested directly
• Most exam questions use ANSI JOIN syntax
• Oracle specific syntax is common in real world Oracle environments

Focusing on ANSI SQL joins ensures compatibility with the exam and with other database systems in the future.

Video 4: What Is a Schema

A schema is a logical container that holds database objects such as tables, views, and sequences. In Oracle, a schema is owned by a user and has the same name as that user.

Although closely related, a user account and a schema are not the same:

• The user account is used to log in
• The schema is the collection of objects owned by that user

The HR user owns the HR schema, which contains tables like EMPLOYEES and DEPARTMENTS. Other users can be granted access to these tables, but ownership remains with the HR schema.

This concept is important for understanding permissions, fully qualified object names, and exam questions involving multiple users.

Video 5: Database Objects

Database objects are the structural components of an Oracle database. This lesson focuses only on objects that are relevant for the 1Z0-071 exam.

The key objects you must understand include:

• Tables, which store data
• Indexes, which improve search performance
• Views, which present data through stored queries
• Sequences, which generate numeric values
• Synonyms, which act as aliases for objects
• Constraints, which enforce data rules
• Users, who own schemas
• Roles, which group privileges

Advanced objects such as PL/SQL packages and Java stored procedures are not tested and are intentionally excluded.

Video 6: Database Object Naming Rules

Oracle object naming rules are one of the most frequently tested topics in the exam and often appear as trick questions.

Important rules include:

• Object names are up to 30 characters for exam purposes
• Names must start with a letter
• Allowed characters include letters, numbers, underscore, dollar sign, and hash
• Names are not case sensitive by default
• Reserved words cannot be used as object names

Quoted identifiers allow spaces, case sensitivity, and special characters, but require exact referencing. These are discouraged in real projects but are exam relevant.

Namespace rules are also important. Some object types share namespaces while others do not. For example, a table and a view cannot share the same name, but a table and an index can.

Video 7: Oracle DUAL Table

The DUAL table is a special system table that exists in every Oracle database. It contains exactly one row and one column.

Oracle requires a FROM clause when selecting expressions, constants, or functions. The DUAL table fulfills this requirement.

Common uses include:

• Retrieving system values such as SYSDATE
• Performing calculations
• Returning constants or expressions

The optimizer does not actually scan the DUAL table, so there is no performance impact. The DUAL table frequently appears in exam questions and should be well understood.

Section Outcome

After completing this section, you should:

• Understand how relational databases organize data
• Be comfortable reading ERDs and identifying relationships
• Know the purpose and behavior of different SQL statement types
• Understand schemas, database objects, and naming rules
• Recognize exam relevant concepts such as commits, namespaces, and DUAL

These fundamentals are essential for every SQL topic that follows and form the conceptual backbone of the Oracle SQL 1Z0-071 certification exam.

So, since you’re already involved in this SQL journey, you can subscribe to the YouTube channel and maybe subscribe to newsletter below?