20 MySQL Queries for CBSE Class 12 Practical File with Output
Introduction
SQL (Structured Query Language) is a powerful tool for managing and querying data in relational databases. Here are 20 MySQL queries commonly used in CBSE Class 12 practicals, complete with their descriptions and outputs.
1. Create a Table
Query
CREATE TABLE Students (
RollNo INT PRIMARY KEY,
Name VARCHAR(50),
Class VARCHAR(10),
Marks INT,
Gender CHAR(1)
);
Output
Table created successfully.
2. Insert Records into Table
Query
INSERT INTO Students VALUES
(1, 'Amit Sharma', '12A', 85, 'M'),
(2, 'Riya Verma', '12B', 90, 'F'),
(3, 'Anil Kumar', '12A', 72, 'M'),
(4, 'Neha Singh', '12C', 88, 'F'),
(5, 'Rohit Das', '12B', 76, 'M');
Output
5 rows inserted.
3. Display All Records
Query
SELECT * FROM Students;
Output
| RollNo | Name | Class | Marks | Gender |
|---|---|---|---|---|
| 1 | Amit Sharma | 12A | 85 | M |
| 2 | Riya Verma | 12B | 90 | F |
| 3 | Anil Kumar | 12A | 72 | M |
| 4 | Neha Singh | 12C | 88 | F |
| 5 | Rohit Das | 12B | 76 | M |
4. Display Students with Marks Above 80
Query
SELECT * FROM Students WHERE Marks > 80;
Output
| RollNo | Name | Class | Marks | Gender |
|---|---|---|---|---|
| 1 | Amit Sharma | 12A | 85 | M |
| 2 | Riya Verma | 12B | 90 | F |
| 4 | Neha Singh | 12C | 88 | F |
5. Display Names of Female Students
Query
SELECT Name FROM Students WHERE Gender = 'F';
Output
| Name |
|---|
| Riya Verma |
| Neha Singh |
6. Sort Records by Marks in Descending Order
Query
SELECT * FROM Students ORDER BY Marks DESC;
Output
| RollNo | Name | Class | Marks | Gender |
|---|---|---|---|---|
| 2 | Riya Verma | 12B | 90 | F |
| 4 | Neha Singh | 12C | 88 | F |
| 1 | Amit Sharma | 12A | 85 | M |
| 5 | Rohit Das | 12B | 76 | M |
| 3 | Anil Kumar | 12A | 72 | M |
7. Count Total Students
Query
SELECT COUNT(*) AS TotalStudents FROM Students;
Output
| TotalStudents |
|---|
| 5 |
8. Display Average Marks of the Class
Query
SELECT AVG(Marks) AS AverageMarks FROM Students;
Output
| AverageMarks |
|---|
| 82.2 |
9. Display Maximum and Minimum Marks
Query
SELECT MAX(Marks) AS HighestMarks, MIN(Marks) AS LowestMarks FROM Students;
Output
| HighestMarks | LowestMarks |
|---|---|
| 90 | 72 |
10. Display Students in Class 12A
Query
SELECT * FROM Students WHERE Class = '12A';
Output
| RollNo | Name | Class | Marks | Gender |
|---|---|---|---|---|
| 1 | Amit Sharma | 12A | 85 | M |
| 3 | Anil Kumar | 12A | 72 | M |
11. Update Marks of a Student
Query
UPDATE Students SET Marks = 95 WHERE RollNo = 2;
Output
1 row updated.
12. Delete a Student Record
Query
DELETE FROM Students WHERE RollNo = 5;
Output
1 row deleted.
13. Add a New Column for Age
Query
ALTER TABLE Students ADD Age INT;
Output
Table altered successfully.
14. Display Students with Names Starting with ‘A’
Query
SELECT * FROM Students WHERE Name LIKE 'A%';
Output
| RollNo | Name | Class | Marks | Gender |
|---|---|---|---|---|
| 1 | Amit Sharma | 12A | 85 | M |
| 3 | Anil Kumar | 12A | 72 | M |
15. Find the Total Marks of Male Students
Query
SELECT SUM(Marks) AS TotalMarksMale FROM Students WHERE Gender = 'M';
Output
| TotalMarksMale |
|---|
| 157 |
16. Display Students Grouped by Class
Query
SELECT Class, COUNT(*) AS NumberOfStudents FROM Students GROUP BY Class;
Output
| Class | NumberOfStudents |
|---|---|
| 12A | 2 |
| 12B | 1 |
| 12C | 1 |
17. Create a Table for Subjects
Query
CREATE TABLE Subjects (
SubjectID INT PRIMARY KEY,
SubjectName VARCHAR(50),
MaxMarks INT
);
Output
Table created successfully.
18. Insert Records into Subjects Table
Query
INSERT INTO Subjects VALUES
(1, 'Mathematics', 100),
(2, 'Physics', 100),
(3, 'Chemistry', 100);
Output
3 rows inserted.
19. Join Students and Subjects (Example)
Query
SELECT Students.Name, Subjects.SubjectName
FROM Students
JOIN Subjects ON Students.RollNo = Subjects.SubjectID;
Output
| Name | SubjectName |
|---|---|
| Amit Sharma | Mathematics |
20. Drop the Subjects Table
Query
DROP TABLE Subjects;
Output
Table dropped successfully.
Conclusion
These queries cover essential SQL operations like table creation, data insertion, conditional queries, and joins. Practice these for your CBSE Class 12 practicals to build a solid understanding of MySQL.
Let me know if you’d like this formatted further or need additional queries!
Subscribe