Introduction

1. Introduction
4m 35s

Installation

1. Installing Oracle 12c in Windows 10
11m 49s
2. Using SQL Developer
2m 5s
3. Troubleshooting Connection Issues
3m 16s

Infrastructure Architecture

1. Client Server Architecture
2m 8s
2. Different types of Clients
3m 53s

Oracle RDBMS Architecture

1. Quick Overview of Oracle RDBMS Architecture
5m

Connecting to an Oracle Database

1. Connecting to an Oracle Database
2m 13s
2. Demo - Connecting as SYSDBA
1m 59s

Stopping and Starting an Oracle Database

1. DEMO - Stopping an Oracle Database
1m 13s
2. DEMO - Starting an Oracle Database
3m 6s

Table

1. What is a Table?
1m 31s
2. Data Types
1m 43s

SELECT - Basic SELECT Statement

1. Basics of a SELECT Statement
2m 24s
2. DESCRIBE Command - DESC
1m 31s
3. SELECT COUNT - Find the total number of Rows
38s

DBA Info - DBA_TABLESPACES

1. DBA_TABLESPACES
52s

SELECT - Selecting Specific Data

1. Selecting Particular Columns
1m 31s
2. WHERE Clause - Basics
2m 9s

DBA Info - Oracle Database User Architecture

1. Oracle Database User Architecture
5m 36s
2. Schema Vs User
35s
3. How objects are stored?
53s
4. Accessing the Objects
39s

Classification of SQL Commands

1. Classification of SQL Commands
2m 36s

Creating a Schema

1. Create a User
2m 14s
2. Create a Table
1m 9s

DML - Data Manipulation Language

1. Basics of DML
1m 3s
2. DML - SELECT STATEMENT
24s
3. DML - INSERT STATEMENT
1m 22s
4. DBA Info - METADATA
45s
5. DBA Info - DATA DICTIONARY
15s
6. DBA Info - DEFAULT PERMANENT TABLESPACE
2m 38s
7. DML - Fixing the Failed INSERT
16s
8. Demonstration of the commands to fix the INSERT
3m 11s
9. DML - UPDATE STATEMENT
1m 51s
10. Demo of an UPDATE STATEMENT
1m 12s
11. DML - DELETE STATEMENT
46s
12. Demo - DELETE STATEMENT
1m

TCL - Transaction Control Language

1. TCL - Basics
1m 2s
2. What is a Transaction?
3m 33s
3. Demo of a Transaction
1m 45s
4. DBA Info - Internal handling of Transaction through REDO
3m 19s
5. DBA Info - Internal handling of Transaction through UNDO
2m 53s
6. TCL - COMMIT STATEMENT
1m 30s
7. TCL - COMMIT Demo
2m 22s
8. TCL - ROLLBACK STATEMENT
1m 10s
9. TCL - SAVEPOINT STATEMENT
1m 33s
10. TCL - SAVEPOINT Demo
2m 2s

DCL - Data Control Language

1. DCL - GRANT STATEMENT
29s
2. DCL - SYSTEM PRIVILEGES
20s
3. DCL - OBJECT PRIVILEGES
26s
4. DCL - ROLE PRIVILEGES
46s
5. DCL - REVOKE STATEMENT
13s
6. DCL - GRANT and REVOKE - FULL DEMO
5m 29s
7. DBA Info - Find the DBAs
48s

DDL - Data Definition Language

1. DDL - CREATE STATEMENT
28s
2. DDL - ALTER STATEMENT
6s
3. DDL - DROP STATEMENT
7s
4. DDL - RENAME STATEMENT
11s
5. DDL - TRUNCATE STATEMENT
5s
6. DDL - TRUNCATE vs DELETE
21s
7. DDL - COMMENT STATEMENT
15s
8. ALL THE DDL STATEMENTS - FULL DEMO
7m 46s
9. Addressing the Objects
1m 3s

Database Design

1. OLTP - Online Transaction Processing System
40s
2. DSS - Decision Support System
33s
3. DATA MODELING - ENTITY RELATIONSHIP MODEL or ER MODEL
44s
4. ER MODEL - CREATION & NORMALIZATION
10m 10s
5. ER MODEL - CONCEPTUAL MODEL
32s
6. ER MODEL - LOGICAL MODEL
39s
7. ER MODEL - PHYSICAL MODEL
28s
8. ER MODEL vs ER DIAGRAM
15s
9. ENTITY, ATTRIBUTE, TUPLE & RELATIONSHIPS
1m 30s

Build a SELECT Statement

1. SELECT EVERYTHING
35s
2. SELECT PARTICULAR COLUMNS
20s
3. Filtering Data using WHERE Clause
18s
4. JOIN Two Tables
1m 43s

Oracle Certification Exam Concepts - SELECT

1. PROJECTION, SELECTION & JOINING
57s
2. Mandatory and Optional Clauses of a SELECT Statement
22s
3. ORACLE RESERVED WORDS
41s

CASE SENSITIVITY & TERMINATORS

1. LOWER and UPPER CASE in SQL Statements
2m 40s
2. SQL STATEMENT TERMINATORS
1m 36s

A QUICK RECAP

1. STRUCTURE OF A TABLE - DESCRIBE COMMAND
1m 21s
2. SELECT EVERYTHING - SELECT *
1m 10s
3. FIND THE TOTAL NUMBER OF ROWS - COUNT (*)
40s

SELECT ANYTHING - Even the NON-EXISTENT DATA..!

1. To Quote or Not to Quote..!
2m 17s
2. DUAL has a DUMMY with an X – Wait, What?
1m 27s
3. Excuse me, Who am I and What is today's date please? - SYSDATE
45s

ARITHMETIC OPERATIONS

1. Additions and Subtractions ( + and - )
1m 7s
2. Multiplication and Division ( * and / )
56s
3. Order of Precedence between Multiple Operators
2m 44s
4. Changing the Order of Precedence using Parenthesis
1m 13s
5. Arithmetic Operations on Tables
1m 7s

COLUMN ALIAS AND CONCATENATION

1. Alias for Columns
2m 30s
2. CONCATENATION - Creating Sentences from the Data
2m 6s
3. Exception for Inner Single Quotes
1m 26s
4. Replacing the Quote Delimiters
2m 47s

DISTINCT

1. DISTINCT - Avoiding Duplicates in the Results
1m 22s

SQL DEVELOPER - Exploring a Schema

1. Unlocking the HR Schema and Connecting
2m 33s
2. Explore a Schema using SQL Developer
1m 50s

NULL VALUES

1. NULL Values
2m 38s
2. Retrieving NULL Values - IS NULL
1m 33s
3. Retrieving NON-NULL Values - IS NOT NULL
55s
4. Arithmetic Operations on NULL Values
1m 45s
5. Concatenation on NULL Values
2m 25s
6. Substituting NULL Values in the Results
2m 14s

Restricting and Sorting Data

1. Use the ORDER BY clause to sort SQL query results
2m 10s
2. Sorting NULL Values and Expressions
1m 59s
3. Positional and Composite Sorting
2m 33s
4. Limiting the Rows with a WHERE clause
2m 58s
5. Column based and Date based Conditions in WHERE clause
3m 30s

COMPARISON OPERATORS

1. INEQUALITY OPERATORS - GREATER THAN & LESS THAN
2m 27s
2. COMPOSITE INEQUALITY OPERATORS - GREATER/LESS THAN OR EQUAL TO
1m 53s
3. COMPOSITE INEQUALITY OPERATORS -NOT EQUAL TO
1m 46s
4. INEQUALITY OPERATORS ON DATE VALUES
1m 39s
5. RANGE COMPARISON - BETWEEN OPERATOR
2m 9s
6. SET COMPARISON - IN OPERATOR
2m 3s

PATTERN COMPARISON - LIKE OPERATOR

1. LIKE OPERATOR - WITH WILDCARDS % AND _ (UNDERSCORE)
3m 17s
2. LIKE OPERATOR - HANDS ON
5m 3s
3. PATTERN COMPARISON - ESCAPE CHARACTER
3m 12s

BOOLEAN OR LOGICAL OPERATORS

1. BOOLEAN or LOGICAL OPERATORS - CONCEPTS
2m 28s
2. BOOLEAN OPERATOR - OR
4m 51s
3. BOOLEAN OPERATOR - AND
2m 35s
4. BOOLEAN OPERATOR - NOT
2m 6s
5. BOOLEAN OPERATORS - HANDS ON DEMO
4m 26s

SUBSTITUTIONS - USING AMPERSANDS

1. SUBSTITUTIONS - USING AMPERSANDS, DEFINING AND UNDEFINING THE VARIABLES
2m 18s
2. SUBSTITUTIONS - DOUBLE AMPERSANDS
4m 24s

FUNCTIONS

1. FUNCTIONS - INTRODUCTION
3m 32s
2. NUMERICAL SINGLE ROW FUNCTIONS
5m 49s
3. CHARACTER BASED SINGLE ROW FUNCTIONS
5m
4. CHARACTER FUNCTIONS WITH NUMERICAL RESULTS
2m 14s
5. DATETIME FUNCTIONS INTRODUCTION
5m 26s
6. DATE AND TIME FUNCTIONS - MORE FUNCTIONS
7m 31s
7. DATE AND TIME FUNCTIONS - TO_DATE
3m 5s
8. COMPARISON FUNCTIONS - GREATEST AND LEAST
1m 25s

AGGREGATE FUNCTIONS

1. AGGREGATE FUNCTIONS - GROUPS and SUBGROUPS
4m 3s
2. AGGREGATE FUNCTIONS - EXAMPLES
5m 40s
3. FILTERING AGGREGATE FUNCTIONS - HAVING CLAUSE
2m 26s

CONDITIONAL FUNCTIONS

1. CONDITIONAL FUNCTIONS - NVL, NVL2 and NULLIF
3m 3s
2. CONDITIONAL FUNCTIONS - DECODE and CASE
2m 17s

DISPLAYING DATA FROM MULTIPLE TABLES - JOINS

1. BASICS OF A JOIN - CARTESIAN PRODUCT OR CROSS JOIN
6m 1s
2. BASICS OF A JOIN - AMBIGUOUS COLUMN NAMES
2m 8s
3. DEMO - CARTESIAN PRODUCT OR CROSS JOIN
1m 45s
4. DEMO - AMBIGUOUS COLUMN NAMES
2m 16s
5. EQUIJOIN - OLD METHOD
2m 32s
6. EQUIJOIN - NEW ANSI STANDARD - JOIN ON CLAUSE
2m 2s
7. EQUIJOIN - NEW ANSI STANDARD - JOIN USING CLAUSE
4m 5s
8. EQUIJOIN - NEW ANSI STANDARD - NATURAL JOIN CLAUSE
1m 55s
9. NON-EQUIJOINS
1m 56s
10. OUTER JOINS - LEFT, RIGHT and FULL OUTER JOINS
2m 22s
11. DEMO - OUTER JOINS - LEFT, RIGHT and FULL OUTER JOINS
1m 37s

USING SUBQUERIES TO SOLVE QUERIES

1. SUBQUERIES - DEFINITION AND SUBQUERY IN THE SELECT CLAUSE
2m 57s
2. SUBQUERY - IN THE WHERE CLAUSE
3m 23s
3. SUBQUERY - IN THE FROM CLAUSE
1m 54s
4. SUBQUERY - IN DMLs - IN INSERT UPDATE DELETE
3m 3s

USING THE SET OPERATORS

1. UNION - COMBINING ROWS FROM TWO QUERIES WITHOUT DUPLICATES
4m 37s
2. UNION ALL - COMBINING ROWS FROM TWO QUERIES WITH DUPLICATES
2m 43s
3. INTERSECT - FETCHING THE COMMON ROWS FROM THE RESULTS
1m 53s
4. MINUS - FIRST RESULT SET MINUS SECOND RESULT SET
3m 37s

MANIPULATING DATA

1. INSERT - VARIOUS METHODS OF INSERTING DATA
4m 32s
2. UPDATE - VARIOUS METHODS OF UPDATING DATA
3m 22s
3. DELETE - VARIOUS METHODS OF DELETING DATA
1m 48s

CONSTRAINTS

1. NULL - DEFAULT
1m 41s
2. NOT NULL CONSTRAINT
1m 51s
3. UNIQUE CONSTRAINT
3m 43s
4. PRIMARY KEY CONSTRAINT
2m 39s
5. FOREIGN KEY CONSTRAINTS
5m 18s

ADDITIONAL KNOWLEDGE BASE - DATABASE OBJECTS, DBA ARTICLES, SCRIPTS etc

1. INDEXES
2m 11s

1z0-071: Oracle Database SQL

Introduction

Installation

Infrastructure Architecture

Oracle RDBMS Architecture

Connecting to an Oracle Database

Stopping and Starting an Oracle Database

Table

SELECT - Basic SELECT Statement

DBA Info - DBA_TABLESPACES

SELECT - Selecting Specific Data

DBA Info - Oracle Database User Architecture

Classification of SQL Commands

Creating a Schema

DML - Data Manipulation Language

TCL - Transaction Control Language

DCL - Data Control Language

DDL - Data Definition Language

Database Design

Build a SELECT Statement

Oracle Certification Exam Concepts - SELECT

CASE SENSITIVITY &amp; TERMINATORS

A QUICK RECAP

SELECT ANYTHING - Even the NON-EXISTENT DATA..!

ARITHMETIC OPERATIONS

COLUMN ALIAS AND CONCATENATION

DISTINCT

SQL DEVELOPER - Exploring a Schema

NULL VALUES

Restricting and Sorting Data

COMPARISON OPERATORS

PATTERN COMPARISON - LIKE OPERATOR

BOOLEAN OR LOGICAL OPERATORS

SUBSTITUTIONS - USING AMPERSANDS

FUNCTIONS

AGGREGATE FUNCTIONS

CONDITIONAL FUNCTIONS

DISPLAYING DATA FROM MULTIPLE TABLES - JOINS

USING SUBQUERIES TO SOLVE QUERIES

USING THE SET OPERATORS

MANIPULATING DATA

CONSTRAINTS

ADDITIONAL KNOWLEDGE BASE - DATABASE OBJECTS, DBA ARTICLES, SCRIPTS etc

CASE SENSITIVITY & TERMINATORS