Mastering SQL
PRESENTED BY
MACALIN AXMED
Presentation Objectives
• Understand the Basics of SQL • Create and Use Views
• Create and Modify Databases and • Develop and Use Stored
Tables Procedures
• Insert, Update, and Delete Data • Write and Implement Functions in
SQL
• Master Querying Data
• Implement Triggers for Data
• Understand Database Integrity
Relationships
• Understand Transactions and Data
• Perform Advanced SQL Queries Consistency
Using Joins
• Apply SQL Concepts in Real-World
Scenarios
Introduction to SQL and DBMS
SQL (Structured Query Language)
• A standardized language for managing and manipulating relational
databases.
• Used for querying (`SELECT`), inserting (`INSERT`), updating (`UPDATE`),
and deleting (`DELETE`) data.
• Works with relational database management systems (RDBMS) like MySQL,
PostgreSQL, and Oracle.
DBMS (Database Management System):
• Software that stores, manages, and retrieves data efficiently.
• Types include Relational DBMS (e.g., MySQL, SQL Server), NoSQL DBMS
(e.g., MongoDB), and others.
• Provides features like data storage, retrieval, security, integrity, and backup.
Types of SQL Commands
• DDL: Data Definition Language (CREATE, ALTER, DROP)
• DML: Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE)
• DCL: Data Control Language (GRANT, REVOKE)
• TCL: Transaction Control Language (COMMIT, ROLLBACK)
Creating/drop/use a Database
• SQL Command: CREATE DATABASE
• CREATE DATABASE School;
• USE School;
• DROP DATABASE SCHOOL;
Creating a Table
SQL Command: CREATE TABLE
CREATE TABLE Students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT CHECK (age >= 5),
email VARCHAR(100) UNIQUE,
grade VARCHAR(5) NOT NULL
);
Common Constraints
• Types of Constraints
• NOT NULL: Ensures a column cannot store NULL values.
• UNIQUE: Ensures all values in a column are distinct.
• CHECK: Ensures a column meets a specific condition.
• PRIMARY KEY: Uniquely identifies each row in a table.
• FOREIGN KEY: Ensures referential integrity between tables.
Altering Tables
• SQL Command: ALTER TABLE
• ALTER TABLE Students ADD COLUMN address VARCHAR(50);
• ALTER TABLE Students ADD CONSTRAINT chk_age CHECK (age >= 5);
Altering Tables
• SQL Command: ALTER TABLE
• ALTER TABLE Students ADD COLUMN address VARCHAR(50);
• ALTER TABLE Students ADD CONSTRAINT chk_age CHECK (age >= 5);
Inserting Data
• SQL Command: INSERT INTO
• INSERT INTO Students (name, age, email, grade)
• VALUES ('Alice', 14, 'alice@email.com', '8th');
Updating Data
• SQL Command: UPDATE
• UPDATE Students SET age = 15 WHERE name = 'Alice';
Deleting Data
• SQL Command: DELETE
• DELETE FROM Students WHERE name = 'Alice';
Selecting Data
• SQL Command: SELECT
• SELECT FROM Students;
• SELECT name, age FROM Students;
Select with WHERE Clause
• SQL Command: SELECT ... WHERE
• SELECT name, age FROM Students WHERE age > 10;
Select with ORDER BY
• SQL Command: SELECT ... ORDER BY
• SELECT name, age FROM Students ORDER BY age DESC;
Select with LIKE
• SQL Command: SELECT ... LIKE
• SELECT name FROM Students WHERE name LIKE 'A%';
Select with BETWEEN
• SQL Command: SELECT ... BETWEEN
• SELECT name FROM Students WHERE age BETWEEN 10 AND 15;
Select with TOP
• SQL Command: SELECT ... LIMIT or TOP
• SELECT FROM Students LIMIT 5;
Aggregate Functions
• SQL Command: COUNT(), AVG(), SUM()
• SELECT COUNT() FROM Students;
• SELECT AVG(age) FROM Students;
• SELECT SUM(age) FROM Students;
One-to-One Relationships
• Example: A Student has one Profile.
• CREATE TABLE StudentProfile (
• student_id INT PRIMARY KEY,
• address VARCHAR(255),
• FOREIGN KEY (student_id) REFERENCES Students(id)
• );
One-to-Many Relationships
• Example: A Teacher teaches many Students.
• CREATE TABLE Teachers (
• id INT PRIMARY KEY AUTO_INCREMENT,
• name VARCHAR(50) NOT NULL
• );
•
• CREATE TABLE Students (
• id INT PRIMARY KEY AUTO_INCREMENT,
• name VARCHAR(50) NOT NULL,
• teacher_id INT,
• FOREIGN KEY (teacher_id) REFERENCES Teachers(id)
• );
Many-to-One Relationships
• Example: Many Students belong to one Grade.
• CREATE TABLE Grades (
• id INT PRIMARY KEY AUTO_INCREMENT,
• grade_name VARCHAR(5) NOT NULL
• );
•
• ALTER TABLE Students ADD COLUMN grade_id INT;
• ALTER TABLE Students ADD FOREIGN KEY (grade_id) REFERENCES
Grades(id);
Many-to-Many Relationships
• Example: Many Students enroll in many Courses.
• CREATE TABLE Courses (
• id INT PRIMARY KEY AUTO_INCREMENT,
• course_name VARCHAR(100) NOT NULL
• );
•
• CREATE TABLE StudentCourses (
• student_id INT,
• course_id INT,
• PRIMARY KEY (student_id, course_id),
• FOREIGN KEY (student_id) REFERENCES Students(id),
• FOREIGN KEY (course_id) REFERENCES Courses(id)
• );
Inner Join
• Example: Matching records from both tables.
• SELECT Students.name, Courses.course_name
• FROM Students
• INNER JOIN StudentCourses ON Students.id =
StudentCourses.student_id
• INNER JOIN Courses ON StudentCourses.course_id = Courses.id;
Left Join
• Example: All students, even without courses.
• SELECT Students.name, Courses.course_name
• FROM Students
• LEFT JOIN StudentCourses ON Students.id =
StudentCourses.student_id
• LEFT JOIN Courses ON StudentCourses.course_id = Courses.id;
Right Join
• Example: All courses, even without students.
• SELECT Students.name, Courses.course_name
• FROM Students
• RIGHT JOIN StudentCourses ON Students.id =
StudentCourses.student_id
• RIGHT JOIN Courses ON StudentCourses.course_id = Courses.id;
Full Outer Join
• Example: Returns all students and all courses.
• SELECT Students.name, Courses.course_name
• FROM Students
• LEFT JOIN StudentCourses ON Students.id = StudentCourses.student_id
• LEFT JOIN Courses ON StudentCourses.course_id = Courses.id
• UNION
• SELECT Students.name, Courses.course_name
• FROM Students
• RIGHT JOIN StudentCourses ON Students.id = StudentCourses.student_id
• RIGHT JOIN Courses ON StudentCourses.course_id = Courses.id;
Creating a View
• SQL Command: CREATE VIEW
• CREATE VIEW TeenStudents AS
• SELECT FROM Students WHERE age >= 13;
• SELECT FROM TeenStudents;
Creating a Simple Stored
Procedure
• Definition : A stored procedure is a set of SQL statements that can
be executed as a unit
• SQL Command: CREATE PROCEDURE
• DELIMITER //
• CREATE PROCEDURE GetStudentsByGrade(IN gradeLevel VARCHAR(5))
• BEGIN
• SELECT FROM Students WHERE grade = gradeLevel;
• END //
• DELIMITER ;
Stored Procedure with Output
Parameters
• Example: Returning student count.
• DELIMITER //
• CREATE PROCEDURE GetStudentCountByGrade(IN gradeLevel
VARCHAR(5), OUT studentCount INT)
• BEGIN
• SELECT COUNT() INTO studentCount FROM Students WHERE grade
= gradeLevel;
• END //
• DELIMITER ;
Calling a Stored Procedure
• Usage: Calling a stored procedure and fetching results.
• CALL GetStudentCountByGrade('8th', @count);
• SELECT @count;
Stored Procedure with
Multiple Queries
• Example: Retrieving student details and their grade.
• DELIMITER //
• CREATE PROCEDURE GetStudentDetails(IN studentID INT)
• BEGIN
• SELECT FROM Students WHERE id = studentID;
• SELECT FROM Grades WHERE student_id = studentID;
• END //
• DELIMITER ;
Creating a Function
• Definition : Functions in SQL return a single value and are used for
calculations.
• Example: Calculate student age.
• DELIMITER //
• CREATE FUNCTION CalculateAge(birthdate DATE) RETURNS INT
DETERMINISTIC
• BEGIN
• RETURN TIMESTAMPDIFF(YEAR, birthdate, CURDATE());
• END //
• DELIMITER ;
Using Functions
• SQL Command: Using CalculateAge function.
• SELECT CalculateAge('2005-04-20');
Function with Conditional
Logic
• Example: Get student grade based on age.
• DELIMITER //
• CREATE FUNCTION GetGradeByAge(age INT) RETURNS VARCHAR(5)
• BEGIN
• IF age >= 15 THEN
• RETURN 'High School';
• ELSE
• RETURN 'Middle School';
• END IF;
• END //
• DELIMITER ;
Advanced Function Example
• Example: Calculate total fee based on number of courses.
• DELIMITER //
• CREATE FUNCTION CalculateFee(courseCount INT) RETURNS
DECIMAL(10,2)
• BEGIN
• RETURN courseCount 100;
• END //
• DELIMITER ;
• Introduction to Triggers
Creating a BEFORE INSERT
Trigger
• Definition : Triggers are automatic actions executed when certain events occur in a
database.
• SQL Command: Prevent inserting students under age 5.
• DELIMITER //
• CREATE TRIGGER before_student_insert
• BEFORE INSERT ON Students
• FOR EACH ROW
• BEGIN
• IF NEW.age < 5 THEN
• SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age must be at least 5';
• END IF;
• END //
• DELIMITER ;
Creating a BEFORE UPDATE
Trigger
• SQL Command: Prevent grade downgrade.
• DELIMITER //
• CREATE TRIGGER prevent_grade_downgrade
• BEFORE UPDATE ON Students
• FOR EACH ROW
• BEGIN
• IF NEW.grade < OLD.grade THEN
• SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Grade downgrade not
allowed';
• END IF;
• END //
• DELIMITER ;
AFTER INSERT Trigger
• Example: Logging student insertions.
• DELIMITER //
• CREATE TRIGGER after_student_insert
• AFTER INSERT ON Students FOR EACH ROW BEGIN INSERT INTO
StudentLog (student_id, action) VALUES (NEW.id, 'Student Inserted');
END // DELIMITER ;
AFTER UPDATE Trigger
• Example: Log student grade change.
• DELIMITER //
• CREATE TRIGGER after_grade_update
• AFTER UPDATE ON Students
• FOR EACH ROW
• BEGIN
• IF NEW.grade != OLD.grade THEN
• INSERT INTO GradeChangeLog (student_id, old_grade, new_grade, timestamp)
• VALUES (NEW.id, OLD.grade, NEW.grade, NOW());
• END IF;
• END //
• DELIMITER ;
BEFORE DELETE Trigger
• Example: Prevent deletion of a student under 18.
• DELIMITER //
• CREATE TRIGGER before_student_delete
• BEFORE DELETE ON Students
• FOR EACH ROW
• BEGIN
• IF OLD.age < 18 THEN
• SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete student
under 18';
• END IF;
• END //
• DELIMITER ;
Trigger Example for Audit
• Example: Track changes in students' grades.
• DELIMITER //
• CREATE TRIGGER audit_student_changes
• AFTER UPDATE ON Students
• FOR EACH ROW
• BEGIN
• INSERT INTO AuditLog (student_id, action, old_value, new_value,
timestamp)
• VALUES (NEW.id, 'Grade Change', OLD.grade, NEW.grade, NOW());
• END //
• DELIMITER ;
TRANSACTIONS IN SQL
• Transactions ensure data integrity by grouping multiple SQL
operations into a single logical unit. They follow the ACID properties:
• Atomicity: All operations succeed or none do.
• Consistency: The database remains valid before and after the
transaction.
• Isolation: Concurrent transactions don’t interfere.
• Durability: Changes are permanent once committed.
Example: Simple Transaction
• START TRANSACTION;
• UPDATE Students SET age = age + 1 WHERE grade = '8th';
• IF (SELECT COUNT() FROM Students WHERE grade = '8th') > 10 THEN
• COMMIT;
• ELSE
• ROLLBACK;
• END IF;
Example: Bank Transfer (Transaction)
• START TRANSACTION;
• UPDATE Accounts SET balance = balance - 500 WHERE account_id =
1;
• UPDATE Accounts SET balance = balance + 500 WHERE account_id =
2;
• IF ROW_COUNT() = 0 THEN
• ROLLBACK;
• ELSE
• COMMIT;
• END IF;
