Introduction to ORACLE SQL 12C Training

In this 5 day instructor led class, students will learn the following Oracle 12C SQL skills: 

Class Outline

• Chapter 1 – Basic RDBMS Principles..  
• Chapter Overview…  
• Relational Design Principles.  
• Accessing Data Through A Structured Query Language.  
• Entity Relationship Diagrams.  
• Data Domains.  
• NULL Values.  
• Indexes.  
• Views.  
• Denormalization..  
• Data Model Review…  
• LAB 1:  Basic RDBMS Principles.  
• LAB 1 Solutions:  Basic RDBMS Principles.  
• Chapter Summary.  

• Chapter 2 – The SQL Language And Tools..  
• Chapter Overview…  
• Using SQL*Plus.  
• Why Use SQL*Plus When Other Tools Are Available?.  
• Starting SQL*Plus.  
• EZConnect 
• SQL Commands.  
• PL/SQL Commands.  
• SQL*Plus Commands.  
• The COLUMN Command.  
• The HEADING Clause.  
• The FORMAT Clause.  
• The NOPRINT Clause.  
• The NULL Clause.  
• The CLEAR Clause.  
• Predefined DEFINE Variables.  
• Login.sql (login.sql In Linux / UNIX) 
• Command History.  
• Copy And Paste In SQL*Plus.  
• Entering SQL Commands.  
• Entering PL/SQL Commands.  
• Entering SQL*Plus Commands.  
• Default Output From SQL*Plus.  
• Entering Queries.  
• What About PL/SQL?.  
• LAB 2: SQL Language And Tools.  
• LAB 2 Solutions:  SQL Language And Tools.  
• Chapter Summary.  

• Chapter 3 – Using SQL Developer..  
• Chapter Overview…  
• Choosing A SQL Developer Version..  
• Configuring Connections.  
• Creating A Basic Connection.  
• Creating A TNS Connection.  
• Connecting.  
• Configuring Preferences.  
• Using SQL Developer..  
• The Columns Tab.  
• The Data Tab.  
• The Constraints Tab.  
• The Grants Tab.  
• The Statistics Tab.  
• Other Tabs.  
• Queries In SQL Developer 
• Query Builder 
• Accessing Objects Owned By Other Users.  
• The Actions Pulldown Menu.  
• Differences Between SQL Developer And SQL*Plus.  
• Reporting Commands Missing In SQL Developer 
• General Commands Missing In SQL Developer 
• Data Dictionary Reports.  
• User Defined Reports.  
• Using Scripts In SQL Developer..  
• LAB 3:  Using SQL Developer..  
• LAB 3 Solutions:  Using SQL Developer..  
• Chapter Summary.  

• Chapter 4 – SQL Query Basics..  
• Chapter Overview…  
• Understanding The Data Dictionary.  
• Exporting Key Data Dictionary Information.  
• The Dictionary View…  
• Components Of A SELECT Statement.  
• The SELECT Clause.  
• The FROM Clause.  
• The WHERE Clause.  
• The GROUP BY Clause.  
• The HAVING Clause.  
• The ORDER BY Clause.  
• The START WITH And CONNECT BY Clauses.  
• The FOR UPDATE Clause.  
• Set Operators.  
• Column Aliases.  
• Fully Qualifying Tables And Columns.  
• Table Aliases.  
• Using DISTINCT And ALL In SELECT Statements.  
• LAB 4:  SQL Query Basics.  
• LAB 4 Solutions:  SQL Query Basics.  
• Chapter Summary.  

• Chapter 5 – Data Manipulation..  
• Chapter Overview…  
• The Data Manipulation Language.  
• The INSERT Command.  
• The UPDATE Command.  
• The Delete Command.  
• Using The DEFAULT Keyword With Updates And Inserts.  
• Using SQL Developer For DML.  
• The Transaction Control Language (TCL) 
• Implicit TCL.  
• LAB 5:  Data Manipulation..  
• LAB 5 Solutions:  Data Manipulation..  
• Chapter Summary.  

• Chapter 6 — WHERE And ORDER BY..  
• Chapter Overview…  
• WHERE Clause Basics.  
• Comparison Operators.  
• Literals And Constants In SQL.  
• Simple Pattern Matching..  
• Logical Operators.  
• The DUAL Table.  
• Arithmetic Operators.  
• Expressions In SQL.  
• Character Operators.  
• Pseudo Columns.  
• ORDER BY Clause Basics.  
• Ordering NULLs.  
• Accent And Case Insensitive Sorts.  
• Sampling Data.  
• WHERE And ORDER BY IN SQL Developer..  
• ALL, ANY, SOME
• LAB 6:  WHERE And ORDER BY..  
• LAB 6 Solutions:  WHERE And ORDER BY..  
• Chapter Summary.  

• Chapter 7 – Functions..  
• Chapter Overview…  
• The Basics Of Oracle Functions.  
• Number Functions.  
• Character Functions.  
• Date Functions.  
• Conversion Functions.  
• Other Functions.  
• Large Object Functions.  
• Error Functions.  
• The RR Format Model.  
• Leveraging Your Knowledge.  
• LAB 7:  Functions.  
• LAB 7 Solutions: Functions.  
• Chapter Summary.  

• Chapter 8 – ANSI 92 Joins..  
• Chapter Overview…  
• Basics Of ANSI 92 Joins.  
• Using Query Builder With Multiple Tables.  
• Table Aliases.  
• Outer Joins.  
• Outer Joins In Query Builder 
• Set Operators.  
• Self-Referential Joins.  
• Non-Equijoins.  
• LAB 8: ANSI 92 Joins.  
• LAB 8 Solutions:  ANSI 92 Joins.  
• Chapter Summary.  

• Chapter 9 – ANSI 99 Joins..  
• Chapter Overview…  
• Changes With ANSI99.  
• CROSS JOIN..  
• NATURAL JOIN..  
• JOIN USING..  
• JOIN ON..  
• LEFT / RIGHT OUTER JOIN..  
• FULL OUTER JOIN..  
• LAB 9:  ANSI 99 Joins.  
• LAB 9 Solutions:  ANSI 99 Joins.  
• Chapter Summary

• Chapter 10 – GROUP BY And HAVING..  
• Chapter Overview…  
• Introduction To Group Functions.  
• Limiting Rows.  
• Including NULLs.  
• Using DISTINCT With Group Functions.  
• Group Function Requirements.  
• The HAVING Clause.  
• Other Group Function Rules.  
• Using Query Builder With Group Clauses.  
• ROLLUP And CUBE..  
• The GROUPING Function.  
• GROUPING SETS..  
• LAB 10:  GROUP BY And HAVING..  
• LAB 10 Solutions:  GROUP BY And HAVING..  
• Chapter Summary

• Chapter 11 – Subqueries..  
• Chapter Overview…  
• Why Use Subqueries?.  
• WHERE Clause Subqueries.  
• From Clause Subqueries.  
• Having Clause Subqueries.  
• Correlated Subqueries.  
• Scalar Subqueries.  
• DML And Subqueries.  
• EXISTS Subqueries.  
• Hierarchical Queries.  
• Top N And Bottom N Queries.  
• Creating Subqueries Using Query Builder 
• LAB 11:  Subqueries.  
• LAB 11 Solutions:  Subqueries.  
• Chapter Summary

• Chapter 12 – Basic Reporting..  
• Chapter Overview…  
• Basic Reporting..  
• The COLUMN Command.  
• Setting Column Width.  
• PRINT | NOPRINT..  
• TTITLE | BTITLE..  
• REPHEADER / REPFOOTER..  
• NEW_VALUE / OLD_VALUE..  
• Using Substitution Variables.  
• The COMPUTE Command.  
• Comments In Script Files.  
• Substitution Variables.  
• Named Substitution Variables.  
• Numbered Substitution Variables.  
• Dealing With Multiple References.  
• Using The DEFINE Command.  
• The ACCEPT And PROMPT Commands.  
• Running Scripts Unattended.  
• LAB 12:  Basic Reporting..  
• LAB 12 Solutions:  Basic Reporting
• Chapter Summary

• Chapter 13 – Data Import And Export..  
• Chapter Overview…  
• Using SQL*Loader With Field Delimited Data.  
• Using SQL*Loader With Comma Delimited Data.  
• Data Loading Using SQL Developer..  
• Exporting Oracle Data Into Excel.  
• Doing An ODBC Query.  
• A Word About Data Pump.  
• LAB 13:  Data Import And Export.  
• LAB 13 Solutions:  Data Import And Export.  
• Chapter Summary

• Chapter 14 – Security..  
• Chapter Overview…  
• Basic Security.  
• SYSTEM Privileges.  
• Object Privileges.  
• The Data Dictionary And Security.  
• Using Roles For Privilege Management.  
• Using Profiles.  
• Kernel Limits.  
• Password Limits.  
• Creating And Using Profiles.  
• LAB 14:  Security.  
• LAB 14 Solutions:  Security.  
• Chapter Summary

• Chapter 15 – Advanced Data Manipulation..  
• Chapter Overview…  
• The MERGE Command..  
• Multiple Column Subquery UPDATEs and DELETEs.  
• DML Against Views.  
• Transactions And Read Consistency.  
• DML Locks.  
• Flashback Technologies.  
• Inserting Large Objects.  
• Changed Data Tracking..  
• Flashback Versions Query.  
• Log Miner 
• Change Data Capture.  
• Flashback Data Archive.  
• LAB 15:  Advanced Data Manipulation..  
• LAB 15 Solutions:  Advanced Data Manipulation..  
• Chapter Summary

• Chapter 16 – Introduction To Data Definition..  
• Chapter Overview…  
• Introduction To DDL Commands.  
• Key Objects.  
• Object Naming Rules.  
• The Data Dictionary.  
• Available Datatypes.  
• Using Extended Datatypes.  
• The CREATE TABLE Statement.  
• Naming Constraints.  
• Integrity Constraints.  
• Primary Keys.  
• Foreign Keys.  
• NOT NULL Constraints.  
• UNIQUE Constraints.  
• CHECK Constraints.  
• DEFAULT Values.  
• IDENTITY Columns.  
• Constraints And CREATE TABLE… AS SELECT..  
• Constraint Limitations.  
• Creating Tables In SQL Developer..  
• Other DDL Actions In SQL Developer..  
• The ALTER TABLE Command..  
• Dropping Objects.  
• Renaming Objects.  
• The TRUNCATE Command..  
• The COMMENT Command..  
• Creating Simple Views.  
• LAB 16:  Introduction To Data Definition..  
• LAB 16 Solutions:  Introduction To Data Definition..  
• Chapter Summary

• Chapter 17 – Advanced Data Definition..  
• Chapter Overview…  
• DDL And The Data Dictionary.  
• Disabling Constraints.  
• Enabling Constraints.  
• Handling Constraint Exceptions.  
• Using Deferrable Constraints.  
• Sequences.  
• External Tables For Data Storage.  
• Why Are External Tables Useful 
• Privileges Needed.  
• Syntax For Creating External Tables.  
• External Tables And The ORACLE_DATAPUMP Driver..  
• Indexes.  
• Guidelines.  
• Index Creation Syntax.  
• Rebuilding Indexes.  
• Function Based Indexes.  
• Comments.  
• Synonyms.  
• CREATE SYNONYM Syntax.  
• Complex Views.  
• Syntax For Views.  
• Virtual Columns.  
• Compressed Tables.  
• Invisible Indexes.  
• Online DDL Enhancements.  
• Invisible Columns.  
• Creating Multiple Indexes On Columns.  
• LAB 17:  Advanced Data Definition..  
• LAB 17 Solutions: Advanced Data Definition..  
• Chapter Summary.  

• Chapter 18 – Regular Expressions..  
• Chapter Overview…  
• Available Regular Expression Functions.  
• Regular Expression Operators.  
• Character Classes.  
• Pattern Matching Options.  
• REGEX_LIKE..  
• REGEXP_SUBSTR..  
• REGEXP INSTR..  
• REGEXP_REPLACE..  
• REGEXP_COUNT.  
• LAB 18:  Regular Expressions.  
• LAB 18 Solutions:  Regular Expressions.  
• Chapter Summary

• Chapter 19 – Analytics..  
• Chapter Overview…  
• The WITH Clause.  
• Reporting Aggregate Functions.  
• Analytical Functions.  
• User-Defined Bucket Histograms.  
• The MODEL Clause.  
• PIVOT And UNPIVOT.  
• Temporal Validity.  
• LAB 19:  Analytics.  
• LAB 19 Solutions:  Analytics.  
• Chapter Summary.  

• Chapter 20 – Analytics II 
• RANKING FUNCTIONS..  
• RANK..  
• DENSE_RANK..  
• CUME_DIST..  
• PERCENT_RANK..  
• ROW_NUMBER..  
• Windowing Aggregate Functions.  
• RATIO_TO_REPORT..  
• LAG / LEAD..  
• Linear Regression Functions.  
• Inverse Percentile Functions.  
• Hypothetical Ranking Functions.  
• Pattern Matching..  
• Appendix A: The EL Data Model..  
• The EL Sample Schema.  
• The Taxes Table.  
• The ZipCodes Table.  
• The Jobs Table.  
• The Departments Table.  
• The Employees Table.  
• Objects In The EL Model 
• Analysis.  
• Join Conditions.  
• The Entity Relationship Model.  
• Appendix B: The ELL Data Model..  
• The ELL Sample Schema.  
• The Nations Table.  
• The Customers Table.  
• The Items Table.  
• The Promotions Table.  
• The SalesData Table.  
• Objects In The ELL Model 
• Analysis.  
• Join Conditions.  
• The Entity Relationship Model.  
• The Channel Function

To Hire a proven Oracle SQL 12C Subject Matter Expert Consultant / Course Author and Instructor who teaches this class, call 800-798-3901 today!