Altering Tables is a practical SQL concept used to design, query, secure, operate, or analyze relational data correctly.
ALTER TABLE customers ADD COLUMN phone VARCHAR(30);Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use.
An online shop stores customers, products, orders, and order items. The schema must prevent duplicate identities, missing required values, and orders that reference customers that do not exist.
BEGIN;\n-- Preview the exact target set first.\nSELECT id FROM orders WHERE status = 'pending';\n-- Apply the Altering Tables operation, verify affected rows, then commit.\nCOMMIT;The schema or operation satisfies the stated rule, rejects invalid data, and can be verified with a repeatable query.
This lesson reuses picostore. Relevant tables: customers, products, orders, order_items. Keep the starter rows from the Introduction lesson so results remain comparable.
ALTER TABLE products\n ADD COLUMN active BOOLEAN NOT NULL DEFAULT TRUE;\n\nSELECT product_id, name, active FROM products;Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct.
Start with a small customer table and retrieve active customers in a predictable order.
SELECT customer_id, name, email\nFROM customers\nWHERE status = 'active'\nORDER BY name;Use a CTE and a window function to rank customer revenue while keeping the query readable and testable.
WITH customer_revenue AS (\n SELECT customer_id, SUM(total_amount) AS revenue\n FROM orders\n WHERE order_status = 'completed'\n GROUP BY customer_id\n)\nSELECT customer_id, revenue,\n DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank\nFROM customer_revenue\nORDER BY revenue_rank, customer_id;Model the rule with keys and constraints instead of relying only on application code. MySQL and PostgreSQL support the core relational design, but generated-column, check-constraint, and alteration details can differ by server version.
Altering Tables is a practical SQL concept used to design, query, secure, operate, or analyze relational data correctly.
ALTER TABLE customers ADD COLUMN phone VARCHAR(30);Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use.
An online shop stores customers, products, orders, and order items. The schema must prevent duplicate identities, missing required values, and orders that reference customers that do not exist.
BEGIN;\n-- Preview the exact target set first.\nSELECT id FROM orders WHERE status = 'pending';\n-- Apply the Altering Tables operation, verify affected rows, then commit.\nCOMMIT;The schema or operation satisfies the stated rule, rejects invalid data, and can be verified with a repeatable query.
This lesson reuses picostore. Relevant tables: customers, products, orders, order_items. Keep the starter rows from the Introduction lesson so results remain comparable.
ALTER TABLE products\n ADD COLUMN active BOOLEAN NOT NULL DEFAULT TRUE;\n\nSELECT product_id, name, active FROM products;Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct.
Start with a small customer table and retrieve active customers in a predictable order.
SELECT customer_id, name, email\nFROM customers\nWHERE status = 'active'\nORDER BY name;Use a CTE and a window function to rank customer revenue while keeping the query readable and testable.
WITH customer_revenue AS (\n SELECT customer_id, SUM(total_amount) AS revenue\n FROM orders\n WHERE order_status = 'completed'\n GROUP BY customer_id\n)\nSELECT customer_id, revenue,\n DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank\nFROM customer_revenue\nORDER BY revenue_rank, customer_id;Model the rule with keys and constraints instead of relying only on application code. MySQL and PostgreSQL support the core relational design, but generated-column, check-constraint, and alteration details can differ by server version.