Nikhil Learn Hub - SQL Basics

1. Introduction to SQL

What is SQL?

SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. It lets you create, read, update, and delete data.

Databases and DBMS

A database is an organized collection of data. A DBMS (Database Management System) like MySQL or PostgreSQL is software that manages databases.

Types of SQL Commands:

DDL (Data Definition Language): Defines database structure (e.g., CREATE, ALTER, DROP)
DML (Data Manipulation Language): Modifies data (e.g., INSERT, UPDATE, DELETE)
DQL (Data Query Language): Retrieves data (SELECT)
DCL (Data Control Language): Manages permissions (GRANT, REVOKE)
TCL (Transaction Control Language): Controls transactions (COMMIT, ROLLBACK)

2. Setting Up SQL Environment

Installation

Download tools like MySQL, PostgreSQL, or SQLite to run SQL locally.

Online Playgrounds

Practice SQL without installation using websites like SQLFiddle or DB Fiddle.

IDE Tools

Use graphical tools like MySQL Workbench or DBeaver to write and execute SQL queries easily.

3. Database and Table Operations

Create Database/Table:

CREATE DATABASE my_db;  
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(100) UNIQUE
);

Data Types

Define what kind of data a column holds:

INT - Whole numbers
VARCHAR(n) - Variable-length text
DATE - Date values
BOOLEAN - True/False values

Constraints

PRIMARY KEY - Unique identifier
FOREIGN KEY - Links between tables
NOT NULL - Mandatory field
UNIQUE - No duplicate values

4. Basic SQL Queries

SELECT Statement

-- Select all columns
SELECT * FROM users;

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

WHERE Clause

SELECT * FROM users 
WHERE age > 25;

Operators

Comparison:

=, >, <, >=, <=, !=

Logical:

AND, OR, NOT

ORDER BY

SELECT * FROM users
ORDER BY name DESC;

LIMIT

SELECT * FROM users
LIMIT 10;

5. Modifying Data (DML)

INSERT

-- Single record
INSERT INTO users 
VALUES (1, 'John', 30, 'john@example.com');

-- Multiple records
INSERT INTO users (name, age, email)
VALUES ('Alice', 25, 'alice@example.com'),
       ('Bob', 28, 'bob@example.com');

UPDATE

UPDATE users
SET name = 'Alice', age = 26
WHERE id = 1;

DELETE

-- Delete specific records
DELETE FROM users
WHERE id = 1;

-- Delete all records (keeps table structure)
TRUNCATE TABLE users;

6. Advanced Filtering

IN / NOT IN

SELECT * FROM users
WHERE id IN (1, 3, 5);

SELECT * FROM users
WHERE id NOT IN (2, 4);

BETWEEN

SELECT * FROM users
WHERE age BETWEEN 20 AND 30;

LIKE

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

-- Names ending with 'n'
SELECT * FROM users
WHERE name LIKE '%n';

-- Names with 'o' as second letter
SELECT * FROM users
WHERE name LIKE '_o%';

NULL Checks

SELECT * FROM users
WHERE email IS NULL;

SELECT * FROM users
WHERE email IS NOT NULL;

7. Working with Multiple Tables

Joins

-- INNER JOIN (only matching rows)
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d 
ON e.dept_id = d.id;

-- LEFT JOIN (all left + matching right)
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d 
ON e.dept_id = d.id;

-- RIGHT JOIN (all right + matching left)
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d 
ON e.dept_id = d.id;

-- FULL JOIN (MySQL workaround)
SELECT e.name, d.department_name 
FROM employees e LEFT JOIN departments d ON e.dept_id = d.id
UNION
SELECT e.name, d.department_name 
FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id;

UNION

SELECT name FROM employees
UNION
SELECT name FROM contractors;

8. Aggregation and Grouping

Aggregate Functions

SELECT 
    COUNT(*) AS total_employees,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees;

GROUP BY & HAVING

SELECT 
    department, 
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

9. Subqueries

Single-row Subquery

SELECT name FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

Multi-row Subquery

SELECT name FROM employees
WHERE dept_id IN (SELECT id FROM departments 
                 WHERE location = 'NY');

Correlated Subquery

SELECT e.name FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d 
             WHERE d.id = e.dept_id 
             AND d.location = 'CA');

Subqueries in SELECT

SELECT name, 
       (SELECT COUNT(*) FROM orders 
        WHERE orders.emp_id = employees.id) AS order_count
FROM employees;

10. Views and Indexes

Views

-- Create a view
CREATE VIEW active_users AS
SELECT * FROM users 
WHERE status = 'active';

-- Use a view
SELECT * FROM active_users;

Indexes

-- Create an index
CREATE INDEX idx_user_email ON users(email);

-- When to use indexes:
-- * Frequently queried columns
-- * Columns used in JOIN conditions
-- * Columns used in WHERE clauses

11. Basic SQL Functions

String Functions

SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    UPPER(name) AS upper_name,
    LOWER(name) AS lower_name,
    LENGTH(name) AS name_length,
    SUBSTRING(name, 1, 3) AS name_prefix
FROM employees;

Date Functions

SELECT 
    NOW() AS current_datetime,
    CURDATE() AS current_date,
    CURTIME() AS current_time,
    DATEDIFF('2025-12-31', NOW()) AS days_remaining,
    DATE_ADD(NOW(), INTERVAL 7 DAY) AS next_week;

Math Functions

SELECT 
    ROUND(3.14159, 2) AS rounded,
    ABS(-5) AS absolute,
    CEIL(3.2) AS ceiling,
    FLOOR(3.9) AS floor,
    MOD(10, 3) AS remainder;

12. Transactions and ACID

Transactions

START TRANSACTION;

-- Transfer $100 from account 1 to account 2
UPDATE accounts SET balance = balance - 100 
WHERE user_id = 1;

UPDATE accounts SET balance = balance + 100 
WHERE user_id = 2;

-- Only commit if both updates succeed
COMMIT;

-- Or rollback if there's an error
-- ROLLBACK;

ACID Properties

Atomicity: Transactions are all-or-nothing

Consistency: Database remains in valid state

Isolation: Concurrent transactions don't interfere

Durability: Committed transactions persist

13. Stored Procedures & Functions

Stored Procedures

DELIMITER //
CREATE PROCEDURE GetEmployeeCount(IN dept_id INT, OUT emp_count INT)
BEGIN
    SELECT COUNT(*) INTO emp_count
    FROM employees
    WHERE department_id = dept_id;
END //
DELIMITER ;

-- Call the procedure
CALL GetEmployeeCount(5, @count);
SELECT @count;

User-Defined Functions

DELIMITER //
CREATE FUNCTION CalculateTax(salary DECIMAL(10,2)) 
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE tax DECIMAL(10,2);
    IF salary > 100000 THEN
        SET tax = salary * 0.30;
    ELSE
        SET tax = salary * 0.20;
    END IF;
    RETURN tax;
END //
DELIMITER ;

-- Use the function
SELECT name, salary, CalculateTax(salary) AS tax
FROM employees;

MYSQL SQL Basics