SQL Masterclass | AutoLearn

SQL Intro

SQL (Structured Query Language) is the standard language for relational database management systems. It allows you to create, read, update, and delete data within a database.

-- SQL Introduction
-- SQL is used to query databases.
SELECT "Welcome to SQL Masterclass!";

SQL Syntax

SQL keywords are case-insensitive, but it is best practice to write them in UPPERCASE. Statements typically end with a semicolon (;).

Example Structure:

SELECT column_name FROM table_name;

-- Select all columns from the 'users' table
SELECT * FROM users;

SQL Select

The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.

SELECT column1, column2 FROM table_name;

-- Select specific columns
SELECT name, city FROM users;

SQL Select Distinct

The SELECT DISTINCT statement is used to return only distinct (different) values.

SELECT DISTINCT Country FROM Customers;

-- Select distinct cities from users
SELECT DISTINCT city FROM users;

SQL Where

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.

-- Find users from London
SELECT * FROM users WHERE city = 'London';

SQL Order By

The ORDER BY keyword is used to sort the result-set in ascending or descending order. By default, it sorts in ascending order. Use DESC for descending.

Examples:

-- Sort users by age (Ascending)
SELECT * FROM users ORDER BY age;

-- Sort by Name Descending
SELECT * FROM users ORDER BY name DESC;

-- Sort by city then age
SELECT * FROM users ORDER BY city, age;

-- Try sorting users
SELECT * FROM users ORDER BY age DESC;

SQL And

The AND operator displays a record if all the conditions separated by AND are TRUE.

Examples:

-- Users from London AND younger than 30
SELECT * FROM users WHERE city = 'London' AND age < 30;

-- Users with name starting with J and age >= 25
SELECT * FROM users WHERE name LIKE 'J%' AND age >= 25;

-- Multiple AND conditions
SELECT * FROM users WHERE city = 'London' AND age > 20 AND name IS NOT NULL;

SELECT * FROM users WHERE city = 'London' AND age < 30;

SQL Or

The OR operator displays a record if any of the conditions separated by OR is TRUE.

Examples:

-- Users from London OR New York
SELECT * FROM users WHERE city = 'London' OR city = 'New York';

-- Users age less than 20 OR older than 40
SELECT * FROM users WHERE age < 20 OR age > 40;

-- Combine AND with OR
SELECT * FROM users WHERE city = 'London' AND (age < 25 OR age > 45);

SELECT * FROM users WHERE city = 'London' OR city = 'New York';

SQL Not

The NOT operator displays a record if the condition(s) is NOT TRUE.

Examples:

-- Users NOT from London
SELECT * FROM users WHERE NOT city = 'London';

-- NOT with LIKE
SELECT * FROM users WHERE name NOT LIKE 'A%';

-- NOT with BETWEEN
SELECT * FROM users WHERE age NOT BETWEEN 20 AND 30;

SELECT * FROM users WHERE NOT city = 'London';

SQL Insert Into

The INSERT INTO statement is used to insert new records in a table.

Examples:

-- Specific columns
INSERT INTO users (id, name, email, city) 
VALUES (4, 'Alice Wonder', 'alice@test.com', 'Paris');

-- All columns
INSERT INTO users VALUES (5, 'Bob Brown', 'bob@test.com', 'Berlin', 35);

-- Insert multiple (supported in most DBs)
INSERT INTO users (id, name) VALUES (6, 'Charlie'), (7, 'David');

INSERT INTO users (id, name, age) VALUES (10, 'Test User', 25);
SELECT * FROM users;

SQL Null Values

A field with a NULL value is a field with no value. It is different from zero or an empty string.

Examples:

-- Select users where city is empty
SELECT * FROM users WHERE city IS NULL;

-- Select users where email has a value
SELECT * FROM users WHERE email IS NOT NULL;

-- Update nulls to a default value
UPDATE users SET city = 'Unknown' WHERE city IS NULL;

-- Create a null value first
INSERT INTO users (id, name) VALUES (8, 'Null Tester');
SELECT * FROM users WHERE city IS NULL;

SQL Update

The UPDATE statement is used to modify the existing records in a table.

Examples:

-- Update one record
UPDATE users SET city = 'London' WHERE id = 1;

-- Update multiple columns
UPDATE users SET age = 40, city = 'New York' WHERE id = 2;

-- Update multiple records
UPDATE users SET city = 'UK' WHERE city = 'London';

UPDATE users SET age = age + 1;
SELECT * FROM users;

SQL Delete

The DELETE statement is used to delete existing records in a table.

Examples:

-- Delete specific record
DELETE FROM users WHERE id = 3;

-- Delete multiple records
DELETE FROM users WHERE age > 50;

-- Delete all records (keeping table structure)
DELETE FROM users;

DELETE FROM users WHERE id = 1;
SELECT * FROM users;

SQL Select Top/Limit

The SELECT TOP clause specifies the number of records to return. MySQL and SQLite use LIMIT.

Examples:

-- Get first 3 records
SELECT * FROM users LIMIT 3;

-- Get oldest user
SELECT * FROM users ORDER BY age DESC LIMIT 1;

-- Combined with WHERE
SELECT * FROM users WHERE city = 'London' LIMIT 2;

SELECT * FROM users LIMIT 10;

SQL Aggregate Functions

Aggregates perform calculations on a set of values and return a single summary result (e.g. COUNT, AVG, SUM).

Examples:

-- Count records
SELECT COUNT(*) as TotalUsers FROM users;

-- Average age
SELECT AVG(age) as AverageAge FROM users;

-- Sum of specific values
SELECT SUM(age) as TotalYears FROM users WHERE city = 'London';

SELECT COUNT(*) FROM users;

SQL Min and Max

The MIN() function returns the smallest value. The MAX() function returns the largest value.

Examples:

-- Youngest user
SELECT MIN(age) FROM users;

-- Oldest user
SELECT MAX(age) FROM users;

-- Combined with Aliases
SELECT MIN(age) as Youngest, MAX(age) as Oldest FROM users;

SELECT MIN(age), MAX(age) FROM users;

SQL Count

The COUNT() function returns the number of rows that matches a specified criterion.

Examples:

-- Count all records
SELECT COUNT(*) FROM users;

-- Count with condition
SELECT COUNT(id) FROM users WHERE age > 25;

-- Count distinct values
SELECT COUNT(DISTINCT city) FROM users;

SELECT COUNT(*) FROM users WHERE city = 'London';

SQL Sum

The SUM() function returns the total sum of a numeric column.

Examples:

-- Sum total amounts
SELECT SUM(amount) FROM orders;

-- Sum with filtering
SELECT SUM(amount) FROM orders WHERE user_id = 1;

-- Sum of ages
SELECT SUM(age) as TotalAge FROM users;

SELECT SUM(amount) FROM orders;

SQL Avg

The AVG() function returns the average value of a numeric column.

Examples:

-- Average age of all users
SELECT AVG(age) FROM users;

-- Average order amount
SELECT AVG(amount) FROM orders;

-- Average age by city
SELECT AVG(age) FROM users WHERE city = 'London';

SELECT AVG(age) FROM users;

SQL Like

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Examples:

-- Starts with 'J'
SELECT * FROM users WHERE name LIKE 'J%';

-- Ends with 'com'
SELECT * FROM users WHERE email LIKE '%com';

-- Contains 'nn'
SELECT * FROM users WHERE name LIKE '%nn%';

SELECT * FROM users WHERE name LIKE 'J%';

SQL Wildcards

Wildcards (`%`, `_`) are used to substitute one or more characters in a string.

Examples:

-- Single char (_)
SELECT * FROM users WHERE city LIKE 'L_nd_n';

-- Starts with 'a' and is at least 3 chars long
SELECT * FROM users WHERE name LIKE 'a__%';

-- Combined wildcards
SELECT * FROM users WHERE city LIKE '_ew%';

SELECT * FROM users WHERE name LIKE '_a%';

SQL In

The IN operator allows you to specify multiple values in a WHERE clause.

Examples:

-- Select specific cities
SELECT * FROM users WHERE city IN ('London', 'Paris');

-- Select specific IDs
SELECT * FROM users WHERE id IN (1, 3, 5);

-- NOT IN
SELECT * FROM users WHERE city NOT IN ('Berlin', 'Oslo');

SELECT * FROM users WHERE city IN ('London', 'Paris');

SQL Between

The BETWEEN operator selects values within a given range.

Examples:

-- Age range
SELECT * FROM users WHERE age BETWEEN 25 AND 30;

-- Amount range
SELECT * FROM orders WHERE amount BETWEEN 500 AND 1500;

-- Text range
SELECT * FROM users WHERE name BETWEEN 'A' AND 'M';

SELECT * FROM users WHERE age BETWEEN 20 AND 40;

SQL Aliases

Aliases (AS) are used to give a table or column a temporary name.

Examples:

-- Column Alias
SELECT name AS CharacterName FROM users;

-- Table Alias
SELECT u.name, o.product FROM users u, orders o WHERE u.id = o.user_id;

-- Alias with expression
SELECT name, age + 10 AS FutureAge FROM users;

SELECT name AS [User Name] FROM users;

SQL Joins

A JOIN clause combines rows from two or more tables, based on a related column.

Examples:

-- Inner Join (Default)
SELECT users.name, orders.product FROM orders
JOIN users ON orders.user_id = users.id;

-- Multiple tables
SELECT u.name, o.product, o.amount FROM users u
JOIN orders o ON u.id = o.user_id;

-- Join with filtering
SELECT u.name, o.product FROM users u
JOIN orders o ON u.id = o.user_id WHERE o.amount > 1000;

SELECT users.name, orders.product FROM orders
JOIN users ON orders.user_id = users.id;

SQL Inner Join

The INNER JOIN selects records that have matching values in both tables.

Examples:

-- Simple Inner Join
SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id;

-- With Aggregates
SELECT users.name, COUNT(orders.id) as OrderCount FROM users
INNER JOIN orders ON users.id = orders.user_id GROUP BY users.name;

-- Multi-condition Join
SELECT u.name, o.product FROM users u
INNER JOIN orders o ON u.id = o.user_id AND o.amount > 500;

SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id;

SQL Left Join

The LEFT JOIN returns all records from the left table, and the matched records from the right table.

Examples:

-- All users, including those without orders
SELECT users.name, orders.product FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- Find users with NO orders
SELECT users.name FROM users
LEFT JOIN orders ON users.id = orders.user_id WHERE orders.id IS NULL;

-- Left Join with Aliases
SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id;

SELECT users.name, orders.product FROM users
LEFT JOIN orders ON users.id = orders.user_id;

SQL Right Join

The RIGHT JOIN returns all records from the right table, and the matched records from the left table.

Examples:

-- All orders, including those with deleted users
SELECT orders.id, users.name FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

-- Right Join with condition
SELECT o.product, u.name FROM users u
RIGHT JOIN orders o ON u.id = o.user_id WHERE o.amount > 100;

-- (Note: SQLite doesn't support RIGHT JOIN natively)
-- Use LEFT JOIN with swapped tables instead.

SELECT orders.id, users.name FROM orders 
LEFT JOIN users ON orders.user_id = users.id;

SQL Full Join

Returns all records when there is a match in either left or right table.

-- Select everything from both
SELECT users.name, orders.product 
FROM users 
FULL OUTER JOIN orders ON users.id = orders.user_id;

SQL Self Join

A self join is a regular join, but the table is joined with itself.

-- Match users from same city
SELECT A.name AS User1, B.name AS User2, A.city
FROM users A, users B
WHERE A.id <> B.id
AND A.city = B.city;

SQL Union

The UNION operator combines the result-set of two or more SELECT statements (Distinct values by default).

-- Combined list of IDs
SELECT id FROM users
UNION
SELECT id FROM orders;

SQL Union All

The UNION ALL operator combines the result-set, allowing duplicate values.

Examples:

-- Combined names (all)
SELECT name FROM users UNION ALL SELECT name FROM orders;

-- Combined cities (all)
SELECT city FROM users UNION ALL SELECT city FROM suppliers;

-- All IDs from both tables
SELECT id FROM table1 UNION ALL SELECT id FROM table2;

SELECT name FROM users UNION ALL SELECT name FROM users;

SQL Group By

The GROUP BY statement groups rows that have the same values into summary rows.

Examples:

-- Count per city
SELECT city, COUNT(*) FROM users GROUP BY city;

-- Average age per city
SELECT city, AVG(age) FROM users GROUP BY city;

-- Sum amounts per user
SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;

SELECT city, COUNT(*) FROM users GROUP BY city;

SQL Having

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Examples:

-- Filter grouped data
SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 1;

-- Filter by aggregate sum
SELECT user_id, SUM(amount) FROM orders GROUP BY user_id HAVING SUM(amount) > 1000;

-- Filter by aggregate average
SELECT city, AVG(age) FROM users GROUP BY city HAVING AVG(age) > 25;

SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 1;

SQL Exists

The EXISTS operator is used to test for the existence of any record in a subquery.

Examples:

-- Users with orders
SELECT name FROM users WHERE EXISTS (SELECT product FROM orders WHERE user_id = users.id);

-- Products never ordered
SELECT name FROM products WHERE NOT EXISTS (SELECT * FROM orders WHERE product_id = products.id);

-- Suppliers with products
SELECT name FROM suppliers WHERE EXISTS (SELECT * FROM products WHERE supplier_id = suppliers.id);

SELECT name FROM users WHERE EXISTS (SELECT * FROM orders WHERE user_id = users.id);

SQL Any/All

ANY returns true if any subquery values meet the condition. ALL requires all values to meet it.

Examples:

-- Match ANY in subquery
SELECT name FROM users WHERE id = ANY (SELECT user_id FROM orders);

-- Match ALL in subquery
SELECT name FROM users WHERE age > ALL (SELECT age FROM staff);

-- Combined with conditions
SELECT * FROM products WHERE price > ANY (SELECT price FROM competitors);

SELECT name FROM users WHERE id = ANY (SELECT user_id FROM orders);

SQL Select Into

Copies data from one table into a new table.

Examples:

-- Full Backup
SELECT * INTO users_backup FROM users;

-- Selective Columns Backup
SELECT name, email INTO contacts_backup FROM users;

-- Backup with WHERE
SELECT * INTO london_users FROM users WHERE city = 'London';

-- Concept (T-SQL)
SELECT * INTO BackupUsers FROM Users;

SQL Insert Into Select

Copies data from one table and inserts it into an existing table.

Examples:

-- Copy all
INSERT INTO users_backup SELECT * FROM users;

-- Copy specific columns
INSERT INTO contacts (name, phone) SELECT name, phone FROM main_db;

-- Copy with condition
INSERT INTO archive SELECT * FROM users WHERE active = 0;

CREATE TABLE backup (name TEXT);
INSERT INTO backup SELECT name FROM users;

SQL Case

The CASE expression returns a value when the first condition is met (like if-then-else).

Examples:

-- Categorize by Age
SELECT name, CASE WHEN age > 18 THEN 'Adult' ELSE 'Minor' END AS Status FROM users;

-- Multi-condition Case
SELECT name, CASE 
    WHEN city = 'London' THEN 'Local' 
    WHEN city = 'Paris' THEN 'International' 
    ELSE 'Unknown' 
END AS Region FROM users;

-- Case in Order By
SELECT * FROM users ORDER BY (CASE WHEN city IS NULL THEN 1 ELSE 0 END);

SELECT name, CASE WHEN age < 30 THEN 'Young' ELSE 'Senior' END FROM users;

SQL Null Functions

Functions like COALESCE() or IFNULL() handle NULL values.

Examples:

-- First non-null value
SELECT name, COALESCE(city, 'Unknown') FROM users;

-- Specific NULL check (IFNULL)
SELECT name, IFNULL(email, 'No Email Provided') FROM users;

-- Using in expressions
SELECT name, (salary + IFNULL(bonus, 0)) FROM staff;

SELECT name, IFNULL(city, 'Default City') FROM users;

SQL Stored Procedures

Stored procedures are reusable SQL code blocks.

Examples:

-- Create Procedure
CREATE PROCEDURE SelectAllUsers AS SELECT * FROM users; EXEC SelectAllUsers;

-- Procedure with Parameters
CREATE PROCEDURE FindUser @name varchar(50) AS SELECT * FROM users WHERE name = @name;

-- Call Procedure
EXEC FindUser @name = 'John';

-- Stored procedures are for reusable logic
-- EXEC ProcedureName;

SQL Comments

Comments explain sections of SQL statements. Single line starts with --. Multi-line starts with /* */.

Examples:

-- This is a single line comment
SELECT * FROM users;

SELECT * FROM orders; -- This is an inline comment

/* This is a 
multi-line comment block */
SELECT 1;

-- Practice comments
SELECT * FROM users; -- Get all records

SQL Operators

Operators include Arithmetic (+, -), Bitwise (&, |), Comparison (=, >), and Compound (+=).

Examples:

-- Arithmetic
SELECT 10 + 20 - 5;

-- Comparison
SELECT * FROM users WHERE age >= 25 AND age <= 40;

-- Modulo
SELECT 10 % 3;

SELECT 5 * 10 / 2;