SQL

Introduction to SQL

SQL (Structured Query Language) is the standard language for working with relational databases. It enables defining schemas, querying data, and enforcing data integrity.

Relational databases organize data into tables with keys and relationships, enabling consistent querying and transaction safety.

SQL is widely used for web applications, data storage, and enterprise solutions. Learning SQL helps in managing databases efficiently.

SQL Basics

Database & Tables

-- Create Database
CREATE DATABASE School;

-- Use Database
USE School;

-- Create Table
CREATE TABLE Students (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(50),
    Age INT,
    Grade VARCHAR(10)
);

Use appropriate data types and constraints (PRIMARY KEY, UNIQUE, NOT NULL) to ensure data quality.

Basic Operations

-- Insert Data
INSERT INTO Students (Name, Age, Grade) VALUES ('John', 15, '10th');

-- Select Data
SELECT * FROM Students;

-- Update Data
UPDATE Students SET Age = 16 WHERE Name = 'John';

-- Delete Data
DELETE FROM Students WHERE Name = 'John';

Always use WHERE clauses with UPDATE/DELETE to avoid accidental full-table changes.

SQL Queries

Filtering Data

SELECT * FROM Students WHERE Age > 15;
SELECT * FROM Students WHERE Grade = '10th';

Use WHERE with AND/OR, IN, BETWEEN, and LIKE to refine results efficiently.

Sorting Data

SELECT * FROM Students ORDER BY Name ASC;
SELECT * FROM Students ORDER BY Age DESC;

Sort multiple columns to ensure stable ordering when values tie.

Aggregations

SELECT COUNT(*) FROM Students;
SELECT AVG(Age) FROM Students;
SELECT SUM(Age) FROM Students;
SELECT MAX(Age), MIN(Age) FROM Students;

Combine aggregations with GROUP BY to summarize by category.

Joins in SQL

-- Inner Join
SELECT Orders.ID, Customers.Name
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.ID;

-- Left Join
SELECT Orders.ID, Customers.Name
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.ID;

-- Right Join
SELECT Orders.ID, Customers.Name
FROM Orders
RIGHT JOIN Customers ON Orders.CustomerID = Customers.ID;

-- Full Outer Join (use UNION)
SELECT Orders.ID, Customers.Name
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.ID
UNION
SELECT Orders.ID, Customers.Name
FROM Orders
RIGHT JOIN Customers ON Orders.CustomerID = Customers.ID;

Joins combine rows across tables using key relationships. Use INNER JOIN for matches, LEFT/RIGHT JOIN to preserve one side, and FULL OUTER JOIN to include all rows.

For performance, ensure join keys are indexed and avoid joining on non-sargable expressions.

Advanced SQL

Advanced SQL focuses on reusable logic, performance tuning, and transactional safety for production systems.

Views

CREATE VIEW StudentView AS
SELECT Name, Age, Grade
FROM Students
WHERE Age > 15;

SELECT * FROM StudentView;

Stored Procedures

DELIMITER $$
CREATE PROCEDURE GetStudents()
BEGIN
    SELECT * FROM Students;
END$$
DELIMITER ;

CALL GetStudents();

Triggers

CREATE TRIGGER before_student_insert
BEFORE INSERT ON Students
FOR EACH ROW
SET NEW.Grade = 'Not Assigned';

Indexes

CREATE INDEX idx_name ON Students(Name);

Indexes speed up queries but add write overhead. Use them on columns used in WHERE, JOIN, and ORDER BY.

Transactions

START TRANSACTION;
UPDATE Students SET Age=17 WHERE Name='John';
COMMIT;
-- Use ROLLBACK to revert

Transactions ensure ACID properties and protect data integrity during multi-step changes.

Important SQL Shortcuts