1️⃣ Constraints in SQL

Constraints are rules applied to table columns to maintain data accuracy and integrity.

• PRIMARY KEY – Uniquely identifies each row in a table
• FOREIGN KEY – Establishes a relationship with another table
• UNIQUE – Ensures all column values are different
• NOT NULL – Prevents empty (NULL) values
• CHECK – Validates data before insertion or update

2️⃣ SQL Views

Views are virtual tables created using SQL queries. They simplify complex queries and improve security.

CREATE VIEW top_students AS
SELECT name, marks
FROM students
WHERE marks > 90;

3️⃣ Indexing

Indexes significantly improve query performance by allowing faster data retrieval.

CREATE INDEX idx_name
ON employees(name);

4️⃣ SQL Transactions

Transactions ensure that multiple operations are executed safely and maintain database consistency.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

5️⃣ Triggers

Triggers automatically execute SQL code in response to database events like INSERT, UPDATE, or DELETE.

CREATE TRIGGER log_update
AFTER UPDATE ON employees
FOR EACH ROW
INSERT INTO logs(action)
VALUES ('Employee updated');

6️⃣ Stored Procedures

Stored procedures are reusable SQL blocks that improve performance and reduce redundancy.

CREATE PROCEDURE getTopStudents()
BEGIN
  SELECT *
  FROM students
  WHERE marks > 90;
END;

7️⃣ Common Table Expressions (CTEs)

CTEs are temporary result sets that make complex queries easier to read and maintain.

WITH dept_count AS (
  SELECT department, COUNT(*) AS total
  FROM employees
  GROUP BY department
)
SELECT *
FROM dept_count;