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
Teach online with
DEMO OF ALL THE DDL STATEMENTS
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock