Lesson 6.1
Calculated Columns in SQL
Columns do not have to come straight from a table. You can calculate new ones as your query runs.
So far every column you've selected has come straight out of a table. But SQL can also calculate columns on the fly. Any arithmetic you put in the SELECT list becomes part of the result.
A calculated column starts out unnamed, which makes for an ugly header. Give it a clear name with AS, the same aliasing you met back in Choosing columns.
Pattern
select item_name,
price,
price * 1.06 as price_with_tax
from menuReady to run it?
Open the DuckDB playground with the matching dataset and query already filled in.
Schema · Garden ShopTable · products9 columns · 24 rows
Table · products
9 columns · 24 rowsOne row per product, with price, cost, and inventory levels.
product_id intproduct_name textcategory_id intsupplier_id intprice decimalcost decimalquantity_on_hand intreorder_level intdiscontinued bool
Your task
For every product, show its price,cost, and the profit margin (price minus cost). Name the calculated column margin and sort so the most profitable products appear first.
SQL Workbench
⌘↵ to run·
Expected answer
- Columns: product_name, price, cost, margin.
- Rows: 24 products.
- margin is price minus cost, so the Fiddle-Leaf Fig (32 - 18) shows 14.