9.1 Introduction

What is SQL?

SQL (Structured Query Language) is a standard language used to:

• Store data
• Retrieve data
• Modify data
• Control access to data in a relational database

📌 SQL works on tables (relations).

---

Why SQL is Needed?

• Handle large amounts of data
• Retrieve specific information
• Maintain data integrity
• Support multi-user access

---

9.2 Structured Query Language (SQL)

Categories of SQL Commands (VERY IMPORTANT)

Category	Purpose	Commands
DDL	Define structure	CREATE, ALTER, DROP
DML	Manipulate data	INSERT, UPDATE, DELETE
DQL	Query data	SELECT
DCL	Control access	GRANT, REVOKE

📌 CBSE focuses mainly on DDL, DML, DQL

---

9.3 Data Types and Constraints in MySQL

---

🔹 Common MySQL Data Types

Data Type	Description
INT	Integer values
FLOAT	Decimal values
CHAR(n)	Fixed length string
VARCHAR(n)	Variable length string
DATE	Date (YYYY-MM-DD)

---

🔹 Constraints (VERY IMPORTANT)

Constraint	Meaning
PRIMARY KEY	Unique + NOT NULL
UNIQUE	No duplicate values
NOT NULL	Cannot be empty
DEFAULT	Assigns default value
CHECK	Condition check (conceptual)
FOREIGN KEY	Links tables

---

9.4 SQL for Data Definition (DDL)

🔹 CREATE TABLE

CREATE TABLE STUDENT (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(20),
    Class INT,
    Marks INT
);

---

🔹 SHOW TABLE

DESC STUDENT;

---

🔹 DROP TABLE

DROP TABLE STUDENT;

---

🔹 ALTER TABLE

Add Column

ALTER TABLE STUDENT ADD Grade CHAR(1);

Modify Column

ALTER TABLE STUDENT MODIFY Name VARCHAR(30);

Drop Column

ALTER TABLE STUDENT DROP Grade;

---

9.5 SQL for Data Manipulation (DML)

---

🔹 INSERT INTO

INSERT INTO STUDENT VALUES
(101, 'Amit', 12, 85);

INSERT INTO STUDENT (RollNo, Name)
VALUES (102, 'Ravi');

---

🔹 INSERT Multiple Records

INSERT INTO STUDENT VALUES
(103, 'Suman', 12, 90),
(104, 'Kiran', 11, 78);

---

9.6 SQL for Data Query (SELECT)

---

🔹 Display All Records

SELECT * FROM STUDENT;

---

🔹 Display Specific Columns

SELECT Name, Marks FROM STUDENT;

---

🔹 WHERE Clause

SELECT * FROM STUDENT WHERE Marks > 80;

---

🔹 Logical Operators

SELECT * FROM STUDENT
WHERE Class = 12 AND Marks > 75;

---

🔹 BETWEEN

SELECT * FROM STUDENT
WHERE Marks BETWEEN 70 AND 90;

---

🔹 IN

SELECT * FROM STUDENT
WHERE RollNo IN (101, 103);

---

🔹 LIKE

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

---

🔹 ORDER BY

SELECT * FROM STUDENT
ORDER BY Marks DESC;

---

9.7 Data Updation and Deletion

---

🔹 UPDATE

UPDATE STUDENT
SET Marks = 95
WHERE RollNo = 101;

---

🔹 UPDATE Multiple Columns

UPDATE STUDENT
SET Marks = 88, Class = 11
WHERE Name = 'Kiran';

---

🔹 DELETE

DELETE FROM STUDENT
WHERE RollNo = 104;

---

🔹 DELETE All Records

DELETE FROM STUDENT;

📌 Table remains, records deleted.

---

9.8 Functions in SQL

---

🔹 Aggregate Functions (VERY IMPORTANT)

Function	Purpose
COUNT()	Number of rows
SUM()	Total
AVG()	Average
MAX()	Maximum
MIN()	Minimum

---

Examples

SELECT COUNT(*) FROM STUDENT;

SELECT AVG(Marks) FROM STUDENT;

SELECT MAX(Marks) FROM STUDENT;

---

9.9 GROUP BY Clause in SQL

Used to group rows having similar values.

---

Example

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

---

GROUP BY with HAVING

SELECT Class, COUNT(*)
FROM STUDENT
GROUP BY Class
HAVING COUNT(*) > 2;

📌 HAVING works on groups 📌 WHERE works on rows

---

9.10 Operations on Relations

---

🔹 UNION

SELECT Name FROM STUDENT1
UNION
SELECT Name FROM STUDENT2;

📌 Removes duplicates

---

🔹 UNION ALL

SELECT Name FROM STUDENT1
UNION ALL
SELECT Name FROM STUDENT2;

📌 Keeps duplicates

---

🔹 INTERSECT (Conceptual – MySQL doesn’t support directly)

Common records between two tables.

---

🔹 MINUS / EXCEPT (Conceptual)

Records in first table but not in second.

---

9.11 Using Two Relations in a Query (JOIN)

---

Example Tables

STUDENT

RollNo	Name

MARKS | RollNo | Marks |

---

🔹 INNER JOIN

SELECT STUDENT.Name, MARKS.Marks
FROM STUDENT
INNER JOIN MARKS
ON STUDENT.RollNo = MARKS.RollNo;

---

🔹 LEFT JOIN

SELECT STUDENT.Name, MARKS.Marks
FROM STUDENT
LEFT JOIN MARKS
ON STUDENT.RollNo = MARKS.RollNo;

---

🔹 RIGHT JOIN

SELECT STUDENT.Name, MARKS.Marks
FROM STUDENT
RIGHT JOIN MARKS
ON STUDENT.RollNo = MARKS.RollNo;

---

📝 NCERT EXAM SUMMARY (MUST MEMORISE)

• SQL works on tables
• DDL → structure
• DML → data
• SELECT → query
• WHERE filters rows
• GROUP BY groups data
• HAVING filters groups
• JOIN combines tables
• PRIMARY KEY = unique + not null
• FOREIGN KEY links tables

---

Perfect 👍 Below are 25 BOARD-LEVEL SQL QUESTIONS WITH ANSWERS, exactly in CBSE Class 12 format.

✔ NCERT-aligned ✔ Practical-exam ready ✔ Uses reference tables wherever required ✔ Covers DDL, DML, SELECT, WHERE, GROUP BY, JOIN, functions

---

📘 Chapter 9: SQL

🧑‍💻 25 Board-Level SQL Queries with Answers

---

🔰 REFERENCE TABLES (Used in Questions)

STUDENT

RollNo	Name	Class	Marks	City
101	Amit	12	85	Delhi
102	Ravi	12	78	Mumbai
103	Suman	11	92	Delhi
104	Kiran	12	65	Chennai
105	Neha	11	88	Mumbai

---

FEES

RollNo	Amount
101	45000
102	42000
103	46000
105	40000

---

🟦 SECTION A: Basic SQL Queries

---

Q1. Display all records from STUDENT table.

SELECT * FROM STUDENT;

---

Q2. Display names and marks of all students.

SELECT Name, Marks FROM STUDENT;

---

Q3. Display students studying in Class 12.

SELECT * FROM STUDENT WHERE Class = 12;

---

Q4. Display students who scored more than 80 marks.

SELECT * FROM STUDENT WHERE Marks > 80;

---

Q5. Display students belonging to Delhi city.

SELECT * FROM STUDENT WHERE City = 'Delhi';

---

🟦 SECTION B: Conditional Queries

---

Q6. Display students of Class 11 with marks greater than 85.

SELECT * FROM STUDENT
WHERE Class = 11 AND Marks > 85;

---

Q7. Display students whose marks are between 70 and 90.

SELECT * FROM STUDENT
WHERE Marks BETWEEN 70 AND 90;

---

Q8. Display students whose names start with ‘A’.

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

---

Q9. Display students whose city is Delhi or Mumbai.

SELECT * FROM STUDENT
WHERE City IN ('Delhi', 'Mumbai');

---

Q10. Display student details in descending order of marks.

SELECT * FROM STUDENT
ORDER BY Marks DESC;

---

🟦 SECTION C: Aggregate Functions

---

Q11. Find the total number of students.

SELECT COUNT(*) FROM STUDENT;

---

Q12. Find the highest marks obtained.

SELECT MAX(Marks) FROM STUDENT;

---

Q13. Find the lowest marks obtained.

SELECT MIN(Marks) FROM STUDENT;

---

Q14. Find the average marks of students.

SELECT AVG(Marks) FROM STUDENT;

---

Q15. Find total marks scored by all students.

SELECT SUM(Marks) FROM STUDENT;

---

🟦 SECTION D: GROUP BY & HAVING

---

Q16. Display average marks class-wise.

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

---

Q17. Display number of students in each class.

SELECT Class, COUNT(*)
FROM STUDENT
GROUP BY Class;

---

Q18. Display classes having more than 2 students.

SELECT Class, COUNT(*)
FROM STUDENT
GROUP BY Class
HAVING COUNT(*) > 2;

---

🟦 SECTION E: Data Updation & Deletion

---

Q19. Increase marks of Amit by 5.

UPDATE STUDENT
SET Marks = Marks + 5
WHERE Name = 'Amit';

---

Q20. Delete the record of student RollNo 104.

DELETE FROM STUDENT
WHERE RollNo = 104;

---

🟦 SECTION F: Queries Using Two Tables (JOIN)

---

Q21. Display names of students who have paid fees.

SELECT STUDENT.Name
FROM STUDENT
INNER JOIN FEES
ON STUDENT.RollNo = FEES.RollNo;

---

Q22. Display student name and fee amount.

SELECT STUDENT.Name, FEES.Amount
FROM STUDENT
INNER JOIN FEES
ON STUDENT.RollNo = FEES.RollNo;

---

Q23. Display students who have NOT paid fees.

SELECT Name
FROM STUDENT
WHERE RollNo NOT IN (SELECT RollNo FROM FEES);

---

Q24. Display total fees collected.

SELECT SUM(Amount) FROM FEES;

---

Q25. Display students with their marks and fees (even if fees not paid).

SELECT STUDENT.Name, STUDENT.Marks, FEES.Amount
FROM STUDENT
LEFT JOIN FEES
ON STUDENT.RollNo = FEES.RollNo;

---

✅ EXAM TIPS (VERY IMPORTANT)

• WHERE → filters rows
• GROUP BY → groups rows
• HAVING → filters groups
• INNER JOIN → matching records
• LEFT JOIN → all left table records
• BETWEEN, IN, LIKE → favourite CBSE tools

---