Test Your Oracle SQL PIVOT Skills with This Data Transformation Quiz

Progress

True / False

In Oracle, the PIVOT operator is used to rotate rows into columns.

True False

The PIVOT operator transforms rows into columns, allowing you to display summary data in a more readable format.

True / False

The PIVOT operator in Oracle can only be used with numeric data types.

True False

The PIVOT operator can be used with various data types, not just numeric data types.

True / False

In Oracle, the PIVOT clause must include an aggregate function such as SUM, COUNT, or AVG.

True False

The PIVOT clause requires an aggregate function to summarize data as it rotates rows into columns.

True / False

In Oracle, the PIVOT clause can be used in combination with the UNPIVOT clause in the same query.

True False

You can combine PIVOT and UNPIVOT clauses in the same query to transform data as needed.

True / False

The PIVOT clause in Oracle allows for multiple aggregate functions to be used simultaneously.

True False

You can use multiple aggregate functions in the PIVOT clause to generate different summaries in the pivoted result set.

True / False

In Oracle, you can use column aliases within the PIVOT clause to rename the pivoted columns.

True False

Column aliases can be used within the PIVOT clause to provide meaningful names to the pivoted columns.

True / False

In Oracle, the PIVOT operator can be used in subqueries to create complex pivoted datasets.

True False

The PIVOT operator can be used in subqueries to transform and summarize data before using it in the main query.

True / False

The PIVOT clause in Oracle can be optimized using indexes on the columns involved in the pivot operation.

True False

Proper indexing on the columns involved in the PIVOT operation can improve query performance by speeding up data retrieval and aggregation.

True / False

In Oracle, the PIVOT operator can only pivot data based on one column at a time.

True False

The PIVOT operator can pivot data based on multiple columns simultaneously by specifying multiple columns in the pivoting clause.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on using the PIVOT operator effectively, optimizing its performance, and understanding its syntax and use cases in complex SQL queries.

True False

The OCP certification covers advanced topics, including the effective use of the PIVOT operator, optimizing its performance, and understanding its syntax and use cases in complex SQL queries.

Q11

Single Choice

What is the primary purpose of the PIVOT clause in ORACLE SQL?

To transpose rows into columns To delete rows from a table To join multiple tablesTo aggregate data without changing the structure

The PIVOT clause in Oracle SQL is used to rotate rows into columns, making it easier to analyze data in a cross-tabular format.

Q12

Single Choice

Which of the following is a required component of an ORACLE SQL PIVOT clause?

An aggregate function like SUM or COUNT A WHERE clause A JOIN clauseA GROUP BY clause

The PIVOT clause requires an aggregate function to summarize data while transposing rows into columns.

Q13

Single Choice

What does the following ORACLE SQL query do?

SQL Code

SELECT *
FROM sales
PIVOT (SUM(amount) FOR product_id IN (101, 102, 103));

Transposes product_id values 101, 102, 103 into columns with their respective sales amounts Filters the sales table for product_id 101, 102, 103 Joins the sales table with another table on product_idReturns a summary of sales grouped by product_id

The query uses the PIVOT clause to transform the specified product_id values into columns, with the SUM of the amount for each.

Q14

Single Choice

In ORACLE SQL, which part of the PIVOT clause specifies the values to be transposed into columns?

In ORACLE SQL, which part of the PIVOT clause specifies the values to be transposed into columns? The FOR keyword The IN listThe FROM clause

The query uses the PIVOT clause to transform the specified product_id values into columns, with the SUM of the amount for each.

Q15

Single Choice

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

SQL Code

SELECT *
FROM employees
PIVOT (COUNT(employee_id) FOR department_id IN (10, 20, 30));

The total number of employees in departments 10, 20, and 30 A count of employees in departments 10, 20, and 30, transposed into columns The total count of employees in the companyA list of departments with their employee IDs

The query counts the number of employees in departments 10, 20, and 30, and displays these counts in separate columns.

Q16

Single Choice

Which of the following ORACLE SQL queries correctly uses PIVOT to display sales amounts by region?

SELECT region, product_id, SUM(amount) FROM sales GROUP BY region, product_id; SELECT * FROM sales PIVOT (SUM(amount) FOR region IN ('North', 'South', 'East', 'West')); SELECT * FROM sales PIVOT (COUNT(region) FOR amount IN ('North', 'South', 'East', 'West'));SELECT region, SUM(amount) FROM sales WHERE region IN ('North', 'South', 'East', 'West') GROUP BY region;

This query correctly uses the PIVOT clause to display sales amounts by region, with regions transposed into columns.

Q17

Single Choice

What is the result of using multiple aggregate functions within the PIVOT clause in ORACLE SQL?

The query will return an error The query will aggregate data for each specified function and transpose it into multiple columns The query will only execute the first aggregate functionThe query will ignore the PIVOT clause

Using multiple aggregate functions in a PIVOT clause will result in multiple columns, one for each aggregate function, transposed based on the specified criteria.

Q18

Single Choice

Consider the following ORACLE SQL query:What will this query return?

SQL Code

SELECT *
FROM orders
PIVOT (SUM(order_total) AS total, COUNT(order_id) AS order_count FOR order_date IN ('2023-01-01', '2023-01-02'));

The total sales and number of orders for each specified date, transposed into columns The total sales only for the specified dates A count of orders only for the specified datesA list of orders placed on the specified dates

The query uses PIVOT to calculate the total sales and order count for each specified date and transposes these results into columns.

Q19

Single Choice

Which of the following ORACLE SQL queries will correctly transpose department names into columns showing the average salary of employees in each department?

SELECT department_name, AVG(salary) FROM employees GROUP BY department_name; SELECT * FROM employees PIVOT (AVG(salary) FOR department_name IN ('HR', 'IT', 'Finance')); SELECT department_name, salary FROM employees PIVOT (AVG(salary) FOR department_name IN ('HR', 'IT', 'Finance'));SELECT * FROM employees PIVOT (SUM(salary) FOR department_name IN ('HR', 'IT', 'Finance'));

This query correctly uses the PIVOT clause to transpose department names into columns and calculate the average salary for each department.

Q20

Single Choice

Given the following ORACLE SQL query:What does this query accomplish?

SQL Code

SELECT *
FROM (SELECT department_id, employee_id, salary FROM employees)
PIVOT (MAX(salary) FOR department_id IN (10, 20, 30));

Displays the maximum salary for each employee in departments 10, 20, and 30, transposed into columns Displays the maximum salary for each department without transposing Displays the total salary for each employee in departments 10, 20, and 30Displays all employees in departments 10, 20, and 30 without any aggregation

This query transposes the department IDs into columns and shows the maximum salary for each employee in departments 10, 20, and 30.

Q21

Multiple Choice

Which SQL code snippet demonstrates the use of SQL PIVOT to transform rows into columns for monthly sales data in Oracle?

SQL Code

SELECT *
FROM (SELECT sale_month, product_name, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR sale_month IN ('Jan', 'Feb', 'Mar'));

SELECT *
FROM (SELECT sale_month, product_name, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR sale_month IN ('Jan', 'Feb', 'Mar'));

SELECT product_name, 'Jan' AS January, 'Feb' AS February, 'Mar' AS March
FROM (SELECT sale_month, product_name, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR sale_month IN ('Jan', 'Feb', 'Mar'));

SELECT * FROM (SELECT sale_month, product_name, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR sale_month IN ('Jan', 'Feb', 'Mar')); SELECT * FROM (SELECT sale_month, product_name, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR sale_month IN ('Jan', 'Feb', 'Mar')); SELECT product_name, 'Jan' AS January, 'Feb' AS February, 'Mar' AS March FROM (SELECT sale_month, product_name, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR sale_month IN ('Jan', 'Feb', 'Mar'));SELECT product_name, sale_amount FROM sales WHERE sale_month = 'Jan';

Options A, B, and C correctly demonstrate the use of SQL PIVOT to transform rows into columns for monthly sales data. Option D is incorrect because it does not correctly apply the SQL PIVOT function.

Q22

Multiple Choice

Which SQL code snippet uses SQL PIVOT to display the total sales amount per product for each quarter in Oracle?

SQL Code

SELECT *
FROM (SELECT product_name, quarter, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));

SELECT *
FROM (SELECT product_name, quarter, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));

SELECT product_name, 'Q1' AS Q1, 'Q2' AS Q2, 'Q3' AS Q3, 'Q4' AS Q4
FROM (SELECT product_name, quarter, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));

SELECT * FROM (SELECT product_name, quarter, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')); SELECT * FROM (SELECT product_name, quarter, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')); SELECT product_name, 'Q1' AS Q1, 'Q2' AS Q2, 'Q3' AS Q3, 'Q4' AS Q4 FROM (SELECT product_name, quarter, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));SELECT product_name, sale_amount FROM sales WHERE quarter = 'Q1';

Options A, B, and C correctly use SQL PIVOT to display the total sales amount per product for each quarter. Option D is incorrect because it does not use the SQL PIVOT function.

Q23

Multiple Choice

Which SQL code snippet demonstrates the use of SQL PIVOT to calculate the average sales amount per region for each month in Oracle?

SQL Code

SELECT *
FROM (SELECT region_name, sale_month, sale_amount
FROM sales)
PIVOT (AVG(sale_amount) FOR sale_month IN ('Jan', 'Feb', 'Mar', 'Apr'));

SELECT *
FROM (SELECT region_name, sale_month, sale_amount
FROM sales)
PIVOT (AVG(sale_amount) FOR sale_month IN ('Jan', 'Feb', 'Mar', 'Apr'));

SELECT region_name, 'Jan' AS January, 'Feb' AS February, 'Mar' AS March, 'Apr' AS April
FROM (SELECT region_name, sale_month, sale_amount
FROM sales)
PIVOT (AVG(sale_amount) FOR sale_month IN ('Jan', 'Feb', 'Mar', 'Apr'));

SELECT * FROM (SELECT region_name, sale_month, sale_amount FROM sales) PIVOT (AVG(sale_amount) FOR sale_month IN ('Jan', 'Feb', 'Mar', 'Apr')); SELECT * FROM (SELECT region_name, sale_month, sale_amount FROM sales) PIVOT (AVG(sale_amount) FOR sale_month IN ('Jan', 'Feb', 'Mar', 'Apr')); SELECT region_name, 'Jan' AS January, 'Feb' AS February, 'Mar' AS March, 'Apr' AS April FROM (SELECT region_name, sale_month, sale_amount FROM sales) PIVOT (AVG(sale_amount) FOR sale_month IN ('Jan', 'Feb', 'Mar', 'Apr'));SELECT region_name, sale_amount FROM sales WHERE sale_month = 'Jan';

Options A, B, and C correctly demonstrate the use of SQL PIVOT to calculate the average sales amount per region for each month. Option D is incorrect because it does not use the SQL PIVOT function.

Q24

Multiple Choice

Which SQL code snippet demonstrates advanced use of SQL PIVOT to display total sales by category for different years in Oracle?

SQL Code

SELECT *
FROM (SELECT category_name, sale_year, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR sale_year IN (2019, 2020, 2021));

SELECT *
FROM (SELECT category_name, sale_year, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR sale_year IN (2019, 2020, 2021));

SELECT category_name, '2019' AS Year_2019, '2020' AS Year_2020, '2021' AS Year_2021
FROM (SELECT category_name, sale_year, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR sale_year IN (2019, 2020, 2021));

SELECT * FROM (SELECT category_name, sale_year, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR sale_year IN (2019, 2020, 2021)); SELECT * FROM (SELECT category_name, sale_year, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR sale_year IN (2019, 2020, 2021)); SELECT category_name, '2019' AS Year_2019, '2020' AS Year_2020, '2021' AS Year_2021 FROM (SELECT category_name, sale_year, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR sale_year IN (2019, 2020, 2021));SELECT category_name, sale_amount FROM sales WHERE sale_year = 2019;

Options A, B, and C correctly demonstrate advanced use of SQL PIVOT to display total sales by category for different years. Option D is incorrect because it does not use the SQL PIVOT function.

Q25

Multiple Choice

Which SQL code snippet demonstrates the use of SQL PIVOT to find the maximum sales per employee for each region in Oracle?

SQL Code

SELECT *
FROM (SELECT employee_name, region_name, sale_amount
FROM sales)
PIVOT (MAX(sale_amount) FOR region_name IN ('North', 'South', 'East', 'West'));

SELECT *
FROM (SELECT employee_name, region_name, sale_amount
FROM sales)
PIVOT (MAX(sale_amount) FOR region_name IN ('North', 'South', 'East', 'West'));

SELECT employee_name, 'North' AS North_Region, 'South' AS South_Region, 'East' AS East_Region, 'West' AS West_Region
FROM (SELECT employee_name, region_name, sale_amount
FROM sales)
PIVOT (MAX(sale_amount) FOR region_name IN ('North', 'South', 'East', 'West'));

SELECT * FROM (SELECT employee_name, region_name, sale_amount FROM sales) PIVOT (MAX(sale_amount) FOR region_name IN ('North', 'South', 'East', 'West')); SELECT * FROM (SELECT employee_name, region_name, sale_amount FROM sales) PIVOT (MAX(sale_amount) FOR region_name IN ('North', 'South', 'East', 'West')); SELECT employee_name, 'North' AS North_Region, 'South' AS South_Region, 'East' AS East_Region, 'West' AS West_Region FROM (SELECT employee_name, region_name, sale_amount FROM sales) PIVOT (MAX(sale_amount) FOR region_name IN ('North', 'South', 'East', 'West'));SELECT employee_name, sale_amount FROM sales WHERE region_name = 'North';

Options A, B, and C correctly use SQL PIVOT to find the maximum sales per employee for each region. Option D is incorrect because it does not use the SQL PIVOT function.

Q26

Multiple Choice

Which SQL code snippet demonstrates advanced use of SQL PIVOT to generate a report showing the total sales amount by quarter and product category in Oracle?

SQL Code

SELECT *
FROM (SELECT category_name, quarter, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));

SELECT *
FROM (SELECT category_name, quarter, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));

SELECT category_name, 'Q1' AS Q1_Sales, 'Q2' AS Q2_Sales, 'Q3' AS Q3_Sales, 'Q4' AS Q4_Sales
FROM (SELECT category_name, quarter, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));

SELECT * FROM (SELECT category_name, quarter, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')); SELECT * FROM (SELECT category_name, quarter, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')); SELECT category_name, 'Q1' AS Q1_Sales, 'Q2' AS Q2_Sales, 'Q3' AS Q3_Sales, 'Q4' AS Q4_Sales FROM (SELECT category_name, quarter, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));SELECT category_name, sale_amount FROM sales WHERE quarter = 'Q1';

Options A, B, and C correctly demonstrate advanced use of SQL PIVOT to generate a report showing the total sales amount by quarter and product category. Option D is incorrect because it does not use the SQL PIVOT function.

Q27

Multiple Choice

Which SQL code snippet demonstrates certification-level use of SQL PIVOT to analyze sales data by product and year in Oracle?

SQL Code

SELECT *
FROM (SELECT product_name, sale_year, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR sale_year IN (2018, 2019, 2020, 2021));

SELECT *
FROM (SELECT product_name, sale_year, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR sale_year IN (2018, 2019, 2020, 2021));

SELECT product_name, '2018' AS Year_2018, '2019' AS Year_2019, '2020' AS Year_2020, '2021' AS Year_2021
FROM (SELECT product_name, sale_year, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR sale_year IN (2018, 2019, 2020, 2021));

SELECT * FROM (SELECT product_name, sale_year, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR sale_year IN (2018, 2019, 2020, 2021)); SELECT * FROM (SELECT product_name, sale_year, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR sale_year IN (2018, 2019, 2020, 2021)); SELECT product_name, '2018' AS Year_2018, '2019' AS Year_2019, '2020' AS Year_2020, '2021' AS Year_2021 FROM (SELECT product_name, sale_year, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR sale_year IN (2018, 2019, 2020, 2021));SELECT product_name, sale_amount FROM sales WHERE sale_year = 2018;

Options A, B, and C correctly demonstrate certification-level use of SQL PIVOT to analyze sales data by product and year. Option D is incorrect because it does not use the SQL PIVOT function.

Q28

Multiple Choice

Which SQL code snippet demonstrates certification-level use of SQL PIVOT to compare sales performance across different stores and quarters in Oracle?

SQL Code

SELECT *
FROM (SELECT store_name, quarter, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));

SELECT *
FROM (SELECT store_name, quarter, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));

SELECT store_name, 'Q1' AS Q1_Sales, 'Q2' AS Q2_Sales, 'Q3' AS Q3_Sales, 'Q4' AS Q4_Sales
FROM (SELECT store_name, quarter, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));

SELECT * FROM (SELECT store_name, quarter, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')); SELECT * FROM (SELECT store_name, quarter, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')); SELECT store_name, 'Q1' AS Q1_Sales, 'Q2' AS Q2_Sales, 'Q3' AS Q3_Sales, 'Q4' AS Q4_Sales FROM (SELECT store_name, quarter, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));SELECT store_name, sale_amount FROM sales WHERE quarter = 'Q1';

Options A, B, and C correctly demonstrate certification-level use of SQL PIVOT to compare sales performance across different stores and quarters. Option D is incorrect because it does not use the SQL PIVOT function.

Q29

Multiple Choice

Which SQL code snippet demonstrates certification-level use of SQL PIVOT to generate a dynamic report showing sales trends by month and region in Oracle?

SQL Code

SELECT *
FROM (SELECT region_name, sale_month, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR sale_month IN ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'));

SELECT *
FROM (SELECT region_name, sale_month, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR sale_month IN ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'));

SELECT region_name, 'Jan' AS January, 'Feb' AS February, 'Mar' AS March, 'Apr' AS April, 'May' AS May, 'Jun' AS June, 'Jul' AS July, 'Aug' AS August, 'Sep' AS September, 'Oct' AS October, 'Nov' AS November, 'Dec' AS December
FROM (SELECT region_name, sale_month, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR sale_month IN ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'));

SELECT * FROM (SELECT region_name, sale_month, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR sale_month IN ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')); SELECT * FROM (SELECT region_name, sale_month, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR sale_month IN ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')); SELECT region_name, 'Jan' AS January, 'Feb' AS February, 'Mar' AS March, 'Apr' AS April, 'May' AS May, 'Jun' AS June, 'Jul' AS July, 'Aug' AS August, 'Sep' AS September, 'Oct' AS October, 'Nov' AS November, 'Dec' AS December FROM (SELECT region_name, sale_month, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR sale_month IN ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'));SELECT region_name, sale_amount FROM sales WHERE sale_month = 'Jan';

Options A, B, and C correctly demonstrate certification-level use of SQL PIVOT to generate a dynamic report showing sales trends by month and region. Option D is incorrect because it does not use the SQL PIVOT function.

Q30

Multiple Choice

Which SQL code snippet demonstrates certification-level use of SQL PIVOT to create a multi-dimensional analysis report for sales by product, year, and region in Oracle?

SQL Code

SELECT *
FROM (SELECT product_name, sale_year, region_name, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR (sale_year, region_name) IN ((2019, 'North'), (2019, 'South'), (2020, 'North'), (2020, 'South')));

SELECT *
FROM (SELECT product_name, sale_year, region_name, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR (sale_year, region_name) IN ((2019, 'North'), (2019, 'South'), (2020, 'North'), (2020, 'South')));

SELECT product_name, '2019_North' AS '2019 North', '2019_South' AS '2019 South', '2020_North' AS '2020 North', '2020_South' AS '2020 South'
FROM (SELECT product_name, sale_year, region_name, sale_amount
FROM sales)
PIVOT (SUM(sale_amount) FOR (sale_year, region_name) IN ((2019, 'North'), (2019, 'South'), (2020, 'North'), (2020, 'South')));

SELECT * FROM (SELECT product_name, sale_year, region_name, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR (sale_year, region_name) IN ((2019, 'North'), (2019, 'South'), (2020, 'North'), (2020, 'South'))); SELECT * FROM (SELECT product_name, sale_year, region_name, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR (sale_year, region_name) IN ((2019, 'North'), (2019, 'South'), (2020, 'North'), (2020, 'South'))); SELECT product_name, '2019_North' AS '2019 North', '2019_South' AS '2019 South', '2020_North' AS '2020 North', '2020_South' AS '2020 South' FROM (SELECT product_name, sale_year, region_name, sale_amount FROM sales) PIVOT (SUM(sale_amount) FOR (sale_year, region_name) IN ((2019, 'North'), (2019, 'South'), (2020, 'North'), (2020, 'South')));SELECT product_name, sale_amount FROM sales WHERE sale_year = 2019 AND region_name = 'North';

Options A, B, and C correctly demonstrate certification-level use of SQL PIVOT to create a multi-dimensional analysis report for sales by product, year, and region. Option D is incorrect because it does not use the SQL PIVOT function.

Oracle Database Quiz Questions