Oracle SQL Syllabus - Advanced
Module 1: Introduction to Databases & SQL
- Introduction to SQL and Databases
- DBMS vs. RDBMS
- SQL Standards
- SQL Sub-languages (DDL, DML, DCL, TCL, DQL)
- Introduction to SQL*Plus and Developer Tools
- Oracle Data Types
- Understanding NULL values
- SQL Operators
Module 2: Schema Design and Data Definition Language (DDL)
Schema Objects in Oracle
Creating Tables (from scratch & from another table)
Altering Tables (add/modify/rename columns)
Dropping and Truncating Tables
Difference between DELETE and TRUNCATE
Module 3: Data Manipulation Language (DML)
INSERT (Single/Multiple Rows)
UPDATE (with conditions)
DELETE (with conditions)
Copying data/structure between tables
Transactions: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION
Module 4: User & Access Control with DCL
GRANT and REVOKE permissions
Creating Users and Roles
System vs Object Privileges
Invoking and Revoking Privileges
Module 5: Integrity Constraints
Column-level vs Row-level Constraints
Types of Constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT
Composite Key, Alternate Key
Enabling, Disabling, and Querying Constraints
Module 6: Data Querying and Clauses
SELECT Statement Variants
Table and Column Aliases
Data Filtering (WHERE, LIKE, IN, BETWEEN)
Sorting Data (ORDER BY)
Grouping Data (GROUP BY, HAVING)
Other Clauses: FROM, USING, CONSTRAINT, FOR UPDATE, LIMIT
Module 7: Built-in Functions in SQL
Single Row Functions: String, Date, Math, Conversion, Special Functions
Using DUAL Table for Functions
Multi-row Functions: COUNT, SUM, MAX, MIN, AVG
GROUP BY vs HAVING Clause
Module 8: Joins and Subqueries
Types of Joins: EQUI JOIN, NON-EQUI JOIN, SELF JOIN, OUTER JOINS (LEFT, RIGHT, FULL), CROSS JOIN
Subqueries: Single-row, Multi-row, Multi-column, Nested, Correlated Subqueries, Inline Views & Scalar Queries
Module 9: Set Operators and Pseudo Columns
Set Operators: UNION, UNION ALL, INTERSECT, MINUS
Pseudo Columns: ROWID, ROWNUM
Module 10: Advanced SQL Concepts
Wildcards and Pattern Matching
Null Functions
IN vs EXISTS
Recursive Queries & Common Table Expressions (CTEs)
Date and Time Operations
Using Sequences
Auto Increment Techniques
Module 11: Stored Code Objects
Views and Materialized Views
Stored Procedures
User-Defined Functions (UDFs)
Triggers: Creation, Use Cases, Management
Temporary vs Permanent Tables
Module 12: Performance & Optimization
SQL Indexes: Create, Drop, Clustered, Non-clustered
Query Optimization Techniques
Reading Execution Plans
Database Tuning Essentials
Module 13: Security in SQL
User Authentication and Authorization
SQL Injection Prevention
Data Encryption Basics
Hosting SQL Databases Securely
Module 14: SQL Integration & Reporting
Using SQL with Python (Basics of Integration)
Generating Reports using SQL
Connecting SQL with BI Tools (Power BI, Tableau overview)