Oracle Database SQL Certified Associate 1Z0-071 Training

Course Content

Introduction

Course Agenda
System Development Life Cycle & Database Definition
What is a Table?
Data Models & ERM
About Primary key & foreign key
SQL and Types of SQL Statements
Oracle Database 12c Architecture
HR Schema in this Course

Live SQL & Download/ Install

Connecting to the Database

Retrieving data using the select statement

Restricting and Sorting Data

Substitution Variables

Using Single-Row Functions to Customize Output

Single Row Function Introduction
Character functions (Upper, Lower, Initcap )
Character functions (concat, substr, length )
Character functions ( instr )
Character functions ( lpad, rpad , replace, trim)
Number functions ( Round, Trunc, MOD )
Date Functions (Sysdate)
Date Functions ( months_between, add_months, next_day, last_day)
Date Functions ( round , trunc )
Nesting Functions

Using Conversion Functions and Conditional Expressions

Reporting Aggregated Data Using the Group Functions

Displaying Data from multiple tables using joins

Before You Start, important notes
HR Tables Diagram, Joins Clarification
What is Cartesian product?
Old Joins: Equijoin
Old Joins: nonEquijoins
Old Joins: outer join
Old Joins: Self Join and More Practices
1999 Syntax: Cross Join (Cartesian product )
1999 Syntax: Natural Join
1999 Syntax: USING Clause
1999 Syntax: ON Clause
1999 Syntax: Left/Right/full Outer Join

Using Subqueries to Solve Queries

Using the Set Operators

Managing tables using DML Statments

Introduction to DML
Insert Statement
Inserting common errors
Update Statement
Delete Statement
What is Database Transactions?
About Commit & Rollback
Practice ( Commit and rollback )
SAVEPOINT
ROW Lock
FOR UPDATE Clause

Introduction to Data Definition Language

DDL & Naming Rules
Data Types Part 1
Data Types Part 2
Creating Tables (without constraints)
Types of constraints and why we use it?
Creating Tables ( Column Level Constraints )
Creating Tables ( Table Level Constraints )
Constraints Guidelines
ON DELETE cascade / ON DELETE set null
Create Table AS subquery
Alter Table/ Add Columns
Alter Table/ Modify Columns
Alter Table/ Drop Columns
Alter Table/ Set Unused
ALTER TABLE READ ONLY / READ write
Drop Table
Rename Column / Rename Table

Tips you Should know

Exam 1Z0-061 / Oracle Database 12c: SQL Fundamentals

Introduction to Data Dictionary Views

Creating sequences, synonyms, and indexes

Creating Views

Managing Schema Objects

Retrieving Data By Using Subqueries

Manipulating Data By Using Subqueries

Controlling User Access

Manipulating Data

Managing Data in Different Time Zones

Exam 1Z0-071 for SQL Certified Associate

Bonus Section

Using SELECT statement to create dynamic scripts
Installing oracle 11g express edition
Rownum and rowid
About PL/SQL
Understanding NULLS when used with ( IN/ NOT IN )
permanent solution for ORA-01033 oracle initialization or shutdown in progress
DML logs using package dbms_errlog
oracle 18c express edition part 1 (download and install )
oracle 18c express edition part 2 (unlock hr )
oracle 18c express edition part 3 ( sql developer )

What next? it is time for PL/SQL

About Course

The Oracle Database SQL Certified Associate (1Z0-071) certification validates foundational SQL knowledge and skills required for working with Oracle Database. It covers essential SQL concepts such as querying, data manipulation, and schema objects. Aimed at database developers, data analysts, and administrators, it demonstrates proficiency in SQL fundamentals necessary for managing Oracle databases effectively. Certification enhances career prospects by validating SQL expertise and prepares professionals for roles involving database development, reporting, and data analysis using Oracle Database technology, ensuring they can efficiently retrieve, manipulate, and manage data to meet organizational requirements.

Management

Cloud Computing

Networking

Business Analysis

Database

CyberSecurity

Software Development

Human Resource

Sales and Marketing

Art

Culture

Literature

Welcome

Labore et dolore magna aliqua. Ut enim ad minim veniam

Find your desired course

Select Your Favourite Category And Start Learning.

Management

Cloud Computing

Networking

Business Analysis

Database

CyberSecurity

Software Development

Human Resource

Sales and Marketing

Art

Culture

Literature

Oracle Database SQL Certified Associate 1Z0-071 Training

All Levels

39h

183

Course Content

Introduction

Course Agenda

System Development Life Cycle & Database Definition

What is a Table?

Data Models & ERM

About Primary key & foreign key

SQL and Types of SQL Statements

Oracle Database 12c Architecture

HR Schema in this Course

Live SQL & Download/ Install

Oracle Live SQL / No Installation for anything

Oracle Live SQL limitation

before you install oracle DB

Downloading Oracle DB 12c

Installing Oracle DB 12c

About Oracle 18c express edition

About oracle 11g express edition

Connecting to the Database

Connecting SYS using SQL plus & SQL Developer

if you do not remember the sys password

Unlock HR Account Part 1

Unlock HR Account Part 2

ORA-01033 oracle initialization or shutdown in progress 12c

ORA-65054: Cannot open a pluggable database in the desired mode

Creating new pluggable database contains HR user

Retrieving data using the select statement

Navigate HR schema

Capabilities of SQL SELECT Statements

Arithmetic expressions and NULL values

Column Alias, Concatenation , Distinct and DESCRIBE

Important Notes in Select Statement

Restricting and Sorting Data

The WHERE Clause & Comparison Operators

Using Between and / IN / Like Operators

Using IS NULL / NOT / Not equal Operators

Logical Operators (AND/ OR/ NOT)

Order by Clause

The FETCH Clause

Substitution Variables

What is a Substitution Variables (&)

DEFINE / UNDEFINE

ACCEPT / PROMPT

Double-Ampersand / SET VERIFY / SET DEFINE

Using Single-Row Functions to Customize Output

Single Row Function Introduction

Character functions (Upper, Lower, Initcap )

Character functions (concat, substr, length )

Select Your Favourite
Category And Start Learning.