Welcome to SQL

SQL (Structured Query Language) is used to communicate with databases. It helps you store, retrieve, and manipulate data efficiently.

-- Example: Creating a simple database
CREATE DATABASE School;
    

Tables store data in rows and columns. Each column has a specific data type like INT, VARCHAR, DATE, etc.

-- Create a table
CREATE TABLE Students (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    EnrollmentDate DATE
);
    

You can insert data into tables using the INSERT INTO statement.

-- Insert a record
INSERT INTO Students (ID, Name, Age, EnrollmentDate)
VALUES (1, 'Alice', 20, '2026-04-05');
    

The SELECT statement retrieves data from one or more tables.

-- Get all students
SELECT * FROM Students;

-- Get specific columns
SELECT Name, Age FROM Students;
    

Use WHERE to filter data based on conditions.

-- Get students older than 18
SELECT * FROM Students
WHERE Age > 18;

-- Get student named Alice
SELECT * FROM Students
WHERE Name = 'Alice';
    

Use ORDER BY to sort results in ascending (ASC) or descending (DESC) order.

-- Get students sorted by age ascending
SELECT * FROM Students
ORDER BY Age ASC;

-- Get students sorted by name descending
SELECT * FROM Students
ORDER BY Name DESC;
    

Use UPDATE to modify existing records in a table.

-- Update Alice's age
UPDATE Students
SET Age = 21
WHERE Name = 'Alice';
    

Use DELETE to remove records from a table.

-- Delete student named Alice
DELETE FROM Students
WHERE Name = 'Alice';
    

Use LIMIT to restrict the number of rows returned by a query.

-- Get only the first 3 students
SELECT * FROM Students
LIMIT 3;
    

DISTINCT removes duplicate values from the results.

-- Get unique ages of students
SELECT DISTINCT Age FROM Students;
    

COUNT() counts the number of rows that match a condition.

-- Count all students
SELECT COUNT(*) FROM Students;

-- Count students older than 18
SELECT COUNT(*) FROM Students
WHERE Age > 18;
    

These functions calculate averages, totals, minimum, and maximum values.

-- Average age
SELECT AVG(Age) FROM Students;

-- Sum of ages
SELECT SUM(Age) FROM Students;

-- Minimum and maximum age
SELECT MIN(Age), MAX(Age) FROM Students;
    

GROUP BY groups rows that have the same values in a column for aggregate functions.

-- Count students by age
SELECT Age, COUNT(*) 
FROM Students
GROUP BY Age;
    

HAVING filters groups after aggregation (unlike WHERE, which filters rows before grouping).

-- Count students by age, only ages with more than 1 student
SELECT Age, COUNT(*) 
FROM Students
GROUP BY Age
HAVING COUNT(*) > 1;
    

Aliases rename columns or tables in the result for readability.

-- Rename column in result
SELECT Name AS StudentName, Age AS StudentAge
FROM Students;

-- Alias a table
SELECT s.Name, s.Age
FROM Students AS s;
    

INNER JOIN combines rows from two tables where there is a match in both tables.

-- Example tables: Students and Courses
SELECT Students.Name, Courses.CourseName
FROM Students
INNER JOIN Courses
ON Students.ID = Courses.StudentID;
    

LEFT JOIN returns all rows from the left table, and matched rows from the right table. If no match, NULL is returned.

SELECT Students.Name, Courses.CourseName
FROM Students
LEFT JOIN Courses
ON Students.ID = Courses.StudentID;
    

RIGHT JOIN returns all rows from the right table, and matched rows from the left table. If no match, NULL is returned.

SELECT Students.Name, Courses.CourseName
FROM Students
RIGHT JOIN Courses
ON Students.ID = Courses.StudentID;
    

FULL OUTER JOIN returns all rows when there is a match in either left or right table. NULLs appear when there is no match.

SELECT Students.Name, Courses.CourseName
FROM Students
FULL OUTER JOIN Courses
ON Students.ID = Courses.StudentID;
    

UNION combines results of two SELECT queries into a single result. Duplicate rows are removed unless UNION ALL is used.

-- Combine two queries
SELECT Name FROM Students
UNION
SELECT Name FROM Teachers;

-- Use UNION ALL to keep duplicates
SELECT Name FROM Students
UNION ALL
SELECT Name FROM Teachers;
    

Use LIKE to search for a pattern in a column.

-- Find students whose name starts with 'A'
SELECT * FROM Students
WHERE Name LIKE 'A%';

-- Find students whose name contains 'li'
SELECT * FROM Students
WHERE Name LIKE '%li%';
    

IN checks if a value matches any value in a list.

-- Get students with ID 1, 2, or 3
SELECT * FROM Students
WHERE ID IN (1, 2, 3);
    

BETWEEN filters data within a range.

-- Get students aged between 18 and 22
SELECT * FROM Students
WHERE Age BETWEEN 18 AND 22;
    

Check for NULL values using IS NULL or IS NOT NULL.

-- Find students with no age recorded
SELECT * FROM Students
WHERE Age IS NULL;

-- Find students with an age
SELECT * FROM Students
WHERE Age IS NOT NULL;
    

CASE allows conditional logic in SELECT queries.

-- Label students as Adult or Minor
SELECT Name, Age,
CASE 
    WHEN Age >= 18 THEN 'Adult'
    ELSE 'Minor'
END AS Status
FROM Students;
    

Subqueries are queries inside another query to filter or calculate data.

-- Get students older than the average age
SELECT * FROM Students
WHERE Age > (SELECT AVG(Age) FROM Students);
    

EXISTS checks if a subquery returns any rows.

-- Get students who have enrolled in courses
SELECT * FROM Students s
WHERE EXISTS (
    SELECT 1 FROM Courses c
    WHERE c.StudentID = s.ID
);
    

Indexes improve query performance by allowing faster searches.

-- Create an index on the Name column
CREATE INDEX idx_name
ON Students(Name);
    

Views are virtual tables created from a SELECT query.

-- Create a view for adult students
CREATE VIEW AdultStudents AS
SELECT Name, Age
FROM Students
WHERE Age >= 18;

-- Query the view
SELECT * FROM AdultStudents;
    

Transactions ensure a set of operations are completed together or rolled back in case of error.

-- Start a transaction
BEGIN;

-- Update multiple records
UPDATE Students SET Age = Age + 1 WHERE ID = 1;
UPDATE Students SET Age = Age + 1 WHERE ID = 2;

-- Commit changes
COMMIT;

-- Or rollback if something goes wrong
ROLLBACK;