Here’s a complete set of SQL queries with table structure based on the Class 12 CBSE
Computer Science syllabus. This includes:
Table creation
Data manipulation (INSERT, UPDATE, DELETE)
Data retrieval (SELECT)
Functions, conditions, sorting, grouping, etc.
✅ Sample Table: Student
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(30),
Class INT,
Marks INT,
City VARCHAR(30)
);
🔹 Insert Records
INSERT INTO Student VALUES (101, 'Ravi', 12, 87, 'Delhi');
INSERT INTO Student VALUES (102, 'Anu', 11, 92, 'Mumbai');
INSERT INTO Student VALUES (103, 'Kiran', 12, 74, 'Kolkata');
INSERT INTO Student VALUES (104, 'Meena', 11, 65, 'Chennai');
INSERT INTO Student VALUES (105, 'Raj', 12, 56, 'Delhi');
🔍 Basic SELECT Queries
-- 1. Display all records
SELECT * FROM Student;
-- 2. Display only names and marks
SELECT Name, Marks FROM Student;
-- 3. Display students from Delhi
SELECT * FROM Student WHERE City = 'Delhi';
-- 4. List students who scored above 70 marks
SELECT * FROM Student WHERE Marks > 70;
-- 5. Students of Class 12
SELECT Name FROM Student WHERE Class = 12;
⚙️Using Conditions
-- 6. Students with marks between 60 and 90
SELECT * FROM Student WHERE Marks BETWEEN 60 AND 90;
-- 7. Students whose name starts with 'R'
SELECT * FROM Student WHERE Name LIKE 'R%';
-- 8. Students not from 'Mumbai'
SELECT * FROM Student WHERE City <> 'Mumbai';
-- 9. Class is 11 or 12
SELECT * FROM Student WHERE Class IN (11, 12);
🔢 Aggregate Functions
-- 10. Count of total students
SELECT COUNT(*) FROM Student;
-- 11. Maximum marks
SELECT MAX(Marks) FROM Student;
-- 12. Average marks
SELECT AVG(Marks) FROM Student;
-- 13. Total marks of Class 12 students
SELECT SUM(Marks) FROM Student WHERE Class = 12;
🔁 GROUP BY and HAVING
-- 14. Count of students in each city
SELECT City, COUNT(*) FROM Student GROUP BY City;
-- 15. Average marks by class
SELECT Class, AVG(Marks) FROM Student GROUP BY Class;
-- 16. Cities with more than 1 student
SELECT City, COUNT(*) FROM Student GROUP BY City HAVING COUNT(*) > 1;
🔃 ORDER BY
-- 17. Display students sorted by name
SELECT * FROM Student ORDER BY Name;
-- 18. Students sorted by marks (high to low)
SELECT * FROM Student ORDER BY Marks DESC;
✏️UPDATE, DELETE Queries
-- 19. Increase marks by 5 for all students
UPDATE Student SET Marks = Marks + 5;
-- 20. Change city of student 'Meena' to 'Hyderabad'
UPDATE Student SET City = 'Hyderabad' WHERE Name = 'Meena';
-- 21. Delete student with marks less than 60
DELETE FROM Student WHERE Marks < 60;
🧱 ALTER Table
-- 22. Add phone number column
ALTER TABLE Student ADD Phone VARCHAR(15);
-- 23. Drop column phone
ALTER TABLE Student DROP COLUMN Phone;
❌ Drop Table
-- 24. Delete the entire table
DROP TABLE Student;
✅ Tables for JOIN Queries
🔹 Table 1: Student
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(30),
Class INT,
City VARCHAR(30)
);
🔹 Table 2: Marks
CREATE TABLE Marks (
RollNo INT,
Subject VARCHAR(20),
Marks INT,
FOREIGN KEY (RollNo) REFERENCES Student(RollNo)
);
📝 Sample Data
Insert into Student
INSERT INTO Student VALUES (101, 'Ravi', 12, 'Delhi');
INSERT INTO Student VALUES (102, 'Anu', 11, 'Mumbai');
INSERT INTO Student VALUES (103, 'Kiran', 12, 'Kolkata');
INSERT INTO Student VALUES (104, 'Meena', 11, 'Chennai');
Insert into Marks
INSERT INTO Marks VALUES (101, 'Maths', 87);
INSERT INTO Marks VALUES (101, 'English', 75);
INSERT INTO Marks VALUES (102, 'Maths', 92);
INSERT INTO Marks VALUES (103, 'English', 68);
INSERT INTO Marks VALUES (104, 'Maths', 80);
🔹 4. JOIN with Condition
SELECT Student.Name, Marks.Subject, Marks.Marks
FROM Student
INNER JOIN Marks ON Student.RollNo = Marks.RollNo
WHERE Marks.Marks > 80;
🔹 5. JOIN with ORDER BY
SELECT Student.Name, Marks.Subject, Marks.Marks
FROM Student
INNER JOIN Marks ON Student.RollNo = Marks.RollNo
ORDER BY Marks.Marks DESC;
✅ Explanation: Displays all joined data sorted by marks in descending order.
