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