1z0-071: Oracle Database SQL

1z0-071: Oracle Database SQL

Get ready for your exam by enrolling in our comprehensive training course. This course includes a full set of instructional videos designed to equip you with in-depth knowledge essential for passing the certification exam with flying colors.

$14.99 / $24.99

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