SELECT with expressions and calculations in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate total price with tax

Given a table sales with columns item, price, and quantity, what is the output of this query?

SELECT item, price * quantity * 1.1 AS total_price_with_tax FROM sales ORDER BY item;

SQL

SELECT item, price * quantity * 1.1 AS total_price_with_tax FROM sales ORDER BY item;

A [{"item": "Apple", "total_price_with_tax": 10.0}, {"item": "Banana", "total_price_with_tax": 5.5}]

B [{"item": "Apple", "total_price_with_tax": 10.0}, {"item": "Banana", "total_price_with_tax": 5.0}]

C [{"item": "Apple", "total_price_with_tax": 11.0}, {"item": "Banana", "total_price_with_tax": 5.0}]

D [{"item": "Apple", "total_price_with_tax": 11.0}, {"item": "Banana", "total_price_with_tax": 5.5}]

💡 Hint

Remember to multiply price by quantity first, then apply the 10% tax.

The query multiplies price by quantity, then multiplies by 1.1 to add 10% tax. The result is ordered by item name.

❓ query_result

intermediate

Calculate age from birth year

Given a table users with columns name and birth_year, what is the output of this query if the current year is 2024?

SELECT name, 2024 - birth_year AS age FROM users ORDER BY age DESC;

SQL

SELECT name, 2024 - birth_year AS age FROM users ORDER BY age DESC;

A [{"name": "Alice", "age": 40}, {"name": "Bob", "age": 31}]

B [{"name": "Alice", "age": 39}, {"name": "Bob", "age": 30}]

C [{"name": "Alice", "age": 40}, {"name": "Bob", "age": 30}]

D [{"name": "Alice", "age": 39}, {"name": "Bob", "age": 31}]

💡 Hint

Subtract birth year from 2024 to get age.

The query calculates age by subtracting birth_year from 2024 and orders results by age descending.

📝 Syntax

advanced

Identify the syntax error in calculation

Which option contains a syntax error in the SELECT statement that calculates the discounted price?

SELECT product, price - price * discount AS discounted_price FROM products;

SQL

SELECT product, price - price * discount AS discounted_price FROM products;

A SELECT product, price - price * discount AS discounted_price products;

B SELECT product, price - (price * discount) AS discounted_price FROM products;

C SELECT product, price - price * discount discounted_price FROM products;

D SELECT product, price - price * discount AS discounted_price FROM products;

💡 Hint

Check the FROM clause syntax carefully.

Option A is missing the FROM keyword before the table name, causing a syntax error.

❓ optimization

advanced

Optimize calculation in SELECT for performance

You want to calculate the total revenue as price * quantity for each sale in the sales table. Which query is more efficient and why?

A SELECT price * quantity AS total_revenue FROM sales;

B SELECT price, quantity, price * quantity AS total_revenue FROM sales;

C SELECT price * quantity AS total_revenue, price * quantity * 1.05 AS revenue_with_tax FROM sales;

D SELECT price + quantity AS total_revenue FROM sales;

💡 Hint

Consider the amount of data returned and calculation complexity.

Option A calculates only the needed total_revenue column, minimizing data transfer and computation.

🧠 Conceptual

expert

Understanding expression evaluation order in SELECT

Consider the query:

SELECT price, quantity, price * quantity AS total, total * 0.9 AS discounted_total FROM sales;

What will happen when this query runs?

SQL

SELECT price, quantity, price * quantity AS total, total * 0.9 AS discounted_total FROM sales;

A The query will run successfully and return price, quantity, total, and discounted_total columns with correct calculations.

B The query will raise an error because the alias 'total' cannot be used in the same SELECT clause for calculation.

C The query will return NULL for discounted_total because 'total' is not recognized.

D The query will ignore the discounted_total column and return only price, quantity, and total.

💡 Hint

Think about how SQL processes SELECT expressions and aliases.

In SQL, you cannot use an alias defined in the SELECT list in another expression in the same SELECT list. This causes an error.