Test Your Oracle JSON Data Type Knowledge

Progress

True / False

Oracle supports storing JSON data within its database tables.

True False

Oracle supports the storage of JSON data in database tables, allowing for flexible and efficient data management.

True / False

In Oracle, JSON data can be stored in columns of type VARCHAR2.

True False

JSON data can be stored in columns of type VARCHAR2, CLOB, and BLOB in Oracle databases.

True / False

JSON stands for JavaScript Object Notation.

True False

JSON is a lightweight data interchange format that stands for JavaScript Object Notation, used to store and exchange data.

True / False

Oracle provides functions to query and manipulate JSON data stored in the database.

True False

Oracle provides several functions, such as JSON_VALUE, JSON_QUERY, and JSON_TABLE, to query and manipulate JSON data stored in the database.

True / False

The IS JSON condition in Oracle is used to validate that a column contains valid JSON data.

True False

The IS JSON condition can be used to validate that a column contains well-formed JSON data.

True / False

In Oracle, JSON data can only be stored in tables with a JSON column type.

True False

Oracle does not have a dedicated JSON column type; instead, JSON data can be stored in VARCHAR2, CLOB, or BLOB columns.

True / False

Oracle's JSON_TABLE function can be used to transform JSON data into relational data.

True False

The JSON_TABLE function can transform JSON data into a relational format, making it possible to work with JSON data as if it were structured in a traditional table.

True / False

In Oracle, indexing JSON data stored in a CLOB column requires a different approach than indexing JSON data stored in a VARCHAR2 column.

True False

Indexing JSON data in CLOB columns might require different indexing strategies compared to VARCHAR2 columns, often involving functional indexes and specialized JSON indexing techniques.

True / False

Oracle SQL provides support for JSON path expressions to query JSON data.

True False

Oracle SQL supports JSON path expressions, allowing for efficient querying of JSON data using expressions that navigate through the JSON structure.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes topics on storing, querying, and indexing JSON data in Oracle databases.

True False

The OCP certification exam covers advanced topics, including the best practices for storing, querying, and indexing JSON data in Oracle databases, reflecting the candidate's comprehensive understanding of handling JSON data within Oracle.

Q11

Single Choice

Which Oracle SQL data type is specifically designed to store JSON data?

VARCHAR2 CLOB JSONBLOB

Oracle introduced a native JSON data type specifically for storing JSON data starting from Oracle 21c. It provides optimized storage and indexing for JSON documents.

Q12

Single Choice

What does the following Oracle SQL statement do?

SQL Code

CREATE TABLE orders (
 order_id NUMBER PRIMARY KEY,
 order_data JSON
);

Creates a table with a BLOB column to store binary data. Creates a table with a CLOB column for large text data. Creates a table with a JSON column to store JSON data.Creates a table with a VARCHAR2 column to store text data.

The JSON data type allows the storage of JSON-formatted data within the order_data column.

Q13

Single Choice

In Oracle, which function is used to validate that a column contains properly formatted JSON data?

JSON_FORMAT JSON_VALIDATE IS_JSONJSON_CHECK

The IS_JSON function is used to validate that a column or string contains properly formatted JSON data in Oracle.

Q14

Single Choice

What will the following Oracle SQL query return?

SQL Code

SELECT order_data.name FROM orders WHERE order_data.name = 'John Doe';

The name field from all rows where the name is 'John Doe'. An error because the syntax is incorrect. The entire order_data for the name 'John Doe'.The name field from all rows where the order_id is 'John Doe'

In Oracle, JSON data is accessed using JSON path expressions. The correct syntax would involve using a JSON path expression with the . operator or the JSON_VALUE function.

Q15

Single Choice

How can you extract the name field from a JSON column order_data in Oracle?

SQL Code

SELECT JSON_VALUE(order_data, '$.name') FROM orders;

Returns the entire order_data JSON object. Returns the value of the name field from the order_data JSON column. Returns an error because JSON cannot be queried.Returns a numeric value from the JSON data.

The JSON_VALUE function extracts a scalar value from a JSON column based on a specified JSON path expression.

Q16

Single Choice

Which Oracle SQL function is used to pretty-print a JSON object stored in a JSON column?

SQL Code

SELECT JSON_PRETTY(order_data) FROM orders WHERE order_id = 1;

JSON_FORMAT JSON_STRINGIFY JSON_PRETTYJSON_PRINT

The JSON_PRETTY function formats a JSON object stored in a column or variable for easy reading by human users, with proper indentation and line breaks.

Q17

Single Choice

What is the purpose of the JSON_EXISTS function in Oracle SQL?

SQL Code

SELECT * FROM orders WHERE JSON_EXISTS(order_data, '$.shipping_address');

Checks if a table contains any JSON columns. Checks if the order_data column contains a valid JSON object. Checks if the specified JSON path exists within the order_data column.Checks if the shipping_address column exists in the orders table.

The JSON_EXISTS function checks whether a given path expression exists within a JSON document.

Q18

Single Choice

Which of the following Oracle SQL statements would create a JSON index on the name field within a JSON column?

SQL Code

CREATE INDEX idx_name ON orders (JSON_VALUE(order_data, '$.name'));

Creates a standard index on the entire order_data column. Creates an index on the name field extracted from the order_data JSON column. Creates an index on the order_id column.Creates a composite index on multiple JSON fields.

The CREATE INDEX statement creates an index on a specific field within a JSON column using the JSON_VALUE function.

Q19

Single Choice

How would you merge two JSON objects stored in Oracle JSON columns?

SQL Code

SELECT JSON_MERGEPATCH(order_data, new_data) FROM orders;

Merges the two JSON objects into a single JSON object. Replaces the first JSON object with the second one. Returns an error because JSON objects cannot be merged.Combines the two JSON objects into a text string.

The JSON_MERGEPATCH function merges two JSON objects, allowing you to combine or update the contents of one with the contents of another.

Q20

Single Choice

Which of the following is true about using the JSON data type in Oracle compared to storing JSON data in a CLOB column?

The JSON data type is deprecated in favor of CLOB. The JSON data type offers better performance and storage efficiency for JSON data. The CLOB data type is better suited for indexing JSON data.Both JSON and CLOB provide the same performance for JSON data storage.

The JSON data type is optimized for storing and querying JSON data in Oracle, offering better performance and storage efficiency compared to using CLOB or other text-based data types.

Q21

Multiple Choice

Identify the SQL scripts that correctly define a JSON column in an Oracle table.

CREATE TABLE products ( product_id NUMBER PRIMARY KEY, product_details JSON ); CREATE TABLE products ( product_id NUMBER PRIMARY KEY, product_details CLOB CHECK (product_details IS JSON) ); CREATE TABLE products ( product_id NUMBER PRIMARY KEY, product_details VARCHAR2(4000) CHECK (product_details IS JSON) );CREATE TABLE products ( product_id NUMBER PRIMARY KEY, product_details JSON CONSTRAINT check_json CHECK (product_details IS JSON) );

Defining a JSON column in Oracle involves using the appropriate data type and constraints to ensure the integrity of JSON data. The correct scripts will demonstrate how to define a JSON column that validates the JSON structure.

Q22

Multiple Choice

Select the SQL scripts that correctly insert JSON data into a table with a JSON column in Oracle.

INSERT INTO orders (order_id, order_data) VALUES (1, '{"customer": "John Doe", "items": ["item1", "item2"]}'); INSERT INTO orders (order_id, order_data) VALUES (1, JSON '{"customer": "John Doe", "items": ["item1", "item2"]}'); INSERT INTO orders (order_id, order_data) VALUES (1, '{"customer": "John Doe", "items": ["item1", "item2"]}'::JSON);INSERT INTO orders (order_id, order_data) VALUES (1, '{"customer": "John Doe", "items": ["item1", "item2"]}'::CLOB);

Inserting JSON data into a JSON column requires the data to be correctly formatted and validated as JSON. The correct scripts will demonstrate the proper syntax for inserting JSON data.

Q23

Multiple Choice

Determine the SQL scripts that correctly query data from a JSON column in Oracle.

SELECT order_id, JSON_VALUE(order_data, '$.customer') AS customer_name FROM orders; SELECT order_id, JSON_QUERY(order_data, '$.customer') AS customer_name FROM orders; SELECT order_id, order_data.customer AS customer_name FROM orders;SELECT order_id, JSON_VALUE(order_data, '$.customer') AS customer_name FROM orders WHERE JSON_EXISTS(order_data, '$.customer');

Querying JSON data involves using functions like JSON_VALUE or JSON_QUERY to extract specific values. The correct scripts will demonstrate how to retrieve data from a JSON column.

Q24

Multiple Choice

Identify the SQL scripts that correctly update a JSON column in Oracle.

UPDATE orders SET order_data = JSON_MERGEPATCH(order_data, '{"items": ["item3"]}') WHERE order_id = 1; UPDATE orders SET order_data = JSON_MERGE(order_data, '{"items": ["item3"]}') WHERE order_id = 1; UPDATE orders SET order_data = JSON_MODIFY(order_data, '$.items', 'item3') WHERE order_id = 1;UPDATE orders SET order_data = JSON_TRANSFORM(order_data, '$.items', 'item3') WHERE order_id = 1;

Updating JSON data requires using JSON functions like JSON_MERGEPATCH to modify specific parts of the JSON structure while preserving the overall format. The correct scripts will demonstrate this process.

Q25

Multiple Choice

Select the SQL scripts that correctly delete JSON data within a column in Oracle.

UPDATE orders SET order_data = JSON_REMOVE(order_data, '$.customer') WHERE order_id = 1; UPDATE orders SET order_data = JSON_MERGEPATCH(order_data, '{"customer": null}') WHERE order_id = 1; UPDATE orders SET order_data = JSON_DELETE(order_data, '$.customer') WHERE order_id = 1;UPDATE orders SET order_data = JSON_TRANSFORM(order_data, '$.customer', null) WHERE order_id = 1;

Deleting parts of JSON data can be done using functions that manipulate the JSON structure, such as JSON_REMOVE or JSON_MERGEPATCH. The correct scripts will demonstrate this.

Q26

Multiple Choice

Determine the SQL scripts that correctly validate JSON data in Oracle.

ALTER TABLE orders ADD CONSTRAINT chk_json CHECK (order_data IS JSON); ALTER TABLE orders MODIFY order_data JSON CHECK (order_data IS VALID_JSON); ALTER TABLE orders ADD CONSTRAINT chk_json CHECK (order_data IS VALID_JSON);ALTER TABLE orders MODIFY order_data JSON CONSTRAINT chk_json CHECK (order_data IS JSON);

Validating JSON data can be done using constraints or checks that ensure the data conforms to JSON standards. The correct scripts will demonstrate how to implement this validation.

Q27

Multiple Choice

Identify the SQL scripts that correctly define a table with multiple JSON columns in Oracle.

CREATE TABLE user_profiles ( user_id NUMBER PRIMARY KEY, profile_data JSON, settings JSON ); CREATE TABLE user_profiles ( user_id NUMBER PRIMARY KEY, profile_data CLOB CHECK (profile_data IS JSON), settings CLOB CHECK (settings IS JSON) ); CREATE TABLE user_profiles ( user_id NUMBER PRIMARY KEY, profile_data JSON, settings CLOB CHECK (settings IS JSON) );CREATE TABLE user_profiles ( user_id NUMBER PRIMARY KEY, profile_data CLOB CHECK (profile_data IS JSON), settings JSON );

Creating a table with multiple JSON columns requires defining each column with the JSON data type and ensuring JSON validation. The correct scripts will demonstrate this setup.

Q28

Multiple Choice

Select the SQL scripts that correctly handle indexing on a JSON column in Oracle.

CREATE INDEX idx_customer ON orders (JSON_VALUE(order_data, '$.customer')); CREATE INDEX idx_customer ON orders (JSON_QUERY(order_data, '$.customer')); CREATE INDEX idx_customer ON orders (order_data.customer);CREATE INDEX idx_customer ON orders (JSON_EXTRACT(order_data, '$.customer'));

Indexing JSON data involves creating a function-based index on specific keys within the JSON structure. The correct scripts will demonstrate how to create such an index.

Q29

Multiple Choice

Determine the SQL scripts that correctly handle the scenario of altering a table to add a JSON column in Oracle.

ALTER TABLE customers ADD preferences JSON; ALTER TABLE customers ADD preferences CLOB CHECK (preferences IS JSON); ALTER TABLE customers MODIFY preferences JSON CONSTRAINT chk_json CHECK (preferences IS JSON);ALTER TABLE customers ADD preferences CLOB CHECK (preferences IS VALID_JSON);

Altering a table to add a JSON column involves defining the new column with the JSON data type and adding constraints for validation. The correct scripts will demonstrate this process.

Q30

Multiple Choice

Identify the SQL scripts that correctly drop a JSON column from an Oracle table.

ALTER TABLE orders DROP COLUMN order_data; ALTER TABLE orders REMOVE COLUMN order_data; ALTER TABLE orders DROP order_data;ALTER TABLE orders DELETE COLUMN order_data;

Dropping a column from a table in Oracle is done using the ALTER TABLE DROP COLUMN statement. The correct scripts will demonstrate how to drop a JSON column.

Oracle Database Quiz Questions