Star schema concept in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Identify the Fact Table in a Star Schema

In a star schema, which table typically contains the measurable, quantitative data for analysis?

A Staging table

B Fact table

C Lookup table

D Dimension table

💡 Hint

Think about where the numbers you want to analyze are stored.

The fact table stores measurable data such as sales amounts or quantities. Dimension tables provide context like dates or product details.

❓ data_modeling

intermediate

Choose the Correct Star Schema Structure

Which of the following SQL table structures best represents a star schema for sales data?

A One large table with all sales and product details combined

B Multiple normalized tables with many-to-many relationships between sales and products

C A central sales fact table linked to separate product, customer, and date dimension tables

D A single table with repeated product and customer information for each sale

💡 Hint

Star schema uses a central fact table connected to dimension tables.

Star schema organizes data with a central fact table linked to dimension tables, simplifying queries and improving performance.

❓ dax_lod_result

advanced

Calculate Total Sales Using DAX in a Star Schema

Given a fact table 'Sales' with a column 'Amount', which DAX measure correctly calculates the total sales amount?

SQL

Total Sales = SUM(Sales[Amount])

A Total Sales = SUMX(Sales, Sales[Quantity])

B Total Sales = COUNT(Sales[Amount])

C Total Sales = AVERAGE(Sales[Amount])

D Total Sales = SUM(Sales[Amount])

💡 Hint

To get total sales, add up all amounts.

SUM aggregates the total of the Amount column. COUNT counts rows, AVERAGE finds mean, SUMX sums an expression.

❓ visualization

advanced

Best Visualization for Star Schema Sales Data

You have sales data in a star schema with dimensions for Product, Date, and Customer. Which visualization best shows total sales by product category over time?

A Line chart showing sales trends by product category over months

B Table listing all sales transactions

C Pie chart showing sales by product category

D Scatter plot of sales amount vs. customer age

💡 Hint

Think about showing changes over time by category.

A line chart effectively shows trends over time for different categories, making it ideal for this data.

🔧 Debug

expert

Identify the Error in Star Schema SQL Query

Consider this SQL query joining a fact table 'Sales' with dimension 'Product' to get total sales per product name:

SELECT Product.Name, SUM(Sales.Amount) AS TotalSales
FROM Sales
JOIN Product ON Sales.ProductID = Product.ID
GROUP BY Product.Name, Sales.Amount;

What is the error in this query?

A Grouping by Sales.Amount causes incorrect aggregation

B Missing WHERE clause to filter sales data

C JOIN condition uses wrong columns

D SUM function cannot be used with GROUP BY

💡 Hint

Check which columns should be in GROUP BY when using aggregation.

Grouping by Sales.Amount breaks aggregation because Amount is a measure. Only group by Product.Name to sum amounts correctly.