Views

Views are virtual tables derived from SQL queries. They don't store data physically but present filtered perspectives of base tibles.

-- Create sample view for male students
CREATE VIEW male_students AS
SELECT * FROM students WHERE gender = 'male';

-- Join view with classes table
CREATE VIEW male_student_classes AS
SELECT m.*, c.class_name 
FROM male_students m
LEFT JOIN classes c ON m.class_id = c.class_id;

-- Query view
SELECT * FROM male_student_classes;

View Management

-- List all views in schema
SELECT TABLE_NAME FROM information_schema.VIEWS 
WHERE TABLE_SCHEMA = 'school_db';

-- Drop view
DROP VIEW male_students;

Stored Procedures

Precompiled SQL routines that accept parameters and perform complex operations.

-- Basic procedure example
DELIMITER $$
CREATE PROCEDURE get_student_data()
BEGIN
    SELECT * FROM students;
END $$
DELIMITER ;

-- Parameterized procedure
DELIMITER $$
CREATE PROCEDURE process_scores(
    IN student_id INT,
    OUT total_score INT,
    INOUT adjustment INT
)
BEGIN
    SELECT SUM(score) INTO total_score 
    FROM exam_results WHERE id = student_id;
    SET adjustment = total_score + adjustment;
END $$
DELIMITER ;

Execution Control

-- Call procedure
CALL process_scores(101, @total, @adjust);

-- Page query example
DELIMITER $$
CREATE PROCEDURE paginate_records(
    IN page_num INT,
    IN page_size INT,
    OUT total_records INT,
    OUT total_pages INT
)
BEGIN
    DECLARE offset_val INT;
    SET offset_val = (page_num - 1) * page_size;
    
    SELECT COUNT(*) INTO total_records FROM students;
    SET total_pages = CEIL(total_records / page_size);
    
    SELECT * FROM students 
    LIMIT offset_val, page_size;
END $$
DELIMITER ;

Trigggers

Automated procedures that execute in response to table events.

-- Delete cascade trigger
DELIMITER $$
CREATE TRIGGER archive_deleted_students
BEFORE DELETE ON students
FOR EACH ROW
BEGIN
    INSERT INTO deleted_records (student_id, name, deletion_time)
    VALUES (OLD.id, OLD.name, NOW());
END $$
DELIMITER ;

Trigger Management

-- View existing triggers
SELECT TRIGGER_NAME FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'school_db';

-- Delete trigger
DROP TRIGGER archive_deleted_students;

Implementation Considerations

• Views simplify complex queries but have performence limitations
• Stored procedures improve performance through precompilation
• Triggers enable data integrity but require careful dependency management
• Use OUT parameters for procedure result sets
• Implement error handling in transactional operations