Oracle 12 Cloud: Advanced SQL/PLSQL Hands-On

• get familiar with the fundamentals of the Oracle database, including tables, constraints, writing SQL queries, and using Oracle SQL Developer

Oracle SQL is a necessary skill for any Oracle professional. In this course we overs advanced aspects of coding using SQL in an Oracle Database 12c environment.The following key topics 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.

• 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

  Oracle SQL is a necessary skill for any Oracle professional, alongside expertise in PL/SQL, performance tuning, database administration, data modeling, and understanding of Oracle architecture. Mastery of these skills ensures effective database management and optimization.

  1. SQL Proficiency

     • Writing complex queries, joins, subqueries, and set operations.

     • Familiarity with common functions (aggregate, string, date/time, etc.).

     • Understanding of SQL standards and best practices.

  2. Database Administration

     • Creating, modifying, and managing databases, tables, indexes, and schemas.

     • User and role management, including permissions and security.

     • Backup and restore procedures using pg_dump and pg_restore.

  3. Performance Tuning

     • Analyzing query performance using EXPLAIN and ANALYZE.

     • Index optimization and understanding indexing strategies.

     • Optimizing queries for better performance and reduced resource usage.

  4. PL/pgSQL Programming

     • Writing and maintaining stored procedures, functions, and triggers.

     • Error handling and control structures in PL/pgSQL.

     • Implementing complex business logic within the database.

  5. Data Modeling

     • Designing and normalizing database schemas.

     • Understanding relational database concepts and entity-relationship modeling.

     • Implementing constraints (primary key, foreign key, unique, etc.).