Practice Tasks
<< Hide
Basic Queries ▼
SELECT ▼
Task 1:
Answer
Retrieve all columns from the customer table.
Task 2:
Answer
Get the first and last names of all customers.
Task 3:
Answer
Get the names and RGB values of all colors.
Task 4:
Answer
Retrieve the name and category of all products.
DISTINCT ▼
Task 1:
Answer
Get a list of unique cities from the address table.
Task 2:
Answer
List all distinct product categories.
LIMIT ▼
Task 1:
Answer
Retrieve the first 5 orders placed (by ID).
Task 2:
Answer
Retrieve the first 10 films from the film table.
Task 3:
Answer
Retrieve the first 3 customers ordered by last name.
ORDER BY ▼
Task 1:
Answer
Show all articles sorted by original price from highest to lowest.
Task 2:
Answer
List all customers ordered by last_name in ascending order.
Task 3:
Answer
Retrieve all products ordered by their creation date from newest to oldest.
WHERE ▼
Task 1:
Answer
List all products that are currently active.
Task 2:
Answer
Find all customers who were born after January 1st, 1990.
Task 3:
Answer
Find customers whose first name starts with 'A'.
Task 4:
Answer
List articles with a discount greater than 20 percent.
Task 5:
Answer
Retrieve the first_name and last_name of customers whose last_name starts with the letter 'A'.
Task 6:
Answer
Retrieve all films that have a rental_rate greater than 4.
Task 7:
Answer
Retrieve all customers who do not have an email address.
Task 8:
Answer
List all customers born before January 1st, 1980.
Task 9:
Answer
Find all articles that have a discount of exactly 50 percent.
Task 10:
Answer
Retrieve all colors whose name starts with the letter 'B'.
Task 11:
Answer
Find all addresses where the ZIP code starts with '10'.
Task 12:
Answer
Retrieve all articles where the reduced price is lower than the original price.
Joins & Relations ▼
INNER JOIN ▼
Task 1:
Answer
List product names and their corresponding label names.
Task 2:
Answer
Show customer names and their associated addresses.
Task 3:
Answer
Get article descriptions and their associated product names.
Task 4:
Answer
Find the stock count for each article description.
Task 5:
Answer
List articles and the names of their assigned colors.
Task 6:
Answer
Show products and their associated size descriptions.
Task 7:
Answer
Retrieve the first_name, last_name, and address of each customer.
Task 8:
Answer
Retrieve all film titles and their category names.
Task 9:
Answer
Retrieve product names and the category they belong to.
Task 10:
Answer
Retrieve article descriptions and their associated color names.
Task 11:
Answer
List order IDs and the cities they were shipped to.
Task 12:
Answer
Retrieve product names and article descriptions belonging to them.
Task 13:
Answer
Retrieve stock counts together with article descriptions.
LEFT JOIN ▼
Task 1:
Answer
List all products and their labels, including products that might not have a label assigned.
Task 2:
Answer
Retrieve all customers and their payments if they have made any.
Task 3:
Answer
Retrieve all customers and their orders including customers with no orders.
Task 4:
Answer
Retrieve all products and their associated articles, including products with no articles.
MULTI JOIN ▼
Task 1:
Answer
Retrieve the first_name, last_name, and city of each customer.
MULTIPLE JOIN ▼
Task 1:
Answer
Retrieve customer names and the cities they have shipped orders to.
Task 2:
Answer
Show order IDs and the names of the products contained in each order.
Task 3:
Answer
Get the order ID and total for all orders placed by customers with the last name 'Smith'.
Task 4:
Answer
Retrieve order IDs and the color of each ordered article.
Task 5:
Answer
Retrieve order IDs and the sizes of articles contained in each order.
Task 6:
Answer
Retrieve customer names and the descriptions of articles they ordered.
Aggregations ▼
AVG ▼
Task 1:
Answer
Get the average original price of all articles.
Task 2:
Answer
Calculate the average reduced price of articles.
COUNT ▼
Task 1:
Answer
Find the total number of customers.
Task 2:
Answer
Count the total number of customers.
Task 3:
Answer
Count the total number of products.
GROUP BY ▼
Task 1:
Answer
Count how many products exist in each product category.
Task 2:
Answer
Find the total quantity (sum of amount) of each article sold.
Task 3:
Answer
Calculate the average shipping cost for each address city.
Task 4:
Answer
Count how many customers belong to each store.
Task 5:
Answer
Count how many customers exist for each gender.
Task 6:
Answer
Count how many articles exist for each product.
Task 7:
Answer
Calculate the total number of items ordered per order.
Task 8:
Answer
Count how many orders each address received.
HAVING ▼
Task 1:
Answer
List categories that have more than 5 products.
Task 2:
Answer
Retrieve customer IDs that have made more than 30 payments.
Task 3:
Answer
Retrieve product IDs that have more than 3 articles.
MAX ▼
Task 1:
Answer
Identify the highest reduced price among all articles.
Task 2:
Answer
Find the maximum stock count.
MIN ▼
Task 1:
Answer
Find the earliest date of birth among customers.
Task 2:
Answer
Find the smallest discount percentage among articles.
SUM ▼
Task 1:
Answer
Calculate the total revenue (sum of total) from all orders.
Task 2:
Answer
Get the total amount of items currently in stock across all articles.
Task 3:
Answer
Calculate the total payment amount for each customer.
Task 4:
Answer
Calculate the total shipping cost of all orders.
Advanced Logic ▼
CASE ▼
Task 1:
Answer
Classify products as 'Active' or 'Inactive' based on the currentlyactive column.
CTE ▼
Task 1:
Answer
Using a CTE, calculate the total spent per customer and list those who spent more than 500.
Task 2:
Answer
Find the top 3 customers by total order value using a CTE.
Task 3:
Answer
Using a CTE, calculate the total quantity sold per article.
Task 4:
Answer
Using a CTE, list customers and their total number of orders.
EXISTS ▼
Task 1:
Answer
Retrieve customers who have made at least one payment.
Task 2:
Answer
Find products that have at least one article.
LOGIC ▼
Task 1:
Answer
Find the most popular product category based on total quantity sold.
Task 2:
Answer
Calculate the total value of current stock (sum of stock count multiplied by reduced price).
NOT EXISTS ▼
Task 1:
Answer
Find products that do not have any articles.
SUBQUERY ▼
Task 1:
Answer
Find products that have an original price higher than the average original price of all articles.
Task 2:
Answer
List customers who have placed at least one order using a subquery.
Task 3:
Answer
Find the names of products that have never been ordered.
Task 4:
Answer
List customers whose total order count is higher than the average order count per customer.
Task 5:
Answer
Find articles that are currently out of stock (count = 0 or no entry in stock table).
Task 6:
Answer
Retrieve the titles of films that have a rental_rate greater than the average rental_rate.
Task 7:
Answer
Retrieve films that belong to the 'Action' category.
Task 8:
Answer
Retrieve customers whose date of birth is earlier than the average date of birth.
Task 9:
Answer
Retrieve articles whose reduced price is below the average reduced price.
WINDOW FUNCTION ▼
Task 1:
Answer
Rank articles within each product by their original price using a window function.
Task 2:
Answer
Assign a row number to articles ordered by original price.
Task 3:
Answer
Calculate the running total of order values ordered by timestamp.
Task 4:
Answer
Rank articles by their reduced price within each product.
Analytical Queries ▼
RANK ▼
Task 1:
Answer
Rank customers based on their total payment amount.
WINDOW ▼
Task 1:
Answer
Assign a row number to each customer ordered by last_name.
Task 2:
Answer
Calculate a running total of payments ordered by payment_date.
Functions ▼
DATE ▼
Task 1:
Answer
Retrieve all payments made in the year 2007.
LENGTH ▼
Task 1:
Answer
Retrieve film titles and the length of each title.
ROUND ▼
Task 1:
Answer
Round the rental_rate of each film to one decimal place.
STRING ▼
Task 1:
Answer
Convert all customer first names to uppercase.
Database schema
Database diagram
| Column | Type |
|---|---|
| id | integer |
| customer_id | integer |
| firstname | text |
| lastname | text |
| address1 | text |
| address2 | text |
| city | text |
| zip | text |
| created | timestamp with time zone |
| updated | timestamp with time zone |
| Column | Type |
|---|---|
| id | integer |
| product_id | integer |
| ean | text |
| color_id | integer |
| size_id | integer |
| description | text |
| originalprice | money |
| reducedprice | money |
| taxrate | numeric |
| discountinpercent | integer |
| currentlyactive | boolean |
| created | timestamp with time zone |
| updated | timestamp with time zone |
| Column | Type |
|---|---|
| id | integer |
| name | text |
| rgb | text |
| Column | Type |
|---|---|
| id | integer |
| firstname | text |
| lastname | text |
| gender | USER-DEFINED |
text |
|
| dateofbirth | date |
| currentaddressid | integer |
| created | timestamp with time zone |
| updated | timestamp with time zone |
| Column | Type |
|---|---|
| id | integer |
| name | text |
| slugname | text |
| icon | bytea |
| Column | Type |
|---|---|
| id | integer |
| customer_id | integer |
| ordertimestamp | timestamp with time zone |
| address_id | integer |
| total | money |
| shippingcost | money |
| created | timestamp with time zone |
| updated | timestamp with time zone |
| Column | Type |
|---|---|
| id | integer |
| order_id | integer |
| article_id | integer |
| amount | smallint |
| price | money |
| created | timestamp with time zone |
| updated | timestamp with time zone |
| Column | Type |
|---|---|
| id | integer |
| name | text |
| label_id | integer |
| category | USER-DEFINED |
| gender | USER-DEFINED |
| currentlyactive | boolean |
| created | timestamp with time zone |
| updated | timestamp with time zone |
| Column | Type |
|---|---|
| id | integer |
| gender | USER-DEFINED |
| category | USER-DEFINED |
| size | text |
| size_us | int4range |
| size_uk | int4range |
| size_eu | int4range |
| Column | Type |
|---|---|
| id | integer |
| article_id | integer |
| count | integer |
| created | timestamp with time zone |
| updated | timestamp with time zone |