Duration: 5 days – 35 hrs.
Overview
Welcome to the Oracle Introduction to SQL Training Course! This comprehensive 5-day program is designed to provide you with a solid foundation in SQL (Structured Query Language) using Oracle’s database platform. Whether you’re new to databases or seeking to enhance your SQL skills, this course will guide you through the essentials of data querying and manipulation. Through hands-on exercises, practical examples, and interactive discussions, you’ll learn how to retrieve, modify, and manage data efficiently using SQL within the Oracle database environment. This training teaches you how to write sub queries, combine multiple queries into a single query using SET operators and report aggregated data using group functions.
Objectives
- Understand the fundamentals of relational databases and SQL.
- Write basic to intermediate SQL queries to retrieve and manipulate data.
- Gain proficiency in querying data from Oracle databases.
- Perform data manipulation tasks such as updating, inserting, and deleting records.
- Develop a strong foundation for more advanced SQL concepts and Oracle database usage.
Audience
- IT professionals seeking to gain proficiency in SQL and Oracle databases.
- Database administrators, developers, and analysts interested in data manipulation.
- Individuals aiming to master SQL for querying and managing data within Oracle environments.
Pre- requisites
- Basic familiarity with databases and data concepts is recommended.
- No prior experience with Oracle databases or SQL is required
Course Content
Introduction to Oracle Database:
- Oracle Database Overview
- Relational Database Concepts
- Evolution and History of Oracle Database
- Oracle Database Architecture
- Editions of Oracle Database and Their Features
Retrieve Data using the SQL SELECT Statement:
- Introduction to Retrieving Data
- Basic SELECT Statement Syntax
- Retrieving All Columns vs. Specific Columns
- Using DISTINCT to Eliminate Duplicates
- Writing Simple WHERE Clauses
Learn to Restrict and Sort Data:
- Using WHERE Clause for Conditions
- Logical Operators (AND, OR, NOT)
- Comparison Operators (=, <>, >, <, etc.)
- Sorting Data Using ORDER BY
- Sorting by Multiple Columns
Usage of Single-Row Functions to Customize Output:
- Understanding Single-Row Functions
- String Functions (UPPER, LOWER, SUBSTR, etc.)
- Numeric Functions (ROUND, TRUNC, ABS, etc.)
- Date Functions (SYSDATE, TO_CHAR, etc.)
- Conversion Functions (TO_NUMBER, TO_DATE, etc.)
Invoke Conversion Functions and Conditional Expressions:
- Using CASE Expressions
- COALESCE and NULLIF Functions
- NVL, NVL2, and NULL Functions
- Conditional Operators (DECODE, CASE)
- Implicit vs. Explicit Data Type Conversion
Aggregate Data Using the Group Functions:
- Introduction to Aggregation
- SUM, AVG, MIN, MAX Functions
- COUNT Function and Handling NULL Values
- GROUP BY Clause and Grouping Data
- HAVING Clause for Filtering Groups
Display Data From Multiple Tables Using Joins:
- Introduction to Joins
- Inner Joins and Equijoins
- Outer Joins (LEFT, RIGHT, FULL)
- Self-Joins and Cross-Joins
- Joining More Than Two Tables
Use Sub-queries to Solve Queries:
- Sub-query Concepts and Benefits
- Single-Row Sub-queries
- Multi-Row Sub-queries
- Correlated Sub-queries
- Sub-queries in WHERE and HAVING Clauses
The SET Operators:
- Introduction to SET Operators
- UNION Operator
- UNION ALL Operator
- INTERSECT Operator
- MINUS Operator (Oracle-Specific)
Data Manipulation Statements:
- INSERT Statement
- UPDATE Statement
- DELETE Statement
- MERGE Statement (Oracle-Specific)
- DML Statements and Transaction Control
Use of DDL Statements to Create and Manage Tables:
- Data Definition Language (DDL) Overview
- CREATE TABLE Statement Syntax
- ALTER TABLE Statement for Modifications
- TRUNCATE TABLE Statement
- DROP TABLE Statement
Other Schema Objects:
- Introduction to Schema Objects
- Views and Their Benefits
- Indexes and Their Types
- Synonyms and Their Usage
- Sequences for Generating Unique Numbers
Control User Access:
- User Authentication and Authorization
- Granting and Revoking Privileges
- Object Privileges vs. System Privileges
- Roles and Their Benefits
- Managing Password Security
Management of Schema Objects:
- Renaming and Moving Schema Objects
- Flashback and Recycle Bin Concepts
- Managing Tablespace Allocation
- Undo and Temporary Tablespaces
- Segment Management and Storage
Manage Objects with Data Dictionary Views:
- Introduction to Data Dictionary Views
- Useful Data Dictionary Views
- USER, ALL, and DBA Views
- Querying Object Information
- Data Dictionary Best Practices
Manipulate Large Data Sets:
- Handling Large Data Sets
- Pagination Techniques
- Using ROWNUM and ROWID
- Fetching Batches of Data (FETCH FIRST, OFFSET)
- Efficient Data Manipulation Techniques
Data Management in Different Time Zones:
- Time Zones and Timestamps
- Using TIMESTAMP Data Type
- AT TIME ZONE Conversion
- Working with Interval Data Types
- Dealing with Daylight Saving Time
Retrieve Data Using Sub-queries:
- Sub-query vs. JOIN Comparison
- Sub-queries in SELECT Statements
- Sub-queries in WHERE and HAVING Clauses
- Correlated Sub-queries Revisited
- Pros and Cons of Sub-queries
Regular Expression Support:
- Introduction to Regular Expressions
- REGEXP_LIKE for Pattern Matching
- REGEXP_SUBSTR for Extracting Patterns
- REGEXP_REPLACE for Pattern Replacement
- Combining Regular Expressions with SQL
