Pivot and unpivot concepts in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the year column from the sales table.

SQL

SELECT [1] FROM sales;

Drag options to blanks, or click blank then click option'

APIVOT

Byear

CSUM

Dsales

Attempts:

3 left

💡 Hint

Common Mistakes

Using aggregate functions like SUM instead of column names.

Using table names instead of column names.

The year column contains the year data we want to select from the sales table.

2fill in blank

medium

Complete the code to pivot the sales data by year using SUM of amount.

SQL

SELECT * FROM sales PIVOT (SUM(amount) FOR [1] IN (2019, 2020, 2021));

Drag options to blanks, or click blank then click option'

Asales

Bamount

Cyear

Dcategory

Attempts:

3 left

💡 Hint

Common Mistakes

Using the amount column instead of the year column for pivoting.

Using table names instead of column names.

The year column is used to pivot the sales data by year.

3fill in blank

hard

Fix the error in the UNPIVOT clause by completing the missing column name.

SQL

SELECT year, category, amount FROM sales UNPIVOT (amount FOR [1] IN (Q1, Q2, Q3, Q4));

Drag options to blanks, or click blank then click option'

Aquarter

Byear

Camount

Dcategory

Attempts:

3 left

💡 Hint

Common Mistakes

Using the amount column instead of the quarter column in UNPIVOT.

Using the year or category column incorrectly.

The quarter column is used to unpivot the quarterly sales data into rows.

4fill in blank

hard

Fill both blanks to create a pivot table showing total sales by category for each year.

SQL

SELECT category, [1] FROM sales PIVOT (SUM(amount) FOR [2] IN (2019, 2020, 2021));

Drag options to blanks, or click blank then click option'

A2019, 2020, 2021

Bamount

Cyear

Dcategory

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'amount' instead of the list of years for the first blank.

Using 'amount' or 'category' instead of 'year' for the second blank.

The years 2019, 2020, and 2021 are the columns created by pivoting on the year column.

5fill in blank

hard

Fill all three blanks to unpivot quarterly sales data into a long format with columns quarter and amount.

SQL

SELECT year, category, [1], [2] FROM sales UNPIVOT ([3] FOR quarter IN (Q1, Q2, Q3, Q4));

Drag options to blanks, or click blank then click option'

Aquarter

Bamount

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up the quarter and amount columns.

Using the same name for both blanks incorrectly.

The unpivoted columns are quarter and amount. The FOR clause uses 'quarter' and the unpivoted value is 'amount'.