🗄️ SQL INTERACTIVE LEARNING

🗄️ COMPLETE SQL INTERACTIVE GUIDE

Learn SQL through definitions, examples, tables, queries and step-by-step results

🌟 What is SQL?

SQL stands for Structured Query Language. It is used to communicate with relational databases.

SQL can be used to:
✔ Create tables
✔ Insert records
✔ Retrieve records
✔ Update records
✔ Delete records
✔ Filter data
✔ Sort data
✔ Group data
✔ Join tables
✔ Perform calculations
✔ Combine query results
✔ Manage database transactions

📚 Example Database

We will use these tables throughout the examples.

STUDENT TABLE

ID	Name	Dept_ID	Marks
101	Rahul	10	85
102	Anita	20	78
103	Arjun	10	92
104	Riya	30	68
105	Amit	20	88

DEPARTMENT TABLE

Dept_ID	Dept_Name
10	Computer Science
20	Management
30	Commerce
40	English

💡 Click a button above to learn that SQL operation.

🔍 SELECT — Retrieve Data

SELECT is used to retrieve information from one or more tables.

SELECT Name, Marks FROM STUDENT;

1

SQL opens the STUDENT table.

2

It selects the Name and Marks columns.

3

The selected values are displayed.

Rahul → 85
Anita → 78
Arjun → 92
Riya → 68
Amit → 88

🎯 WHERE — Filtering Rows

WHERE selects only those rows that satisfy a condition.

SELECT * FROM STUDENT WHERE Marks >= 80;

Step-by-step

1

Rahul: 85 ≥ 80 → TRUE → Select.

2

Anita: 78 ≥ 80 → FALSE → Ignore.

3

Arjun: 92 ≥ 80 → TRUE → Select.

4

Riya: 68 ≥ 80 → FALSE → Ignore.

5

Amit: 88 ≥ 80 → TRUE → Select.

Result: Rahul, Arjun, Amit

📋 IN and NOT IN

IN checks whether a value belongs to a list.

NOT IN checks whether a value does NOT belong to a list.

Example 1 — IN

SELECT * FROM STUDENT WHERE Dept_ID IN (10,20);

1

Read Dept_ID of every student.

2

Check whether Dept_ID is 10 OR 20.

3

Students belonging to department 10 or 20 are selected.

101 Rahul → 10 ✔
102 Anita → 20 ✔
103 Arjun → 10 ✔
105 Amit → 20 ✔

Example 2 — NOT IN

SELECT * FROM STUDENT WHERE Dept_ID NOT IN (10,20);

104 Riya → Dept_ID 30 ✔

Only students whose department is NOT 10 or 20 are returned.

IN: Matches any value in the list.
NOT IN: Excludes values in the list.

🔤 LIKE — Pattern Matching

LIKE is used to search for a pattern in text.

% = zero or more characters
_ = exactly one character

SELECT * FROM STUDENT WHERE Name LIKE 'A%';

1

Check whether the Name starts with A.

2

Anita starts with A → Match.

3

Arjun starts with A → Match.

4

Amit starts with A → Match.

Result: Anita, Arjun, Amit

↔️ BETWEEN

BETWEEN checks whether a value lies within a range. The boundary values are normally included.

SELECT * FROM STUDENT WHERE Marks BETWEEN 80 AND 90;

1

Check Rahul: 85 → between 80 and 90 → YES.

2

Check Anita: 78 → NO.

3

Check Arjun: 92 → NO.

4

Check Riya: 68 → NO.

5

Check Amit: 88 → YES.

Result: Rahul and Amit.

✨ DISTINCT

DISTINCT removes duplicate values.

SELECT DISTINCT Dept_ID FROM STUDENT;

1

Read: 10, 20, 10, 30, 20.

2

Remove duplicate 10 and 20.

Result: 10, 20, 30

↕️ ORDER BY

ORDER BY sorts the result.

SELECT Name, Marks FROM STUDENT ORDER BY Marks DESC;

92 → Arjun
88 → Amit
85 → Rahul
78 → Anita
68 → Riya

ASC → Ascending order.
DESC → Descending order.

📊 GROUP BY

GROUP BY creates groups containing rows with the same value.

SELECT Dept_ID, AVG(Marks) FROM STUDENT GROUP BY Dept_ID;

Step-by-step

1

Department 10 → 85, 92.

2

Department 20 → 78, 88.

3

Department 30 → 68.

Dept 10 Average = (85 + 92) ÷ 2 = 88.5

Dept 20 Average = (78 + 88) ÷ 2 = 83

Dept 30 Average = 68 ÷ 1 = 68

🔎 HAVING

HAVING filters groups created by GROUP BY.

SELECT Dept_ID, AVG(Marks) FROM STUDENT GROUP BY Dept_ID HAVING AVG(Marks) > 80;

1

Calculate average for each department.

2

Dept 10 → 88.5 → greater than 80 → Keep.

3

Dept 20 → 83 → greater than 80 → Keep.

4

Dept 30 → 68 → not greater than 80 → Remove.

Result: Department 10 and 20.

🧮 Aggregate Functions

Aggregate functions calculate a single result from multiple rows.

COUNT() | SUM() | AVG() | MAX() | MIN()

SELECT COUNT(*) AS TotalStudents, SUM(Marks) AS TotalMarks, AVG(Marks) AS AverageMarks, MAX(Marks) AS Highest, MIN(Marks) AS Lowest FROM STUDENT;

COUNT = 5
SUM = 85 + 78 + 92 + 68 + 88 = 411
AVG = 411 ÷ 5 = 82.2
MAX = 92
MIN = 68

➕ INSERT

INSERT adds new records to a table.

INSERT INTO STUDENT (ID,Name,Dept_ID,Marks) VALUES (106,'Suman',10,91);

1

Specify the table.

2

Specify the columns.

3

Provide values in the same column order.

New student Suman is inserted.

✏️ UPDATE

UPDATE changes existing data.

UPDATE STUDENT SET Marks = 95 WHERE ID = 101;

1

Find ID 101.

2

Old marks = 85.

3

Set Marks = 95.

Rahul's marks become 95.

⚠️ Always use WHERE carefully with UPDATE.

🗑️ DELETE

DELETE removes rows from a table.

DELETE FROM STUDENT WHERE ID = 105;

1

Find student ID 105.

2

Check the WHERE condition.

3

Delete the matching row.

⚠️ DELETE without WHERE may remove all rows.

🏗️ CREATE TABLE

CREATE TABLE creates a new table.

CREATE TABLE STUDENT( ID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, Dept_ID INT, Marks INT );

1

Create the table name STUDENT.

2

Define columns.

3

Define data types.

4

Apply constraints.

🔧 ALTER TABLE

ALTER modifies the structure of an existing table.

Add Column

ALTER TABLE STUDENT ADD Email VARCHAR(100);

Modify / Change Structure

ALTER TABLE STUDENT DROP COLUMN Email;

ALTER can be used to add, modify or remove columns, depending on the database system.

❌ DROP

DROP removes the database object itself.

DROP TABLE STUDENT;

Table structure + data → removed.

⚠️ Use carefully.

🧹 TRUNCATE

TRUNCATE removes all rows while retaining the table structure.

TRUNCATE TABLE STUDENT;

DELETE → removes selected rows.

TRUNCATE → removes all rows.

DROP → removes the table itself.

🔐 SQL CONSTRAINTS

Constraints are rules that protect the correctness and integrity of database data.

PRIMARY KEY • FOREIGN KEY • UNIQUE • NOT NULL • CHECK • DEFAULT

CREATE TABLE STUDENT( ID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, Email VARCHAR(100) UNIQUE, Age INT CHECK(Age >= 18), City VARCHAR(30) DEFAULT 'Kolkata' );

PRIMARY KEY → uniquely identifies a row.
FOREIGN KEY → creates relationship between tables.
UNIQUE → prevents duplicate values.
NOT NULL → value must be provided.
CHECK → validates a condition.
DEFAULT → provides a default value.

🔗 ALL IMPORTANT SQL JOINS

A JOIN combines related information from two or more tables. We will use:

STUDENT and DEPARTMENT.

Relationship: STUDENT.Dept_ID = DEPARTMENT.Dept_ID

1️⃣ INNER JOIN

Returns only rows that have matching values in both tables.

SELECT S.Name, D.Dept_Name FROM STUDENT S INNER JOIN DEPARTMENT D ON S.Dept_ID = D.Dept_ID;

1

Rahul Dept_ID 10 matches Computer Science.

2

Anita Dept_ID 20 matches Management.

3

Arjun Dept_ID 10 matches Computer Science.

4

Riya Dept_ID 30 matches Commerce.

5

Amit Dept_ID 20 matches Management.

Only matching records are returned.

2️⃣ LEFT JOIN

Returns ALL rows from the left table and matching rows from the right table.

SELECT S.Name, D.Dept_Name FROM STUDENT S LEFT JOIN DEPARTMENT D ON S.Dept_ID = D.Dept_ID;

All STUDENT records are retained. If a student has no matching department, the department columns become NULL.

3️⃣ RIGHT JOIN

Returns ALL rows from the right table and matching rows from the left table.

SELECT S.Name, D.Dept_Name FROM STUDENT S RIGHT JOIN DEPARTMENT D ON S.Dept_ID = D.Dept_ID;

All DEPARTMENT records are retained. Department 40 — English has no student, so the student columns become NULL.

4️⃣ FULL OUTER JOIN

Returns all matching and non-matching rows from both tables.

SELECT S.Name, D.Dept_Name FROM STUDENT S FULL OUTER JOIN DEPARTMENT D ON S.Dept_ID = D.Dept_ID;

Students without departments are retained.
Departments without students are also retained.
Depending on the DBMS, FULL OUTER JOIN may need alternative syntax if it is not supported directly.

5️⃣ CROSS JOIN

CROSS JOIN produces the Cartesian product. Every row of the first table is combined with every row of the second table.

SELECT S.Name, D.Dept_Name FROM STUDENT S CROSS JOIN DEPARTMENT D;

Number of results = Rows in STUDENT × Rows in DEPARTMENT

5 × 4 = 20 rows

⚠️ CROSS JOIN can produce a very large result.

6️⃣ SELF JOIN

A SELF JOIN joins a table with itself. Example: an EMPLOYEE table containing employee and manager information.

SELECT E.Name AS Employee, M.Name AS Manager FROM EMPLOYEE E JOIN EMPLOYEE M ON E.Manager_ID = M.Employee_ID;

The same EMPLOYEE table is treated as two logical tables:
E → Employee
M → Manager

### JOIN QUICK REVISION
INNER JOIN → Matching rows.
LEFT JOIN → All left + matching right.
RIGHT JOIN → All right + matching left.
FULL OUTER JOIN → All rows from both sides.
CROSS JOIN → Every possible combination.
SELF JOIN → Table joined with itself.

∪ ALL SQL SET OPERATIONS

Set operators combine the results of two or more SELECT statements. The commonly taught SQL set operations are:

UNION • UNION ALL • INTERSECT • EXCEPT

Example Tables

BCA	BBA
Rahul	Anita
Arjun	Amit
Riya	Rahul

1️⃣ UNION

UNION combines two query results and removes duplicates.

SELECT Name FROM BCA UNION SELECT Name FROM BBA;

1

BCA names → Rahul, Arjun, Riya.

2

BBA names → Anita, Amit, Rahul.

3

Combine both lists.

4

Remove duplicate Rahul.

Rahul, Arjun, Riya, Anita, Amit

2️⃣ UNION ALL

UNION ALL combines results but does NOT remove duplicates.

SELECT Name FROM BCA UNION ALL SELECT Name FROM BBA;

Rahul
Arjun
Riya
Anita
Amit
Rahul ← duplicate retained

3️⃣ INTERSECT

INTERSECT returns only values appearing in both query results.

SELECT Name FROM BCA INTERSECT SELECT Name FROM BBA;

1

Read BCA names.

2

Read BBA names.

3

Find common values.

Common value: Rahul

4️⃣ EXCEPT

EXCEPT returns rows from the first query that do not appear in the second query.

SELECT Name FROM BCA EXCEPT SELECT Name FROM BBA;

1

Start with BCA: Rahul, Arjun, Riya.

2

Find values also appearing in BBA.

3

Remove Rahul.

Result: Arjun, Riya

Some database systems use MINUS instead of EXCEPT.

### SET OPERATION RULE For UNION, UNION ALL, INTERSECT and EXCEPT, the SELECT queries generally need compatible numbers of columns and compatible data types.

📦 SUBQUERY

A subquery is a query inside another query.

SELECT Name, Marks FROM STUDENT WHERE Marks > ( SELECT AVG(Marks) FROM STUDENT );

1

Inner query calculates AVG(Marks).

2

Average = 82.2.

3

Compare every student's marks with 82.2.

Rahul → 85 ✔
Arjun → 92 ✔
Amit → 88 ✔

❔ NULL

NULL means a value is missing, unknown or unavailable. NULL is not the same as zero.

SELECT * FROM STUDENT WHERE Email IS NULL;

Use:
IS NULL

IS NOT NULL

Do not normally use: Email = NULL

🔀 CASE

CASE allows conditional logic inside SQL.

SELECT Name, Marks, CASE WHEN Marks >= 90 THEN 'Excellent' WHEN Marks >= 80 THEN 'Very Good' WHEN Marks >= 60 THEN 'Good' ELSE 'Needs Improvement' END AS Performance FROM STUDENT;

92 → Excellent
88 → Very Good
85 → Very Good
78 → Good
68 → Good

👁️ VIEW

A VIEW is a virtual table created from a query.

CREATE VIEW TopStudents AS SELECT Name, Marks FROM STUDENT WHERE Marks >= 80;

SELECT * FROM TopStudents;

Rahul → 85
Arjun → 92
Amit → 88

⚡ INDEX

An INDEX can improve data retrieval performance for appropriate queries.

CREATE INDEX idx_student_name ON STUDENT(Name);

The database can use the index to locate values in the indexed column more efficiently.

Indexes require storage and can add overhead when data is inserted, updated or deleted.

🔄 TRANSACTION

A transaction is a logical unit of database work.

START TRANSACTION; UPDATE STUDENT SET Marks = 95 WHERE ID = 101; SAVEPOINT S1; COMMIT;

COMMIT → Save transaction changes.

ROLLBACK → Undo transaction changes.

SAVEPOINT → Create a point inside a transaction.

❓ EXTRA SQL BIT QUESTIONS & ANSWERS

Click a question to reveal its answer.

Q1. What is SQL?

SQL stands for Structured Query Language and is used to work with relational databases.

Q2. Which command retrieves data?

SELECT.

Q3. Which clause filters rows?

WHERE.

Q4. Which clause sorts records?

ORDER BY.

Q5. What does IN do?

IN checks whether a value belongs to a specified list.

Q6. What does NOT IN do?

NOT IN excludes values belonging to the specified list.

Q7. What does LIKE do?

LIKE performs pattern matching.

Q8. What does DISTINCT do?

DISTINCT removes duplicate values from the result.

Q9. What is GROUP BY?

GROUP BY groups rows having the same values.

Q10. What is HAVING?

HAVING filters groups produced by GROUP BY.

Q11. What is INNER JOIN?

INNER JOIN returns matching rows from both tables.

Q12. What is LEFT JOIN?

LEFT JOIN returns all rows from the left table and matching rows from the right table.

Q13. What is RIGHT JOIN?

RIGHT JOIN returns all rows from the right table and matching rows from the left table.

Q14. What is FULL OUTER JOIN?

It returns matching and non-matching rows from both tables.

Q15. What is CROSS JOIN?

CROSS JOIN creates the Cartesian product of two tables.

Q16. What is UNION?

UNION combines query results and removes duplicate rows.

Q17. What is UNION ALL?

UNION ALL combines query results while retaining duplicates.

Q18. What is INTERSECT?

INTERSECT returns rows common to both query results.

Q19. What is EXCEPT?

EXCEPT returns rows from the first query that are not in the second query.

Q20. What is a Subquery?

A Subquery is a query nested inside another query.

Q21. What is the difference between DELETE and TRUNCATE?

DELETE can remove selected rows using a condition. TRUNCATE removes all rows while retaining the table structure.

Q22. What is DROP?

DROP removes a database object such as a table.

Q23. Name five aggregate functions.

COUNT(), SUM(), AVG(), MAX() and MIN().

Q24. What is a Primary Key?

A Primary Key uniquely identifies each row in a table.

Q25. What is a Foreign Key?

A Foreign Key references a key in another table and helps represent relationships between tables.

Q26. Which set operation removes duplicates?

UNION removes duplicate rows. UNION ALL retains them.

Q27. Which set operation finds common rows?

INTERSECT.

Q28. Which set operation finds rows only in the first query?

EXCEPT (or MINUS in some DBMSs).

Q29. What is a VIEW?

A VIEW is a virtual table based on a SQL query.

Q30. What is an INDEX?

An INDEX is a database structure that can improve the speed of suitable data searches.

🎯 SQL QUICK REVISION

SELECT → Retrieve
WHERE → Filter Rows
IN → Match List
NOT IN → Exclude List
LIKE → Pattern Search
BETWEEN → Range
GROUP BY → Create Groups
HAVING → Filter Groups
JOIN → Combine Tables
UNION → Combine + Remove Duplicates
UNION ALL → Combine + Keep Duplicates
INTERSECT → Common Rows
EXCEPT → First Query − Second Query

B.K. PAUL

Bhairab Ganguly College

Database Management System • SQL Interactive Learning

SQL • DBMS • Interactive Visual Learning

© 2026 B.K. PAUL • Bhairab Ganguly College