TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 03

DDL and DML: practice questions

400 questions in four levels, all on RetailMart, the practice database of this course. Write every query yourself, get it wrong, read the error, fix it. That is how it sticks.

Open the SQL PlaygroundPut RetailMart on your laptop
Answers are coming soon. Post your query in the WhatsApp Community or Discord and we will check it together.

Core syntax, applied directly

DATA-TYPE & DDL CONCEPTS

  1. Q1Your junior asks "what is DDL?" - answer in one sentence + give 3 example commands.
  2. Q2What does CREATE TABLE actually do behind the scenes?
  3. Q3Why do we declare data types instead of letting the DB figure it out?
  4. Q4What's the difference between VARCHAR(50) and TEXT? When would you pick each?
  5. Q5Why is NUMERIC preferred over FLOAT for storing money?
  6. Q6What does SERIAL do? Name 3 things it gives you for free.
  7. Q7When would you use BIGSERIAL instead of SERIAL?
  8. Q8What's the difference between TIMESTAMP and DATE?
  9. Q9When should you use TIMESTAMPTZ instead of plain TIMESTAMP?
  10. Q10What is JSONB used for, and how does it differ from TEXT containing JSON?
  11. Q11What is UUID and when would you use it instead of SERIAL?
  12. Q12What's the difference between CREATE DATABASE and CREATE SCHEMA?
  13. Q13Can you have two tables with the same name in one database? Explain.
  14. Q14A junior writes CREATE TABLE Customers (...). Why is lowercase 'customers' usually safer in PostgreSQL?
  15. Q15Explain what IF NOT EXISTS does in CREATE TABLE - and why it's useful.
  16. Q16What does DROP TABLE CASCADE do that DROP TABLE alone doesn't?
  17. Q17What is the danger of running DROP DATABASE in production?
  18. Q18ALTER TABLE ... ADD COLUMN - what's the default value for the new column on existing rows?
  19. Q19ALTER TABLE ... DROP COLUMN - is this reversible? Why or why not?
  20. Q20What happens to existing data when you change a column's TYPE with ALTER TABLE?
  21. Q21A teammate asks: "what's the difference between CHAR(10) and VARCHAR(10)?" - answer.
  22. Q22When would you use BOOLEAN vs a 0/1 INT column?
  23. Q23Why do experienced developers AVOID adding columns at the end of huge tables in production?
  24. Q24Explain what "domain integrity" means and how data types enforce it.
  25. Q25Why is it bad to store phone numbers as INT?

CREATE TABLE PRACTICE (25) Topics: Build tables of various shapes Each question below is to be run in YOUR practice practice DB.

  1. Q26Create a schema called practice in your practice database.
  2. Q27In practice, create a table employees with: emp_id (SERIAL PK), full_name (VARCHAR 100), salary (NUMERIC 10,2), joined (DATE).
  3. Q28Create a table products with: product_id SERIAL PK, name VARCHAR(150), price NUMERIC(12,2), in_stock BOOLEAN.
  4. Q29Create a table customers with: cust_id SERIAL PK, email VARCHAR(100), phone VARCHAR(20), is_active BOOLEAN, created_at TIMESTAMP.
  5. Q30Create a table orders_demo with: order_id BIGSERIAL PK, cust_id INT, total NUMERIC(12,2), order_date DATE.
  6. Q31Create a table reviews with: review_id SERIAL PK, rating INT, comment TEXT, posted_on TIMESTAMPTZ.
  7. Q32Create a table events_log with: event_id BIGSERIAL PK, payload JSONB, occurred_at TIMESTAMPTZ.
  8. Q33Create a table sessions with: session_id UUID PK, user_id INT, started TIMESTAMPTZ, ended TIMESTAMPTZ.
  9. Q34Create a tiny lookup table tier_master with: tier_id INT PK, tier_name VARCHAR(20), min_points INT.
  10. Q35Create an audit table login_log with: log_id BIGSERIAL PK, user_id INT, login_time TIMESTAMP, ip_address VARCHAR(45).
  11. Q36Create a table feedback with: feedback_id SERIAL PK, customer_id INT, rating INT, freeform TEXT.
  12. Q37Create a SECOND schema called archive (for old data).
  13. Q38Create a table archive.old_orders with: order_id INT PK, archived_at TIMESTAMP.
  14. Q39Create a table practice.cities with city_id SERIAL PK, city_name VARCHAR(50), state VARCHAR(50).
  15. Q40Create a table practice.brands: brand_id SERIAL PK, brand_name VARCHAR(100), country VARCHAR(50).
  16. Q41Drop the practice.cities table you just created.
  17. Q42Recreate cities - but this time use IF NOT EXISTS so it doesn't error if it already exists.
  18. Q43Create a table inventory with composite primary key (warehouse_id INT, product_id INT, snapshot_date DATE) and quantity_on_hand INT.
  19. Q44Create a table phone_book with name VARCHAR(100) and phone_number VARCHAR(20) - NO primary key (intentional, to learn what happens).
  20. Q45Create a table products_v2 (in practice) that is identical to products but adds a sku_code VARCHAR(50) column.
  21. Q46Create a table practice.payment_modes: mode_id SERIAL PK, mode_name VARCHAR(30), is_active BOOLEAN.
  22. Q47Create a table practice.bank_accounts: account_id SERIAL PK, holder VARCHAR(100), balance NUMERIC(14,2), opened_on DATE.
  23. Q48Create a table practice.transactions: txn_id BIGSERIAL PK, from_account INT, to_account INT, amount NUMERIC(14,2), txn_time TIMESTAMPTZ.
  24. Q49Create a table practice.subscriptions: sub_id SERIAL PK, user_id INT, plan_name VARCHAR(50), starts DATE, ends DATE, auto_renew BOOLEAN.
  25. Q50Create a table practice.notifications: notif_id BIGSERIAL PK, user_id INT, channel VARCHAR(20), sent_at TIMESTAMPTZ, payload JSONB.

ALTER TABLE PRACTICE (25) Topics: Add, drop, rename columns; change types

  1. Q51Add a new column 'department' VARCHAR(50) to practice.employees.
  2. Q52Add a new column 'last_login' TIMESTAMP to practice.customers.
  3. Q53Add a column 'discount_pct' INT to practice.products.
  4. Q54Add a column 'is_verified' BOOLEAN to practice.customers.
  5. Q55Add a column 'updated_at' TIMESTAMPTZ to practice.products.
  6. Q56Rename the column 'department' in employees to 'dept_name'.
  7. Q57Rename the column 'discount_pct' in products to 'discount_percent'.
  8. Q58Rename the table 'reviews' to 'product_reviews'.
  9. Q59Drop the column 'last_login' from customers.
  10. Q60Drop the column 'is_active' from customers.
  11. Q61Change the type of products.price from NUMERIC(12,2) to NUMERIC(14,2) - to allow larger prices.
  12. Q62Change the type of employees.salary to NUMERIC(12,2).
  13. Q63Change customers.phone from VARCHAR(20) to VARCHAR(25).
  14. Q64Add a column 'tags' of type TEXT[] (array of text) to practice.products.
  15. Q65Add a column 'metadata' of type JSONB to practice.products.
  16. Q66Add a column 'rating_avg' NUMERIC(3,2) to practice.products.
  17. Q67Drop the column 'metadata' you just added to products.
  18. Q68Add a column 'created_at' TIMESTAMP with DEFAULT CURRENT_TIMESTAMP to practice.products.
  19. Q69Add a column 'is_archived' BOOLEAN with DEFAULT FALSE to practice.products.
  20. Q70Set a DEFAULT of 0 on the discount_percent column in products.
  21. Q71Drop the DEFAULT from discount_percent.
  22. Q72Rename the schema archive to cold_storage.
  23. Q73Drop the table practice.notifications.
  24. Q74Drop the entire cold_storage schema with CASCADE (removes everything inside).
  25. Q75Drop the practice.subscriptions table only if it exists (use IF EXISTS).

PICK THE RIGHT DATA TYPE (25) For each scenario, the question asks you to write the column declaration with the type you'd choose. Run nothing - just declare the type.

  1. Q76Storing an Indian phone number ("+91 9876543210"). What type?
  2. Q77Storing a customer's lifetime spend (Rs with paisa precision). What type?
  3. Q78Storing a product description that could be a few sentences. What type?
  4. Q79Storing whether a customer has opted in to marketing emails. What type?
  5. Q80Storing the timestamp a user clicked a button (millisecond precision, with timezone). What type?
  6. Q81Storing just the date someone joined the company. What type?
  7. Q82Storing a UPI VPA like "priya@axis". What type and length?
  8. Q83Storing a pincode (always 6 digits). What type and constraint?
  9. Q84Storing the latitude of a delivery address. What type and precision?
  10. Q85Storing a primary key for a small lookup table (~50 rows). What type?
  11. Q86Storing a primary key for a high-volume events table (1B+ rows). What type?
  12. Q87Storing event payload from an API webhook (nested JSON). What type?
  13. Q88Storing a session token that should be globally unique. What type?
  14. Q89Storing an order quantity (small integer, 1-1000). What type?
  15. Q90Storing a percentage value like 12.50% with 2 decimal precision. What type?
  16. Q91Storing a long product review that might be 5,000 characters. What type?
  17. Q92Storing a yes/no flag for is_email_verified. What type?
  18. Q93Storing a date range start (just a date, no time). What type?
  19. Q94Storing a website URL (could be 2,000 chars). What type?
  20. Q95Storing tax_rate as a percentage with 4 decimal precision (e.g., 18.0000). What type?
  21. Q96Storing tags for a blog post (multiple tags per post). What type or design?
  22. Q97Storing IPv4 / IPv6 addresses. What type?
  23. Q98Storing a hash (SHA-256, 64 hex chars). What type?
  24. Q99Storing a price in USD that may need precision up to 4 decimals (currency conversion). What type?
  25. Q100Storing a one-letter status code ('A', 'I', 'D'). What type?

Combined ideas, multi-step thinking

DDL & DATA-TYPE DEEPER CONCEPTUAL

  1. Q1Compare NUMERIC(10,2), NUMERIC, and FLOAT - when do you pick each?
  2. Q2Defend why a 4-byte INT is enough for most SERIAL columns but not for high-volume events.
  3. Q3What is a GENERATED COLUMN in PostgreSQL - give a real RetailMart-like use case.
  4. Q4Compare CHAR(64) vs VARCHAR(64) vs TEXT for storing a SHA-256 hex hash. Which is best?
  5. Q5When does TIMESTAMPTZ matter MORE than TIMESTAMP - give a concrete example.
  6. Q6Why is INTERVAL its own type? When have you actually needed it?
  7. Q7Compare TEXT[] (array of text) vs a separate child table. Defend each design with one use case.
  8. Q8When would you use ENUM in PostgreSQL - and what's the migration tax?
  9. Q9Defend the choice of UUID vs SERIAL for a "tracking event" primary key.
  10. Q10What's the difference between SMALLINT, INT, BIGINT - and how much disk do they each use?
  11. Q11Why do experienced engineers ALMOST NEVER use FLOAT/REAL columns?
  12. Q12Compare DOUBLE PRECISION vs NUMERIC(20,4) for a "GPS latitude" column.
  13. Q13What's the difference between DEFAULT NOW() and DEFAULT CURRENT_TIMESTAMP?
  14. Q14Why is "ON UPDATE CURRENT_TIMESTAMP" not native to PostgreSQL - what alternative do PG users use?
  15. Q15Defend the choice to use BOOLEAN vs a 1-character CHAR(1) for a yes/no flag.
  16. Q16What is BYTEA used for - and when would you NOT store binary in the database?
  17. Q17Compare TIMESTAMP(3) vs TIMESTAMP - what does the (3) mean?
  18. Q18A junior types JSONB-everything. Argue when JSONB is OVERKILL vs RIGHT.
  19. Q19Why is "INET" type useful for storing IP addresses (over VARCHAR)?
  20. Q20Compare GENERATED ALWAYS AS IDENTITY vs SERIAL - which does modern PostgreSQL prefer and why?
  21. Q21Why is choosing the SMALLEST appropriate integer type still important even with cheap disk?
  22. Q22Defend the use of CITEXT for case-insensitive comparisons over LOWER()-everywhere.
  23. Q23Explain the design tradeoff between storing a calculated field vs computing on-the-fly.
  24. Q24When designing a new table, what FIVE columns should almost every business table have?
  25. Q25Compare schema-per-tenant vs row-per-tenant (multi-tenant SaaS) - when do you pick each?

ADVANCED CREATE TABLE

  1. Q26Create practice.audit_logs with composite PK (table_name, record_id, changed_at), columns user_id INT, action VARCHAR(20).
  2. Q27Create practice.order_items with FK to a (hypothetical) practice.orders table - on CREATE.
  3. Q28Create a table with a generated column: full_name = first_name || ' ' || last_name.
  4. Q29Create a table that uses GENERATED ALWAYS AS IDENTITY for the primary key (instead of SERIAL).
  5. Q30Create a table with an inline UNIQUE constraint on (email, tenant_id).
  6. Q31Create a table with three CHECK constraints inline: price > 0, quantity >= 0, name NOT NULL.
  7. Q32Create a table that uses TEXT[] for a 'tags' column.
  8. Q33Create a table that uses JSONB for 'metadata' with a CHECK that requires 'source' key (use jsonb_exists).
  9. Q34Create a table that uses NUMERIC(20,4) for a 'exchange_rate' column.
  10. Q35Create a table with a DEFAULT NOW() on created_at AND a separate updated_at with DEFAULT NOW().
  11. Q36Create a table with a CHECK that validates email contains '@' (regex check).
  12. Q37Create a table partitioned by RANGE on created_at by month - for events.
  13. Q38Create a table that has BOTH a SERIAL surrogate PK and a UNIQUE business key (e.g., sku_code).
  14. Q39Create a self-referencing FK table: categories(category_id PK, parent_category_id INT REFERENCES same table).
  15. Q40Create a table with a multi-column FK: order_line(order_id INT, line_no INT, product_id INT) -> orders(order_id) AND constraint on (order_id, line_no) being unique.
  16. Q41Create a junction table for products and tags with composite PK (product_id, tag_id) and both FKs.
  17. Q42Create a table with column-level COMMENTS using COMMENT ON COLUMN.
  18. Q43Create a table with a CHECK using BETWEEN and IN combined.
  19. Q44Create a table that uses INET for an IP address column.
  20. Q45Create a table using CITEXT for case-insensitive email column (must enable citext extension first).
  21. Q46Create a table where one column is generated stored: total = quantity * unit_price (STORED).
  22. Q47Create a table with TWO surrogate keys (one INT SERIAL for legacy, one UUID for new system).
  23. Q48Create a table with a DEFERRABLE INITIALLY DEFERRED FK (advanced - for chicken-and-egg insert order).
  24. Q49Create a temporary table using CREATE TEMP TABLE - what's the lifetime?
  25. Q50Create an UNLOGGED table - when is this appropriate and what do you give up?

COMPLEX ALTER PATTERNS

  1. Q51Add THREE columns to practice.products in a single ALTER TABLE statement.
  2. Q52Drop TWO columns from practice.products in a single ALTER.
  3. Q53Add a column with a DEFAULT and NOT NULL in one shot - what's the migration risk?
  4. Q54Change the data type of products.price from NUMERIC(12,2) to NUMERIC(14,2) USING a cast.
  5. Q55Migrate an INT column 'status_id' to a VARCHAR 'status' - show the multi-step migration.
  6. Q56Rename a table AND its index in two statements.
  7. Q57ALTER TABLE to add a CHECK constraint on existing data - what if some rows violate?
  8. Q58Add a NOT NULL to a column that has existing NULLs - what's the safe two-step approach?
  9. Q59Use ALTER TABLE ... ATTACH PARTITION to add a partition to a partitioned table.
  10. Q60Use ALTER TABLE ... SET TABLESPACE to move a table to a different tablespace.
  11. Q61Add a UNIQUE constraint to an existing column with duplicate values - what happens?
  12. Q62Combine ADD COLUMN + ALTER TYPE + SET NOT NULL in one ALTER statement.
  13. Q63Use ALTER TABLE ... DISABLE TRIGGER to temporarily silence a trigger during bulk load.
  14. Q64Use ALTER TABLE ... INHERIT to make one table inherit from a parent (legacy partitioning).
  15. Q65Drop a column that's referenced by a view - what error do you get and how do you resolve?
  16. Q66Rename a column referenced by an FK constraint - does the FK still work?
  17. Q67ALTER COLUMN to change a column's collation.
  18. Q68ALTER COLUMN ... SET STATISTICS to increase planner accuracy on a heavily-queried column.
  19. Q69ALTER COLUMN ... SET STORAGE EXTENDED - what is column storage and when does it matter?
  20. Q70Add an INDEX to an existing column via CREATE INDEX (not ALTER TABLE).
  21. Q71Add a PARTIAL INDEX (WHERE clause-bound) on an active rows only.
  22. Q72Add a UNIQUE INDEX (not a UNIQUE constraint) - what's the difference?
  23. Q73Drop an existing index that's no longer used.
  24. Q74Rename an index.
  25. Q75Use CREATE INDEX CONCURRENTLY to add an index without locking the table.

DESIGN EXERCISES

  1. Q76Design a 'feature_flags' table for a SaaS app: feature_name, percent_rollout, active_from. List columns + types + constraints.
  2. Q77Design a 'user_sessions' table that tracks login activity. List columns + types + indexes.
  3. Q78Design a 'price_history' table - every time a product's price changes, log a row.
  4. Q79Design a 'subscription' table for monthly billing - list columns including next_billing_date.
  5. Q80Design a 'product_categories_tree' table that can store a hierarchy of unlimited depth.
  6. Q81Design a 'two-factor_auth_codes' table - temporary codes with expiry.
  7. Q82Design a 'cron_jobs' table that schedules background tasks.
  8. Q83Design a 'webhook_deliveries' table that tracks attempt/retry/success.
  9. Q84Design a 'shopping_cart_items' table (cart_id, product_id, qty, price_at_add).
  10. Q85Design a 'addresses' table that supports MULTIPLE addresses per customer with one default.
  11. Q86Design a 'coupons' table with code, discount_pct, max_uses, valid_until.
  12. Q87Design a 'banking_transactions' table with idempotency_key + ACID guarantees.
  13. Q88Design a 'audit_record_changes' table that logs every UPDATE on a target table.
  14. Q89Design a 'inventory_movements' table that tracks every stock-in / stock-out event.
  15. Q90Design a 'kyc_documents' table for storing customer document uploads.
  16. Q91Design a 'leaderboard' table for a game (user_id, score, season_id).
  17. Q92Design a 'support_ticket_messages' table - many messages per ticket.
  18. Q93Design an 'email_outbox' table that the application reads to send emails.
  19. Q94Design a 'rate_limit_buckets' table for an API rate limiter.
  20. Q95Design a 'tenants' table for multi-tenant SaaS.
  21. Q96Design a 'product_variants' table (one product, many variants like size/color).
  22. Q97Design a 'survey_responses' table where each survey has dynamic questions (JSONB or normalized?).
  23. Q98Design a 'gdpr_data_requests' table - track delete-my-data requests with status.
  24. Q99Design a 'ml_predictions' table to store model output: input_id, prediction, confidence, model_version, timestamp.
  25. Q100Design a 'a_b_experiments' table to track which user got which variant.

Interview grade, edge cases

ADVANCED DDL - CONCEPTUAL

  1. Q1What is a GENERATED ALWAYS AS column - give 3 use cases.
  2. Q2Compare STORED vs VIRTUAL generated columns (Postgres only supports one).
  3. Q3Why is PARTITION BY RANGE on order_date the standard pattern for time-series?
  4. Q4When do you choose PARTITION BY LIST vs RANGE vs HASH?
  5. Q5What is "partition pruning" - and why does it require the planner to see the partition key in WHERE?
  6. Q6Compare table INHERITANCE (legacy) vs declarative PARTITIONING.
  7. Q7What is a DOMAIN type - and when is it cleaner than a CHECK constraint?
  8. Q8What is a COMPOSITE type - give a use case (address: street/city/zip).
  9. Q9When do you use ENUM types vs a lookup table?
  10. Q10Why is ENUM hard to extend in production (ADD VALUE limitations)?
  11. Q11Walk through CHECK with regex (e.g., email format) - and when to NOT do this in DDL.
  12. Q12What is an EXCLUSION constraint - give a booking-overlap example.
  13. Q13ON DELETE CASCADE vs ON DELETE SET NULL vs ON DELETE RESTRICT - when each.
  14. Q14What is a DEFERRABLE constraint - when is INITIALLY DEFERRED useful?
  15. Q15Why does Postgres require UNIQUE constraints on the partition key for partitioned tables?
  16. Q16Walk through how attach/detach partition works without downtime.
  17. Q17What is sub-partitioning - and why do most setups avoid it?
  18. Q18Compare GENERATED column vs trigger-based computed column - tradeoffs.
  19. Q19What is a "default partition" - what happens when a row matches no defined partition?
  20. Q20Why is HASH partitioning bad for time-range queries?
  21. Q21Explain how foreign keys interact with partitioned parents (PG12+).
  22. Q22What is "constraint exclusion" (legacy) vs "partition pruning"?
  23. Q23Walk through schema-only DDL (CREATE TABLE ... LIKE) - what's copied?
  24. Q24What is CREATE TABLE AS vs CREATE TABLE + INSERT - when does each preserve constraints?
  25. Q25Explain why ALTER TYPE ... ADD VALUE cannot run in a transaction.

PARTITIONED TABLES

  1. Q26Create a partitioned table orders_p PARTITION BY RANGE(order_date) with 24 monthly partitions for 2024-2025.
  2. Q27Create a default partition for any row outside defined ranges.
  3. Q28Create monthly partitions using a DO loop in PL/pgSQL.
  4. Q29ATTACH an existing table as a partition of orders_p.
  5. Q30DETACH a partition - show how data is preserved.
  6. Q31Create a HASH-partitioned customer_events with 8 partitions on customer_id.
  7. Q32Create LIST-partitioned regional_orders by region_id (one partition per region).
  8. Q33INSERT 100 rows into orders_p across multiple months - verify auto-routing.
  9. Q34Query a single partition directly (FROM ONLY orders_p_2025_03).
  10. Q35Verify partition pruning with EXPLAIN on WHERE order_date BETWEEN x AND y.
  11. Q36Add an index on each partition (per-partition indexing).
  12. Q37Add a global-ish index by creating an index on the parent (PG11+ propagates).
  13. Q38Drop an old partition cheaply (DROP TABLE orders_p_2024_01).
  14. Q39Archive a partition by DETACH + COPY out + DROP.
  15. Q40Use pg_partitions or pg_inherits to list children of orders_p.
  16. Q41Show how DELETE WHERE order_date < ... can be replaced by DROP PARTITION.
  17. Q42Create a CHECK constraint that mirrors the partition key for safety.
  18. Q43Add a NEW monthly partition for next month (CREATE TABLE ... PARTITION OF).
  19. Q44Show what happens when you INSERT a row with order_date outside any partition (no default).
  20. Q45Use a default partition + run a job to move rows into proper partitions.
  21. Q46Verify that UPDATE on a row that crosses partitions works in PG11+ (row moves to right partition).
  22. Q47Show the storage size per partition (pg_total_relation_size).
  23. Q48Build a partitioned page_views table HASH(customer_id) with 16 partitions.
  24. Q49Build a LIST-partitioned tickets table by priority.
  25. Q50Build a RANGE-partitioned pay_slips table by salary_year + sub-partition by salary_month using LIST.

CUSTOM TYPES (DOMAINS, ENUMS, COMPOSITES)

  1. Q51Create a DOMAIN positive_money AS numeric(10,2) CHECK (VALUE > 0).
  2. Q52Create a DOMAIN us_state AS char(2) CHECK (VALUE ~ '^[A-Z]{2}$').
  3. Q53Create a DOMAIN email_address AS text CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$').
  4. Q54Use the email domain in a customers_strict table.
  5. Q55Create a COMPOSITE TYPE address (street text, city text, state char(2), zip varchar(10)).
  6. Q56Use the address composite type as a column in a customers_with_addr table.
  7. Q57Insert a row with address as ROW(...) literal - INSERT INTO customers_with_addr VALUES (..., ROW('123 Main','LA','CA','90001')).
  8. Q58Access composite fields with (addr).city syntax.
  9. Q59Create an ENUM order_status AS ENUM ('Placed','Paid','Shipped','Delivered','Cancelled').
  10. Q60Use the ENUM in a strict_orders table.
  11. Q61Add a value to the ENUM with ALTER TYPE ADD VALUE 'Returned'.
  12. Q62Reorder ENUM with ALTER TYPE ... ADD VALUE 'X' BEFORE 'Y'.
  13. Q63Compare ENUM column storage vs varchar storage.
  14. Q64Drop a DOMAIN and discuss why it requires no dependents.
  15. Q65Build a hierarchy: domain -> composite -> table column.
  16. Q66Show error message when INSERT violates the DOMAIN check.
  17. Q67Create RANGE TYPE int4range for stock_levels (low, high).
  18. Q68Create a CITEXT (case-insensitive text) column for emails.
  19. Q69Use UUID type with DEFAULT gen_random_uuid() (requires pgcrypto).
  20. Q70Create a JSONB column with a CHECK that value is an object (jsonb_typeof).
  21. Q71Build a SERIAL vs BIGSERIAL vs IDENTITY comparison.
  22. Q72Show how dropping a domain cascades to columns.
  23. Q73ALTER COLUMN to use a domain instead of base type.
  24. Q74Build a composite type product_dimensions (length, width, height numerics).
  25. Q75Add a CHECK on a composite type: ((dim).length > 0 AND (dim).width > 0).

CONSTRAINT PATTERNS

  1. Q76CHECK (price > 0 AND discount_pct BETWEEN 0 AND 100) on products_strict.
  2. Q77CHECK that end_date >= start_date in a campaigns_strict table.
  3. Q78CHECK that email matches regex (in CREATE TABLE, then test insert).
  4. Q79EXCLUDE USING gist (room WITH =, during WITH &&) - bookings overlap prevention.
  5. Q80EXCLUDE USING gist on shifts table - no overlapping shifts per employee.
  6. Q81UNIQUE (customer_id, product_id, review_date) - one review per customer-product-day.
  7. Q82UNIQUE NULLS NOT DISTINCT (PG15+) - treat multiple NULLs as duplicate.
  8. Q83CHECK with regex on phone_number column.
  9. Q84ON DELETE CASCADE chain: customer -> orders -> order_items.
  10. Q85ON DELETE SET NULL on reviews.customer_id when customer is deleted.
  11. Q86ON UPDATE CASCADE when product_id changes.
  12. Q87DEFERRABLE INITIALLY DEFERRED on a circular FK between two tables.
  13. Q88NOT VALID FK - add a constraint without checking existing data, then VALIDATE later.
  14. Q89NOT NULL with DEFAULT - show how to add to a huge table without rewrite (PG11+).
  15. Q90ADD COLUMN with GENERATED ALWAYS expression - used for full_name = first_name || ' ' || last_name.
  16. Q91ADD COLUMN with STORED GENERATED + index on the generated column.
  17. Q92CHECK constraint on a partitioned table - must be repeated on each partition.
  18. Q93PRIMARY KEY must include partition key on partitioned tables - show why.
  19. Q94FOREIGN KEY pointing to a partitioned table (PG12+) - show declaration.
  20. Q95SELF-REFERENCING FK (manager_id -> employee_id) - show CASCADE/SET NULL choice.
  21. Q96MULTI-COLUMN FK matching composite primary key.
  22. Q97UNIQUE INDEX vs UNIQUE CONSTRAINT - show difference using partial unique.
  23. Q98Partial unique: UNIQUE(email) WHERE deleted_at IS NULL - soft delete safety.
  24. Q99CHECK with subquery? Show why it's NOT allowed - and the workaround (trigger).
  25. Q100Build a "rich" employees table with: identity PK, email domain, FK to dim_department, CHECK age >= 18, CHECK salary > 0, UNIQUE(email), generated full_name, audit timestamps with DEFAULT now().

Production scenarios, optimisation

SHARDING-READY DESIGN

  1. Q1Design sales.orders with a "tenant_id" column for future sharding.
  2. Q2Choose between (tenant_id, order_id) PK vs separate UUID order_id.
  3. Q3Make every FK include tenant_id (composite).
  4. Q4Avoid auto-increment IDs - use ULID or UUID.
  5. Q5Design "fanout-resistant" customer table.
  6. Q6Co-locate related tables via tenant_id.
  7. Q7Reference table pattern for small dim tables.
  8. Q8Add a "sharding hint" column (computed).
  9. Q9Build a "shard map" lookup.
  10. Q10Implement Snowflake IDs (time + node + seq).
  11. Q11Avoid global sequences - use per-shard.
  12. Q12Design audit log that survives shard moves.
  13. Q13Tag every row with created_at + last_modified for replication.
  14. Q14Avoid SELECT MAX(id) - use a sidecar sequences table.
  15. Q15Use logical replication-ready primary keys.
  16. Q16Allow "cross-shard reads" via a coordinator view.
  17. Q17Use partial indexes to keep hot subsets small per shard.
  18. Q18Design soft-delete pattern that's idempotent across shards.
  19. Q19Reference data via materialized views replicated to each shard.
  20. Q20Plan for "shard split" - even ID ranges.
  21. Q21Plan for "shard merge" - same.
  22. Q22Build a shard-aware DML wrapper (function).
  23. Q23Add CHECK constraint matching shard predicate (CHECK tenant_id = ...).
  24. Q24Tier customers into "small/medium/large" with different table strategies.
  25. Q25Implement zero-FK design (FK enforced in app, not DB).

MULTI-LEVEL PARTITIONING

  1. Q26RANGE(year) -> LIST(month) for pay_slips.
  2. Q27LIST(tenant_id) -> RANGE(order_date) for sales.orders.
  3. Q28RANGE(quarter) -> HASH(customer_id) for page_views.
  4. Q29Create all 24 monthly partitions for 2024-2025 via DO loop.
  5. Q30Add daily partitions for the current quarter.
  6. Q31Drop oldest year's partitions.
  7. Q32ATTACH a foreign table as a partition (S3 cold storage).
  8. Q33Mix logged + unlogged partitions for hot vs cold.
  9. Q34Convert flat table to partitioned (PG12+ ALTER TABLE ... DETACH/ATTACH).
  10. Q35Verify partition pruning with EXPLAIN.
  11. Q36Use generated column as partition key.
  12. Q37Use expression partitioning (PARTITION BY RANGE (date_trunc('day', ts))).
  13. Q38Partition by tenant_id LIST.
  14. Q39Partition tickets by priority LIST.
  15. Q40HASH-partitioned big table (16 partitions) for parallel scan.
  16. Q41Build per-partition indexes.
  17. Q42Add a "global" UNIQUE constraint when possible.
  18. Q43Add per-partition CHECK constraints matching range.
  19. Q44Add a default partition + monitor its size.
  20. Q45Implement "rolling window" auto-create + auto-drop.
  21. Q46Sub-partition page_views by device_type.
  22. Q47Use partition-wise JOIN.
  23. Q48Test partition-wise aggregation.
  24. Q49Partition INHERITANCE for legacy tables (rare).
  25. Q50Detach partition + run pg_dump on it.

TYPE MODELING AT SCALE

  1. Q51Domain for non-negative numeric.
  2. Q52Domain for email + regex.
  3. Q53Domain for phone (E.164).
  4. Q54Domain for currency (3-letter ISO).
  5. Q55Composite type for address (street/city/zip/country).
  6. Q56Composite type for money (amount + currency).
  7. Q57ENUM for tier - and the cost of adding values.
  8. Q58ENUM for ticket priority.
  9. Q59Range type for stock_level.
  10. Q60Range type for valid_from/valid_to.
  11. Q61JSONB column with CHECK for shape.
  12. Q62Use ARRAY type for tags.
  13. Q63Use ARRAY type with UNNEST in queries.
  14. Q64Use HSTORE for sparse attributes (vs JSONB).
  15. Q65Use UUID v4 vs v7 (time-ordered) for primary keys.
  16. Q66Generated columns for full_name.
  17. Q67Generated columns for lower(email).
  18. Q68Generated columns indexed.
  19. Q69Use citext for case-insensitive email.
  20. Q70Use tsvector for searchable description.
  21. Q71Domain for percent (0..100).
  22. Q72Domain for slug (URL-safe).
  23. Q73Composite type for delivery coords (lat/long).
  24. Q74PostGIS geometry types (intro).
  25. Q75Build a "rich" customer table using 6+ custom types.

SCHEMA EVOLUTION

  1. Q76Add column with default - pre-PG11 vs PG11+ behavior.
  2. Q77Drop column on huge table without lock.
  3. Q78Rename column without breaking app.
  4. Q79Change column type from INT to BIGINT (online).
  5. Q80Change column from TEXT to JSONB (online).
  6. Q81ADD NOT NULL on existing column safely.
  7. Q82ADD FK on huge table - NOT VALID then VALIDATE.
  8. Q83ADD UNIQUE on huge table - CREATE INDEX CONCURRENTLY first.
  9. Q84Split a column into two.
  10. Q85Merge two columns into one.
  11. Q86Rename a table (atomic swap with view).
  12. Q87Move a table to a different schema.
  13. Q88Move a table to a different tablespace.
  14. Q89Convert HEAP to UNLOGGED safely.
  15. Q90Add a generated column to existing huge table.
  16. Q91Rebuild a primary key online (pg_repack).
  17. Q92Add a composite index online.
  18. Q93Replace an index with a more selective one.
  19. Q94Migrate a partitioned table to a different partition strategy.
  20. Q95Versioning a table schema via dual-table writes during migration.
  21. Q96Implement schema migrations as idempotent SQL files.
  22. Q97Add a CHECK constraint NOT VALID then VALIDATE.
  23. Q98Convert a single-tenant table to multi-tenant by adding tenant_id.
  24. Q99Build "shadow table + trigger" pattern for zero-downtime migration.
  25. Q100Document a 10-step zero-downtime migration plan (any non-trivial change).