IIRF Online > Development > Database Design & Development > Oracle SQL > The Complete Oracle SQL Bootcamp (2024)
The Complete Oracle SQL Bootcamp (2024) by Udemy
Become an In-demand SQL Professional! Pass Oracle SQL 1Z0-071 (OCA) Certification Exam Confidently!
Course Highlights
- Pass Oracle 1Z0-071 SQL Database SQL Certification Exam easily on your first try!
- Be Able to Answer any SQL Interview Questions Confidently!
- Become a Job-Ready SQL Developer by Learning All the Skills You will Need!
- Learn Oracle SQL by Professionally Designed Content Step by Step!
- Write, Read and Analyze Any SQL Queries Easily and Learn How to Play with Data!
- Solve any SQL-related Problems by Yourself Creating Analytical Solutions!
- Test Your SQL Skills with Hundreds of Hands-on Examples, Quizzes, Challenges!
- Lifetime Support from the Instructors. Get a Well-Organized Recap Document!
Skills you will learn!
Curriculum
13 Topics
What is a Database?
Why Oracle Database?
What is a Table?
UDEMY 101: How to Use Udemy? +Some Useful Tips (Do not Skip)
The Working Document (Course Slide for Course Participants)
Special Gifts for Our Students!(SQL Cheatsheet & SQL Certification Exam Samples)
What is a Relational Database (RDBMS)?
Entity Relationship Logic in Databases
What is Pluggable Database?
Introduction to Database Objects
The Sample (HR) Schema Used in This Course
What is SQL?
Quiz (Database Concepts)
19 Topics
About the Database Installation
The Ways to Get a Database
Option 1: Installing VMware and VirtualBox on Windows
Option 1: How to Install the Virtual Box on Mac OS X?
Option 1: Downloading Virtual Machine Image (19c)
Option 1: Downloading Virtual Machine Image(If the previous lecture didn't work)
Option 1: Configuring the VMware Virtualization Software
Option 1: Configuring the Oracle VirtualBox Virtualization Software
Option 1: Configuring SQL Developer inside of the Virtual Machine
Important Note
Option 2: Downloading And Installing Oracle Database
Option 2: Unlocking the HR Schema
Option 2: Configuring and Using the SQL Developer
Option 2: Installing Sample Schemas in Oracle Database
Option 2: HR Schema Create Code (if you could not get the HR user in other ways)
Option 3 : Using Oracle Live SQL
Option 3: Oracle Live SQL Limitations
SQL Statements Used in This Course
Don't Forget to Leave a Rating!
26 Topics
Oracle Data Types
What is a NULL Value?
DESCRIBE Command
INFORMATION Command
INFORMATION Command (Code Samples)
SQL Statement Basics
SQL Statement Basics (Code Samples)
Oracle Error Messages
Oracle Error Messages (Code Samples)
Using SELECT Statements
Using SELECT Statements (Code Samples)
Using Column Aliases
CODE: Using Column Aliases
Quote (Q) Operator
Quote (Q) Operator (Code Samples)
DISTINCT and UNIQUE Operator
DISTINCT and UNIQUE Operators (Code Samples)
Write a SQL Query that Returns All the Possible Product Status Values
Coding Exercise 1 Solution
Concatenation Operators
Concatenation Operators (Code Samples)
Arithmetic Expressions and NULL Values
Arithmetic Expressions and NULL Values (Code Samples)
Write a SQL Query to Calculate New Salaries
Quiz (Retrieving Data)
Coding Exercise 2 Solution
23 Topics
Using WHERE Clause
Using WHERE Clause (Code Samples)
Write a SQL Query to Fetch the Employees Working as Sales Managers
Comparison Operators
Comparison Operators (Code Samples)
BETWEEN … AND Operator
BETWEEN...AND Operator (Code Samples)
IN Operator
IN Operator (Code Samples)
Type a SQL Query which Returns the Employees who Work as Sales People
LIKE Operator
LIKE Operator (Code Samples)
Return all the managers based on their job titles
IS NULL Operator
IS NULL Operator (Code Samples)
Logical Operators (AND OR NOT)
Logical Operators (Code Samples)
Return the managers who earn less than 10000
Rules of Precedence
Rules of Precedence (Code Samples)
A Challenging Exercise on the Rules of Precedence
Quiz
Quiz (Restricting Data)
13 Topics
ORDER BY Clause
ORDER BY Clause (Code Samples)
ASC and DESC Operators
ASC and DESC Operators (Code Samples)
It's Time Order the Rows
NULLS FIRST and NULLS LAST Operators
NULLS FIRST and NULLS LAST Operators (Code Samples)
ROWNUM & ROWID in SQL
ROWNUM & ROWID in SQL (Code Samples)
Oracle FETCH Clause
Oracle FETCH Clause (Code Samples)
Bring the second top 10 orders
Quiz (Sorting Data)
8 Topics
What is a Substitution Variable?
What is a Substitution Variable (Code Samples)
Double Ampersand (&&) and DEFINE & UNDEFINE Commands
Double Ampersand (&&) and DEFINE & UNDEFINE Commands (Code Samples)
ACCEPT and PROMPT Commands
ACCEPT and PROMPT Commands (Code Samples)
SET VERIFY ON / SET VERIFY OFF Commands
SET VERIFY ON-OFF Commands(Code Samples)
21 Topics
Single Row Functions
Character Functions - Case Conversion (LOWER UPPER INITCAP) Functions
Case Conversion (LOWER UPPER INITCAP) Functions (Code Samples)
Character Functions - Character Manipulation Functions (Part 1)
Character Manipulation Functions (Part 1) (Code Samples)
Character Functions - Character Manipulation Functions Part 2 (INSTR Function)
Character Manipulation Functions Part 2 (INSTR Function) (Code Samples)
Character Functions - Part 3 (TRIM LTRIM RTRIM Functions)
Character Functions - Part 3 (TRIM LTRIM RTRIM Functions) (Code Samples)
Character Functions - Part 4 (REPLACE LPAD RPAD Functions)
Character Functions - Part 4 (REPLACE LPAD RPAD Functions) (Code Samples)
Numeric Functions
Numeric Functions (Code Samples)
Nested Functions
Nested Functions (Code Samples)
Date Values & Date Formats in Oracle
Date Functions & Arithmetic Operations on Dates
Date Functions & Arithmetic Operations on Dates (Code Samples)
Date Manipulation Functions in SQL
Date Manipulation Functions in SQL (Code Samples)
Quiz (Single-Row Functions)
9 Topics
Conversion Functions
Conversion Functions (Code Samples)
TO_CHAR TO_DATE TO_NUMBER Functions (Part 1)
TO_CHAR TO_DATE TO_NUMBER Functions (Part 1) (Code Samples)
TO_CHAR TO_DATE TO_NUMBER Functions (Part 2)
TO_CHAR TO_DATE TO_NUMBER Functions (Part 2) (Code Samples)
Null-Related (NVL NVL2 NULLIF COALESCE) Functions
Null-Related (NVL NVL2 NULLIF COALESCE) Functions (Code Samples)
Quiz (Conversion Functions)
5 Topics
Oracle Conditional Expressions: CASE Expressions
Oracle Conditional Expressions: CASE Expressions (Code Samples)
Oracle Conditional Expressions: DECODE Function
Oracle Conditional Expressions: DECODE Function (Code Samples)
Quiz (Conditional Expressions)
17 Topics
Group Functions
AVG Function
AVG Function (Code Samples)
COUNT Function
COUNT Function (Code Samples)
Write a SQL query returning the number of married male customers
MAX Function
MAX Function (Code Samples)
MIN Function
MIN Function (Code Samples)
SUM Function
SUM Function (Code Samples)
LISTAGG Function
LISTAGG Function (Code Samples)
Overview Of Group Functions
Overview Of Group Functions (Code Samples)
Quiz (Group Functions)
11 Topics
GROUP BY Clause (Part 1)
GROUP BY Clause (Part 1) (Code Samples)
Type a SQL Query Returning the Number of Distinct Job ID Values for Each Department
GROUP BY Clause (Part 2)
GROUP BY Clause (Part 2) (Code Samples)
HAVING Clause
HAVING Clause (Code Samples)
Type a SQL Query Returns Departments which Have more than 10 employees
Nested Group Functions
Nested Group Functions (Code Samples)
Quiz (Grouping Data)
38 Topics
What is a Join? & Oracle SQL Join Types
Creating a Join
Natural Join
Natural Join (Code Samples)
Join with the USING Clause
Join with the USING Clause (Code Samples)
Handling Ambiguous Column Names
Handling Ambiguous Column Names (Code Samples)
Inner Join & Join with the ON Clause
Inner Join & Join with the ON Clause (Code Samples)
Write a SQL Query Returning the Distribution of Orders Based on the Gender and Order Mode
Multiple Join Operations
Multiple Join Operations (Code Samples)
Restricting Joins
Restricting Joins (Code Samples)
Self Join
Self Join (Code Samples)
Non-Equijoins (Joining Unequal Tables)
Non-Equijoins (Joining Unequal Tables) (Code Samples)
OUTER JOINS
OUTER JOINS (Code Samples)
LEFT OUTER JOIN (LEFT JOIN)
LEFT OUTER JOIN (LEFT JOIN) (Code Samples)
RIGHT OUTER JOIN (RIGHT JOIN)
RIGHT OUTER JOIN (RIGHT JOIN) (Code Samples)
FULL OUTER JOIN (FULL JOIN)
FULL OUTER JOIN (FULL JOIN) (Code Samples)
Cross Join (Cartesian Product / Cross Product)
Cross Join (Cartesian Product / Cross Product) (Code Samples)
Oracle's Old Style Join Syntax (ANSI vs Non-ANSI Joins) (Part 1)
Oracle's Old Style Join Syntax (ANSI vs Non-ANSI Joins) (Part 2)
Oracle's Old Style Join Syntax (ANSI vs Non-ANSI Joins) (Part 3)
Oracle's Old Style Join Syntax (ANSI vs Non-ANSI Joins) (Code Samples)
The Differences Between The Inner Joins & Outer Joins Equijoins & Non-Equijoins
Differences Between Inner Joins-Outer Joins Equijoins-Non-Equijoins (Code)
Which Join Type Should You Use?
Entity-Relationship Models in DBMS - How to Use Them with Joins?
Quiz (Joining Multiple Tables)
21 Topics
Using Subqueries
Using Subqueries (Code Samples)
Return the employees who work at the same department with Douglas
Single Row Subqueries
Single Row Subqueries (Code Samples)
Multiple Row Subqueries
Multiple Row Subqueries (Code Samples)
Multiple Column Subqueries
Multiple Column Subqueries (Code Samples)
Using Subqueries as a Table
Using Subqueries as a Table (Code Samples)
SCALAR Subqueries
SCALAR Subqueries (Code Samples)
Correlated Subqueries
Correlated Subqueries (Code Samples)
EXISTS Operator & Semijoins
EXISTS Operator & Semijoins (Code Samples)
Write a SQL Query to Find the Customers who Paid over 100000 for One Order
NOT EXISTS Operator
NOT EXISTS Operator (Sample Codes)
Quiz (Using Subqueries)
14 Topics
Introduction to SET Operators in Oracle SQL
UNION and UNION ALL Operators
UNION and UNION ALL Operators (Code Samples)
INTERSECT Operator
INTERSECT Operator (Code Samples)
MINUS Operator
MINUS Operator (Code Samples)
Matching Unmatched Queries in SET Operations
Matching Unmatched Queries in SET Operations (Code Samples)
Using the ORDER BY Clause with SET Operators
Using the ORDER BY Clause with SET Operators (Code Samples)
Combining Multiple Queries Using the SET Operators
Combining Multiple Queries Using the SET Operators (Code Samples)
Quiz (Set Operators)
21 Topics
Data Definition Language (DDL)
Naming Rules & Naming Conventions in Oracle
CREATE TABLE Statement
CREATE TABLE Statement (Code Samples)
CREATE TABLE AS SELECT (CTAS) Statement in Oracle
CREATE TABLE AS SELECT (CTAS) Statement in Oracle (Code Samples)
ALTER TABLE Statement
ALTER TABLE Statement (Code Samples)
Marking Columns Unused (Using The SET UNUSED Clause)
Marking Columns Unused (Using The SET UNUSED Clause) (Code Samples)
READ-ONLY Tables in SQL
READ-ONLY Tables in SQL (Code Samples)
DROP TABLE Statement
DROP TABLE Statement (Code Samples)
TRUNCATE TABLE Statement
TRUNCATE TABLE Statement (Code Samples)
COMMENT Statement
COMMENT Statement (Code Samples)
RENAME Statement
RENAME Statement (Code Samples)
Quiz (Data Definition Language)
31 Topics
Data Manipulation Language (DML)
INSERT Statement (Part 1)
INSERT Statement (Part 1) (Code Samples)
INSERT Statement (Part 2)
INSERT Statement (Part 2) (Code Samples)
Multitable Insert Statements
Unconditional Insert Statements (INSERT ALL Statements)
Unconditional Insert Statements (INSERT ALL Statements) (Code Samples)
Conditional INSERT ALL Statements
Conditional INSERT ALL Statements (Code Samples)
Conditional INSERT FIRST Statements
Conditional INSERT FIRST Statements (Code Samples)
Pivoting Insert
Pivoting Insert (Code Samples)
UPDATE Statement
UPDATE Statement (Code Samples)
DELETE Statement
DELETE Statement (Code Samples)
MERGE Statement
MERGE Statement (Code Samples)
(TCL) Transaction Control Language & TCL Commands
(TCL) Transaction Control Language & TCL Commands (Code Samples)
COMMIT and ROLLBACK Statements
COMMIT and ROLLBACK Statements (Code Samples)
Row Lock in Oracle
Row Lock in Oracle (Code Samples)
SAVEPOINT Statement
SAVEPOINT Statement (Code Samples)
FOR UPDATE Statement
FOR UPDATE Statement (Code Samples)
Quiz (Data Manipulation Language)
8 Topics
FLASHBACK Operations (Part 1)
FLASHBACK Operations (Part 2)
FLASHBACK Operations (Part 2) (Code Samples)
PURGE Operations
PURGE Operations (Code Samples)
Tracking Changes In Data In a Particular Time
Tracking Changes In Data In a Particular Time (Code Samples)
Quiz (Flashback Technologies)
32 Topics
What is a Constraint? Oracle Constraint Types
NOT NULL Constraint
NOT NULL Constraint (Code Samples)
UNIQUE Constraint
UNIQUE Constraint (Code Samples)
PRIMARY KEY Constraint
PRIMARY KEY Constraint (Code Samples)
FOREIGN KEY Constraint (Part 1)
FOREIGN KEY Constraint (Part 2)
FOREIGN KEY Constraint (Code Samples)
The ON DELETE CASCADE | ON DELETE SET NULL Clause
The ON DELETE CASCADE | ON DELETE SET NULL Clause (Code Samples)
CHECK Constraint
CHECK Constraint (Code Samples)
Adding Constraints via ALTER TABLE Statements
Adding Constraints via ALTER TABLE Statements (Code Samples)
Dropping (Removing) Constraints
Dropping (Removing) Constraints (Code Samples)
Cascading Constraints in Oracle
Cascading Constraints in Oracle (Code Samples)
Renaming Constraints
Renaming Constraints (Code Samples)
Disabling Constraints
Disabling Constraints (Code Samples)
Enabling Constraints
Enabling Constraints (Code Samples)
Status of Constraints
Status of Constraints (Code Samples)
Deferring Constraints (Part 1)
Deferring Constraints (Part 2)
Deferring Constraints (Code Samples)
Quiz (Constraints)
17 Topics
What is a View and What is it Used for?
View Types in Oracle
Creating Simple Views
Creating Simple Views (Code Samples)
Creating Complex Views
Creating Complex Views (Code Samples)
Modifying Views
Modifying Views (Code Samples)
Performing DML Operations with Views
Performing DML Operations with Views (Code Samples)
Using the WITH CHECK OPTION Clause in SQL
Using the WITH CHECK OPTION Clause in SQL (Code Samples)
Using the WITH READ ONLY Clause on Views
Using the WITH READ ONLY Clause on Views (Code Samples)
Dropping Views
Dropping Views (Code Samples)
Quiz (Views)
18 Topics
What are Data Dictionary Views in Oracle?
The Dictionary View
The Dictionary View (Code Samples)
Difference Between USER ALL DBA and V$ Prefixes
USER_OBJECTS ALL_OBJECTS and DBA_OBJECTS Views
USER_OBJECTS ALL_OBJECTS and DBA_OBJECTS Views (Code Samples)
USER_TABLES Data Dictionary View
USER_TABLES Data Dictionary View (Code Samples)
USER_TAB_COLUMNS Data Dictionary View
USER_TAB_COLUMNS Data Dictionary View (Code Samples)
USER_CONSTRAINTS Data Dictionary View
USER_CONSTRAINTS Data Dictionary View (Code Samples)
USER_CONS_COLUMNS Data Dictionary View
USER_CONS_COLUMNS Data Dictionary View (Code Samples)
USER_VIEWS Data Dictionary View
USER_TAB_COMMENTS and USER_COL_COMMENTS Data Dictionary Views
USER_TAB_COMMENTS and USER_COL_COMMENTS Data Dictionary Views (Code Samples)
Quiz (Data Dictionary Views)
16 Topics
What is a Sequence?
Creating Sequences
Creating Sequences (Code Samples)
Modifying Sequences
Modifying Sequences (Code Samples)
Dropping Sequences
Using Sequences
Using Sequences (Code Samples)
Using Sequences as a Default Value
Using Sequences as a Default Value (Code Samples)
Sequence Caching
The User_Sequences View
Oracle IDENTITY Column (Part 1)
Oracle IDENTITY Column (Part 2)
Oracle IDENTITY Column (Code Samples)
Quiz (Sequences)
6 Topics
What is a Synonym?
Creating Using and Dropping Synonyms
Creating Using and Dropping Synonyms (Code Samples)
Analyzing The USER_SYNONYMS View
Analyzing The USER_SYNONYMS View (Code Samples)
Quiz (Synonyms)
19 Topics
What is an Index in SQL?
Index Types in SQL
How to Create Indexes (Part 1)
How to Create Indexes (Part 1) (Code Samples)
How to Create Indexes (Part 2)
How to Create Indexes (Part 2) (Code Samples)
How to Create Indexes While Table Creation?
How to Create Indexes While Table Creation (Code Samples)
How to Remove (Drop) Indexes?
How to Remove (Drop) Indexes (Code Samples)
Function-Based Indexes
Function-Based Indexes (Code Samples)
Multiple Indexes on the Same Columns & Invisible Indexes
Multiple Indexes on the Same Columns & Invisible Indexes (Code Samples)
Analyzing the USER_INDEXES and USER_IND_COLUMNS Views
Analyzing the USER_INDEXES and USER_IND_COLUMNS Views (Code Samples)
How to Modify (Alter) Indexes?
How to Modify (Alter) Indexes (Code Samples)
Quiz (Indexes)
22 Topics
What are Privileges in SQL?
How to Create a User in Oracle Database? (Part 1)
How to Create a User in Oracle Database? (Part 2)
How to Create a User in Oracle Database (Code Samples)
Changing Database Users' Passwords in Oracle Database
Changing Users' Passwords in Oracle Database (Code Samples)
Granting System Privileges
Granting System Privileges (Code Samples)
Roles and PUBLIC (Part 1)
Roles and PUBLIC (Code Samples)
Roles and PUBLIC (Part 2)
Granting Object Privileges
Granting Object Privileges (Code Samples)
Definer's Rights and Invoker's Rights (Part 1)
Definer's Rights and Invoker's Rights (Part 2)
Definer's Rights and Invoker's Rights (Code Samples)
Revoking Privileges and Roles (Part 1)
Revoking Privileges and Roles (Part 2)
Revoking Privileges and Roles (Code Samples)
Searching Privileges Using Data Dictionary Views
Quiz (Privileges & Roles)
Good News!
5 Topics
Appendix 1: Oracle Database 12c Installation into Your Computer
Appendix 2: How to Unlock the HR Schema in the Oracle Database 12c?
Appendix 3: Configuring and Using Oracle SQL Developer for Oracle Database 12c
Appendix 4: How to Uninstall Oracle Database?
How to Enroll in Oracle 1Z0-071 SQL Associate Certification Exam?
4 Topics
Course Code Samples
SQL Cheatsheet
SQL Certification Exam Samples
Bonus Lecture
The Complete Oracle SQL Bootcamp (2024)
Related Oracle SQL Courses
Thank You!
Your review has been submitted successfully.