Star schema concept in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the SQL code to select all columns from the fact table named 'sales'.

SQL

SELECT [1] FROM sales;

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

Aid

B*

Cdate

Dcustomer_id

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting only one column instead of all columns.

Using column names instead of the wildcard symbol.

Using * selects all columns from the table.

2fill in blank

medium

Complete the SQL code to join the fact table 'sales' with the dimension table 'customers' on the customer ID.

SQL

SELECT sales.*, customers.name FROM sales JOIN customers ON sales.customer_id [1] customers.customer_id;

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

A=

B>

C!=

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using inequality operators instead of equals.

Using greater than or less than operators incorrectly.

The join condition uses the equals sign (=) to match keys between tables.

3fill in blank

hard

Fix the error in the SQL code to create a fact table named 'sales_fact' with a primary key 'sale_id'.

SQL

CREATE TABLE sales_fact (sale_id INT [1] PRIMARY KEY, amount DECIMAL(10,2));

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

AAUTO_INCREMENT

BUNIQUE

CSERIAL

DIDENTITY(1,1)

Attempts:

3 left

💡 Hint

Common Mistakes

Using database-specific keywords from other SQL dialects.

Using UNIQUE instead of auto-increment.

In SQL, AUTO_INCREMENT is used to auto-generate unique keys in MySQL.

4fill in blank

hard

Fill both blanks to create a dimension table 'product_dim' with a surrogate key and product name.

SQL

CREATE TABLE product_dim (product_key INT [1] PRIMARY KEY, product_name VARCHAR(100) [2] NOT NULL);

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

AAUTO_INCREMENT

BNULL

CNOT NULL

DUNIQUE

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to set NOT NULL on important fields.

Not using auto-increment for surrogate keys.

The surrogate key uses AUTO_INCREMENT to generate unique keys. The product name cannot be null, so it uses NOT NULL.

5fill in blank

hard

Fill all three blanks to write a SQL query that calculates total sales amount grouped by product category.

SQL

SELECT category, SUM([1]) AS total_sales FROM sales_fact JOIN product_dim ON sales_fact.product_key = product_dim.product_key GROUP BY [2] ORDER BY [3] DESC;

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

Aamount

Bcategory

Ctotal_sales

Dproduct_key

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping or ordering by wrong columns.

Using column names instead of aliases in ORDER BY.

The SUM(amount) calculates total sales. Grouping and ordering are done by category and the alias total_sales respectively.