Test Your PostgreSQL PIVOT Skills with This Quiz

Progress

True / False

PostgreSQL has a built-in PIVOT function that can be used directly in SQL queries.

True False

PostgreSQL does not have a built-in PIVOT function like some other databases. Instead, you achieve pivoting by using the crosstab function from the tablefunc extension.

True / False

The crosstab function in PostgreSQL requires the tablefunc extension to be enabled.

True False

The crosstab function, which is used for pivoting data, is part of the tablefunc extension, and you need to enable this extension in your PostgreSQL database.

True / False

In PostgreSQL, the crosstab function can pivot data without specifying the columns in advance.

True False

When using the crosstab function, you must specify the columns in advance, as PostgreSQL needs to know the structure of the output table.

True / False

You can use the crosstab function in PostgreSQL to transform rows into columns based on the values of a specified attribute.

True False

The crosstab function is used to pivot rows into columns based on specified attribute values.

True / False

The first parameter of the crosstab function in PostgreSQL is a SQL query that returns three columns.

True False

The first parameter of the crosstab function in PostgreSQL should return two columns, where the first column is the row identifier and the second column is the value to pivot.

True / False

In PostgreSQL, you can use window functions as an alternative to pivoting data when using crosstab is not suitable.

True False

Window functions in PostgreSQL can be used to perform complex aggregations and calculations over a set of rows, which can sometimes serve as an alternative to pivoting data

True / False

In PostgreSQL, the crosstab function can only handle a fixed number of pivot columns, which must be known beforehand.

True False

The crosstab function requires that the number of pivot columns be specified in advance, as it needs to know the structure of the result set.

True / False

To dynamically pivot data in PostgreSQL without knowing the column names in advance, you can use dynamic SQL and procedural code.

True False

Dynamic SQL combined with procedural code (e.g., PL/pgSQL) can be used to create a pivot table dynamically when the column names are not known in advance.

True / False

The crosstab function in PostgreSQL can be used in conjunction with the GROUP BY clause to aggregate data before pivoting.

True False

The crosstab function itself handles the aggregation of data for pivoting, so it is used instead of the GROUP BY clause.

Q10

True / False

PostgreSQL provides a built-in function called pivot_table for easily transforming row data into columns.

True False

PostgreSQL does not have a built-in pivot_table function. Pivoting is done using the crosstab function from the tablefunc extension, which requires some setup and predefined column structure.

Q11

Single Choice

In PostgreSQL, which SQL operation is commonly used to perform a PIVOT transformation on a dataset?

SELECT INTO CREATE TABLE CROSSTABINSERT

In PostgreSQL, the crosstab function from the tablefunc extension is commonly used to perform PIVOT-like operations.

Q12

Single Choice

Which PostgreSQL extension must be enabled to use the crosstab function for PIVOT operations?

hstore pgcrypto tablefuncdblink

The tablefunc extension provides the crosstab function, which is necessary for performing PIVOT operations in PostgreSQL.

Q13

Single Choice

What is the first step in using the crosstab function in PostgreSQL?

Create a temporary table Enable the tablefunc extension Insert data into the tableWrite a SELECT query

Before using the crosstab function, the tablefunc extension must be enabled in PostgreSQL.

Q14

Single Choice

Which crosstab query will PIVOT this data to show products as columns?

SQL Code

month	product	amount
Jan	A	100
Jan	B	150
Feb	A	200
Feb	B	250

SELECT * FROM crosstab('SELECT month, product, amount FROM sales') SELECT * FROM crosstab('SELECT month, product, amount FROM sales ORDER BY 1,2') SELECT * FROM crosstab('SELECT month, product, amount FROM sales ORDER BY 1,3')SELECT * FROM crosstab('SELECT month, product, amount FROM sales ORDER BY 1,4')

The crosstab function requires the input query to be ordered by the first column (row header) and the second column (category header).

Q15

Single Choice

What will be the output columns when using the crosstab function on the following query in PostgreSQL?

SQL Code

SELECT * FROM crosstab('SELECT month, product, amount FROM sales ORDER BY 1,2')
AS ct(month text, A int, B int);

month, product, amount month, A, B product, A, BA, B, amount

The output columns correspond to the row identifier (month) and the unique categories (A, B).

Q16

Single Choice

How do you specify the output column names in a PostgreSQL crosstab function?

In the SELECT query In the FROM clause In the table definition following the AS keywordIn the WHERE clause

The output column names must be defined in the table structure provided after the AS keyword when using the crosstab function.

Q17

Single Choice

Which crosstab query will PIVOT this data to include only the months "Jan" and "Feb" as row headers and "A" and "B" as columns?

SQL Code

month	product	amount
Jan	A	100
Jan	B	150
Feb	A	200
Feb	B	250
Mar	A	300
Mar	B	350

SELECT * FROM crosstab('SELECT month, product, amount FROM sales WHERE month IN (''Jan'', ''Feb'') ORDER BY 1,2') SELECT * FROM crosstab('SELECT month, product, amount FROM sales WHERE month = ''Jan'' OR month = ''Feb'' ORDER BY 1,2') SELECT * FROM crosstab('SELECT month, product, amount FROM sales WHERE month IN (''Jan'', ''Feb'') ORDER BY 1,3')Both A and B

Both queries correctly filter the data and order it for the crosstab function, producing the desired PIVOT output.

Q18

Single Choice

In PostgreSQL, what happens if the crosstab function encounters a category with no data for a particular row?

It returns NULL for that column It skips the row entirely It throws an errorIt duplicates the previous row’s data

If the crosstab function finds no data for a particular category in a row, it returns NULL for that column in the resulting PIVOT table.

Q19

Single Choice

Which of the following best describes the crosstab function in PostgreSQL when applied to large datasets?

It improves performance by reducing the number of rows. It may require careful indexing and optimization to handle large datasets efficiently. It automatically optimizes the query for large datasets.It is unsuitable for large datasets

While the crosstab function is powerful, handling large datasets may require careful indexing and query optimization to maintain performance.

Q20

Single Choice

How would you write a crosstab query in PostgreSQL to PIVOT this data by month as row headers and product as columns, with a dynamically generated column list?

SQL Code

month	product	amount
Jan	A	100
Jan	B	150
Feb	A	200
Feb	B	250
Mar	A	300
Mar	B	350
Apr	A	400
Apr	B	450

SELECT * FROM crosstab('SELECT month, product, amount FROM sales ORDER BY 1,2') AS ct(month text, A int, B int); SELECT * FROM crosstab('SELECT month, product, amount FROM sales ORDER BY 1,2') AS ct(month text, anyarray); SELECT * FROM crosstab('SELECT month, product, amount FROM sales ORDER BY 1,2') AS ct(anyarray);SELECT * FROM crosstab('SELECT month, product, amount FROM sales ORDER BY 1,2') AS ct(month text);

In PostgreSQL, you must define the output columns explicitly, as in this correct query, where the row header is month and the columns are A and B.

Q21

Multiple Choice

Which SQL queries correctly use the PIVOT functionality in PostgreSQL to transform data?

SELECT region, SUM(CASE WHEN quarter = 'Q1' THEN sales ELSE 0 END) AS Q1, SUM(CASE WHEN quarter = 'Q2' THEN sales ELSE 0 END) AS Q2 FROM sales GROUP BY region; SELECT region, PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2')) FROM sales GROUP BY region; SELECT region, SUM(CASE WHEN quarter = 'Q1' THEN sales ELSE 0 END) AS Q1, SUM(CASE WHEN quarter = 'Q2' THEN sales ELSE 0 END) AS Q2, SUM(CASE WHEN quarter = 'Q3' THEN sales ELSE 0 END) AS Q3 FROM sales GROUP BY region;SELECT region, quarter, SUM(sales) FROM sales PIVOT(quarter FOR region) GROUP BY region, quarter;

PostgreSQL does not have a direct PIVOT operator like some other databases, but the same result can be achieved using conditional aggregation and CASE statements.

Q22

Multiple Choice

Identify the SQL queries that correctly apply PIVOT-like transformations using conditional aggregation in PostgreSQL.

SELECT student_id, SUM(CASE WHEN subject = 'Math' THEN grade ELSE 0 END) AS Math, SUM(CASE WHEN subject = 'Science' THEN grade ELSE 0 END) AS Science FROM grades GROUP BY student_id; SELECT student_id, PIVOT(SUM(grade) FOR subject IN ('Math', 'Science')) FROM grades GROUP BY student_id; SELECT student_id, SUM(CASE WHEN subject = 'Math' THEN grade ELSE 0 END) AS Math, SUM(CASE WHEN subject = 'Science' THEN grade ELSE 0 END) AS Science, SUM(CASE WHEN subject = 'History' THEN grade ELSE 0 END) AS History FROM grades GROUP BY student_id;SELECT student_id, subject, SUM(grade) FROM grades PIVOT(subject FOR grade) GROUP BY student_id, subject;

In PostgreSQL, pivot-like functionality can be achieved using conditional aggregation with CASE statements. This approach allows you to transform row data into columns.

Q23

Multiple Choice

Which SQL queries correctly use the PIVOT-like functionality in PostgreSQL to summarize data?

SELECT product_id, SUM(CASE WHEN year = 2022 THEN sales ELSE 0 END) AS Year_2022, SUM(CASE WHEN year = 2023 THEN sales ELSE 0 END) AS Year_2023 FROM product_sales GROUP BY product_id; SELECT product_id, PIVOT(SUM(sales) FOR year IN (2022, 2023)) FROM product_sales GROUP BY product_id; SELECT product_id, SUM(CASE WHEN year = 2022 THEN sales ELSE 0 END) AS Year_2022, SUM(CASE WHEN year = 2023 THEN sales ELSE 0 END) AS Year_2023, SUM(CASE WHEN year = 2021 THEN sales ELSE 0 END) AS Year_2021 FROM product_sales GROUP BY product_id;SELECT product_id, year, SUM(sales) FROM product_sales PIVOT(year FOR sales) GROUP BY product_id, year;

To achieve PIVOT-like transformations in PostgreSQL, you can use CASE statements within an aggregation function to conditionally summarize data based on the desired columns.

Q24

Multiple Choice

Determine the SQL queries that correctly implement PIVOT-like transformations for attendance records in PostgreSQL.

SELECT employee_id, SUM(CASE WHEN day = 'Monday' THEN hours ELSE 0 END) AS Monday, SUM(CASE WHEN day = 'Tuesday' THEN hours ELSE 0 END) AS Tuesday FROM attendance GROUP BY employee_id; SELECT employee_id, PIVOT(SUM(hours) FOR day IN ('Monday', 'Tuesday')) FROM attendance GROUP BY employee_id; SELECT employee_id, SUM(CASE WHEN day = 'Monday' THEN hours ELSE 0 END) AS Monday, SUM(CASE WHEN day = 'Tuesday' THEN hours ELSE 0 END) AS Tuesday, SUM(CASE WHEN day = 'Wednesday' THEN hours ELSE 0 END) AS Wednesday FROM attendance GROUP BY employee_id;SELECT employee_id, day, SUM(hours) FROM attendance PIVOT(day FOR hours) GROUP BY employee_id, day;

To implement PIVOT-like transformations in PostgreSQL, use conditional aggregation to rotate row data into columns based on specific criteria, such as days of the week.

Q25

Multiple Choice

Identify the SQL queries that correctly use PIVOT-like functionality to transform and aggregate sales data in PostgreSQL.

SELECT category, SUM(CASE WHEN quarter = 'Q1' THEN sales ELSE 0 END) AS Q1, SUM(CASE WHEN quarter = 'Q2' THEN sales ELSE 0 END) AS Q2 FROM sales_data GROUP BY category; SELECT category, PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2')) FROM sales_data GROUP BY category; SELECT category, SUM(CASE WHEN quarter = 'Q1' THEN sales ELSE 0 END) AS Q1, SUM(CASE WHEN quarter = 'Q2' THEN sales ELSE 0 END) AS Q2, SUM(CASE WHEN quarter = 'Q3' THEN sales ELSE 0 END) AS Q3 FROM sales_data GROUP BY category;SELECT category, quarter, SUM(sales) FROM sales_data PIVOT(quarter FOR sales) GROUP BY category, quarter;

In PostgreSQL, you can simulate a PIVOT by using CASE statements with aggregation to convert rows into columns based on specific conditions, such as quarters.

Q26

Multiple Choice

Which SQL queries correctly use PIVOT-like functionality in PostgreSQL for financial data transformation?

SELECT department_id, SUM(CASE WHEN month = 'January' THEN revenue ELSE 0 END) AS January, SUM(CASE WHEN month = 'February' THEN revenue ELSE 0 END) AS February FROM financials GROUP BY department_id; SELECT department_id, PIVOT(SUM(revenue) FOR month IN ('January', 'February')) FROM financials GROUP BY department_id; SELECT department_id, SUM(CASE WHEN month = 'January' THEN revenue ELSE 0 END) AS January, SUM(CASE WHEN month = 'February' THEN revenue ELSE 0 END) AS February, SUM(CASE WHEN month = 'March' THEN revenue ELSE 0 END) AS March FROM financials GROUP BY department_id;SELECT department_id, month, SUM(revenue) FROM financials PIVOT(month FOR revenue) GROUP BY department_id, month;

To pivot financial data in PostgreSQL, use conditional aggregation with CASE statements, converting rows into columns based on the month.

Q27

Multiple Choice

Determine the SQL queries that correctly implement PIVOT-like transformations for project management data in PostgreSQL.

SELECT employee_id, SUM(CASE WHEN week = 1 THEN hours ELSE 0 END) AS Week_1, SUM(CASE WHEN week = 2 THEN hours ELSE 0 END) AS Week_2 FROM project_hours GROUP BY employee_id; SELECT employee_id, PIVOT(SUM(hours) FOR week IN (1, 2)) FROM project_hours GROUP BY employee_id; SELECT employee_id, SUM(CASE WHEN week = 1 THEN hours ELSE 0 END) AS Week_1, SUM(CASE WHEN week = 2 THEN hours ELSE 0 END) AS Week_2, SUM(CASE WHEN week = 3 THEN hours ELSE 0 END) AS Week_3 FROM project_hours GROUP BY employee_id;SELECT employee_id, week, SUM(hours) FROM project_hours PIVOT(week FOR hours) GROUP BY employee_id, week;

In PostgreSQL, PIVOT-like functionality can be achieved by using conditional aggregation to transform row data into columns based on the week.

Q28

Multiple Choice

Which SQL queries correctly use PIVOT-like functionality to transform and aggregate survey data in PostgreSQL?

SELECT participant_id, SUM(CASE WHEN question = 'Q1' THEN response ELSE 0 END) AS Q1, SUM(CASE WHEN question = 'Q2' THEN response ELSE 0 END) AS Q2 FROM survey_responses GROUP BY participant_id; SELECT participant_id, PIVOT(SUM(response) FOR question IN ('Q1', 'Q2')) FROM survey_responses GROUP BY participant_id; SELECT participant_id, SUM(CASE WHEN question = 'Q1' THEN response ELSE 0 END) AS Q1, SUM(CASE WHEN question = 'Q2' THEN response ELSE 0 END) AS Q2, SUM(CASE WHEN question = 'Q3' THEN response ELSE 0 END) AS Q3 FROM survey_responses GROUP BY participant_id;SELECT participant_id, question, SUM(response) FROM survey_responses PIVOT(question FOR response) GROUP BY participant_id, question;

To achieve PIVOT-like transformations in PostgreSQL, you can use CASE statements within an aggregation function to conditionally summarize data by question.

Q29

Multiple Choice

Identify the SQL queries that correctly use PIVOT-like functionality in PostgreSQL for employee shift data.

SELECT employee_id, SUM(CASE WHEN day = 'Monday' THEN hours ELSE 0 END) AS Monday, SUM(CASE WHEN day = 'Tuesday' THEN hours ELSE 0 END) AS Tuesday FROM shifts GROUP BY employee_id; SELECT employee_id, PIVOT(SUM(hours) FOR day IN ('Monday', 'Tuesday')) FROM shifts GROUP BY employee_id; SELECT employee_id, SUM(CASE WHEN day = 'Monday' THEN hours ELSE 0 END) AS Monday, SUM(CASE WHEN day = 'Tuesday' THEN hours ELSE 0 END) AS Tuesday, SUM(CASE WHEN day = 'Wednesday' THEN hours ELSE 0 END) AS Wednesday FROM shifts GROUP BY employee_id;SELECT employee_id, day, SUM(hours) FROM shifts PIVOT(day FOR hours) GROUP BY employee_id, day;

In PostgreSQL, you can use conditional aggregation with CASE statements to create a PIVOT-like effect, transforming rows into columns based on the day of the week.

Q30

Multiple Choice

Which SQL queries correctly use PIVOT-like functionality in PostgreSQL for customer order data?

SELECT customer_id, SUM(CASE WHEN quarter = 'Q1' THEN total_orders ELSE 0 END) AS Q1, SUM(CASE WHEN quarter = 'Q2' THEN total_orders ELSE 0 END) AS Q2 FROM orders GROUP BY customer_id; SELECT customer_id, PIVOT(SUM(total_orders) FOR quarter IN ('Q1', 'Q2')) FROM orders GROUP BY customer_id; SELECT customer_id, SUM(CASE WHEN quarter = 'Q1' THEN total_orders ELSE 0 END) AS Q1, SUM(CASE WHEN quarter = 'Q2' THEN total_orders ELSE 0 END) AS Q2, SUM(CASE WHEN quarter = 'Q3' THEN total_orders ELSE 0 END) AS Q3 FROM orders GROUP BY customer_id;SELECT customer_id, quarter, SUM(total_orders) FROM orders PIVOT(quarter FOR total_orders) GROUP BY customer_id, quarter;

To pivot order data in PostgreSQL, use conditional aggregation with CASE statements, converting rows into columns based on the quarter.

PostgreSQL Database Quiz Questions