Views, Procedures & Triggers

1. How do you find and remove duplicate records?

First, group the data and count to find the duplicates. Then delete all but one copy of each duplicate using ROW_NUMBER().

Step 1 — find the duplicates: Group by the column that should be unique and count how many times each value appears.

Users table:

id	email
1	ali@mail.com
2	bob@mail.com
3	ali@mail.com

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Result:

email	COUNT(*)
ali@mail.com	2

This tells you ali@mail.com appears twice.

Step 2 — remove the duplicates, keeping one copy: ROW_NUMBER() assigns a number to each row inside a group. Row 1 of each group is kept; the rest are deleted.

WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

After this, only id 1 (ali@mail.com) remains; id 3 is deleted.

Step 3 — prevent future duplicates: Add a UNIQUE constraint so duplicates can’t be inserted again.

ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

Key takeaway: Find duplicates with GROUP BY and HAVING COUNT(*) > 1. Remove them with ROW_NUMBER() and a CTE. Then add a UNIQUE constraint so they can’t come back.

2. What is the difference between a standard View and a Materialized View?

A standard view is just a saved query. A materialized view stores the query’s result physically.

Standard view: It’s like a saved SELECT statement.

It stores no data.

Every time you query it, the database runs the query again against the base tables.

The data is always fresh.

Materialized view: It runs the query once and stores the result on disk.

Later queries read the stored result, no re-running.

This is much faster for complex queries.

But the stored result can go stale. It only updates when refreshed.

-- Some databases
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT department_id, SUM(salary) AS total
FROM employees
GROUP BY department_id;

Key differences table:

	Standard view	Materialized view
Stores data	No	Yes
Runs query each time	Yes	No
Always fresh	Yes	No, needs refresh
Speed for complex queries	Slower	Faster
Uses storage	No	Yes

Key takeaway: Use a standard view for a simple, always-fresh saved query. Use a materialized view for heavy analytical queries where speed matters more than instant freshness.

3. What is a Stored Procedure, and how does it differ from a User-Defined Function?

A stored procedure is a saved block of SQL that can run complex logic and modify data. A user-defined function is designed to return a single value or table and is used inside queries.

Stored procedure: A named group of SQL statements saved in the database.

It can run INSERT, UPDATE, and DELETE.

It can return multiple values and result sets.

It’s called with EXEC or CALL.

CREATE PROCEDURE update_salary(IN emp_id INT, IN new_salary DECIMAL)
BEGIN
  UPDATE employees SET salary = new_salary WHERE employee_id = emp_id;
END;

User-defined function: Designed to compute and return a value.

It’s used inside a SELECT, like a built-in function.

It usually can’t modify data.

CREATE FUNCTION full_name(first VARCHAR(50), last VARCHAR(50))
RETURNS VARCHAR(100)
RETURN CONCAT(first, ' ', last);

Used as: SELECT full_name('Ali', 'Khan');

Key differences table:

	Stored Procedure	Function
Main purpose	Run logic, modify data	Return a value
Can use INSERT/UPDATE/DELETE	Yes	Usually no
Return type	Multiple values/result sets	Single value/table
Used inside SELECT	No	Yes
Called with	CALL / EXEC	As part of an expression

Key takeaway: Procedures do the work; functions return values. If you need to modify data or run steps, use a procedure. If you need a reusable value inside a query, use a function.

4. What is a View in SQL?

A view is a virtual table based on the result of a SELECT statement. It stores no data itself.

The idea: A view is a saved query you can treat like a table.

It doesn’t hold data.

Every time you query it, it runs the underlying SELECT.

Example:

CREATE VIEW it_employees AS
SELECT employee_id, name
FROM employees
WHERE department_id = 5;

Now you can query it like a table:

SELECT * FROM it_employees;

Why use views:

• Simplify complex queries — write the join once, reuse it.
• Restrict access — show only some columns or rows.
• Hide complexity from other users.

Key takeaway: A view is a named, saved query that acts like a table. It always shows fresh data because it runs the query each time.

5. What is a Trigger?

A trigger is a database object that automatically runs code when an INSERT, UPDATE, or DELETE happens.

The idea: You attach code to a table event.

When the event fires, the code runs automatically.

No one has to call it.

Example — log every delete:

CREATE TRIGGER log_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (employee_id, action) VALUES (OLD.employee_id, 'deleted');
END;

Now every delete also writes an audit entry.

Common uses:

• Enforce business rules.
• Audit changes automatically.
• Keep summary tables up to date.

Key takeaway: A trigger runs automatically on table events. It’s good for audit logs and rules — but use sparingly, since hidden code can surprise you.