SELECT col1, col2 FROM tabella;
SELECT * FROM tabella;

SELECT * FROM employees
WHERE salary > 50000 AND city = 'Roma';

SELECT * FROM products
ORDER BY price DESC, name ASC;

SELECT * FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

SELECT DISTINCT country FROM customers;

SELECT first_name AS nome, last_name AS cognome
FROM employees AS e;

SELECT COUNT(*), AVG(price), SUM(stock),
       MAX(launched_on), MIN(price)
FROM products;

SELECT category_id, COUNT(*) AS n, AVG(price) AS prezzo_medio
FROM products
GROUP BY category_id;

SELECT category_id, COUNT(*) AS n
FROM products
GROUP BY category_id
HAVING COUNT(*) >= 3;

SELECT o.id, c.first_name
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id;

SELECT c.first_name, o.id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;

SELECT c.first_name, p.name, oi.quantity
FROM customers AS c
JOIN orders AS o      ON o.customer_id = c.id
JOIN order_items AS oi ON oi.order_id   = o.id
JOIN products AS p    ON p.id          = oi.product_id;

SELECT c.name AS figlia, p.name AS madre
FROM categories AS c
JOIN categories AS p ON p.id = c.parent_id;

SELECT city FROM customers
UNION
SELECT city FROM employees;

-- anche: INTERSECT, EXCEPT, UNION ALL

WHERE country IN ('Italia', 'Spagna');

WHERE price BETWEEN 50 AND 200;

WHERE shipped_at IS NULL;
SELECT COALESCE(body, '(nessun testo)') FROM reviews;

SELECT name,
  CASE
    WHEN price < 50  THEN 'economico'
    WHEN price < 200 THEN 'medio'
    ELSE 'premium'
  END AS fascia
FROM products;

SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

WHERE email LIKE '%@example.com'
   OR name ILIKE 'cuffie%';

LOWER(s), UPPER(s), LENGTH(s)
CONCAT(a, ' ', b)   -- oppure  a || ' ' || b
SUBSTRING(s FROM 1 FOR 3)
TRIM(s), REPLACE(s, 'old', 'new')

ROUND(x, 2), CEIL(x), FLOOR(x), ABS(x)
MOD(a, b)  -- oppure  a % b

CURRENT_DATE, NOW()
EXTRACT(YEAR  FROM ordered_at)
EXTRACT(MONTH FROM ordered_at)
DATE_TRUNC('month', ordered_at)
ordered_at + INTERVAL '7 days'

CAST(price AS INTEGER)
price::INTEGER       -- shorthand PostgreSQL

SELECT name, price,
  AVG(price) OVER (PARTITION BY category_id) AS media_categoria,
  RANK()     OVER (PARTITION BY category_id ORDER BY price DESC) AS rank
FROM products;

SELECT ordered_at, total,
  LAG(total)  OVER (ORDER BY ordered_at) AS precedente,
  LEAD(total) OVER (ORDER BY ordered_at) AS successivo
FROM orders_summary;

WITH spesa AS (
  SELECT customer_id, SUM(quantity * unit_price) AS tot
  FROM order_items oi JOIN orders o ON o.id = oi.order_id
  GROUP BY customer_id
)
SELECT * FROM spesa WHERE tot > 500;

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth, id;

INSERT INTO customers (first_name, last_name, email, city, country, signed_up_on)
VALUES ('Marco', 'Bianchi', 'm.b@example.com', 'Verona', 'Italia', CURRENT_DATE);

INSERT INTO categories (name, parent_id)
VALUES ('Gaming', 1)
RETURNING id, name;

UPDATE products
SET price = price * 1.10
WHERE category_id = 5;

DELETE FROM reviews
WHERE rating < 3;

CREATE TABLE wishlists (
  id          SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  product_id  INTEGER NOT NULL REFERENCES products(id),
  added_on    DATE    NOT NULL DEFAULT CURRENT_DATE,
  UNIQUE (customer_id, product_id)
);

ALTER TABLE customers ADD COLUMN newsletter BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE customers DROP COLUMN newsletter;
DROP TABLE wishlists;

BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 1;
UPDATE products SET stock = stock + 1 WHERE id = 2;
COMMIT;     -- oppure ROLLBACK;