This course was created with the
course builder. Create your online course today.
Start now
Create your course
with
Autoplay
Autocomplete
Previous Lesson
Complete and Continue
The Complete Oracle SQL Certification Tutorial - Go From Beginner to a Certified Professional
INTRODUCTION
INTRODUCTION (2:38)
PART I - SQL DEVELOPMENT FOR BEGINNERS
BEGINNING OF PART I
INSTALLATION
Installing Oracle 12c in Windows 10 (12:11)
Using Oracle SQL Developer (2:11)
Troubleshooting Connection Issues (3:26)
INFRASTRUCTURE ARCHITECTURE
Client Server Architecture (2:22)
Different Types of Clients (4:02)
ORACLE RDBMS ARCHITECTURE
Quick overview of Oracle RDBMS Architecture (5:18)
CONNECTING TO AN ORACLE DATABASE
Connecting to an Oracle Database (2:25)
Demo - Connecting as a SYSDBA (2:06)
LAB - CONNECTING AS SYSDBA
ORACLE DATABASE - STOP/START
DEMO - Stopping an Oracle Database (1:20)
LAB - Stopping an Oracle Database
DEMO - Starting an Oracle Database (3:18)
LAB - Starting an Oracle Database
TABLE
What is a Table? (1:59)
Data Types (2:01)
BASIC SELECT STATEMENT
Basics of a SELECT statement (2:38)
DESCRIBE Command - DESC (1:43)
LAB - DESCRIBE or DESC Command
SELECT COUNT - Find the total number of Rows (0:46)
LAB - SELECT COUNT
DBA INFO - DBA_TABLESPACES
DBA_TABLESPACES (1:01)
LAB - DBA_TABLESPACES
SELECT - SELECTING SPECIFIC DATA
Selecting Particular Columns (1:46)
LAB - Selecting Particular Columns
WHERE Clause - Basics (2:22)
LAB - WHERE Clause
DBA INFO - ORACLE DATABASE USER ARCHITECTURE
Oracle Databae User Architecture (5:58)
Schema vs User (0:45)
How objects are stored (1:03)
Accessing the Objects (0:54)
CLASSIFICATION OF SQL COMMANDS
SQL Commands Classification (2:53)
DML - DATA MANIPULATION LANGUAGE
DDL - DATA DEFINITION LANGUAGE
DCL - DATA CONTROL LANGUAGE
TCL - TRANSACTION CONTROL LANGUAGE
CREATING A SCHEMA
CREATE AN USER (2:30)
LAB - CREATE AN USER
CREATE A TABLE (1:22)
LAB - CREATE A TABLE
DML - DATA MANIPULATION LANGUAGE
BASICS OF DML (1:35)
DML - SELECT STATEMENT (0:36)
DML - INSERT STATEMENT (1:39)
LAB - DML - INSERT FAILS. WHY?
DBA INFO - METADATA (1:11)
DBA INFO - DATA DICTIONARY (0:34)
DBA INFO - DEFAULT PERMANENT TABLESPACE (3:12)
FIXING THE FAILED INSERT (0:28)
COMMANDS TO FIX THE INSERT (3:31)
LAB - COMMANDS TO FIX INSERT
DML - UPDATE STATEMENT (2:08)
DML - DEMO OF AN UPDATE STATEMENT (1:27)
LAB - DML UPDATE STATEMENT
DML - DELETE STATEMENT (1:08)
DML - DEMO OF A DELETE STATEMENT (1:09)
LAB - DML - DELETE STATEMENT
DML - MERGE STATEMENT
TCL - TRANSACTION CONTROL LANGUAGE
TRANSACTION CONTROL LANGUAGE - BASICS (1:17)
WHAT IS A TRANSACTION? (3:56)
DEMO OF A TRANSACTION (2:06)
LAB - TCL - TRANSACTIONS
INTERNAL HANDLING OF A TRANSACTION THROUGH REDO (3:41)
INTERNAL HANDLING OF A TRANSACTION THROUGH UNDO (3:07)
TCL - COMMIT STATEMENT (1:56)
TCL - COMMIT DEMO (2:54)
LAB - TCL - COMMIT STATEMENT
TCL - ROLLBACK STATEMENT (1:38)
LAB - ROLLBACK STATEMENT
TCL - SAVEPOINT STATEMENT (1:59)
TCL - SAVEPOINT DEMO (2:14)
LAB - SAVEPOINT
DCL - DATA CONTROL LANGUAGE
DCL - GRANT STATEMENT (0:38)
DCL - SYSTEM PRIVILEGES (0:29)
DCL - OBJECT PRIVILEGES (0:35)
DCL - ROLE PRIVILEGES (0:55)
DCL - REVOKE STATEMENT (0:22)
DCL - GRANTS and REVOKES - FULL DEMO (5:40)
LAB - GRANTS and REVOKES - FULL DEMO
DBA INFO - FIND THE DBAs (1:09)
LAB - FIND THE DBAs
DDL - DATA DEFINITION LANGUAGE
DDL - CREATE STATEMENT (0:41)
DDL - ALTER STATEMENT (0:21)
DDL - DROP STATEMENT (0:24)
DDL - RENAME STATEMENT (0:25)
DDL - TRUNCATE STATEMENT (0:14)
DBA Info - TRUNCATE vs DELETE (0:30)
DDL - COMMENT STATEMENT (0:27)
DEMO OF ALL THE DDL STATEMENTS (8:24)
LAB - ALL THE DDL STATEMENTS
ADDRESSING THE OBJECTS (1:19)
DATABASE DESIGN
OLTP - ONLINE TRANSACTION PROCESSING SYSTEM (0:56)
DSS - DECISION SUPPORT SYSTEM (0:48)
DATA MODELING - INTRODUCTION (1:01)
LET'S CREATE A DATA MODEL (10:31)
DATA MODELING - CONCEPTUAL MODEL (0:54)
DATA MODELING - LOGICAL MODEL (0:56)
DATA MODELING - PHYSICAL MODEL (0:41)
ER MODEL vs ER DIAGRAM (0:30)
ENTITY, ATTRIBUTE, TUPLE and RALATIONSHIPS (1:51)
PART II - ORACLE SQL CERTIFICATION MATERIALS
BEGINNING OF PART II
LET'S BUILD A SELECT STATEMENT
DATA FOR THIS SECTION
SELECT EVERYTHING (0:55)
SELECT PARTICULAR COLUMNS (0:36)
FILTER DATA USING WHERE CLAUSE (0:44)
JOIN TWO TABLES (2:03)
LAB - JOIN TWO TABLES
ORACLE CERTIFICATION EXAM CONCEPTS - SELECT STATEMENT
PROJECTION, SELECTION and JOINING (1:19)
MANDATORY AND OPTIONAL CLAUSES OF A SELECT STATEMENT (0:39)
RESERVED WORDS (0:56)
CASE SENSITIVITY AND TERMINATORS
LOWER AND UPPER CASE IN SQL STATEMENTS (2:57)
SQL STATEMENT TERMINATORS (1:45)
LAB - LOWER AND UPPER CASE IN SQL STATEMENTS
A QUICK RECAP
STRUCTURE OF A TABLE - DESCRIBE COMMAND (1:29)
SELECT EVERYTHING (1:20)
FIND THE TOTAL NUMBER OF ROWS (0:51)
LAB - FOR THIS ENTIRE SECTION
SELECT ANYTHING - EVEN THE NON-EXISTING DATA
EXCUSE ME, WHO AM I AND WHAT IS TODAY'S DATE PLEASE? - SYSDATE (0:57)
DUAL HAS A DUMMY WITH AN X – WAIT, WHAT? (1:40)
TO QUOTE OR NOT TO QUOTE (2:29)
LAB - FOR THIS SECTION
ARITHMETIC OPERATIONS
ARITHMETIC OPERATIONS - ADDITION AND SUBTRACTION (1:19)
ARITHMETIC OPERATIONS - MULTIPLICATION AND DIVISION (1:08)
ORDER OF PRECEDENCE BETWEEN MULTIPLE OPERATORS (2:58)
CHANGING THE ORDER OF PRECEDENCE USING PARENTHESIS (1:26)
ARITHMETIC OPERATIONS ON TABLES (1:19)
LAB - FOR THIS SECTION
COLUMN ALIAS AND CONCATENATION
ALIAS FOR COLUMNS (2:43)
CONCATENATION - CREATING SENTENCES FROM THE DATA (2:19)
EXCEPTION FOR INNER SINGLE QUOTES (1:36)
REPLACING THE QUOTE DELIMITERS (2:58)
LAB - FOR THIS SECTION
DISTINCT
DISTINCT - AVOIDING DUPLICATES IN THE RESULTS (1:34)
LAB - FOR DISTINCT
SQL DEVELOPER - EXPLORING A SCHEMA
EXPLORING A SCHEMA IN SQL DEVELOPER (2:05)
UNLOCKING HR SCHEMA AND CONNECTING AS HR (2:43)
LAB - FOR THIS SECTION
NULL VALUES
NULL VALUES (2:47)
RETRIEVING NULL VALUES (1:44)
RETRIEVING NOT NULL VALUES (1:06)
ARITHMETIC OPERATIONS ON NULL (1:59)
CONCATENATION ON NULL VALUES (2:36)
SUBSTITUTING NULL VALUES (2:28)
LAB - FOR THIS SECTION
RESTRICTING AND SORTING DATA
USE THE ORDER BY CLAUSE TO SORT SQL QUERY RESULTS (2:24)
SORTING NULL VALUES AND EXPRESSIONS (2:12)
POSITIONAL AND COMPOSITE SORTING (2:45)
LIMITING THE ROWS WITH A WHERE CLAUSE (3:08)
COLUMN BASED AND DATE BASED CONDITIONS IN WHERE CLAUSE (3:41)
LAB - FOR THIS SECTION
COMPARISON OPERATORS
INEQUALITY OPERATORS - GREATER THAN & LESS THAN (2:40)
COMPOSITE INEQUALITY OPERATORS - GREATER/LESS THAN OR EQUAL TO (2:09)
COMPOSITE INEQUALITY OPERATORS -NOT EQUAL TO (1:57)
INEQUALITY OPERATORS ON DATE VALUES (1:53)
RANGE COMPARISON - BETWEEN OPERATOR (2:24)
SET COMPARISON - IN OPERATOR (2:18)
LAB - FOR THIS SECTION
PATTERN COMPARISON - LIKE OPERATOR
PATTERN COMPARISON - ESCAPE CHARACTER (3:26)
LIKE OPERATOR - HANDS ON (5:15)
LIKE OPERATOR - WITH WILDCARDS % AND _ (UNDERSCORE) (3:32)
LAB - FOR THIS SECTION
BOOLEAN OR LOGICAL OPERATORS
BOOLEAN or LOGICAL OPERATORS - CONCEPTS (2:44)
BOOLEAN OPERATOR - OR (5:05)
BOOLEAN OPERATOR - AND (2:46)
BOOLEAN OPERATOR - NOT (2:17)
BOOLEAN OPERATORS - HANDS ON DEMO (4:41)
LAB - BOOLEAN OPERATORS
SUBSTITUTIONS - USING AMPERSANDS
SUBSTITUTIONS - USING AMPERSANDS, DEFINING AND UNDEFINING THE VARIABLES (2:32)
SUBSTITUTIONS - DOUBLE AMPERSANDS (4:37)
LAB - FOR THIS SECTION
FUNCTIONS
FUNCTIONS - INTRODUCTION (3:44)
NUMERICAL SINGLE ROW FUNCTIONS (6:01)
CHARACTER BASED SINGLE ROW FUNCTIONS (5:14)
LAB I - FOR THIS SECTION
CHARACTER FUNCTIONS WITH NUMERICAL RESULTS (2:29)
DATETIME FUNCTIONS INTRODUCTION (5:41)
DATE AND TIME FUNCTIONS - MORE FUNCTIONS (7:49)
DATE AND TIME FUNCTIONS - TO_DATE (3:17)
COMPARISON FUNCTIONS - GREATEST AND LEAST (1:37)
LAB II - FOR THIS SECTION
AGGREGATE FUNCTIONS
AGGREGATE FUNCTIONS - GROUPS and SUBGROUPS (4:18)
AGGREGATE FUNCTIONS - EXAMPLES (5:54)
FILTERING AGGREGATE FUNCTIONS - HAVING CLAUSE (2:40)
LAB - FOR THIS SECTION
CONDITIONAL FUNCTIONS
CONDITIONAL FUNCTIONS - NVL, NVL2 and NULLIF (3:16)
CONDITIONAL FUNCTIONS - DECODE and CASE (3:50)
LAB - FOR THIS SECTION
DISPLAYING DATA FROM MULTIPLE TABLES - JOINS
BASICS OF A JOIN - CARTESIAN PRODUCT OR CROSS JOIN (6:15)
BASICS OF A JOIN - AMBIGUOUS COLUMN NAMES (2:21)
DEMO - CARTESIAN PRODUCT OR CROSS JOIN (1:57)
DEMO - AMBIGUOUS COLUMN NAMES (2:27)
EQUIJOIN - OLD METHOD (2:47)
EQUIJOIN - NEW ANSI STANDARD - JOIN ON CLAUSE (2:15)
EQUIJOIN - NEW ANSI STANDARD - JOIN USING CLAUSE (4:18)
EQUIJOIN - NEW ANSI STANDARD - NATURAL JOIN CLAUSE (2:09)
NON-EQUIJOINS (2:10)
OUTER JOINS - LEFT, RIGHT and FULL OUTER JOINS (2:36)
DEMO - OUTER JOINS - LEFT, RIGHT and FULL OUTER JOINS (1:52)
LAB - FOR THIS SECTION
USING SUBQUERIES TO SOLVE QUERIES
SUBQUERIES - DEFINITION AND SUBQUERY IN THE SELECT CLAUSE (3:09)
SUBQUERY - IN THE WHERE CLAUSE (3:35)
SUBQUERY - IN THE FROM CLAUSE (2:07)
SUBQUERY - IN DMLs - IN INSERT UPDATE DELETE (3:14)
LAB - FOR THIS SECTION
USING THE SET OPERATORS
UNION - COMBINING ROWS FROM TWO QUERIES WITHOUT DUPLICATES (4:51)
UNION ALL - COMBINING ROWS FROM TWO QUERIES WITH DUPLICATES (2:55)
INTERSECT - FETCHING THE COMMON ROWS FROM THE RESULTS (2:05)
MINUS - FIRST RESULT SET MINUS SECOND RESULT SET (3:51)
LAB - FOR THIS SECTION
MANIPULATING DATA
UPDATE - VARIOUS METHODS OF UPDATING THE DATA (3:34)
DELETE - VARIOUS METHODS OF DELETING THE DATA (2:02)
INSERT - VARIOUS METHODS OF INSERTING DATA (4:50)
LAB - FOR THIS SECTION
CONSTRAINTS
NULL - DEFAULT (1:55)
NOT NULL CONSTRAINT (2:04)
UNIQUE CONSTRAINT (3:58)
PRIMARY KEY CONSTRAINT (2:50)
FOREIGN KEY CONSTRAINT (5:30)
LAB - FOR THIS SECTION
ADDITIONAL KNOWLEDGE BASE - DATABASE OBJECTS, DBA ARTICLES, SCRIPTS etc
INDEXES (2:24)
CONCLUSION
CONCLUSION - THANK YOU
DDL - ALTER STATEMENT
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock