Drill 9: Make it smaller — Views and materialized views

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(10, 2) NOT NULL,
  stock INTEGER NOT NULL DEFAULT 0
);

CREATE VIEW low_stock AS
SELECT id, name, stock
FROM products
WHERE stock < 5;

CREATE MATERIALIZED VIEW product_count_by_name AS
SELECT name, COUNT(*) AS copies
FROM products
GROUP BY name;

REFRESH MATERIALIZED VIEW product_count_by_name;