Oracle Database 12c SQL Certified Associate 1Z0-071

Become Oracle SQL Certified ( OCA ) / Covers ORACLE university track 100%. All the presentations & scripts are attached

4.64 (6715 reviews)
Udemy
platform
English
language
IT Certification
category
26,851
students
20 hours
content
Jan 2020
last update
$109.99
regular price

What you will 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

Description

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….

Screenshots

Oracle Database 12c SQL Certified Associate 1Z0-071 - Screenshot_01Oracle Database 12c SQL Certified Associate 1Z0-071 - Screenshot_02Oracle Database 12c SQL Certified Associate 1Z0-071 - Screenshot_03Oracle Database 12c SQL Certified Associate 1Z0-071 - Screenshot_04

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 )
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

Implicit Data Conversion VS Explicit
TO_CHAR with dates
TO_CHAR with numbers
TO_NUMBER , TO_DATE Functions
nvl , nvl2 , nullif and coalesce Functions
The CASE Function
The DECODE Function

Reporting Aggregated Data Using the Group Functions

Group functions Introduction
Group functions (sum, count, max, min, avg & more)
Group by Clause, Having Clause

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

Single row Subqueries
Multiple rows Subqueries
NULL values and Subqueries
Exists and not Exists

Using the Set Operators

Overview (Union, union all, intersect, minus )
Practice (Union, union all, intersect, minus )

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

Tips you Should know Part 1
Tips you Should know Part 2
Tips you Should know Part 3
Tips you Should know Part 4
Tips you Should know Part 5
Tips you Should know Part 6

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

About Exam 1Z0-061 / Visiting Oracle Website
My recommendations
1Z0-061 retires on 30, Nov 2019

Introduction to Data Dictionary Views

What is Data Dictionary Views?
What is DICTIONARY?
USER_OBJECTS / ALL_OBJECTS
USER_TABLES / ALL_TABLES / USER_TAB_COLUMNS
USER_CONSTRAINTS & USER_CONS_COLUMNS Part 1
USER_CONSTRAINTS & USER_CONS_COLUMNS Part 2
COMMENT ON TABLE / COLUMN

Creating sequences, synonyms, and indexes

Sequences Part 1
Sequences Part 2
Sequences Part 3
Sequences Part 4
Creating Synonyms
Creating Indexes Part 1
Creating Indexes Part 2

Creating Views

What is a View? / What is the benefits of Views?
Creating Simple Views
Creating Complex Views
With READ ONLY / With check option / Force view

Managing Schema Objects

Adding constraints / dropping constraints
Rename Column / Rename Constraint
Enable / Disable Constraints
Understanding DEFERRABLE Constraints
DEFERRABLE Constraints Exercises
GLOBAL TEMPORARY TABLE
About SQL*Loader
External Tables Part 1
External Tables Part 2

Retrieving Data By Using Subqueries

Using the subquery as a source table
Pairwise/ Non Pairwise Subqueries
Scalar Subquery / Correlated Subquery
Reminder of Exists / Not Exists
WITH Clause

Manipulating Data By Using Subqueries

Inserting/ Updating /Deleting Using a subquery as a target
Correlated Update / Correlated Delete

Controlling User Access

Database Security / System Privileges & Roles
Database Security / Object Privileges
System Privileges & Privileges Practice 1
System Privileges & Privileges Practice 2
Creating ROLE Practice
With Grant Option
Drop user Statement

Manipulating Data

What is Data Warehouse?
Explicit default value in insert & update statement
Copy rows from another table
INSERT ALL statement / INSERT FIRST
Another "INSERT ALL" Example & Notes
Creating Matrix Report Using PIVOT
The Merge Statement
FLASHBACK Table & System change Number ( SCN)

Managing Data in Different Time Zones

THE INTERVAL ( YEAR TO MONTH / day TO second)
to_yminterval / to_dsinterval
Extract function
timestamp / timestamp with time zone
V$TIMEZONE_NAMES and some functions
timestamp with local time zone
TZ_OFFSET / FROM_TZ / TO_TIMESTAMP

Exam 1Z0-071 for SQL Certified Associate

About the Exam 1Z0-071
Some helpful websites contains exams Dumps
EXAM 1z0-071 Sample Questions

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

Move your skills to PL/SQL
Move your skills to Oracle Analytic Functions In-Depth & Advanced Oracle SQL

Reviews

Roman
July 7, 2022
Great course for beginners, with great explanations and many excellent examples after each topic. Thank for the great work!
R
May 23, 2022
I am yet to write the certification, but this course has helped me go from knowing nothing about joins to solving SQL problems in a coding platform. Thank you.
Guschaaya
May 23, 2022
Great explanation, very easy to follow. For using DB Express version, the service/connection name might need to adapt a bit(from orclpdb to XEPDB1)
Andrei
May 13, 2022
This course is very well structured. It contains everything you need for preparing for the exam. Specifying the tips/hints for exam is really useful. The exercises are extremely detailed and well explained. Another key point is that the course is very interactive, the speaker is able to keep your attention. I highly recommend this course :)
Justin
April 13, 2022
It is a really good course, my only point is the way he speaks. I can tell English is not his native language, so it sometimes takes me a bit longer to understand. However his effort is amazing and everything is still very clear plus the program is set-out nicely.
Trent
April 6, 2022
Kalid's P's are pronounced as B's, like Brimary Key. It took a little getting use to but it kept me awake thru the videos. There were spelling errors and sometimes what he said didn't match what was presented in the video but overall the content was good and I liked the up beat nature of his presentation.
Stefano
March 2, 2022
non do 5 stelle solo perchè i sottotitoli in italiano non sono ben fatti, in alcuni video vengono mischiati a quelli in inglese Per il resto, corso ben strutturato
Alfredo
February 28, 2022
Difficult to get engaged, pretty boring, plain, and straightforward. Professor fails on making it a little bit more entertaining and keeps you engaged through the 20 hours of course.
Manish
February 16, 2022
This course contains everything one needs to master Oracle Database, Very insightful content and a learned instructor. Thanks so much!
Levi
February 7, 2022
Great instructor, job well done and value for money. My advice, listen at 1.25 speed. I cannot recommend this course enough!
yekhiel
January 17, 2022
Khaled is an experienced instructor, and knows how to deliver his content in a way his students will easily understand. His personable character makes the course a lot more fun! Thank you Khaled, you are amazing!
Ka
January 13, 2022
I really like your courses and your professionalism, but I feel hard to note when some terms are not recognized your speech by translator:( And thanks for everything!
Mohamed
December 8, 2021
Very Useful. I recommend everyone to do this course. But one drawback of this course is the trainer already made the query before teaching and just executing it. This methodology of teaching will not be effective for PHOTO-SENSE students.
Alexander
December 2, 2021
Congratulations, course is very well structured, and he explains everything very well. Unfortunately I can only give five stars.
Ibrahim
November 30, 2021
It is GREAT and easy to understand it covers all aspects in simple way , it is the best course I ever watched (it covers from A TO Z)

Charts

Price

Oracle Database 12c SQL Certified Associate 1Z0-071 - Price chart

Rating

Oracle Database 12c SQL Certified Associate 1Z0-071 - Ratings chart

Enrollment distribution

Oracle Database 12c SQL Certified Associate 1Z0-071 - Distribution chart
1312042
udemy ID
8/3/2017
course created date
10/12/2019
course indexed date
Bot
course submited by