Table Integrity Constraints

Enforce data validity through constraint definitions:

Unique Constraints Prevent duplicate values in specific columns:

ALTER TABLE personnel 
ADD CONSTRAINT uk_email UNIQUE (email_address);

Check Constraints Validate data against boolean expressions:

ALTER TABLE personnel 
ADD CONSTRAINT chk_status 
CHECK (employment_status IN ('Active', 'On Leave', 'Terminated'));

Referential Integrity Establish relationships between tables:

ALTER TABLE enrollment 
ADD CONSTRAINT fk_module 
FOREIGN KEY (course_id) 
REFERENCES training(course_code);

Schema Object Modifications

Standard ALTER TABLE has limitations for renaming operations. Use system stored procedures instead:

Renaming Tables

EXEC sp_rename 'legacy_employees', 'current_staff';

Renaming Columns

EXEC sp_rename 'current_staff.emp_number', 'staff_id', 'COLUMN';

Renaming Indexes

EXEC sp_rename 'current_staff.IX_legacy_email', 'idx_email_unique', 'INDEX';

Note: Renaming objects may break dependent scripts, views, or stored procedures.

Table Removal

Permanently delete tables and dependent objects:

DROP TABLE table_name [RESTRICT | CASCADE];

• RESTRICT: Prevents deletion if foreign keys or views reference the table
• CASCADE: Removes the table and all dependent objects smiultaneously

Index Operations

Indexes optimize query performance on large datasets.

Creating Indexes

CREATE [UNIQUE] [CLUSTERED] INDEX index_name 
ON table_name(column [ASC|DESC], ...);

Clustered Indexes Physical ordering of table data. Only one allowed per table:

CREATE CLUSTERED INDEX idx_division 
ON personnel(department_code);

Unique Indexes Enforce uniqueness while providing fast lookup:

CREATE UNIQUE INDEX idx_staff_id ON personnel(staff_id);
CREATE UNIQUE INDEX idx_course ON training(course_code);
CREATE UNIQUE INDEX idx_enrollment ON enrollment(staff_id ASC, course_code DESC);

Removing Indexes

DROP INDEX idx_division;

Renaming Indexes

ALTER INDEX idx_old_name RENAME TO idx_new_name;

Querying Data

Basic Selection

Retrieve specific columns or all attributes:

SELECT staff_id, full_name 
FROM personnel 
ORDER BY staff_id;

SELECT * FROM personnel;

SELECT full_name, 2024 - years_employed AS birth_year 
FROM personnel;

SELECT full_name, 
       'Department:' AS label, 
       LOWER(department_code) AS division 
FROM personnel;

Eliminating Duplicates

SELECT DISTINCT staff_id FROM enrollment;

Filtering Results

Comparison Operators

SELECT full_name 
FROM personnel 
WHERE department_code = 'Engineering';

SELECT full_name, years_employed 
FROM personnel 
WHERE years_employed < 5;

SELECT DISTINCT staff_id 
FROM enrollment 
WHERE score < 60;

Range Selection

SELECT full_name, department_code, years_employed
FROM personnel
WHERE years_employed BETWEEN 3 AND 7;

Set Membership

SELECT full_name, status
FROM personnel
WHERE department_code IN ('Sales', 'Marketing', 'Engineering');

SELECT full_name, status
FROM personnel
WHERE department_code NOT IN ('HR', 'Finance');

Pattern Matching

-- Exact match equivalent
SELECT * FROM personnel WHERE staff_id LIKE 'EMP2024';

-- Wildcard searches
SELECT full_name, staff_id, status
FROM personnel
WHERE full_name LIKE 'Smith%';

-- Single character wildcard
SELECT full_name
FROM personnel
WHERE full_name LIKE 'G__';

-- Escape sequences for special characters
SELECT course_code, credits
FROM training
WHERE course_name LIKE 'DB\_Design%' ESCAPE '\';

Null Value Handling

SELECT staff_id, course_code
FROM enrollment
WHERE score IS NULL;

SELECT staff_id, course_code
FROM enrollment
WHERE score IS NOT NULL;

Compound Conditions

SELECT full_name
FROM personnel
WHERE department_code = 'Engineering' AND years_employed < 2;

Sorting Results

SELECT staff_id, score
FROM enrollment
WHERE course_code = 'CS101'
ORDER BY score DESC;

SELECT *
FROM personnel
ORDER BY department_code ASC, years_employed DESC;

Null handling in sorts: Ascending places NULLs last; descending places NULLs first.

Aggregation Functions

-- Count all rows
SELECT COUNT(*) AS total_employees FROM personnel;

-- Count distinct values
SELECT COUNT(DISTINCT staff_id) FROM enrollment;

-- Average, max, sum
SELECT AVG(score) FROM enrollment WHERE course_code = 'CS101';
SELECT MAX(score) FROM enrollment WHERE course_code = 'CS101';
SELECT SUM(credits) 
FROM enrollment e 
JOIN training t ON e.course_code = t.course_code 
WHERE e.staff_id = 'EMP001';

Grouping Data

-- Count per course
SELECT course_code, COUNT(staff_id) AS attendees
FROM enrollment
GROUP BY course_code;

-- Filter groups with HAVING
SELECT staff_id
FROM enrollment
GROUP BY staff_id
HAVING COUNT(*) >= 3;

WHERE vs HAVING: WHERE filters rows before grouping; HAVING filters groups after aggregation.

Multi-Table Operations

Joins

Equi-joins

SELECT p.*, e.*
FROM personnel p
JOIN enrollment e ON p.staff_id = e.staff_id;

Self-joins

-- Finding prerequisite chains
SELECT c1.course_code, c1.course_name, c2.prerequisite_code
FROM training c1
JOIN training c2 ON c1.prerequisite_code = c2.course_code;

-- Finding colleagues in same department
SELECT p2.staff_id, p2.full_name
FROM personnel p1
JOIN personnel p2 ON p1.department_code = p2.department_code
WHERE p1.full_name = 'Alice Johnson';

Subqueries

Non-correlated (Independent)

SELECT staff_id, full_name, department_code
FROM personnel
WHERE department_code IN (
    SELECT department_code 
    FROM personnel 
    WHERE full_name = 'Alice Johnson'
);

Correlated (Dependent)

-- Courses where employee exceeded their own average
SELECT e1.staff_id, e1.course_code
FROM enrollment e1
WHERE e1.score >= (
    SELECT AVG(e2.score)
    FROM enrollment e2
    WHERE e2.staff_id = e1.staff_id
);

Quantified Comparisons

-- Employees not enrolled in specific course
SELECT staff_id, full_name
FROM personnel
WHERE staff_id <> ALL (
    SELECT staff_id 
    FROM enrollment 
    WHERE course_code = 'CS101'
);

-- Highest average score
SELECT staff_id
FROM enrollment
GROUP BY staff_id
HAVING AVG(score) >= ALL (
    SELECT AVG(score)
    FROM enrollment
    GROUP BY staff_id
);

Data Modification

Inserting Records

INSERT INTO personnel 
VALUES ('EMP001', 'Alice Johnson', 'Active', 5, 'Engineering');

INSERT INTO training 
VALUES ('CS101', 'Database Systems', 'CS100', 3);

Updating Records

UPDATE personnel 
SET full_name = 'Alice Smith'
WHERE staff_id = 'EMP001';

UPDATE personnel 
SET years_employed = years_employed + 1
WHERE staff_id = 'EMP001';

Sample Schema Setup

CREATE DATABASE company_training;

CREATE TABLE personnel (
    staff_id CHAR(8) PRIMARY KEY,
    full_name VARCHAR(50) UNIQUE,
    employment_status CHAR(10),
    years_employed INT,
    department_code CHAR(20)
);

CREATE TABLE training (
    course_code CHAR(6) PRIMARY KEY,
    course_name VARCHAR(60),
    prerequisite_code CHAR(6),
    credits INT,
    FOREIGN KEY (prerequisite_code) REFERENCES training(course_code)
);

CREATE TABLE enrollment (
    staff_id CHAR(8),
    course_code CHAR(6),
    score INT,
    PRIMARY KEY (staff_id, course_code),
    FOREIGN KEY (staff_id) REFERENCES personnel(staff_id),
    FOREIGN KEY (course_code) REFERENCES training(course_code)
);