🔹 1.
View
A virtual table based on a SQL query. It does not store data, only the query definition.
Why:
Simplify complex queries
Provide security by restricting access to certain columns/tables 3. Reuse query logic
When Used:
When hiding sensitive data 2. When frequently using the same complex query
When creating customized data views
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;
🔹 2. Stored Procedure
Definition:
A precompiled set of SQL statements (with optional parameters) stored in the database.
Why:
Improve performance (compiled once, executed many times)
Modularize and reuse logic
Centralize business rules in the database
When Used:
When the same logic needs to be executed multiple times
When you want to optimize performance
When embedding business logic inside the database
Syntax:
CREATE PROCEDURE proc_name (@param datatype)
AS
BEGIN
-- SQL statements
END;
🔹 3. Derived Table
Definition:
A subquery used in the FROM clause that acts like a temporary table.
Why:
Simplify queries by using intermediate results
Avoid creating a permanent table for one-time use
When Used:
When results are needed temporarily inside a query
When aggregating/filtering data before final SELECT
Syntax:
SELECT *
FROM (
SELECT column1, column2
FROM table_name
WHERE condition
) AS temp;
🔹 4. Trigger
Definition:
A special stored procedure that automatically executes when an event occurs (INSERT, UPDATE,
DELETE).
Why:
Automate tasks
Enforce business rules
Maintain data integrity
Provide auditing (logs)
When Used:
When auto-updating another table on data change
When logging changes in audit tables
When enforcing referential integrity
Syntax:
CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
-- SQL statements
END;
5. CTE (Common Table Expression)
Definition:
A temporary named result set defined within the execution scope of a single SELECT, INSERT,
UPDATE, or DELETE statement.
Why:
Improves readability of complex queries
Breaks down queries into logical parts
Allows recursion (hierarchical queries)
When Used:
When working with hierarchical/recursive data
When simplifying subqueries
When reusing a temporary result multiple times in the same query
Syntax:
WITH CTE_name (col1, col2) AS (
SELECT column1, column2
FROM table name _
WHERE condition
)
SELECT *
FROM CTE_name;
🔹 6. Set Operations
Operations that combine the results of two or more SELECT queries.
Why:
To merge or compare results from multiple queries
To eliminate duplicates or include all results
When Used:
When combining results of similar structured queries
When finding differences or common values between queries
Types & Syntax:
-- UNION: combine and remove duplicates
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
-- UNION ALL: combine with duplicates
SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;
-- INTERSECT: common rows
SELECT column1 FROM table1
INTERSECT
SELECT column1 FROM table2;
-- EXCEPT / MINUS: rows in first but not in second
SELECT column1 FROM table1
EXCEPT
SELECT column1 FROM table2;
🔹 7. CASE Statement
Definition:
A conditional expression in SQL that works like IF-ELSE logic.
Why:
To apply conditional logic inside queries
To derive new columns or classifications
When Used:
When categorizing data (e.g., pass/fail, grade levels)
When applying conditional aggregations
Syntax:
SELECT column1,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END AS alias_name
FROM table_name;
🔹 8. Joins
Definition:
A way to combine rows from two or more tables based on a related column.
Why:
To retrieve data spread across multiple tables
To maintain normalized databases but still get combined results
When Used:
When working with relational data
When fetching dependent data across multiple entities
Types & Syntax:
-- INNER JOIN: only matching rows
SELECT a.col1, b.col2
FROM tableA a
INNER JOIN tableB b ON [Link] = [Link];
-- LEFT JOIN: all rows from left, matching from right
SELECT a.col1, b.col2
FROM tableA a
LEFT JOIN tableB b ON [Link] = [Link];
-- RIGHT JOIN: all rows from right, matching from left
SELECT a.col1, b.col2
FROM tableA a
RIGHT JOIN tableB b ON [Link] = [Link];
-- FULL OUTER JOIN: all rows from both, matching where possible
SELECT a.col1, b.col2
FROM tableA a
FULL OUTER JOIN tableB b ON [Link] = [Link];
-- CROSS JOIN: Cartesian product
SELECT a.col1, b.col2
FROM tableA a
CROSS JOIN tableB b;