The Ultimate Oracle SQL Training: All In One

Learn relational data concepts in Oracle, including tables, normalization, and constraints, how Oracle stores xml and json; query the data dictionary to inspect structure and enforce primary and foreign keys.

Explore how JSON data is stored as formatted text in JavaScript object notation, including objects and arrays, and learn to query a JSON column to inspect its contents.

Create a json table with a json column named json call and insert json samples; build tables with a foreign key to demonstrate differences between json and relational data.

Explore relational sql basics, creating and modifying tables, and enforcing constraints. Learn to join tables and enforce referential integrity using foreign keys.

Compare Oracle json sql with relational sql and query json data using notation to extract fields, while using json value, json table, and explain plan to optimize evaluation.

Explore json notation to navigate json data with dot notation, returning scalar values or json arrays, and learn to query fields like name and title using a table alias.

Learn how to query JSON data using dot notation in Oracle, returning scalars or JSON structures, and use aliases to disambiguate columns while handling case sensitivity and varying structures.

Explore explain plans for dot notation queries and see how Oracle chooses access methods, including full table scans or indexes, and how JSON query and JSON value are applied.

Compare dot notation sql queries with relational queries, highlighting how table joins and constraints affect data retrieval and how json queries translate to json functions.

Explore strict versus lax JSON handling in Oracle, compare standards-compliant JSON with lax, and see how quoted field names and constraints affect insertions in a demo table.

Learn how Oracle enforces json key uniqueness with two models: unique keys and non-unique keys, using a check constraint on a two-column json table, showing inserts that succeed or fail.

Explore the is json function that validates text as json, supporting strict or lax formats and unique or non-unique keys, and apply it in constraints or SQL queries.

This lecture compares Jason's unique keys with relational constraints using the famous zebra table, illustrating how translate and replace based checks struggle to enforce Jason's strict uniqueness in relational databases.

Explore the Jason Exists function in Oracle SQL that searches JSON data for a key or value and returns matching rows, including cases with or without a name key.

Explore the JSON exists function in the where clause of a SQL query, returning true or false to filter records by JSON keys like name and reason.

Learn about the error clause in the JSON exists function and how bad JSON data affects results, with true on error, false on error, and the default behavior.

Explore how explain plan shows the JSON exists predicate is translated into an internal function, filtering data and triggering a full table scan on small tables lacking an index.

Compare json data handling with sql against relational tables in Oracle using json functions, illustrating exists, like, and substring queries with a Marty example.

Learn how the JSON exist function searches JSON data for specific element values, using equality, inequality, and starts with criteria, with Oracle 12.2 cloud examples.

Explore the JSON_EXISTS passing clause in Oracle SQL 12.2 to pass dynamic values, with examples for matching, equality, and inequality on a name element.

Explore the Jason value function to extract json data with dot notation, navigate the root "$" and arrays starting at zero, and filter results with where clauses.

Learn how the json_value error clause shapes behavior on errors, including null on error and a default value, with not there at all and non-scalar cases.

Explore how the returning clause in the Jason value function converts JSON data to a specified data type and length, ensuring upstream compatibility and catching errors.

Explore the empty clause of the chosen value function and how json value behaves when an element is missing, including the null default, the error option, and Oracle Cloud demonstrations.

Explore how Oracle explain plans handle json value function queries on a json table, showing full table scans and filters when where clauses match Zigbee, with returning or error clauses.

Compare relational sql queries with the json value function to extract json data and join relational tables by zebra name Zigbee, illustrating different data navigation approaches.

Learn to work with json data in sql, choosing json value, json query, and json table, and extract nested years into a relational table.

Learn how the columns clause in json functions defines data types and lengths for json_table results, and extract titles and years from json data while avoiding silent failures.

Learn how the returning clause determines the data type and length sent upstream, not the actual function return, and avoid hidden failures with error on error and substring.

Learn how to handle json results from json functions, including arrays and objects, by using inline views and json table functions to extract titles and years and insert multiple rows.

Explore the default on error, empty on error, and error on error clauses with json exists, json query, and json value, highlighting data type and literal requirements in SQL.

Explore querying json data in sql using where clauses with dot notation, filtering by name equals Marty, handling errors with defaults, and using exists and json value for nested arrays.

Learn how wrappers enclose JSON array elements with square brackets and how the wrapper clause shapes scalar and array returns in Oracle Jason functions.

Use the json table function with a nested path to flatten name and reason data into columns (MDM, Title, Year), yielding one row per reason and enabling ordering.

Learn how arrays in JSON use square brackets and zero-based indices to access elements, including handling non-unique values. Practice querying with star and indices to retrieve all or single elements.

Explore how the explain plan helps debug Oracle json queries and verify index usage, using the Dbmr X Plan package to view the access plan and index decisions.

Learn how the json query function parses json data and returns a json object or a scalar value, using wrappers and dot notation with example data.

Explore the Oracle returning clause, handling JSON and scalar outputs, specifying explicit data types, avoiding default VARCHAR2(4000), and preventing type mismatch errors through practical examples.

Learn how the wrapper clause adds square brackets to scalar results in JSON queries. Understand unconditional and conditional wrappers, and when a value is left unwrapped if it's already JSON.

Explore the error clause in the JSON query function, learn default null behavior, and switch to error on error or empty on error with concrete examples.

Discover how Oracle explain plans behave when executing JSON queries, showing that full table access scans occur regardless of JSON wrappers, arrays, or exploding plans on a JSON wannabe table.

Compare json query results with standard sql, highlighting differences in value formatting, length handling, and error behavior, including json returns null or empty json objects while sql raises errors.

Explore how the json table function converts json structures into relational tables in a query, creating an inline data source in the from clause.

Explore the columns clause of the json table function, defining column names and types, and returning scalar, json, or exists results with format json and a wrapper.

Explore error handling in the JSON table function, choosing to return null, raise errors, or supply defaults, with row level and column level rules that override each other.

Learn how to format Jason data in Oracle SQL, using format Jason to return non scalar values from a reason column and handle Jason values.

Learn how to use wrappers in Oracle SQL to return scalar values as JSON, using the with wrapper and conditional wrapper clauses to control square bracket wrapping.

Demonstrate the nested clause of the JSON table function to flatten arrays into columns, with a depth indicator, using the Jason dataset to navigate paths and render titles and years.

Learn how to navigate array values in the JSON table function using index-based access and nested path, then mix and match to display elements as columns or rows.

Explore Oracle query explain plans for JSON table calls, using JSON functions, exists, and where clauses to reveal how data is retrieved, filtered, and displayed.

Compare the json table function with relational sql, explain column length, nested path for arrays, and use subqueries to count elements and mimic exists behavior.

Learn how database indexes speed data access by guiding queries to the index first, avoiding full table scans. See example with json data and explain plans to verify index usage.

Learn how relational table indexes speed queries by creating an index on the zebra name column in Oracle, comparing full table scans to an index range scan with explain plan.

Learn how to create and use json value indexes on json tables in Oracle, including virtual columns, dot notation, and how explain plans show index usage for faster queries.

Explore explain plans to diagnose JSON indexing, compare dot notation and JSON value indexes, and verify estimated versus actual results with real-time plans and JSON exists checks.