Oracle Database 12c: Advanced SQL

2h 37mIntermediate2018-03-29

Authors

David Yahalom

Certified Database Expert and Cloud Solutions Architect

Course details

Mastering SQL is an essential skill for any Oracle professional—and the first step in becoming a true Oracle expert. In this course, Oracle instructor and consultant David Yahalom covers advanced aspects of coding using SQL in an Oracle Database 12c environment. David goes over key topics that can help you develop your Oracle SQL expertise, including database concurrency; transactions; Oracle-specific schema objects such as indexes and PL/SQL procedures, functions, and packages; and table partitioning.

Learning objectives
Setting up the Oracle Database 12c environment
Writing subqueries and inline views
Creating table indexes to boost query performance
Using the Oracle Data Dictionary
Database transactions
Creating sequences and triggers
Table partitioning benefits
Using LIST, RANGE, and HASH partitions

Skills covered

Oracle DatabaseDatabase AdministrationDatabase DevelopmentOracleDatabase ManagementData AnalysisProgramming LanguagesData ScienceBusiness Analysis and StrategyBusiness Software and ToolsSoftware DevelopmentDeep Dive (X:Y)

Concepts

0. Introduction

01 - Welcome
02 - What you need to know
03 - Exercise files

1. Setting Up the Environment

04 - Oracle Developer VM overview
05 - Open Oracle SQL Developer
06 - Create a database connection

2. Subqueries

07 - Introduction to subqueries
08 - Using single-value subqueries
09 - Using multiple-value subqueries
10 - Using correlated subqueries
11 - Using multicolumn subqueries
12 - Using inline views

3. Indexes

13 - Introduction to indexes
14 - Creating indexes

4. The Oracle Data Dictionary

15 - Introduction to the Oracle data dictionary
16 - Using the DICT view
17 - Querying data dictionary views

5. Database Concurrency

18 - Overview of database transactions
19 - Database transactions in action
20 - Transaction locks

6. Advanced Oracle Schema Objects

21 - Introduction to sequences
22 - Creating sequences
23 - Using identity columns
24 - Introduction to table triggers
25 - Creating triggers
26 - Introduction to PL SQL procedures
27 - Introduction to PL SQL functions
28 - Introduction to PL SQL packages
29 - Creating PL SQL procedures and functions
30 - Creating synonyms
31 - Creating simple views
32 - Creating complex views

7. Table Partitioning

33 - Introduction to partitioning
34 - Table partitioning benefits
35 - Table partitioning types
36 - Using LIST partitions
37 - Splitting LIST partitions
38 - Using RANGE partitions
39 - Using HASH partitions

Conclusion

40 - What's next