Learn SQL in One Blog — The Only Guide You'll Ever Need

CREATE DATABASE deogiri_cse;

USE deogiri_cse;

SHOW DATABASES;

-- Delete a database permanently
DROP DATABASE IF EXISTS test_db;

CREATE TABLE students (
    roll_no         INT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    email           VARCHAR(150),
    age             INT,
    city            VARCHAR(80),
    department      VARCHAR(10) DEFAULT 'CS',
    enrollment_date DATE
);

CREATE TABLE company_team (
    emp_id     INT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    role       VARCHAR(100),
    company    VARCHAR(100),
    experience INT,
    salary     INT
);

CREATE TABLE marks (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    roll_no   INT,
    subject   VARCHAR(50),
    score     INT,
    exam_date DATE,
    FOREIGN KEY (roll_no) REFERENCES students(roll_no)
);

DESC students;
SHOW TABLES;

INSERT INTO students VALUES
(1,  'Sagar Waghmare',   'sagar@deogiri.ac.in',     21, 'Chhatrapati Sambhajinagar', 'CS', '2023-08-01'),
(2,  'Atharva Wandhare',  'atharva@deogiri.ac.in',    21, 'Chhatrapati Sambhajinagar', 'CS', '2023-08-01'),
(3,  'Rajat Ranvir',      'rajat@deogiri.ac.in',      21, 'Pune',                      'CS', '2023-08-01'),
(4,  'Sourabh Ugawekar',  'sourabh@deogiri.ac.in',    21, 'Chhatrapati Sambhajinagar', 'CS', '2023-08-02'),
(5,  'Omkar Pandhekar',   'omkar@deogiri.ac.in',      22, 'Chhatrapati Sambhajinagar', 'IT', '2023-08-02'),
(6,  'Ankita Patil',      'ankita@deogiri.ac.in',     21, 'Jalna',                     'CS', '2023-08-01'),
(7,  'Krishna Shelke',    'krishna@deogiri.ac.in',    21, 'Nashik',                    'IT', '2023-08-03'),
(8,  'Nikhil Pawar',      'nikhil@deogiri.ac.in',     22, 'Chhatrapati Sambhajinagar', 'CS', '2023-08-01'),
(9,  'Shrikant Borse',    'shrikant@deogiri.ac.in',   21, 'Dhule',                     'IT', '2023-08-02'),
(10, 'Tanvi Kale',        'tanvi@deogiri.ac.in',      21, 'Chhatrapati Sambhajinagar', 'CS', '2023-08-01'),
(11, 'Siddhali Kathar',   'siddhali@deogiri.ac.in',   21, 'Beed',                      'CS', '2023-08-03'),
(12, 'Isha Deshpandhe',   'isha@deogiri.ac.in',       21, 'Chhatrapati Sambhajinagar', 'IT', '2023-08-02'),
(13, 'Tanmay Jadhav',     'tanmay@deogiri.ac.in',     22, 'Latur',                     'CS', '2023-08-01');

INSERT INTO company_team VALUES
(1, 'Raj',             'Senior Developer',     'Coding Jr', 5, 95000),
(2, 'Akshay',          'Full Stack Developer', 'Coding Jr', 3, 75000),
(3, 'Sneh Soni',       'Mentor & Developer',  'Coding Jr', 4, 85000),
(4, 'Aditya',          'Backend Developer',   'Coding Jr', 2, 65000),
(5, 'Sagar Waghmare',  'Developer Intern',    'Coding Jr', 1, 500000);

INSERT INTO marks (roll_no, subject, score, exam_date) VALUES
(1,  'DBMS',  92, '2026-01-15'),
(1,  'OS',    88, '2026-01-16'),
(2,  'DBMS',  76, '2026-01-15'),
(2,  'OS',    91, '2026-01-16'),
(3,  'DBMS',  85, '2026-01-15'),
(3,  'OS',    67, '2026-01-16'),
(4,  'DBMS',  72, '2026-01-15'),
(5,  'DBMS',  95, '2026-01-15'),
(5,  'OS',    82, '2026-01-16'),
(6,  'DBMS',  89, '2026-01-15'),
(7,  'OS',    74, '2026-01-16'),
(8,  'DBMS',  58, '2026-01-15'),
(10, 'DBMS',  91, '2026-01-15'),
(10, 'OS',    86, '2026-01-16'),
(13, 'DBMS',  44, '2026-01-15');

-- Get everything
SELECT * FROM students;

-- Get specific columns only
SELECT name, city, department FROM students;

-- Give columns a temporary alias
SELECT name AS 'Student Name', city AS 'Hometown' FROM students;

-- Students in CS department
SELECT * FROM students WHERE department = 'CS';

-- Students older than 21
SELECT name, age FROM students WHERE age > 21;

-- AND — both conditions must be true
SELECT * FROM students
WHERE department = 'CS' AND city = 'Chhatrapati Sambhajinagar';

-- OR — at least one condition must be true
SELECT * FROM students
WHERE city = 'Pune' OR city = 'Nashik';

-- IN — match any value in a list
SELECT * FROM students
WHERE city IN ('Pune', 'Nashik', 'Jalna');

-- BETWEEN — range of values (inclusive)
SELECT * FROM marks
WHERE score BETWEEN 70 AND 90;

-- LIKE — pattern matching (% = any chars, _ = one char)
SELECT * FROM students WHERE name LIKE 'S%';       -- starts with S
SELECT * FROM students WHERE name LIKE '%ar';      -- ends with ar
SELECT * FROM students WHERE name LIKE '%sh%';     -- contains sh
SELECT * FROM students WHERE name LIKE '_a%';      -- 2nd char is a

-- NOT — negate a condition
SELECT * FROM students WHERE NOT department = 'IT';

-- IS NULL / IS NOT NULL
SELECT * FROM students WHERE email IS NULL;
SELECT * FROM students WHERE email IS NOT NULL;

-- Update Sagar's age
UPDATE students
SET age = 22
WHERE roll_no = 1;

-- Give a raise to everyone with 3+ years experience
UPDATE company_team
SET salary = salary + 500000
WHERE experience >= 3;

-- Update multiple columns at once
UPDATE students
SET city = 'Mumbai', age = 22
WHERE roll_no = 3;

-- Delete one student
DELETE FROM students
WHERE roll_no = 13;

-- Delete all IT department students
DELETE FROM students
WHERE department = 'IT';

-- Delete ALL rows (dangerous!)
DELETE FROM students;

-- Add a new column
ALTER TABLE students ADD phone VARCHAR(15);

-- Change a column's data type
ALTER TABLE students MODIFY email VARCHAR(200);

-- Rename a column
ALTER TABLE students CHANGE city hometown VARCHAR(80);

-- Drop a column (permanent!)
ALTER TABLE students DROP COLUMN phone;

-- Rename the entire table
ALTER TABLE students RENAME TO cse_students;

-- Alphabetical order by name
SELECT * FROM students ORDER BY name ASC;

-- Highest salary first
SELECT * FROM company_team ORDER BY salary DESC;

-- Sort by department first, then by age within each department
SELECT name, department, age FROM students
ORDER BY department ASC, age DESC;

-- Top marks in DBMS
SELECT roll_no, score FROM marks
WHERE subject = 'DBMS'
ORDER BY score DESC;

-- Get first 5 students
SELECT * FROM students ORDER BY roll_no LIMIT 5;

-- Skip first 5, get next 5 (page 2)
SELECT * FROM students ORDER BY roll_no LIMIT 5 OFFSET 5;

-- Top 3 highest scores
SELECT roll_no, subject, score FROM marks
ORDER BY score DESC LIMIT 3;

-- All unique cities
SELECT DISTINCT city FROM students;

-- All unique departments
SELECT DISTINCT department FROM students;

-- Count of unique cities
SELECT COUNT(DISTINCT city) AS 'Unique Cities' FROM students;

-- COUNT — total number of rows
SELECT COUNT(*) AS 'Total Students' FROM students;

-- COUNT with condition
SELECT COUNT(*) AS 'CS Students' FROM students
WHERE department = 'CS';

-- SUM — total of a numeric column
SELECT SUM(salary) AS 'Total Payroll' FROM company_team;

-- AVG — average value
SELECT AVG(score) AS 'Avg DBMS Score' FROM marks
WHERE subject = 'DBMS';

-- MAX and MIN
SELECT
    MAX(score) AS 'Highest Score',
    MIN(score) AS 'Lowest Score'
FROM marks;

-- MAX salary in the team
SELECT name, salary FROM company_team
ORDER BY salary DESC LIMIT 1;

-- Students per city
SELECT city, COUNT(*) AS 'total'
FROM students
GROUP BY city;

-- Students per department
SELECT department, COUNT(*) AS 'count'
FROM students
GROUP BY department;

-- Average score per subject
SELECT subject, AVG(score) AS 'avg_score'
FROM marks
GROUP BY subject;

-- HAVING — only show cities with more than 1 student
SELECT city, COUNT(*) AS 'total'
FROM students
GROUP BY city
HAVING COUNT(*) > 1;

-- Subjects where average score is above 80
SELECT subject, AVG(score) AS 'avg'
FROM marks
GROUP BY subject
HAVING AVG(score) > 80;

-- UPPER / LOWER — change case
SELECT UPPER(name) FROM students;
SELECT LOWER(email) FROM students;

-- LENGTH — number of characters
SELECT name, LENGTH(name) AS 'name_length' FROM students;

-- CONCAT — join strings together
SELECT CONCAT(name, ' (', department, ')') AS 'student_info'
FROM students;

-- SUBSTRING — extract part of a string
SELECT SUBSTRING(name, 1, 5) AS 'short_name' FROM students;

-- REPLACE — swap text
SELECT REPLACE(city, 'Chhatrapati Sambhajinagar', 'CSN') AS 'short_city'
FROM students;

-- TRIM — remove leading/trailing spaces
SELECT TRIM(name) FROM students;

-- REVERSE — reverse a string
SELECT name, REVERSE(name) FROM students LIMIT 3;

-- CURDATE() — current date
SELECT CURDATE();              -- 2026-02-18

-- NOW() — current date + time
SELECT NOW();                  -- 2026-02-18 14:30:00

-- YEAR(), MONTH(), DAY() — extract parts of a date
SELECT name,
    YEAR(enrollment_date)  AS 'year',
    MONTH(enrollment_date) AS 'month',
    DAY(enrollment_date)   AS 'day'
FROM students;

-- DATEDIFF() — difference between two dates (in days)
SELECT name,
    DATEDIFF(CURDATE(), enrollment_date) AS 'days_since_enrollment'
FROM students;

-- DATE_FORMAT() — format a date
SELECT name,
    DATE_FORMAT(enrollment_date, '%d-%m-%Y') AS 'formatted_date'
FROM students;

-- DATE_ADD / DATE_SUB — add or subtract time
SELECT DATE_ADD(CURDATE(), INTERVAL 30 DAY) AS '30_days_from_now';
SELECT DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AS 'one_year_ago';

-- Filter by date
SELECT * FROM marks
WHERE exam_date > '2026-01-15';

-- Students enrolled in August 2023
SELECT * FROM students
WHERE MONTH(enrollment_date) = 8
  AND YEAR(enrollment_date) = 2023;

-- Start a transaction
START TRANSACTION;

-- Make changes
UPDATE company_team SET salary = salary - 10000 WHERE emp_id = 1;
UPDATE company_team SET salary = salary + 10000 WHERE emp_id = 5;

-- If everything is correct, save permanently
COMMIT;

START TRANSACTION;

DELETE FROM students WHERE department = 'IT';

-- Oops! That was a mistake. Undo everything.
ROLLBACK;

-- The IT students are still there. Crisis averted.

START TRANSACTION;

UPDATE company_team SET salary = 100000 WHERE emp_id = 1;
SAVEPOINT sp1;

UPDATE company_team SET salary = 0 WHERE emp_id = 5;

-- Undo only the second update, keep the first
ROLLBACK TO sp1;

COMMIT; -- emp_id 1 has 100000, emp_id 5 is unchanged

-- Disable auto-commit
SET autocommit = 0;

-- Now every change needs an explicit COMMIT
UPDATE students SET age = 23 WHERE roll_no = 1;
COMMIT; -- saved

UPDATE students SET age = 99 WHERE roll_no = 2;
ROLLBACK; -- NOT saved, age remains unchanged

-- Re-enable auto-commit
SET autocommit = 1;

SELECT s.name, s.department, m.subject, m.score
FROM students s
INNER JOIN marks m ON s.roll_no = m.roll_no;

SELECT s.name, m.subject, m.score
FROM students s
LEFT JOIN marks m ON s.roll_no = m.roll_no;

SELECT s.name, m.subject, m.score
FROM students s
RIGHT JOIN marks m ON s.roll_no = m.roll_no;

-- Simulating FULL OUTER JOIN in MySQL
SELECT s.name, m.subject, m.score
FROM students s
LEFT JOIN marks m ON s.roll_no = m.roll_no

UNION

SELECT s.name, m.subject, m.score
FROM students s
RIGHT JOIN marks m ON s.roll_no = m.roll_no;

SELECT s.name, m.subject
FROM students s
CROSS JOIN marks m
LIMIT 10;

-- Find students from the same city (no duplicates, no self-pairs)
SELECT
  a.name AS 'Student 1',
  b.name AS 'Student 2',
  a.city
FROM students a
INNER JOIN students b
  ON a.city = b.city
  AND a.roll_no < b.roll_no;

-- Show each employee with their manager's name
SELECT
  emp.name        AS 'Employee',
  emp.role        AS 'Role',
  mgr.name        AS 'Manager'
FROM company_team emp
LEFT JOIN company_team mgr
  ON emp.manager_id = mgr.id
ORDER BY mgr.name ASC;

-- Compare each employee's salary against the company average
SELECT
  a.name,
  a.salary,
  ROUND(AVG(b.salary), 2) AS 'company_avg'
FROM company_team a
CROSS JOIN company_team b
GROUP BY a.name, a.salary
HAVING a.salary > AVG(b.salary);

-- Average score per student (with their name)
SELECT s.name, AVG(m.score) AS 'avg_score'
FROM students s
INNER JOIN marks m ON s.roll_no = m.roll_no
GROUP BY s.name
ORDER BY avg_score DESC;

-- Create a view that shows only Pune students
CREATE VIEW pune_students AS
SELECT roll_no, name, age, marks
FROM students
WHERE city = 'Pune';

-- Now query the view exactly like a table
SELECT * FROM pune_students;

-- View: student name + subject + score (joins students and marks)
CREATE VIEW student_report AS
SELECT
  s.name          AS 'student',
  s.city,
  m.subject,
  m.score
FROM students s
INNER JOIN marks m ON s.roll_no = m.roll_no;

-- Use the view — no JOIN needed anymore!
SELECT * FROM student_report
WHERE subject = 'DBMS'
ORDER BY score DESC;

-- View: average score per student across all subjects
CREATE VIEW avg_scores AS
SELECT
  s.name,
  ROUND(AVG(m.score), 2) AS 'average_score',
  COUNT(m.subject)           AS 'subjects_taken'
FROM students s
LEFT JOIN marks m ON s.roll_no = m.roll_no
GROUP BY s.name;

-- Instantly get the top 3 students by average
SELECT * FROM avg_scores
ORDER BY average_score DESC
LIMIT 3;

-- Update pune_students view to also include grade column
CREATE OR REPLACE VIEW pune_students AS
SELECT roll_no, name, age, marks, grade
FROM students
WHERE city = 'Pune';

-- Show all views in current database
SHOW FULL TABLES
WHERE Table_type = 'VIEW';

-- Drop a single view
DROP VIEW pune_students;

-- Drop multiple views at once
DROP VIEW avg_scores, student_report;

-- Drop only if it exists (no error if it doesn't)
DROP VIEW IF EXISTS pune_students;

-- DELETE — removes rows, table stays
DELETE FROM marks;

-- TRUNCATE — removes all rows fast, resets auto_increment
TRUNCATE TABLE marks;

-- DROP — completely removes the table
DROP TABLE IF EXISTS marks;

-- DROP a database
DROP DATABASE IF EXISTS test_db;

EXPLAIN SELECT * FROM students WHERE city = 'Pune';

-- Create an index on the city column
CREATE INDEX idx_city ON students(city);

-- Now EXPLAIN will show it uses the index
EXPLAIN SELECT * FROM students WHERE city = 'Pune';

-- Change delimiter so MySQL doesn't end the procedure early
DELIMITER //

CREATE PROCEDURE procedure_name()
BEGIN
  -- SQL statements here
END //

DELIMITER ;   -- Reset back to semicolon

DELIMITER //

CREATE PROCEDURE GetPuneStudents()
BEGIN
  SELECT name, age, marks
  FROM students
  WHERE city = 'Pune';
END //

DELIMITER ;

-- Call the procedure
CALL GetPuneStudents();

DELIMITER //

CREATE PROCEDURE GetStudentsByCity(IN p_city VARCHAR(100))
BEGIN
  SELECT name, age, marks
  FROM students
  WHERE city = p_city;
END //

DELIMITER ;

-- Call with Nashik
CALL GetStudentsByCity('Nashik');
-- Call with Mumbai
CALL GetStudentsByCity('Mumbai');

DELIMITER //

CREATE PROCEDURE GetStudentCount(OUT total INT)
BEGIN
  SELECT COUNT(*) INTO total
  FROM students;
END //

DELIMITER ;

-- Call it and read the OUT value
CALL GetStudentCount(@count);
SELECT @count AS 'Total Students';

-- Grade a student based on marks: A / B / C / Fail
DELIMITER //

CREATE PROCEDURE GetGrade(IN p_marks INT, OUT p_grade VARCHAR(10))
BEGIN
  IF p_marks >= 90 THEN
    SET p_grade = 'A';
  ELSEIF p_marks >= 75 THEN
    SET p_grade = 'B';
  ELSEIF p_marks >= 60 THEN
    SET p_grade = 'C';
  ELSE
    SET p_grade = 'Fail';
  END IF;
END //

DELIMITER ;

-- Test it
CALL GetGrade(92, @g); SELECT @g;   -- A
CALL GetGrade(78, @g); SELECT @g;   -- B
CALL GetGrade(45, @g); SELECT @g;   -- Fail

-- Procedure that uses a WHILE loop counter
DELIMITER //

CREATE PROCEDURE CountUp(IN max_num INT)
BEGIN
  DECLARE counter INT DEFAULT 1;
  WHILE counter <= max_num DO
    SELECT counter AS 'Step';
    SET counter = counter + 1;
  END WHILE;
END //

DELIMITER ;

CALL CountUp(3);

DELIMITER //

CREATE PROCEDURE PromoteStudents(IN min_marks INT, OUT promoted_count INT)
BEGIN
  UPDATE students
  SET grade = 'Promoted'
  WHERE marks >= min_marks;

  SELECT ROW_COUNT() INTO promoted_count;
END //

DELIMITER ;

CALL PromoteStudents(70, @cnt);
SELECT @cnt AS 'Students Promoted';

-- Show all procedures in the current database
SHOW PROCEDURE STATUS WHERE Db = 'college_db';

-- See the definition of a procedure
SHOW CREATE PROCEDURE GetPuneStudents;

-- Drop a procedure
DROP PROCEDURE IF EXISTS GetPuneStudents;

-- Ensure marks never exceed 100 before inserting a student
DELIMITER //

CREATE TRIGGER before_student_insert
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
  IF NEW.marks > 100 THEN
    SET NEW.marks = 100;
  END IF;
END //

DELIMITER ;

-- Test: try to insert a student with marks = 105
INSERT INTO students (name, city, marks)
VALUES ('Test', 'Pune', 105);
-- marks will be saved as 100 automatically

-- First, create a log table
CREATE TABLE student_log (
  log_id    INT AUTO_INCREMENT PRIMARY KEY,
  action    VARCHAR(20),
  student   VARCHAR(100),
  logged_at DATETIME DEFAULT NOW()
);

-- Trigger: log every new student insertion
DELIMITER //

CREATE TRIGGER after_student_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN
  INSERT INTO student_log (action, student)
  VALUES ('INSERT', NEW.name);
END //

DELIMITER ;

-- Every INSERT into students now auto-logs to student_log
SELECT * FROM student_log;

-- Log salary changes: save old and new salary when updated
CREATE TABLE salary_log (
  emp_name   VARCHAR(100),
  old_salary DECIMAL(12,2),
  new_salary DECIMAL(12,2),
  changed_at DATETIME DEFAULT NOW()
);

DELIMITER //

CREATE TRIGGER before_salary_update
BEFORE UPDATE ON company_team
FOR EACH ROW
BEGIN
  IF OLD.salary != NEW.salary THEN
    INSERT INTO salary_log (emp_name, old_salary, new_salary)
    VALUES (OLD.name, OLD.salary, NEW.salary);
  END IF;
END //

DELIMITER ;

-- Prevent deletion of the Lead Developer (Sagar)
DELIMITER //

CREATE TRIGGER prevent_lead_delete
BEFORE DELETE ON company_team
FOR EACH ROW
BEGIN
  IF OLD.role = 'Lead Developer' THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Cannot delete the Lead Developer!';
  END IF;
END //

DELIMITER ;

-- This DELETE will fail with an error
DELETE FROM company_team
WHERE role = 'Lead Developer';

CREATE TABLE deleted_students AS
  SELECT * FROM students WHERE 1=0; -- Empty copy of structure

DELIMITER //

CREATE TRIGGER archive_deleted_student
AFTER DELETE ON students
FOR EACH ROW
BEGIN
  INSERT INTO deleted_students
  VALUES (OLD.roll_no, OLD.name, OLD.age, OLD.city,
          OLD.marks, OLD.grade, OLD.email,
          OLD.enrollment_date, OLD.department);
END //

DELIMITER ;

-- See all triggers in the current database
SHOW TRIGGERS;

-- Drop a specific trigger
DROP TRIGGER IF EXISTS before_student_insert;