SQL Complete Guide — Database Queries for B.Tech & Placements (2026)

SQL Kya Hai?

SQL (Structured Query Language) relational databases se data retrieve, insert, update aur delete karne ki language hai. MySQL, PostgreSQL, SQLite, Oracle, SQL Server — sab mein yahi syntax use hota hai (minor differences ke saath).

Database  →  Tables  →  Rows (Records)  →  Columns (Fields)

---

Sample Database

Hum ek College database use karenge:

-- Students table
CREATE TABLE students (
  student_id   INT PRIMARY KEY AUTO_INCREMENT,
  name         VARCHAR(100) NOT NULL,
  branch       VARCHAR(50),
  semester     INT,
  cgpa         DECIMAL(3,2),
  email        VARCHAR(100) UNIQUE,
  joined_year  YEAR
);

-- Courses table
CREATE TABLE courses (
  course_id    INT PRIMARY KEY AUTO_INCREMENT,
  course_name  VARCHAR(100) NOT NULL,
  credits      INT,
  branch       VARCHAR(50)
);

-- Enrollments table (junction table)
CREATE TABLE enrollments (
  enroll_id    INT PRIMARY KEY AUTO_INCREMENT,
  student_id   INT,
  course_id    INT,
  grade        CHAR(2),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id)  REFERENCES courses(course_id)
);

---

DDL — Data Definition Language

-- CREATE TABLE
CREATE TABLE teachers (
  teacher_id  INT PRIMARY KEY,
  name        VARCHAR(100),
  department  VARCHAR(50),
  salary      DECIMAL(10,2)
);

-- ALTER TABLE (columns add/modify/drop)
ALTER TABLE students ADD COLUMN phone VARCHAR(15);
ALTER TABLE students MODIFY COLUMN cgpa DECIMAL(4,2);
ALTER TABLE students DROP COLUMN phone;
ALTER TABLE students RENAME COLUMN cgpa TO gpa;

-- DROP TABLE
DROP TABLE IF EXISTS old_table;

-- TRUNCATE (sab rows delete, structure rakhta hai)
TRUNCATE TABLE students;

---

DML — Data Manipulation Language

INSERT

-- Single row
INSERT INTO students (name, branch, semester, cgpa, email, joined_year)
VALUES ('Rahul Kumar', 'CS', 3, 8.5, 'rahul@college.edu', 2023);

-- Multiple rows
INSERT INTO students (name, branch, semester, cgpa, email, joined_year) VALUES
  ('Priya Singh',  'IT',  4, 9.1, 'priya@college.edu',  2022),
  ('Amit Sharma',  'ECE', 2, 7.8, 'amit@college.edu',   2024),
  ('Neha Gupta',   'CS',  6, 8.9, 'neha@college.edu',   2021),
  ('Rohan Verma',  'ME',  1, 7.2, 'rohan@college.edu',  2025);

SELECT

-- Sab columns
SELECT * FROM students;

-- Specific columns
SELECT name, branch, cgpa FROM students;

-- Alias use karna
SELECT name AS student_name, cgpa AS gpa FROM students;

-- DISTINCT (duplicates remove)
SELECT DISTINCT branch FROM students;

-- Calculated column
SELECT name, cgpa, cgpa * 9.5 AS percentage FROM students;

WHERE — Filtering

-- Basic conditions
SELECT * FROM students WHERE branch = 'CS';
SELECT * FROM students WHERE cgpa >= 8.0;
SELECT * FROM students WHERE semester BETWEEN 3 AND 6;

-- Multiple conditions
SELECT * FROM students WHERE branch = 'CS' AND cgpa >= 8.5;
SELECT * FROM students WHERE branch = 'CS' OR branch = 'IT';
SELECT * FROM students WHERE branch != 'ME';

-- IN operator
SELECT * FROM students WHERE branch IN ('CS', 'IT', 'ECE');

-- LIKE (pattern matching)
SELECT * FROM students WHERE name LIKE 'R%';    -- R se shuru
SELECT * FROM students WHERE email LIKE '%@college.edu';
SELECT * FROM students WHERE name LIKE '_____'; -- exactly 5 chars

-- NULL check
SELECT * FROM students WHERE phone IS NULL;
SELECT * FROM students WHERE phone IS NOT NULL;

UPDATE aur DELETE

-- UPDATE
UPDATE students SET cgpa = 9.0 WHERE student_id = 1;
UPDATE students SET semester = semester + 1 WHERE joined_year = 2023;

-- DELETE
DELETE FROM students WHERE student_id = 5;
DELETE FROM students WHERE cgpa < 5.0;
-- DELETE FROM students; -- DANGER: sab rows delete!

---

ORDER BY aur LIMIT

-- Sort karna
SELECT * FROM students ORDER BY cgpa DESC;      -- highest first
SELECT * FROM students ORDER BY name ASC;        -- A-Z
SELECT * FROM students ORDER BY branch, cgpa DESC; -- multiple columns

-- Top N results
SELECT * FROM students ORDER BY cgpa DESC LIMIT 5;     -- top 5
SELECT * FROM students ORDER BY cgpa DESC LIMIT 5 OFFSET 5; -- 6-10

---

Aggregate Functions

SELECT COUNT(*)           AS total_students FROM students;
SELECT COUNT(DISTINCT branch) AS branches    FROM students;
SELECT AVG(cgpa)          AS avg_cgpa       FROM students;
SELECT MAX(cgpa)          AS highest_cgpa   FROM students;
SELECT MIN(cgpa)          AS lowest_cgpa    FROM students;
SELECT SUM(credits)       AS total_credits  FROM courses;

---

GROUP BY aur HAVING

-- Group by branch
SELECT branch,
       COUNT(*) AS student_count,
       AVG(cgpa) AS avg_cgpa,
       MAX(cgpa) AS topper_cgpa
FROM students
GROUP BY branch;

-- HAVING — groups filter karna (aggregate conditions)
SELECT branch, AVG(cgpa) AS avg_cgpa
FROM students
GROUP BY branch
HAVING AVG(cgpa) >= 8.0;  -- Only branches with avg >= 8

-- WHERE + GROUP BY + HAVING
SELECT branch, COUNT(*) AS count
FROM students
WHERE joined_year >= 2022          -- pehle rows filter
GROUP BY branch                    -- phir group
HAVING COUNT(*) > 10               -- phir groups filter
ORDER BY count DESC;

---

JOINS — Most Important Topic

students                    enrollments               courses
+-----------+--------+       +-----------+-----------+    +----------+-----------+
|student_id | name   |       |student_id | course_id |    |course_id | name      |
+-----------+--------+       +-----------+-----------+    +----------+-----------+
| 1         | Rahul  |       | 1         | 101       |    | 101      | DBMS      |
| 2         | Priya  |       | 1         | 102       |    | 102      | OS        |
| 3         | Amit   |       | 2         | 101       |    | 103      | Networks  |
+-----------+--------+       +-----------+-----------+    +----------+-----------+

INNER JOIN

-- Sirf matching records (dono tables mein hone chahiye)
SELECT s.name, c.course_name, e.grade
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id
INNER JOIN courses c     ON e.course_id  = c.course_id;

LEFT JOIN

-- Left table ke sab records + matching right records (no match → NULL)
SELECT s.name, c.course_name
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
LEFT JOIN courses c     ON e.course_id  = c.course_id;
-- Enrolled nahi wale students bhi aayenge (course = NULL)

RIGHT JOIN

-- Right table ke sab records
SELECT s.name, c.course_name
FROM enrollments e
RIGHT JOIN courses c ON e.course_id = c.course_id
LEFT JOIN students s ON e.student_id = s.student_id;

Self JOIN

-- Same table se join (hierarchy, manager-employee etc.)
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.emp_id;

---

Subqueries

-- WHERE mein subquery
SELECT name, cgpa
FROM students
WHERE cgpa > (SELECT AVG(cgpa) FROM students);

-- IN ke saath
SELECT name FROM students
WHERE student_id IN (
  SELECT student_id FROM enrollments
  WHERE course_id = 101
);

-- EXISTS
SELECT name FROM students s
WHERE EXISTS (
  SELECT 1 FROM enrollments e
  WHERE e.student_id = s.student_id
);

-- Correlated subquery
SELECT name, cgpa,
  (SELECT COUNT(*) FROM enrollments e WHERE e.student_id = s.student_id) AS course_count
FROM students s;

---

String Functions

SELECT UPPER(name), LOWER(email)  FROM students;
SELECT LENGTH(name)                FROM students;
SELECT SUBSTRING(name, 1, 5)       FROM students; -- first 5 chars
SELECT CONCAT(name, ' (', branch, ')') FROM students;
SELECT TRIM('  hello  ');          -- 'hello'
SELECT REPLACE(email, '@college.edu', ''); -- domain remove

---

Date Functions

SELECT NOW();                      -- current datetime
SELECT CURDATE();                  -- current date
SELECT YEAR(NOW());                -- 2026
SELECT DATEDIFF('2026-06-01', CURDATE()); -- days remaining
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y');    -- 10/03/2026

---

Indexes — Performance

-- Index banana (search speed badhata hai)
CREATE INDEX idx_branch ON students(branch);
CREATE UNIQUE INDEX idx_email ON students(email);

-- Index dekhna
SHOW INDEXES FROM students;

-- Drop index
DROP INDEX idx_branch ON students;

-- Composite index
CREATE INDEX idx_branch_sem ON students(branch, semester);

---

Interview Questions

| Question | Answer | |---|---| | CHAR vs VARCHAR | CHAR fixed length, VARCHAR variable length | | DELETE vs TRUNCATE | DELETE row-by-row + rollback possible, TRUNCATE faster + no rollback | | View kya hai? | Virtual table based on SELECT query | | Stored procedure | Precompiled SQL code block | | Normalization | Redundancy reduce karna (1NF → 2NF → 3NF → BCNF) | | Transaction ACID | Atomicity, Consistency, Isolation, Durability | | Index advantage | Fast SELECT; disadvantage = slow INSERT/UPDATE |