Categories

There are currently no items in your shopping cart.

User Panel

Forgot your password?.

Oracle Database 12c SQL Certified Associate 1Z0-071

Video Introducing this tutorial


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
Downloading Oracle DB 12c
Installing Oracle DB 12c
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 :
4 lectures
Single row Subqueries
Multiple rows Subqueries
NULL values and Subqueries
Exists and not Exists

Using the Set Operators :
2 lectures
Overview (Union, union all, intersect, minus )
Practice (Union, union all, intersect, minus )

Managing tables using DML Statments :
11 lectures
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 :
17 lectures
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 :
6 lectures
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 :
2 lectures
About Exam 1Z0-061 / Visiting Oracle Website
My recommendations

Introduction to Data Dictionary Views :
7 lectures
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 :
7 lectures
Sequences Part 1
Sequences Part 2
Sequences Part 3
Sequences Part 4
Creating Synonyms
Creating Indexes Part 1
Creating Indexes Part 2

Creating Views :
4 lectures
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 :
9 lectures
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 :
5 lectures
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 :
2 lectures
Inserting/ Updating /Deleting Using a subquery as a target
Correlated Update / Correlated Delete

Controlling User Access :
7 lectures
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 :
8 lectures
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 :
7 lectures
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 :
3 lectures
About the Exam 1Z0-071
Some helpful websites contains exams Dumps
EXAM 1z0-071 Sample Questions

Bonus Section :
7 lectures
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

What next? it is time for PL/SQL :
2 lectures
Move your skills to PL/SQL
Move your skills to Oracle Analytic Functions In-Depth & Advanced Oracle SQL