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