Expressions and Operators

SELECT price * quantity AS line_total FROM myschema.order_items
SELECT price * quantity - discount AS net_total FROM myschema.order_items
SELECT total % 100 AS cents_portion FROM myschema.orders

SELECT -price AS negative_price FROM myschema.products
SELECT +total AS total FROM myschema.orders   -- unary plus is a no-op

SELECT flags & 3 AS lower_two_bits FROM myschema.permissions  -- AND
SELECT flags | 4 AS with_flag_set FROM myschema.permissions   -- OR
SELECT flags ^ 1 AS toggled FROM myschema.permissions         -- XOR
SELECT flags << 2 AS shifted_left FROM myschema.permissions   -- left shift
SELECT flags >> 1 AS shifted_right FROM myschema.permissions  -- right shift
SELECT ~flags AS inverted FROM myschema.permissions           -- bitwise NOT

SELECT first_name || ' ' || last_name AS full_name FROM myschema.users
SELECT 'Order #' || CAST(id AS VARCHAR) AS label FROM myschema.orders

SELECT
  id,
  CASE
    WHEN total >= 1000 THEN 'large'
    WHEN total >= 100  THEN 'medium'
    ELSE 'small'
  END AS order_size
FROM myschema.orders

SELECT
  id,
  CASE status
    WHEN 'pending'   THEN 'Awaiting payment'
    WHEN 'shipped'   THEN 'On the way'
    WHEN 'delivered' THEN 'Complete'
    ELSE 'Unknown'
  END AS status_label
FROM myschema.orders

SELECT COALESCE(nickname, first_name, 'Anonymous') AS display_name
FROM myschema.users

SELECT NULLIF(discount, 0) AS discount FROM myschema.orders

SELECT id, ISNULL(shipped_at) AS not_yet_shipped FROM myschema.orders

SELECT id, price * quantity AS line_total
FROM myschema.order_items
ORDER BY line_total