Thank you for sending your enquiry! One of our team members will contact you shortly.
Thank you for sending your booking! One of our team members will contact you shortly.
Duration 35 hours
Course Outline
Overview of Oracle Database Infrastructure
- Core database architectural components
- Principles of the relational data model
- Management of users, schemas, and active sessions
- Standard tools for database interaction
Foundations of Data Retrieval with the SELECT Statement
- Record filtering and selection via the WHERE clause
- Ordering of query outputs
- Data typing, logical operators, and NULL value management
- Application of built-in scalar functions
- Execution of date-based calculations
- National and regional parameter configuration in SQL
Analysis of Aggregate Data
- Implementation of grouping functions
- Use of the DISTINCT clause for unique results
- Application of GROUP BY and HAVING clauses for filtered aggregation
Complex Data Retrieval Across Multiple Tables
- Implementation of inner and outer joins (INNER JOIN, OUTER JOIN)
- ANSI SQL syntax standards and alternative join mechanisms (SELF JOIN, NATURAL JOIN)
- Application of set operators (UNION, UNION ALL, INTERSECT, MINUS)
Subquery Implementation Strategies
- Construction of simple subqueries
- Development of correlated subqueries
- Utilization of EXISTS and NOT EXISTS logic
- Advanced subquery variants
Hierarchical Structures and Data Sampling
- Construction of hierarchical queries using CONNECT BY PRIOR and START WITH clauses
- Path representation via the SYS_CONNECT_BY_PATH function
- Statistical sampling techniques using the SAMPLE clause
Data Manipulation Operations (DML)
- Execution of INSERT, UPDATE, and DELETE commands
- Efficient handling of large datasets (INSERT FIRST, INSERT ALL, MERGE)
System Data Dictionary
- Navigation and utilization of the Oracle metadata repository
Concurrency and Transaction Control
- Transaction lifecycle management
- Database locking mechanisms
- Implementation of FLASHBACK technology for recovery
Access Control and Security
- Provisioning and modification of user accounts and schemas
- Administration of privileges and role assignments
Logical Data Storage Management
- Configuration of standard tables, temporary tables, and index-organized tables
- Enforcement of data integrity constraints
- Indexing strategies for performance optimization
- Creation and maintenance of views, sequences, synonyms, and materialized views
- Deployment of stored PL/SQL procedures and functions
Data Model Management via Oracle SQL Modeler
- Utilization of Oracle SQL Modeler for schema design and restoration tasks
Data Migration and Transfer
- Logical data movement using Data Pump import and export utilities
- Bulk data loading via SQL*Loader
- Integration of external tables
- Establishment of database links for remote access
Task Automation and Scheduling
- Automation of routine maintenance using DBMS_JOB and DBMS_SCHEDULER
Conclusion and Professional Development Pathways
Requirements
- General foundational knowledge of information technology
Target Audience
- Software Developers
- Database Administrators
Testimonials (2)
Greg was very patient and helpful
Chris Havel - Encyclopaedia Britannica
Course - ORACLE SQL Fundamentals
presentation skills, prompt answers to all questions
