Comprehensive Database Concepts
1. Views in SQL
A view in SQL is a virtual table that does not store data itself but displays data stored in other tables. It is
defined by a SQL query and can represent a subset of data or a join of multiple tables.
Advantages:
- Provides abstraction for complex queries
- Helps enforce security by exposing only specific data
- Allows reuse of SQL logic
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2 FROM table_name WHERE condition;
Example:
CREATE VIEW ActiveEmployees AS
SELECT EmployeeID, Name FROM Employees WHERE Status = 'Active';
2. Views vs Tables
| Feature | Views | Tables |
|-----------------|-------------------------------------------|--------------------------------------------|
| Storage | No physical data; based on SQL query | Stores actual data in rows and columns |
| Modification | Limited; depends on definition | Full INSERT/UPDATE/DELETE support |
| Structure | Defined by SELECT statement | Schema-defined, fixed structure |
| Use Cases | Data abstraction, query simplification | Primary storage of application data |
3. Multiple Subqueries
Subqueries are SQL queries embedded within another query to help break down complex logic.
They can be nested multiple levels deep.
Comprehensive Database Concepts
Example: Employees in the same department as 'John':
SELECT Name FROM Employees WHERE DepartmentID IN (
SELECT DepartmentID FROM Employees WHERE Name = 'John'
);
Deep nesting example:
SELECT Name FROM Employees WHERE DepartmentID IN (
SELECT DepartmentID FROM Departments WHERE LocationID IN (
SELECT LocationID FROM Locations WHERE City = 'New York'
);
4. Triggers in Databases
A trigger is a stored procedure that runs automatically in response to events like INSERT, UPDATE, or
DELETE on a table or view.
Types:
- BEFORE Trigger: Executes before the operation
- AFTER Trigger: Executes after the operation
- INSTEAD OF Trigger: Replaces an operation (commonly used with views)
Use Cases:
- Maintain audit logs
- Enforce business rules
- Automatically validate or modify data
Example:
CREATE TRIGGER after_book_insert
AFTER INSERT ON Books
Comprehensive Database Concepts
FOR EACH ROW
BEGIN
INSERT INTO BookLog (BookID, Title) VALUES (NEW.BookID, NEW.Title);
END;
5. Recursion in Databases
Recursive queries are used to handle hierarchical or self-referencing relationships.
Employee hierarchy example using CTE:
WITH RECURSIVE Subordinates AS (
SELECT EmployeeID, Name, ManagerID FROM Employees WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.Name, e.ManagerID FROM Employees e
INNER JOIN Subordinates s ON e.ManagerID = s.EmployeeID
SELECT * FROM Subordinates;
Category tree example:
WITH RECURSIVE CategoryTree AS (
SELECT CategoryID, CategoryName, ParentCategoryID FROM Categories WHERE ParentCategoryID IS
NULL
UNION ALL
SELECT c.CategoryID, c.CategoryName, c.ParentCategoryID FROM Categories c
JOIN CategoryTree ct ON c.ParentCategoryID = ct.CategoryID
SELECT * FROM CategoryTree;
6. SQL Statements - Basic Commands
SQL allows interaction with databases using various commands:
Comprehensive Database Concepts
- SELECT: Fetch data from tables
- INSERT: Add new records
- UPDATE: Modify existing data
- DELETE: Remove records
- CREATE: Define new tables or databases
- DROP: Remove objects like tables or views
7. SQL Command Categories
SQL commands are categorized as:
DDL - Data Definition Language:
- CREATE, ALTER, DROP, TRUNCATE
DML - Data Manipulation Language:
- SELECT, INSERT, UPDATE, DELETE
DCL - Data Control Language:
- GRANT, REVOKE
TCL - Transaction Control Language:
- COMMIT, ROLLBACK, SAVEPOINT
8. Joins in SQL
Joins retrieve data from multiple tables using relationships.
INNER JOIN: Only matching rows in both tables.
LEFT JOIN: All rows from left table + matched rows from right.
RIGHT JOIN: All rows from right table + matched rows from left.
FULL OUTER JOIN: All rows from both, NULLs for unmatched.
Comprehensive Database Concepts
Syntax Example:
SELECT e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;
