स्ट्रक्चर्ड क्वेरी लैंग्वेज (SQL)

Table Constraints

Rules enforced on columns to ensure data validity and referential integrity:

• `PRIMARY KEY`: Uniquely identifies each row (implies `UNIQUE` and `NOT NULL`).
• `FOREIGN KEY...REFERENCES`: Enforces referential integrity pointing to a parent table primary key.
• `NOT NULL`: Forbids column from holding missing/NULL values.
• `UNIQUE`: Ensures all column values are distinct (allows NULLs).
• `CHECK`: Validates an algebraic condition (e.g., `CHECK (Price > 0)`).
• `DEFAULT`: Assigns a fallback value when none is supplied (e.g., `DEFAULT 'Active'`).

A. Table Creation & Schema Alteration (DDL)

-- Create Parent Table:
CREATE TABLE DEPARTMENT (
    DeptID VARCHAR(5) PRIMARY KEY,
    DeptName VARCHAR(30) NOT NULL
);

-- Create Child Table with Foreign Key:
CREATE TABLE EMPLOYEE (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(40) NOT NULL,
    Salary DECIMAL(10, 2) CHECK (Salary >= 10000),
    DeptID VARCHAR(5),
    FOREIGN KEY (DeptID) REFERENCES DEPARTMENT(DeptID)
);

-- Alter Table: Adding a new column:
ALTER TABLE EMPLOYEE ADD Email VARCHAR(50);

-- Alter Table: Modifying existing column data type:
ALTER TABLE EMPLOYEE MODIFY Name VARCHAR(60);

-- Alter Table: Dropping a column:
ALTER TABLE EMPLOYEE DROP COLUMN Email;

B. Data Manipulation (DML)

-- Insert record:
INSERT INTO DEPARTMENT VALUES ('D01', 'Engineering');
INSERT INTO EMPLOYEE VALUES (101, 'Aarav Sharma', 75000.00, 'D01');

-- Update record (CRITICAL: Always use WHERE to prevent mass updates!):
UPDATE EMPLOYEE SET Salary = Salary * 1.10 WHERE DeptID = 'D01';

-- Delete record:
DELETE FROM EMPLOYEE WHERE EmpID = 101;

A. Pattern Matching with `LIKE`

• Percent (`%`): Matches zero, one, or multiple characters (e.g., `WHERE Name LIKE 'A%'` matches names starting with 'A').
• Underscore (`_`): Matches exactly one single character (e.g., `WHERE Name LIKE '_a%'` matches names whose second letter is 'a').

B. Aggregate Functions & `COUNT(*)` vs `COUNT(col)`

• `COUNT(*)`: Counts the total number of rows in the table, including rows with NULL values.
• `COUNT(col)`: Counts rows where `col` is NOT NULL (ignores NULLs).
• `SUM(col)`, `AVG(col)`, `MIN(col)`, `MAX(col)`: Compute statistical aggregations, strictly ignoring NULL values.

C. `GROUP BY` vs `HAVING` (THE CRITICAL EXAM DISTINCTION)

• WHERE: Filters individual rows *before* grouping occurs. Cannot contain aggregate functions!
• GROUP BY: Combines rows sharing identical values in specified columns into summary groups.
• HAVING: Filters groups *after* aggregation has occurred. Can contain aggregate functions!

-- Find departments where average salary exceeds 50,000, considering only active staff:
SELECT DeptID, COUNT(*) AS TotalStaff, AVG(Salary) AS AvgSal
FROM EMPLOYEE
WHERE Salary > 20000          -- 1. Filters individual rows BEFORE grouping
GROUP BY DeptID              -- 2. Groups remaining rows by Department
HAVING AVG(Salary) > 50000   -- 3. Filters groups AFTER aggregation
ORDER BY AvgSal DESC;        -- 4. Sorts output descending

A JOIN query combines columns from two or more tables based on a related common attribute:

• Equi-Join: Joins tables where the join condition uses the equality operator (`=`):

  SELECT E.Name, E.Salary, D.DeptName
  FROM EMPLOYEE E, DEPARTMENT D
  WHERE E.DeptID = D.DeptID;

• Natural Join: Compares common columns with identical names in both tables automatically, eliminating duplicate join columns from the output.