Oracle Database 12c SQL Certified Associate 1Z0071

Become Oracle SQL Certified ( OCA ) / Covers ORACLE university track 100%. All the presentations & scripts are attached
Oracle Database 12c SQL Certified Associate 1Z0071
File Size :
8.93 GB
Total length :
19h 51m



Khaled Alkhudari


Last update




Oracle Database 12c SQL Certified Associate 1Z0071

What you’ll learn

The Full track of Oracle Database 12c: Introduction to SQL Ed 2.
You will be prepared 100% to pass exams Oracle Database SQL 1Z0-071 And also exam Oracle Database 12c: SQL Fundamentals 1Z0-061
Very strong introduction to make you understand the oracle database and RDBMS.
Oracle Database 12c Architecture
Downloading and installing oracle database 12c in details
Connecting to the Database
Retrieving data using the select statement / Restricting and Sorting Data
Substitution Variables
Using Single-Row Functions to Customize Output
Reporting Aggregated Data Using the Group Functions
Displaying Data from multiple tables using joins
Using Subqueries to Solve Queries
Using the Set Operators
Managing tables using DML Statments ( Insert, Update, Delete )
Data Definition Language / Creating tables
Data Dictionary Views
Creating sequences, synonyms, and indexes
Creating Views in details
Managing Schema Objects
Retrieving Data By Using Subqueries
Manipulating Data By Using Subqueries
Controlling User Access in details
Manipulating Data
Managing Data in Different Time Zones
Exam information / Exams Details / Exam Practice
many other information, you can see the full curriculum

Oracle Database 12c SQL Certified Associate 1Z0071


This course start from zero knowledge to expert level.
Windows Operating system is preferred, but this is not mandatory if you have the knowledge of downloading Oracle database 12c on other OS.
Downloading oracle database and this will be covered in details on Windows OS.
No need for any prerequisite database skills, this course designed from Zero to certified level.


ORACLE is database number one in the world .if you know Oracle SQL, then you can learn any SQL easily  ( mysql, sql server,PostgreSQL, ..).This is one of the most amazing Udemy courses in Oracle SQL.

  The course covers the oracle university track 100% for :

  “Oracle Database 12c SQL Certified Associate 1Z0-071”

  The course starts from zero level to Expert Level, I guarantee for you that you will understand every single lesson in this course because it was created in a very attractive way, you will not feel bored in any lesson in this course.

  Just watch the videos and do the examples and you will be ready passing the exam.

  You will find all the presentations and all the SQL scripts attached in every chapter, so no need to waste your time repeating any example I did.

  Simply this course is the best….


Section 1: Introduction

Lecture 1 Course Agenda

Lecture 2 System Development Life Cycle & Database Definition

Lecture 3 What is a Table?

Lecture 4 Data Models & ERM

Lecture 5 About Primary key & foreign key

Lecture 6 SQL and Types of SQL Statements

Lecture 7 Oracle Database 12c Architecture

Lecture 8 HR Schema in this Course

Section 2: Live SQL & Download/ Install

Lecture 9 Oracle Live SQL / No Installation for anything

Lecture 10 Oracle Live SQL limitation

Lecture 11 before you install oracle DB

Lecture 12 Downloading Oracle DB 12c

Lecture 13 Installing Oracle DB 12c

Lecture 14 About Oracle 18c express edition

Lecture 15 About oracle 11g express edition

Section 3: Connecting to the Database

Lecture 16 Connecting SYS using SQL plus & SQL Developer

Lecture 17 if you do not remember the sys password

Lecture 18 Unlock HR Account Part 1

Lecture 19 Unlock HR Account Part 2

Lecture 20 ORA-01033 oracle initialization or shutdown in progress 12c

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

Lecture 22 Creating new pluggable database contains HR user

Section 4: Retrieving data using the select statement

Lecture 23 Navigate HR schema

Lecture 24 Capabilities of SQL SELECT Statements

Lecture 25 Arithmetic expressions and NULL values

Lecture 26 Column Alias, Concatenation , Distinct and DESCRIBE

Lecture 27 Important Notes in Select Statement

Section 5: Restricting and Sorting Data

Lecture 28 The WHERE Clause & Comparison Operators

Lecture 29 Using Between and / IN / Like Operators

Lecture 30 Using IS NULL / NOT / Not equal Operators

Lecture 31 Logical Operators (AND/ OR/ NOT)

Lecture 32 Order by Clause

Lecture 33 The FETCH Clause

Section 6: Substitution Variables

Lecture 34 What is a Substitution Variables (&)


Lecture 36 ACCEPT / PROMPT

Lecture 37 Double-Ampersand / SET VERIFY / SET DEFINE

Section 7: Using Single-Row Functions to Customize Output

Lecture 38 Single Row Function Introduction

Lecture 39 Character functions (Upper, Lower, Initcap )

Lecture 40 Character functions (concat, substr, length )

Lecture 41 Character functions ( instr )

Lecture 42 Character functions ( lpad, rpad , replace, trim)

Lecture 43 Number functions ( Round, Trunc, MOD )

Lecture 44 Date Functions ( Sysdate)

Lecture 45 Date Functions ( months_between, add_months, next_day, last_day)

Lecture 46 Date Functions ( round , trunc )

Lecture 47 Nesting Functions

Section 8: Using Conversion Functions and Conditional Expressions

Lecture 48 Implicit Data Conversion VS Explicit

Lecture 49 TO_CHAR with dates

Lecture 50 TO_CHAR with numbers

Lecture 51 TO_NUMBER , TO_DATE Functions

Lecture 52 nvl , nvl2 , nullif and coalesce Functions

Lecture 53 The CASE Function

Lecture 54 The DECODE Function

Section 9: Reporting Aggregated Data Using the Group Functions

Lecture 55 Group functions Introduction

Lecture 56 Group functions (sum, count, max, min, avg & more)

Lecture 57 Group by Clause, Having Clause

Section 10: Displaying Data from multiple tables using joins

Lecture 58 Before You Start, important notes

Lecture 59 HR Tables Diagram, Joins Clarification

Lecture 60 What is Cartesian product?

Lecture 61 Old Joins: Equijoin

Lecture 62 Old Joins: nonEquijoins

Lecture 63 Old Joins: outer join

Lecture 64 Old Joins: Self Join and More Practices

Lecture 65 1999 Syntax: Cross Join (Cartesian product )

Lecture 66 1999 Syntax: Natural Join

Lecture 67 1999 Syntax: USING Clause

Lecture 68 1999 Syntax: ON Clause

Lecture 69 1999 Syntax: Left/Right/full Outer Join

Section 11: Using Subqueries to Solve Queries

Lecture 70 Single row Subqueries

Lecture 71 Multiple rows Subqueries

Lecture 72 NULL values and Subqueries

Lecture 73 Exists and not Exists

Section 12: Using the Set Operators

Lecture 74 Overview (Union, union all, intersect, minus )

Lecture 75 Practice (Union, union all, intersect, minus )

Section 13: Managing tables using DML Statments

Lecture 76 Introduction to DML

Lecture 77 Insert Statement

Lecture 78 Inserting common errors

Lecture 79 Update Statement

Lecture 80 Delete Statement

Lecture 81 What is Database Transactions?

Lecture 82 About Commit & Rollback

Lecture 83 Practice ( Commit and rollback )

Lecture 84 SAVEPOINT

Lecture 85 ROW Lock

Lecture 86 FOR UPDATE Clause

Section 14: Introduction to Data Definition Language

Lecture 87 DDL & Naming Rules

Lecture 88 Data Types Part 1

Lecture 89 Data Types Part 2

Lecture 90 Creating Tables (without constraints)

Lecture 91 Types of constraints and why we use it?

Lecture 92 Creating Tables ( Column Level Constraints )

Lecture 93 Creating Tables ( Table Level Constraints )

Lecture 94 Constraints Guidelines

Lecture 95 ON DELETE cascade / ON DELETE set null

Lecture 96 Create Table AS subquery

Lecture 97 Alter Table/ Add Columns

Lecture 98 Alter Table/ Modify Columns

Lecture 99 Alter Table/ Drop Columns

Lecture 100 Alter Table/ Set Unused

Lecture 101 ALTER TABLE READ ONLY / READ write

Lecture 102 Drop Table

Lecture 103 Rename Column / Rename Table

Section 15: Tips you Should know

Lecture 104 Tips you Should know Part 1

Lecture 105 Tips you Should know Part 2

Lecture 106 Tips you Should know Part 3

Lecture 107 Tips you Should know Part 4

Lecture 108 Tips you Should know Part 5

Lecture 109 Tips you Should know Part 6

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

Lecture 110 About Exam 1Z0-061 / Visiting Oracle Website

Lecture 111 My recommendations

Lecture 112 1Z0-061 retires on 30, Nov 2019

Section 17: Introduction to Data Dictionary Views

Lecture 113 What is Data Dictionary Views?

Lecture 114 What is DICTIONARY?






Section 18: Creating sequences, synonyms, and indexes

Lecture 120 Sequences Part 1

Lecture 121 Sequences Part 2

Lecture 122 Sequences Part 3

Lecture 123 Sequences Part 4

Lecture 124 Creating Synonyms

Lecture 125 Creating Indexes Part 1

Lecture 126 Creating Indexes Part 2

Section 19: Creating Views

Lecture 127 What is a View? / What is the benefits of Views?

Lecture 128 Creating Simple Views

Lecture 129 Creating Complex Views

Lecture 130 With READ ONLY / With check option / Force view

Section 20: Managing Schema Objects

Lecture 131 Adding constraints / dropping constraints

Lecture 132 Rename Column / Rename Constraint

Lecture 133 Enable / Disable Constraints

Lecture 134 Understanding DEFERRABLE Constraints

Lecture 135 DEFERRABLE Constraints Exercises


Lecture 137 About SQL*Loader

Lecture 138 External Tables Part 1

Lecture 139 External Tables Part 2

Section 21: Retrieving Data By Using Subqueries

Lecture 140 Using the subquery as a source table

Lecture 141 Pairwise/ Non Pairwise Subqueries

Lecture 142 Scalar Subquery / Correlated Subquery

Lecture 143 Reminder of Exists / Not Exists

Lecture 144 WITH Clause

Section 22: Manipulating Data By Using Subqueries

Lecture 145 Inserting/ Updating /Deleting Using a subquery as a target

Lecture 146 Correlated Update / Correlated Delete

Section 23: Controlling User Access

Lecture 147 Database Security / System Privileges & Roles

Lecture 148 Database Security / Object Privileges

Lecture 149 System Privileges & Privileges Practice 1

Lecture 150 System Privileges & Privileges Practice 2

Lecture 151 Creating ROLE Practice

Lecture 152 With Grant Option

Lecture 153 Drop user Statement

Section 24: Manipulating Data

Lecture 154 What is Data Warehouse?

Lecture 155 Explicit default value in insert & update statement

Lecture 156 Copy rows from another table

Lecture 157 INSERT ALL statement / INSERT FIRST

Lecture 158 Another “INSERT ALL” Example & Notes

Lecture 159 Creating Matrix Report Using PIVOT

Lecture 160 The Merge Statement

Lecture 161 FLASHBACK Table & System change Number ( SCN)

Section 25: Managing Data in Different Time Zones

Lecture 162 THE INTERVAL ( YEAR TO MONTH / day TO second)

Lecture 163 to_yminterval / to_dsinterval

Lecture 164 Extract function

Lecture 165 timestamp / timestamp with time zone

Lecture 166 V$TIMEZONE_NAMES and some functions

Lecture 167 timestamp with local time zone


Section 26: Exam 1Z0-071 for SQL Certified Associate

Lecture 169 About the Exam 1Z0-071

Lecture 170 EXAM 1z0-071 Sample Questions

Section 27: Bonus Section

Lecture 171 Using SELECT statement to create dynamic scripts

Lecture 172 Installing oracle 11g express edition

Lecture 173 Rownum and rowid

Lecture 174 About PL/SQL

Lecture 175 Understanding NULLS when used with ( IN/ NOT IN )

Lecture 176 permanent solution for ORA-01033 oracle initialization or shutdown in progress

Lecture 177 DML logs using package dbms_errlog

Lecture 178 oracle 18c express edition part 1 (download and install )

Lecture 179 oracle 18c express edition part 2 (unlock hr )

Lecture 180 oracle 18c express edition part 3 ( sql developer )

Section 28: What next? it is time for PL/SQL

Lecture 181 Move your skills to PL/SQL

Lecture 182 Move your skills to Oracle Analytic Functions In-Depth & Advanced Oracle SQL

Students who wants to be expert in oracle database 12c.,Students who wants to be certified in oracle database from Oracle University.,Fresh IT students who need strong SQL and database knowledge.,Fresh and junior programmers who need to find better jobs by gaining oracle certification.

Course Information:

Udemy | English | 19h 51m | 8.93 GB
Created by: Khaled Alkhudari

You Can See More Courses in the Developer >> Greetings from

New Courses

Scroll to Top