Test Your Oracle JSON SELECT Knowledge with This Engaging SQL Quiz

Progress

True / False

In Oracle, the JSON_EXISTS function is used to check if a specified JSON path exists in a JSON document.

True False

The JSON_EXISTS function checks if a specified JSON path exists within a JSON document, returning true if the path is found.

True / False

In Oracle, you can use the SELECT statement to query JSON data stored in a column.

True False

You can use the SELECT statement to retrieve JSON data stored in a column in Oracle.

True / False

The JSON_QUERY function in Oracle extracts an array or object from a JSON document and returns it as a VARCHAR2 value.

True False

The JSON_QUERY function extracts a JSON array or object and returns it as a VARCHAR2 value.

True / False

In Oracle, the JSON_VALUE function extracts a scalar value from a JSON document and returns it as a SQL data type.

True False

The JSON_VALUE function extracts a scalar value (such as a string, number, or boolean) from a JSON document and returns it as a SQL data type.

True / False

The JSON_TABLE function in Oracle creates a relational view of JSON data, allowing it to be queried like a regular table.

True False

The JSON_TABLE function creates a relational view of JSON data, allowing it to be queried using standard SQL operations.

True / False

In Oracle, the IS JSON condition can be used to check if a column contains valid JSON data.

True False

The IS JSON condition checks whether a column contains valid JSON data.

True / False

The JSON_OBJECT function in Oracle creates a JSON object from the specified key-value pairs and returns it as a VARCHAR2 value.

True False

The JSON_OBJECT function creates a JSON object from the provided key-value pairs and returns it as a VARCHAR2 value.

True / False

In Oracle, you can use the JSON_TABLE function to generate multiple columns from a single JSON document.

True False

The JSON_TABLE function can be used to generate multiple columns, each representing different parts of the JSON document.

True / False

In Oracle, the JSON_MERGEPATCH function is used to apply a JSON Patch to a JSON document, modifying its content.

True False

The JSON_MERGEPATCH function applies a merge patch to a JSON document, not a JSON Patch. JSON Patch and JSON Merge Patch are different specifications for modifying JSON documents.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on using JSON functions like JSON_QUERY, JSON_TABLE, and JSON_VALUE effectively, understanding their syntax, and optimizing their performance in SQL queries.

True False

The OCP certification covers advanced topics, including the effective use of JSON functions, understanding their syntax, and optimizing their performance in SQL queries involving JSON data.

Q11

Single Choice

Which Oracle function is used to retrieve a value from a JSON document stored in a column?

JSON_QUERY JSON_VALUE JSON_TABLEJSON_EXISTS

The JSON_VALUE function is used in Oracle to extract a scalar value from a JSON document.

Q12

Single Choice

In Oracle, what is the correct syntax to select a specific value from a JSON column named data where the key is name?

SELECT JSON_QUERY(data, '$.name') FROM my_table; SELECT JSON_VALUE(data, '$.name') FROM my_table; SELECT data->'$.name' FROM my_table;SELECT JSON_TABLE(data, '$.name') FROM my_table;

The correct syntax to select a specific value from a JSON document is using JSON_VALUE.

Q13

Single Choice

Which Oracle SQL function is used to check if a JSON path exists within a JSON document?

JSON_QUERY JSON_EXISTS JSON_VALUEJSON_TABLE

The JSON_EXISTS function is used to determine if a specified path exists in a JSON document.

Q14

Single Choice

Consider the following Oracle SQL query:What does this query return?

SQL Code

SELECT JSON_VALUE(data, '$.address.city') AS city
FROM customers;

The entire JSON document stored in data The value associated with the address key in the data column The value associated with the city key under the address key in the data columnThe first element in the data column

The query extracts the value of the city key nested under the address key in the JSON document.

Q15

Single Choice

Which of the following Oracle SQL functions can be used to return an array of JSON elements from a JSON document?

JSON_VALUE JSON_TABLE JSON_QUERYJSON_EXISTS

The JSON_QUERY function is used to return an array or an object from a JSON document, rather than a scalar value.

Q16

Single Choice

Consider the following Oracle SQL query:What does this query return?

SQL Code

SELECT JSON_QUERY(data, '$.items') AS items_array
FROM orders;

The value of the items key as a JSON array The first item in the items array The number of items in the items arrayThe entire data document

The JSON_QUERY function is used to retrieve a JSON array or object, and in this query, it returns the array associated with the items key.

Q17

Single Choice

Given the Oracle SQL query:What does this query accomplish?

SQL Code

SELECT JSON_TABLE(data, '$.items[*]'
 COLUMNS (
 item_name VARCHAR2(50) PATH '$.name',
 item_price NUMBER PATH '$.price'
 ))
FROM orders;

It retrieves the entire data column as a JSON object. It transforms each element of the items array into a row with columns item_name and item_price. It returns the first element in the items array.It checks if the items array exists in the JSON document.

The JSON_TABLE function in Oracle transforms a JSON array into a set of rows, each with columns specified by the COLUMNS clause.

Q18

Single Choice

How does Oracle SQL handle errors when the JSON_VALUE function encounters a path that does not exist?

Returns NULL Returns an empty string Throws an errorIgnores the non-existing path and continues

When JSON_VALUE encounters a non-existent path, it returns NULL unless specified otherwise with an error-handling clause.

Q19

Single Choice

Which Oracle SQL function can be used to join JSON data with relational data by treating JSON data as a relational table?

JSON_VALUE JSON_TABLE JSON_QUERYJSON_EXISTS

The JSON_TABLE function converts JSON data into relational rows and columns, allowing it to be joined with relational data.

Q20

Single Choice

Consider the following Oracle SQL query:What is the result of this query?

SQL Code

SELECT c.customer_id, j.product_name
FROM customers c,
 JSON_TABLE(c.orders, '$.order_list[*]'
 COLUMNS (
 product_name VARCHAR2(100) PATH '$.product'
 )) j
WHERE c.customer_status = 'ACTIVE';

It selects all customer_id and product_name for active customers, where product_name is extracted from the JSON document in the orders column. It selects all customer_id and the entire JSON document in the orders column for active customers. It returns the customer_id and counts of products for active customers.It checks if the orders JSON column exists for active customers.

The query uses JSON_TABLE to extract product_name from the orders JSON document and joins it with the customers table, filtering by active customers.

Q21

Multiple Choice

Which SQL code snippet correctly uses JSON_TABLE to extract a JSON object from a column in Oracle?

SQL Code

SELECT id, jt.name
FROM orders,
 JSON_TABLE(order_details, '$.customer'
 COLUMNS (name VARCHAR2(100) PATH '$.name')) jt;

SELECT id, jt.name
FROM orders,
 JSON_TABLE(order_details, '$.customer'
 COLUMNS (name VARCHAR2(100) PATH '$.name')) jt
WHERE jt.name IS NOT NULL;

SELECT id, jt.name
FROM orders,
 JSON_TABLE(order_details, '$.customer'
 COLUMNS (name VARCHAR2(100) PATH '$.name')) jt
WHERE jt.name = 'John Doe';

SELECT id, jt.name FROM orders, JSON_TABLE(order_details, '$.customer' COLUMNS (name VARCHAR2(100) PATH '$.name')) jt; SELECT id, jt.name FROM orders, JSON_TABLE(order_details, '$.customer' COLUMNS (name VARCHAR2(100) PATH '$.name')) jt WHERE jt.name IS NOT NULL; SELECT id, jt.name FROM orders, JSON_TABLE(order_details, '$.customer' COLUMNS (name VARCHAR2(100) PATH '$.name')) jt WHERE jt.name = 'John Doe';SELECT id, name FROM orders WHERE order_details->'customer'->>'name' = 'John Doe';

Options A, B, and C correctly use JSON_TABLE to extract a JSON object from the 'order_details' column. Option D is incorrect because it does not use JSON_TABLE correctly.

Q22

Multiple Choice

Which SQL code snippet demonstrates how to use JSON_VALUE to extract a value from a JSON column in Oracle?

SQL Code

SELECT id, JSON_VALUE(order_details, '$.customer.name') AS customer_name
FROM orders;

SELECT id, JSON_VALUE(order_details, '$.customer.address.city') AS city
FROM orders;

SELECT id, JSON_VALUE(order_details, '$.order_total') AS order_total
FROM orders;

SELECT id, JSON_VALUE(order_details, '$.customer.name') AS customer_name FROM orders; SELECT id, JSON_VALUE(order_details, '$.customer.address.city') AS city FROM orders; SELECT id, JSON_VALUE(order_details, '$.order_total') AS order_total FROM orders;SELECT id, order_details->>'$.customer.name' AS customer_name FROM orders;

Options A, B, and C correctly use JSON_VALUE to extract values from JSON columns in Oracle. Option D is incorrect because it uses a different method for extracting JSON values.

Q23

Multiple Choice

Which SQL code snippet uses JSON_EXISTS to check if a specific key exists in a JSON column in Oracle?

SQL Code

SELECT id
FROM orders
WHERE JSON_EXISTS(order_details, '$.customer.name');

SELECT id
FROM orders
WHERE JSON_EXISTS(order_details, '$.order_total');

SELECT id
FROM orders
WHERE JSON_EXISTS(order_details, '$.items[0]');

SELECT id FROM orders WHERE JSON_EXISTS(order_details, '$.customer.name'); SELECT id FROM orders WHERE JSON_EXISTS(order_details, '$.order_total'); SELECT id FROM orders WHERE JSON_EXISTS(order_details, '$.items[0]');SELECT id FROM orders WHERE order_details->>'$.customer.name' IS NOT NULL;

Options A, B, and C correctly use JSON_EXISTS to check if a specific key exists in a JSON column. Option D is incorrect because it uses a different method for checking JSON keys.

Q24

Multiple Choice

Which SQL code snippet demonstrates advanced use of JSON_TABLE to flatten a nested JSON array into a relational format in Oracle?

SQL Code

SELECT id, jt.product_name, jt.quantity
FROM orders,
 JSON_TABLE(order_details, '$.items[*]'
 COLUMNS (product_name VARCHAR2(50) PATH '$.product_name',
 quantity NUMBER PATH '$.quantity')) jt;

SELECT id, jt.product_name, jt.quantity
FROM orders,
 JSON_TABLE(order_details, '$.items[*]'
 COLUMNS (product_name VARCHAR2(50) PATH '$.product_name',
 quantity NUMBER PATH '$.quantity')) jt
WHERE jt.quantity > 1;

SELECT id, jt.product_name, jt.quantity
FROM orders,
 JSON_TABLE(order_details, '$.items[*]'
 COLUMNS (product_name VARCHAR2(50) PATH '$.product_name',
 quantity NUMBER PATH '$.quantity')) jt
ORDER BY jt.product_name;

SELECT id, jt.product_name, jt.quantity FROM orders, JSON_TABLE(order_details, '$.items[*]' COLUMNS (product_name VARCHAR2(50) PATH '$.product_name', quantity NUMBER PATH '$.quantity')) jt; SELECT id, jt.product_name, jt.quantity FROM orders, JSON_TABLE(order_details, '$.items[*]' COLUMNS (product_name VARCHAR2(50) PATH '$.product_name', quantity NUMBER PATH '$.quantity')) jt WHERE jt.quantity > 1; SELECT id, jt.product_name, jt.quantity FROM orders, JSON_TABLE(order_details, '$.items[*]' COLUMNS (product_name VARCHAR2(50) PATH '$.product_name', quantity NUMBER PATH '$.quantity')) jt ORDER BY jt.product_name;SELECT id, product_name, quantity FROM orders WHERE order_details->>'$.items[0].product_name' = 'Product A';

Options A, B, and C correctly demonstrate advanced use of JSON_TABLE to flatten a nested JSON array into a relational format. Option D is incorrect because it does not correctly use JSON_TABLE.

Q25

Multiple Choice

Which SQL code snippet demonstrates how to use JSON_QUERY to return a JSON fragment from a column in Oracle?

SQL Code

SELECT id, JSON_QUERY(order_details, '$.customer') AS customer_info
FROM orders;

SELECT id, JSON_QUERY(order_details, '$.items[0]') AS first_item
FROM orders;

SELECT id, JSON_QUERY(order_details, '$.shipping') AS shipping_info
FROM orders;

SELECT id, JSON_QUERY(order_details, '$.customer') AS customer_info FROM orders; SELECT id, JSON_QUERY(order_details, '$.items[0]') AS first_item FROM orders; SELECT id, JSON_QUERY(order_details, '$.shipping') AS shipping_info FROM orders;SELECT id, order_details->'$.customer' AS customer_info FROM orders;

Options A, B, and C correctly use JSON_QUERY to return a JSON fragment from a column in Oracle. Option D is incorrect because it uses a different method to return JSON data.

Q26

Multiple Choice

Which SQL code snippet demonstrates advanced use of JSON_VALUE to extract nested values from a JSON column in Oracle?

SQL Code

SELECT id, JSON_VALUE(order_details, '$.customer.address.city') AS city
FROM orders;

SELECT id, JSON_VALUE(order_details, '$.items[0].product_name') AS first_product_name
FROM orders;

SELECT id, JSON_VALUE(order_details, '$.shipping.method') AS shipping_method
FROM orders;

SELECT id, JSON_VALUE(order_details, '$.customer.address.city') AS city FROM orders; SELECT id, JSON_VALUE(order_details, '$.items[0].product_name') AS first_product_name FROM orders; SELECT id, JSON_VALUE(order_details, '$.shipping.method') AS shipping_method FROM orders;SELECT id, order_details->>'$.customer.address.city' AS city FROM orders;

Options A, B, and C correctly demonstrate advanced use of JSON_VALUE to extract nested values from a JSON column. Option D is incorrect because it does not correctly use JSON_VALUE.

Q27

Multiple Choice

Which SQL code snippet demonstrates certification-level use of JSON_TABLE to parse a complex JSON array in Oracle?

SQL Code

SELECT id, jt.product_name, jt.price
FROM orders,
 JSON_TABLE(order_details, '$.items[*]'
 COLUMNS (product_name VARCHAR2(50) PATH '$.product_name',
 price NUMBER PATH '$.price')) jt;

SELECT id, jt.product_name, jt.price
FROM orders,
 JSON_TABLE(order_details, '$.items[*]'
 COLUMNS (product_name VARCHAR2(50) PATH '$.product_name',
 price NUMBER PATH '$.price')) jt
WHERE jt.price > 20;

SELECT id, jt.product_name, jt.price
FROM orders,
 JSON_TABLE(order_details, '$.items[*]'
 COLUMNS (product_name VARCHAR2(50) PATH '$.product_name',
 price NUMBER PATH '$.price')) jt
ORDER BY jt.price DESC;

SELECT id, jt.product_name, jt.price FROM orders, JSON_TABLE(order_details, '$.items[*]' COLUMNS (product_name VARCHAR2(50) PATH '$.product_name', price NUMBER PATH '$.price')) jt; SELECT id, jt.product_name, jt.price FROM orders, JSON_TABLE(order_details, '$.items[*]' COLUMNS (product_name VARCHAR2(50) PATH '$.product_name', price NUMBER PATH '$.price')) jt WHERE jt.price > 20; SELECT id, jt.product_name, jt.price FROM orders, JSON_TABLE(order_details, '$.items[*]' COLUMNS (product_name VARCHAR2(50) PATH '$.product_name', price NUMBER PATH '$.price')) jt ORDER BY jt.price DESC;SELECT id, product_name, price FROM orders WHERE order_details->>'$.items[0].price' > 20;

Options A, B, and C correctly demonstrate certification-level use of JSON_TABLE to parse a complex JSON array in Oracle. Option D is incorrect because it does not correctly use JSON_TABLE.

Q28

Multiple Choice

Which SQL code snippet demonstrates how to use JSON_EXISTS to filter records based on the existence of a specific key in a JSON array in Oracle?

SQL Code

SELECT id
FROM orders
WHERE JSON_EXISTS(order_details, '$.items[0].product_name');

SELECT id
FROM orders
WHERE JSON_EXISTS(order_details, '$.shipping.method');

SELECT id
FROM orders
WHERE JSON_EXISTS(order_details, '$.customer.name');

SELECT id FROM orders WHERE JSON_EXISTS(order_details, '$.items[0].product_name'); SELECT id FROM orders WHERE JSON_EXISTS(order_details, '$.shipping.method'); SELECT id FROM orders WHERE JSON_EXISTS(order_details, '$.customer.name');SELECT id FROM orders WHERE order_details->>'$.items[0].product_name' IS NOT NULL;

Options A, B, and C correctly demonstrate how to use JSON_EXISTS to filter records based on the existence of a specific key in a JSON array. Option D is incorrect because it does not correctly use JSON_EXISTS.

Q29

Multiple Choice

Which SQL code snippet demonstrates advanced use of JSON_QUERY to return a nested JSON object from a column in Oracle?

SQL Code

SELECT id, JSON_QUERY(order_details, '$.shipping') AS shipping_info
FROM orders;

SELECT id, JSON_QUERY(order_details, '$.customer') AS customer_info
FROM orders;

SELECT id, JSON_QUERY(order_details, '$.items') AS items_info
FROM orders;

SELECT id, JSON_QUERY(order_details, '$.shipping') AS shipping_info FROM orders; SELECT id, JSON_QUERY(order_details, '$.customer') AS customer_info FROM orders; SELECT id, JSON_QUERY(order_details, '$.items') AS items_info FROM orders;SELECT id, order_details->'$.shipping' AS shipping_info FROM orders;

Options A, B, and C correctly demonstrate advanced use of JSON_QUERY to return a nested JSON object from a column in Oracle. Option D is incorrect because it does not correctly use JSON_QUERY.

Q30

Multiple Choice

Which SQL code snippet demonstrates certification-level use of JSON_VALUE to extract multiple values from a JSON column in Oracle?

SQL Code

SELECT id, JSON_VALUE(order_details, '$.customer.name') AS customer_name,
 JSON_VALUE(order_details, '$.shipping.method') AS shipping_method
FROM orders;

SELECT id, JSON_VALUE(order_details, '$.customer.name') AS customer_name,
 JSON_VALUE(order_details, '$.items[0].product_name') AS first_product_name
FROM orders;

SELECT id, JSON_VALUE(order_details, '$.customer.address.city') AS city,
 JSON_VALUE(order_details, '$.order_total') AS total_amount
FROM orders;

SELECT id, JSON_VALUE(order_details, '$.customer.name') AS customer_name, JSON_VALUE(order_details, '$.shipping.method') AS shipping_method FROM orders; SELECT id, JSON_VALUE(order_details, '$.customer.name') AS customer_name, JSON_VALUE(order_details, '$.items[0].product_name') AS first_product_name FROM orders; SELECT id, JSON_VALUE(order_details, '$.customer.address.city') AS city, JSON_VALUE(order_details, '$.order_total') AS total_amount FROM orders;SELECT id, JSON_VALUE(order_details, '$.customer.name') AS customer_name FROM orders;

Options A, B, and C correctly demonstrate certification-level use of JSON_VALUE to extract multiple values from a JSON column in Oracle. Option D is incorrect because it does not correctly use JSON_VALUE.

Oracle Database Quiz Questions