Using Oracle SQL Developer | OracleSQL.org

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

Using Oracle SQL Developer

Complete and Continue