Oracle 10g SQL Programming Training in Garden Grove
We offer private customized training for groups of 3 or more attendees.
|
||
Course Description |
||
In this class, students will learn how to create, retrieve, and manipulate objects in Oracle 10g
Structured Query Language (SQL). Students will also be introduced to Oracle 10gdatabase features and tools. Students will go on to develop deeper insight into relational database design and RDBMS
operation, learn concepts and specific SQL syntax for extended Oracle datatypes, learn analysis and
tuning techniques to increase SQL performance, and master advanced features of Oracle SQL for large
data sets and data warehouses.
Course Length: 5 Days
Course Tuition: $2090 (US) |
Prerequisites |
|
None |
Course Outline |
1. Relational Database and SQL Overview Review of Relational Database Terminology Relational Database Management Systems Introduction to SQL Oracle Versioning and History Logical and Physical Storage Structures Connecting to a SQL Database Datatypes Sample Database 2. Using Oracle SQL*Plus SQL*Plus The SQL Buffer Buffer Manipulation Commands Running SQL*Plus Scripts Tailoring Your SQL*Plus Environment Viewing Table Characteristics SQL*Plus Substitution Variables Interactive SQL*Plus Scripts Using iSQL*Plus 3. SQL Queries - The SELECT Statement The SELECT Statement The CASE...WHEN Statement Choosing Rows with the WHERE Clause NULL Values Compound Expressions IN and BETWEEN The LIKE Operator Creating Some Order 4. Scalar Functions SQL Functions Using SQL Functions String Functions Numeric Functions Date Functions Conversion Functions Date Formats Oracle Pseudocolumns 5. SQL Queries - Joins Selecting from Multiple Tables Joining Tables Self Joins Outer Joins Types of Outer Joins 6. Aggregate Functions and Advanced Techniques Subqueries Correlated Subqueries The EXISTS Operator The Aggregate Functions Grouping Rows Combining SELECT Statements 7. Data Manipulation and Transactions The INSERT Statement The UPDATE Statement The DELETE Statement Transaction Management Concurrency Explicit Locking Data Inconsistencies Loading Tables From External Sources 8. Data Definition and Control Statements Standard Datatypes Defining Tables Constraints Inline Constraints Modifying Table Definitions Deleting a Table Definition Controlling Access to Your Tables 9. Other Database Objects Views Creating Views Updateable Views Sequences Synonyms 10. Database Design Concepts Relational Databases The Relational Model Relational Operations The Database Design Process Normalization Second and Third Normal Forms Other Normal Forms Applications for Relational Databases 11. SQL Subqueries Overview of Subqueries Inline Views Correlated Subqueries EXISTS Clause vs. IN Clause Group Comparisons: ANY and ALL Scalar Subquery Expression Subqueries and DML Statements Subquery Factoring: The WITH Clause Top-N and Bottom-N analysis CREATE TABLE and Subqueries 12. Hierarchical Queries Hierarchical Data Hierarchical Terminology Hierarchical Query Hierarchical Pseudocolumns SYS_CONNECT_BY_PATH Processing Hierarchical Queries 13. Object Types Object-Oriented Programming Oracle's Object Relational Model Creating Object Types Querying Object Types DML with Object Types Object Methods Object Views VARRAYs Nested Tables 14. Times, Dates, and Strings Datetime Fields Dates and Timestamps Intervals Date and Interval Literals Date Arithmetic Date Functions Character Types Session and Database Parameters REGEXP Functions Regular Expressions Supported by REGEXP Applying REGEXP Functions 15. Temporary Tables Undo and Redo Temporary Tables Defined Data Lifetime — Transaction vs. Session Creating Temporary Tables Managing Temporary Tables Storage of Temporary Tables Effects of DML and TRUNCATE 16. SQL Tuning Tools Automated Statistics Gathering The DBMS_STATS Package SQL Tuning Advisor SQL Tuning Sets SQL Access Advisor Retrieving Execution Plans EXPLAIN PLAN Using DBMS_XPLAN Interpreting Explain Plan Results SQL Trace TKPROF 17. SQL Tuning Tuning Goals The Optimizer Optimizer Statistics Identifying SQL to Tune Optimizer Hints Optimizer Goal Hints Access Path Hints Join Hints Additional Hints Plan Stability Creating Stored Outlines 18. Indexes Indexes B-tree and Composite Indexes Reverse Key and Unique Indexes Function-Based Indexes Bitmap Indexes Index-Organized Tables Managing Indexes 19. Oracle Analytic Functions Analytic Functions OVER, PARTITION BY, and ORDER BY Windowing ROLLUP CUBE Grouping Sets RANK Modeling Model Clauses 20. Data Warehouse Features Partitioned Tables Partitioning Methods Partition Pruning and Partition-wise Joins Bitmap Indexes Materialized Views Creating Materialized Views Refreshing Materialized Views The MERGE Statement Multi-table INSERT Statements Parallel Statements 21. Formatting Reports with SQL*Plus Page Formatting Computations SQL*Plus Options for Formatting Saving the Output Data Extraction with SQL*Plus 22. Appendix A - The Data Dictionary Introducing the Data Dictionary DBA, ALL, and USER Data Dictionary Views Some Useful Data Dictionary Queries |
Course Directory [training on all levels]
- .NET Classes
- Agile/Scrum Classes
- Ajax Classes
- Android and iPhone Programming Classes
- Blaze Advisor Classes
- C Programming Classes
- C# Programming Classes
- C++ Programming Classes
- Cisco Classes
- Cloud Classes
- CompTIA Classes
- Crystal Reports Classes
- Design Patterns Classes
- DevOps Classes
- Foundations of Web Design & Web Authoring Classes
- Git, Jira, Wicket, Gradle, Tableau Classes
- IBM Classes
- Java Programming Classes
- JBoss Administration Classes
- JUnit, TDD, CPTC, Web Penetration Classes
- Linux Unix Classes
- Machine Learning Classes
- Microsoft Classes
- Microsoft Development Classes
- Microsoft SQL Server Classes
- Microsoft Team Foundation Server Classes
- Microsoft Windows Server Classes
- Oracle, MySQL, Cassandra, Hadoop Database Classes
- Perl Programming Classes
- Python Programming Classes
- Ruby Programming Classes
- Security Classes
- SharePoint Classes
- SOA Classes
- Tcl, Awk, Bash, Shell Classes
- UML Classes
- VMWare Classes
- Web Development Classes
- Web Services Classes
- Weblogic Administration Classes
- XML Classes