SQL (PostgreSQL, MySQL, SQLite) Module 14
Views, functions, procedures and triggers interview questions
Interview questions on views, functions, procedures and triggers: warm-ups, then what each harder question tests and how a good answer starts.
Warm-up questions
Short questions with short answers, each answered in full.
-
Fresher to mid
What is a view, and does it store any data?
-
Mid
Can you insert or update rows through a view?
-
Fresher to mid
What is the difference between a view and a materialized view?
Answered in the lesson Materialized views and summary tables
-
Fresher to mid
What is the difference between a stored function and a stored procedure?
-
Fresher to mid
When do you use a BEFORE trigger, and when an AFTER trigger?
Answered in the lesson Triggers: automatic actions on change
Questions with model answers
What each question tests, the first step of a good answer and the follow-ups to expect.
-
Mid to senior
The Pune support desk edits customers through a PostgreSQL view, pune_customers, defined with WHERE city = 'Pune'. One agent changes a customer's city to Mumbai through the view, and the customer vanishes from it; another inserts a Delhi customer through it. What allowed this, and how do you prevent it in PostgreSQL, MySQL and SQLite?
- What it tests
- How an updatable view's WHERE treats writes, WITH CHECK OPTION with LOCAL and CASCADED, and checking new rows in an INSTEAD OF trigger where the engine has no check option.
- First step
- Separate what the view's WHERE does for reads from what it does for writes. It limits which rows an UPDATE or DELETE can reach, but it says nothing about the new values those rows get. Then name the clause that checks new rows against the view's condition, and what takes its place when writes go through a trigger.
- Follow-ups
-
- The view is built on another view that keeps only active customers. Which check covers both conditions?
- Can an agent still set a customer's phone number to anything at all?
- How would you test that the protection works?
From the lesson Views and updatable views
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
- Pro pass · 1 week₹79$2.99
- 30 days₹179$5.99
- 1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Mid to senior
A reporting view is defined as SELECT * FROM orders in PostgreSQL, MySQL and SQLite. A migration adds a column, discount_paise, and then drops an old column, note. What happens to the view in each engine at each step, and how would you change the view and the migration?
- What it tests
- When each engine expands * in a view, which engines track a view's dependence on a column, the limits of CREATE OR REPLACE VIEW, and naming a view's columns.
- First step
- Take the two steps one engine at a time. For each, ask when it turns * into a list of columns, once at creation or at every read, and whether it records that the view depends on the column being dropped. Then decide which columns the view should promise its readers, and write that list out.
- Follow-ups
-
- The report must now show discount_paise. How do you add it in PostgreSQL without dropping the view?
- How do you find every view a migration would break before you run it?
- Why can a SELECT * view do harm even when no column is dropped?
From the lesson Views and updatable views
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Mid to senior
A PostgreSQL dashboard reads a materialized view that a job refreshes every 5 minutes with REFRESH MATERIALIZED VIEW. Each refresh takes 40 seconds, and while it runs every dashboard request hangs. Why, what do you change, and what does the change cost?
- What it tests
- The locks taken by REFRESH and REFRESH CONCURRENTLY, the unique index CONCURRENTLY needs, its cost when many rows change, and keeping refresh jobs from piling up.
- First step
- Look at the locks while a refresh runs: pg_locks shows an AccessExclusiveLock on the view, held by the job, and the dashboard queries waiting behind it. Then check whether the view has a unique index on plain columns that covers every row, which the concurrent form needs, and how many rows a typical refresh changes.
- Follow-ups
-
- The view has no single column that is unique. What do you index?
- Most refreshes change 2 % of the rows, but the nightly one changes nearly all of them. Which form do you use for each?
- A slow run makes two refresh jobs overlap. What happens, and how do you prevent it?
From the lesson Materialized views and summary tables
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Mid to senior
Finance finds a nightly daily-revenue summary lower than the orders table on a few days every month, by about 0.4 %. The job adds the days after the last day it summarised, and it has never logged an error. What is going on, how do you prove it, and how do you fix the job?
- What it tests
- Marks on a business date against marks on changes, late and updated rows, idempotent rebuilds of changed groups, comparison queries, and the gap between a row's timestamp and its commit.
- First step
- Compare the summary with the orders day by day, with a FULL JOIN so that a day missing on either side shows up. For each day that differs, look at the orders behind the difference: when they were entered or last changed, against when the job last read that day.
- Follow-ups
-
- Some orders are cancelled days later. Does your fix catch them?
- How do you repair the past year of the summary?
- The orders table has 50 million rows. What does your check cost, and how often do you run it?
From the lesson Materialized views and summary tables
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Senior to staff
A MySQL 8.4 shop has 50 million orders. A dashboard that 200 staff keep open, polling every 10 seconds, shows today's and this month's sales per city, and it may be at most a minute old. Design the summary: the table, how it stays up to date, and what you would measure.
- What it tests
- Designing a summary without materialized views, choosing between a scheduled rebuild of changed groups and triggers, hot rows under write load, the cost of polling, and checking the summary against its source.
- First step
- Estimate both sides first: 200 staff every 10 seconds is 20 reads a second of the same small result, against the rate at which orders are written. Then choose the grain of the summary, city and day, how it catches up within a minute, and how you will check it against the orders.
- Follow-ups
-
- Why not keep the summary current with triggers on orders?
- How do you fill the summary the first time without stopping the shop?
- Finance wants exact month-end figures from the same summary. What changes?
From the lesson Materialized views and summary tables
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes
-
Mid to senior
A PostgreSQL function rate_for(category) reads a gst_rates table and is marked IMMUTABLE so that an expression index can use it. After the electronics rate changes, a search by rate still returns electronics items at the old rate, and nothing reports an error. Explain what happened, and fix it, including the results already affected.
- What it tests
- What IMMUTABLE promises, why index expressions require it, how a false label corrupts results without an error, and keeping changing data out of functions that indexes use.
- First step
- Run the same search with index scans and bitmap scans switched off for the session, and compare. If the answers differ, the index holds values computed under the old rate. Then ask what the function's result depends on besides its argument: here, a row of another table.
- Follow-ups
-
- How do you find other functions whose labels may be wrong?
- The team still wants fast searches by tax rate. What do you build instead?
- Would labelling it STABLE have been enough?
From the lesson Stored functions and procedures
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Senior to staff
Checkout runs nine SQL statements from an application server in one region against a database 30 ms away in another. During a sale, orders for one popular product queue up although the database is nearly idle. A colleague proposes moving checkout into a stored procedure. Argue for and against with numbers, and decide.
- What it tests
- Round trips and lock hold time, the throughput limit of one hot row, the costs of code in the database, and other ways to shorten a transaction.
- First step
- Work out how long the stock row stays locked for each order: from the statement that locks it to the COMMIT, how many round trips pass? At 30 ms each, that time caps the orders a second for one product. Then compare it with the same steps inside one CALL.
- Follow-ups
-
- What else could shorten the lock without a procedure?
- How would you deploy, version and test the procedure?
- What happens to an order if the connection drops during the CALL?
From the lesson Stored functions and procedures
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Mid to senior
A Python import job registers gst_paise on its SQLite connection and creates an index on gst_paise(amount_paise, rate_bp). Since then the team's reporting tool and the sqlite3 shell can read the orders table but fail on every INSERT, UPDATE and DELETE. Why, and what are your options?
- What it tests
- Application-defined functions living in a connection rather than the file, indexes that store function results, the deterministic flag, and designing a file for every program that opens it.
- First step
- Read the error, "unknown function: gst_paise()". Every write must update the index on gst_paise(…), and only a connection that has registered the function can compute it. List the programs that open the file, and which of them could register the function.
- Follow-ups
-
- Could a generated column avoid the problem?
- How do you remove the dependency from an existing file safely?
- What does deterministic=True promise, and what happens when the promise is false?
From the lesson Stored functions and procedures
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Mid to senior
A MySQL 8.4 shop audits order_items with an AFTER DELETE trigger. order_items has a foreign key to orders with ON DELETE CASCADE. When support deletes an order, its items vanish, but the audit log shows nothing. Why, and how do you make the audit complete?
- What it tests
- Which operations fire triggers in MySQL, cascaded foreign key actions, auditing at the right table, and how the other engines differ.
- First step
- Delete one item directly and one order with items, and compare the log. Then read what MySQL says about cascades: rows deleted by a foreign key action are removed by InnoDB itself, not by a DELETE statement on the child table, so the child's triggers never run.
- Follow-ups
-
- Would the same audit work in PostgreSQL or SQLite?
- How do you find the deletions that were missed so far?
- Would you replace the cascade, the trigger, or both?
From the lesson Triggers: automatic actions on change
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Senior to staff
A nightly job loads 2 million rows into a PostgreSQL table that has a row-level AFTER INSERT audit trigger and a row-level trigger that updates a stock table. The load now takes 40 minutes, against 6 without the triggers, and finishes after the morning shift starts. What do you change, and what must stay true?
- What it tests
- The per-row cost of triggers, statement-level triggers with transition tables, doing the trigger's work in bulk, disabling triggers safely, and keeping audit and stock correct.
- First step
- Separate the two costs: time the load with each trigger alone. Then ask what each trigger guarantees, an audit row per inserted row and stock that matches the lines, and whether the job can provide the same result in a few set statements instead of 2 million trigger calls.
- Follow-ups
-
- What does a statement-level trigger with a transition table change?
- Is ALTER TABLE ... DISABLE TRIGGER ALL safe here?
- How do you stop online writes from slipping past while the triggers are off?
From the lesson Triggers: automatic actions on change
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
-
Mid to senior
An SQLite app logs removed prices with an AFTER DELETE trigger on prices. A sync job writes prices with INSERT OR REPLACE, and old prices disappear without a log row, although the log works for ordinary deletes. Explain, and fix the job or the trigger.
- What it tests
- How REPLACE resolves a conflict by deleting, SQLite's recursive_triggers setting, upserts against REPLACE, and auditing changes as well as deletions.
- First step
- Recall what REPLACE does on a primary key conflict: it deletes the old row and inserts the new one. Then check when SQLite fires delete triggers for such deletions, which depends on one PRAGMA that is off by default.
- Follow-ups
-
- What else does PRAGMA recursive_triggers = ON change?
- Why is an upsert the better fix for the sync job?
- How do you audit price changes as well as deletions?
From the lesson Triggers: automatic actions on change
Model answer, Pro.In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
All SQL (PostgreSQL, MySQL, SQLite) interview questions · The SQL (PostgreSQL, MySQL, SQLite) track