SQL Full Course 2026 – Complete SQL Tutorial for Beginners to Advanced

-- Example: STUDENT table structure
CREATE DATABASE edureka;
USE edureka;

CREATE TABLE student (
    student_id    INT           NOT NULL PRIMARY KEY,
    first_name    VARCHAR(50)   NOT NULL,
    last_name     VARCHAR(50)   NOT NULL,
    address       VARCHAR(200),
    city          VARCHAR(100),
    marks         INT
);
      

-- Select all columns from student table
SELECT * FROM student;

-- Select specific columns only
SELECT first_name, last_name, marks
FROM student;

-- Select with condition
SELECT first_name
FROM student
WHERE city = 'Goa';
      

-- AND operator: Both conditions must be true
SELECT * FROM student
WHERE first_name = 'Bharat' AND last_name = 'Singh';

-- OR operator: At least one condition must be true
SELECT * FROM student
WHERE first_name = 'Bhuvi' OR last_name = 'Kumar';

-- NOT operator: Condition must be false
SELECT * FROM student
WHERE NOT first_name = 'Ashok';
      

-- Insert a new record into the student table
INSERT INTO student (student_id, first_name, last_name, address, city, marks)
VALUES (7, 'Manoj', 'Sharma', '07 MG Road', 'Jaipur', 438);
      

-- Update a student's name where student_id = 1
UPDATE student
SET first_name = 'Amar', last_name = 'Kumar'
WHERE student_id = 1;
      

-- Delete a student record from a specific city
DELETE FROM student
WHERE city = 'Mumbai';
      

-- Sort results in descending order by marks
SELECT * FROM student
ORDER BY marks DESC;

-- Count students per city
SELECT city, COUNT(student_id) AS student_count
FROM student
GROUP BY city;

-- Show cities with more than 2 students
SELECT city, COUNT(student_id) AS student_count
FROM student
GROUP BY city
HAVING COUNT(student_id) > 2;
      

-- BETWEEN: Range filter
SELECT * FROM student
WHERE marks BETWEEN 400 AND 500;

-- IN: Multiple value filter
SELECT * FROM student
WHERE city IN ('Delhi', 'Goa', 'Bengaluru');

-- LIKE: Pattern matching
SELECT * FROM student
WHERE first_name LIKE 'A%';   -- Names starting with 'A'

-- IS NULL: Check for empty values
SELECT * FROM student
WHERE marks IS NULL;

-- ALIAS: Rename columns or tables temporarily
SELECT student_id AS id, first_name AS name
FROM student AS s;
      

-- Create a new table with constraints
CREATE TABLE employee (
    emp_id       INT           PRIMARY KEY NOT NULL,
    first_name   VARCHAR(50)   NOT NULL,
    last_name    VARCHAR(50)   NOT NULL,
    department   VARCHAR(100),
    salary       DECIMAL(10, 2),
    hire_date    DATE
);

-- Add a new column to existing table
ALTER TABLE employee ADD email VARCHAR(100);

-- Change data type of a column
ALTER TABLE employee MODIFY salary FLOAT;

-- Delete all rows but keep table structure
TRUNCATE TABLE employee;

-- Permanently remove a table
DROP TABLE employee;
      

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 101;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 202;

-- If everything is correct, save changes
COMMIT;

-- If something went wrong, undo all changes
ROLLBACK;

-- Create a checkpoint to rollback to
SAVEPOINT sp1;
ROLLBACK TO sp1;
      

-- % matches zero or more characters
SELECT * FROM student WHERE first_name LIKE 'A%';     -- Starts with A
SELECT * FROM student WHERE first_name LIKE '%a';     -- Ends with a
SELECT * FROM student WHERE first_name LIKE '%ar%';   -- Contains 'ar'

-- _ matches exactly one character
SELECT * FROM student WHERE first_name LIKE '_____'; -- Exactly 5 chars
SELECT * FROM student WHERE city LIKE 'Che___i';  -- Che + 3 chars + i

-- CASE expression (like IF-THEN-ELSE)
SELECT city,
    CASE
        WHEN city = 'Chennai'   THEN 'Tamil Nadu'
        WHEN city = 'Bengaluru' THEN 'Karnataka'
        ELSE 'Unknown'
    END AS state
FROM department;
      

-- Count of all student IDs
SELECT COUNT(student_id) AS total_students FROM student;

-- Average marks
SELECT AVG(marks) AS avg_marks FROM student;

-- Total marks of all students
SELECT SUM(marks) AS total_marks FROM student;

-- Student with minimum marks
SELECT first_name, MIN(marks) AS min_marks FROM student;

-- Student with maximum marks
SELECT first_name, MAX(marks) AS max_marks FROM student;

-- Count students grouped by city, show cities with count > 500
SELECT city, COUNT(student_id) AS count
FROM student
GROUP BY city
HAVING COUNT(student_id) > 500
ORDER BY count DESC;
      

-- BEFORE (Unnormalized — has repeating groups)
-- | CustomerName | Address        | MoviesRented              | Category     |
-- | Alice Johnson| 1st St, No 3   | Mission Impossible, Clash  | Action, Sci-Fi|

-- 1NF — Atomic values, one movie per row
CREATE TABLE rentals_1nf (
    customer_name  VARCHAR(100),
    address        VARCHAR(200),
    movie_rented   VARCHAR(100),
    category       VARCHAR(50)
);

-- 2NF — Split into Customer and Rentals tables
CREATE TABLE customers_2nf (
    customer_id  INT PRIMARY KEY,
    salutation   VARCHAR(10),
    full_name    VARCHAR(100),
    address      VARCHAR(200)
);

CREATE TABLE movies_rented_2nf (
    rental_id    INT PRIMARY KEY,
    customer_id  INT REFERENCES customers_2nf(customer_id),
    movie_name   VARCHAR(100)
);

-- 3NF — Separate salutations into lookup table
CREATE TABLE salutations_3nf (
    salutation_id  INT PRIMARY KEY,
    salutation     VARCHAR(10)   -- Mr=1, Miss=2, Mrs=3, Dr=4
);
      

-- General syntax for creating a trigger
CREATE TRIGGER trigger_name
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
    ON table_name
    FOR EACH ROW
BEGIN
    -- SQL statements to execute
END;

-- Example: Add 100 bonus marks to every new student
CREATE TRIGGER calculate_marks
    BEFORE INSERT ON student
    FOR EACH ROW
BEGIN
    SET NEW.marks = NEW.marks + 100;
END;

-- AFTER INSERT trigger: Store total marks in another table
CREATE TRIGGER store_total_marks
    AFTER INSERT ON student
    FOR EACH ROW
BEGIN
    INSERT INTO final_marks (student_id, total_marks)
    VALUES (NEW.student_id, NEW.marks);
END;

-- Drop a trigger
DROP TRIGGER trigger_name;

-- Show all triggers in a database
SHOW TRIGGERS IN edureka;
      

-- INNER JOIN — only matching records
SELECT e.emp_id, e.first_name, p.project_name
FROM employee e
INNER JOIN project p ON e.emp_id = p.emp_id;

-- LEFT JOIN — all employees, even those without a project
SELECT e.first_name, e.last_name, p.project_name
FROM employee e
LEFT JOIN project p ON e.emp_id = p.emp_id
ORDER BY e.first_name;

-- RIGHT JOIN — all projects, even those with no assigned employee
SELECT e.first_name, p.project_id, p.project_name
FROM employee e
RIGHT JOIN project p ON e.emp_id = p.emp_id;

-- FULL JOIN — all records from both tables
-- (MySQL workaround using UNION)
SELECT e.first_name, p.project_name
FROM employee e LEFT JOIN project p ON e.emp_id = p.emp_id
UNION
SELECT e.first_name, p.project_name
FROM employee e RIGHT JOIN project p ON e.emp_id = p.emp_id;

-- SELF JOIN — join a table to itself (e.g., supervisor structure)
SELECT e.first_name AS employee, s.first_name AS supervisor
FROM employee e
LEFT JOIN employee s ON e.super_id = s.emp_id;

-- NATURAL JOIN — automatically joins on columns with same name
SELECT * FROM employee NATURAL JOIN department;

-- CROSS JOIN — Cartesian product (every row × every row)
SELECT e.emp_id, d.dept_name
FROM employee e CROSS JOIN department d;
      

-- UPPER and LOWER — Change case
SELECT UPPER('hello sql');          -- 'HELLO SQL'
SELECT LOWER('HELLO SQL');          -- 'hello sql'

-- LTRIM, RTRIM — Remove whitespace
SELECT LTRIM('   Hello');           -- 'Hello' (left spaces removed)
SELECT RTRIM('Hello   ');           -- 'Hello' (right spaces removed)

-- SUBSTRING — Extract part of a string
SELECT SUBSTRING('ABCDEF', 2, 3);   -- 'BCD' (start at 2, length 3)

-- REPLACE — Replace characters
SELECT REPLACE('ABCDE', 'CDE', 'XXX'); -- 'ABXXX'

-- CONCAT — Join strings
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employee;

-- LEN — Length of string
SELECT LEN('Edureka');                -- 7

-- REVERSE — Reverse a string
SELECT REVERSE('ABC');               -- 'CBA'

-- LEFT and RIGHT — Extract from left/right
SELECT LEFT('Edureka', 3);          -- 'Edu'
SELECT RIGHT('Edureka', 3);         -- 'eka'
      

SELECT ABS(-10);          -- 10 (absolute value)
SELECT ROUND(4.567, 2);  -- 4.57 (round to 2 decimal places)
SELECT FLOOR(4.9);        -- 4 (largest integer ≤ value)
SELECT CEILING(4.1);     -- 5 (smallest integer ≥ value)
SELECT SQRT(16);          -- 4 (square root)
SELECT POWER(2, 4);       -- 16 (2 to the power of 4)
SELECT SQUARE(4);         -- 16 (square of 4)
SELECT EXP(4);            -- e^4 exponential value
SELECT RAND();             -- Random value between 0 and 1
      

SELECT GETDATE();                          -- Current date and time
SELECT CURRENT_TIMESTAMP;                  -- Same as GETDATE()
SELECT DATEPART(YEAR, GETDATE());         -- Current year
SELECT DATEPART(MM, GETDATE());           -- Current month number
SELECT DATEDIFF(MM, '2020-01-01', GETDATE()); -- Months between dates
SELECT DATEADD(MM, 2, GETDATE());         -- Add 2 months to today
      

-- CAST — Convert value to specified data type
SELECT CAST(10 AS INT) * 20 AS cast_result;   -- 200

-- CONVERT — Similar to CAST but with more options
SELECT CONVERT(INT, 10) * 20 AS convert_result; -- 200

-- TRY_CAST — Returns NULL on failure instead of error
SELECT TRY_CAST('A' AS INT);                  -- NULL (no error)

-- Logical Functions
SELECT CHOOSE(3, 'test', 'just', 'rest');    -- 'rest' (3rd value)
SELECT IIF(1 > 10, 'true', 'false');       -- 'false'
      

-- Create a stored procedure without parameters
CREATE PROCEDURE get_department_details
AS
BEGIN
    SELECT * FROM department;
END;

-- Execute a stored procedure
EXEC get_department_details;

-- Stored procedure with input parameter
CREATE PROCEDURE get_emp_by_dept
    @dept_no INT
AS
BEGIN
    SELECT * FROM employee
    WHERE department_no = @dept_no;
END;

EXEC get_emp_by_dept @dept_no = 20;

-- Stored procedure with output parameter
CREATE PROCEDURE get_emp_count
    @dept_no   INT,
    @emp_count INT OUTPUT
AS
BEGIN
    SELECT @emp_count = COUNT(*) FROM employee
    WHERE department_no = @dept_no;
END;

-- Exception handling with TRY-CATCH
CREATE PROCEDURE safe_divide
AS
BEGIN TRY
    SELECT 10 / 0;  -- Will throw divide by zero error
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER()  AS error_num,
        ERROR_MESSAGE() AS error_msg,
        ERROR_LINE()    AS error_line;
END CATCH;
      

-- 1. Scalar Function — returns a single value
CREATE FUNCTION get_full_name (
    @first_name VARCHAR(50),
    @last_name  VARCHAR(50)
)
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN @first_name + ' ' + @last_name;
END;

-- Call scalar function
SELECT dbo.get_full_name('John', 'Smith') AS full_name;

-- 2. Inline Table-Valued Function — returns a table
CREATE FUNCTION get_employee_info (@emp_id INT)
RETURNS TABLE
AS
RETURN (
    SELECT * FROM employee
    WHERE emp_id = @emp_id
);

-- Call table-valued function
SELECT * FROM dbo.get_employee_info(1003);

-- Use UDF in WHERE clause
SELECT * FROM employee
WHERE salary > dbo.get_avg_salary();
      

# Windows — Download installer from postgresql.org/download/windows/
# Choose version, select 64-bit or 32-bit, run wizard

# macOS — via Homebrew
brew install postgresql@16
brew services start postgresql@16
psql postgres

# Ubuntu/Debian Linux
sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo -u postgres psql

# CentOS/RHEL
sudo yum install postgresql-server
sudo postgresql-setup initdb
sudo systemctl start postgresql

# Termux (Android)
pkg install postgresql
initdb $PREFIX/var/lib/postgresql
pg_ctl -D $PREFIX/var/lib/postgresql start
psql -U $(whoami) postgres
      

# Step 1: Download SQL Server Developer Edition (free)
# Visit: microsoft.com/en-us/sql-server/sql-server-downloads

# Step 2: Run installer, choose installation type:
#   Basic   — default config, fastest setup
#   Custom  — choose features, useful for enterprise
#   Download Media — offline installer

# Step 3: Accept license terms, set installation directory

# Step 4: Configure authentication:
#   Windows Authentication Mode — uses Windows login
#   Mixed Mode — SQL + Windows (recommended for development)

# Step 5: Name your instance (e.g., SQLEXPRESS or custom)

# Step 6: Download and install SQL Server Management Studio (SSMS)
# Visit: docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

# Step 7: Connect via SSMS
# Server Name: localhost\INSTANCE_NAME
# Authentication: Windows Authentication or SQL Authentication
      

-- MySQL / MariaDB version check
mysql --version
SELECT VERSION();

-- PostgreSQL version check
psql --version
SELECT version();

-- SQL Server version check
SELECT @@VERSION;

-- Show all databases
SHOW DATABASES;             -- MySQL/MariaDB
\l                           -- PostgreSQL
SELECT name FROM sys.databases; -- SQL Server
      

-- Create schema (namespace for tables)
CREATE SCHEMA company;
SET search_path TO company;

-- PostgreSQL supports JSON natively
CREATE TABLE orders (
    order_id  SERIAL PRIMARY KEY,
    data      JSONB
);

INSERT INTO orders (data) VALUES
('{"customer": "Alice", "items": ["book", "pen"], "total": 45.99}');

-- Query JSON fields
SELECT data->>'customer' AS customer_name FROM orders;

-- Window functions (unique to PostgreSQL among open-source DBs)
SELECT
    emp_id,
    first_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank,
    AVG(salary) OVER () AS company_avg
FROM employee;

-- UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();    -- Generates unique UUID each call

-- Creating a stored procedure (PostgreSQL style)
CREATE OR REPLACE PROCEDURE insert_employee(
    p_first_name TEXT,
    p_last_name  TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO employee(first_name, last_name)
    VALUES (p_first_name, p_last_name);
END;
$$;

CALL insert_employee('John', 'Smith');
      

-- TOP keyword (limit results in SQL Server)
SELECT TOP 10 * FROM employee ORDER BY salary DESC;

-- PIVOT — rotate rows into columns
SELECT customer_id, [11], [22], [33]
FROM (
    SELECT customer_id, cost FROM supplier
) AS src
PIVOT (AVG(cost) FOR customer_id IN ([11], [22], [33])) AS pvt;

-- OFFSET-FETCH (pagination)
SELECT * FROM employee
ORDER BY emp_id
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

-- MERGE statement
MERGE target_table AS target
USING source_table AS source
ON (target.id = source.id)
WHEN MATCHED THEN
    UPDATE SET target.name = source.name
WHEN NOT MATCHED BY TARGET THEN
    INSERT (id, name) VALUES (source.id, source.name)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

-- Temp tables (local vs global)
CREATE TABLE #local_temp (id INT);    -- Local: session only
CREATE TABLE ##global_temp (id INT);   -- Global: visible to all sessions

-- Variables and conditional logic
DECLARE @employee_count INT;
SET @employee_count = (SELECT COUNT(*) FROM employee);
PRINT @employee_count;
      

-- Explore dataset: See all employee records
SELECT * FROM employee_details;

-- Find unique job roles in the organization
SELECT DISTINCT title FROM employee_details;

-- Find unique department-job combinations
SELECT DISTINCT department, title FROM employee_details;

-- Filter: All employees in Fire Department
SELECT * FROM employee_details
WHERE department = 'Fire';

-- Employees NOT in Police Department
SELECT * FROM employee_details
WHERE department NOT IN ('Police');

-- Employees hired before year 2000
SELECT * FROM employee_details
WHERE original_hire_date < '2000-01-01';

-- Average salary of surgeons
SELECT AVG(salary_annual) AS avg_surgeon_salary
FROM employee_details
WHERE title = 'Surgeon';

-- Employees eligible for 25% raise above $30,000
SELECT * FROM employee_details
WHERE 1.25 * salary_annual > 30000;

-- Salary range filter
SELECT * FROM employee_details
WHERE salary_annual BETWEEN 24000 AND 50000;

-- View employee name + salary + hire date (who joined before 2008)
SELECT name, salary_annual, original_hire_date
FROM employee_details
WHERE original_hire_date < '2008-02-01';