MySQL Practice Questions for CBSE Class 12 IP Students

Published on November 11, 2025 by @mritxperts
MySQL Practice Questions for CBSE Class 12 IP Students

Master MySQL for your CBSE Class 12 Informatics Practices exam with these comprehensive practice questions, sample tables, and detailed solutions.

Introduction

Are you preparing for your CBSE Class 12 Informatics Practices (IP) exam and feeling overwhelmed with MySQL queries? Don’t worry! This complete guide will help you master MySQL concepts including Joins, Aggregate Functions, String Functions, Date Functions, and much more.

At Itxperts, we understand the challenges students face while learning database concepts. That’s why we’ve created this comprehensive practice set with real-world examples and detailed explanations.

Sample Database Tables

Let’s start by creating three interconnected tables that simulate a school management system. These tables will help you practice various SQL queries and joins.

Table 1: STUDENTS

CREATE TABLE STUDENTS (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Class VARCHAR(5),
    Section CHAR(1),
    DOB DATE,
    City VARCHAR(30),
    Marks INT
);

INSERT INTO STUDENTS VALUES
(101, 'Aarav Sharma', '12', 'A', '2007-05-15', 'Delhi', 92),
(102, 'Diya Patel', '12', 'A', '2007-08-22', 'Mumbai', 88),
(103, 'Rohan Kumar', '12', 'B', '2007-03-10', 'Bangalore', 95),
(104, 'Ananya Singh', '12', 'A', '2007-11-05', 'Delhi', 78),
(105, 'Arjun Reddy', '12', 'B', '2007-07-18', 'Hyderabad', 85),
(106, 'Priya Gupta', '12', 'C', '2007-09-30', 'Mumbai', 91),
(107, 'Karan Mehta', '12', 'C', '2007-02-14', 'Delhi', 82),
(108, 'Ishita Jain', '12', 'B', '2007-12-25', 'Pune', 89);

Table 2: LIBRARY

CREATE TABLE LIBRARY (
    BookID INT PRIMARY KEY,
    BookName VARCHAR(60),
    Author VARCHAR(40),
    Publisher VARCHAR(40),
    Price DECIMAL(7,2),
    Quantity INT,
    StudentID INT,
    IssueDate DATE,
    FOREIGN KEY (StudentID) REFERENCES STUDENTS(StudentID)
);

INSERT INTO LIBRARY VALUES
(201, 'Python Programming', 'John Smith', 'Tech Publications', 450.00, 5, 101, '2024-10-15'),
(202, 'Data Structures', 'Robert Sedgewick', 'Pearson', 650.00, 3, 103, '2024-10-20'),
(203, 'Database Management', 'Raghu Ramakrishnan', 'McGraw Hill', 550.00, 4, 102, '2024-10-18'),
(204, 'Web Development', 'Jennifer Robbins', 'O Reilly', 720.00, 2, 105, '2024-11-01'),
(205, 'Artificial Intelligence', 'Stuart Russell', 'Prentice Hall', 890.00, 3, 103, '2024-10-25'),
(206, 'Computer Networks', 'Andrew Tanenbaum', 'Pearson', 600.00, 4, 106, '2024-11-05'),
(207, 'Operating Systems', 'Abraham Silberschatz', 'Wiley', 750.00, 2, NULL, NULL),
(208, 'Machine Learning', 'Tom Mitchell', 'McGraw Hill', 820.00, 3, NULL, NULL);

Table 3: COURSES

CREATE TABLE COURSES (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(50),
    Duration INT,
    Fees DECIMAL(8,2),
    StudentID INT,
    FOREIGN KEY (StudentID) REFERENCES STUDENTS(StudentID)
);

INSERT INTO COURSES VALUES
(301, 'Python Programming', 6, 15000.00, 101),
(302, 'Web Development', 8, 18000.00, 102),
(303, 'Data Science', 10, 25000.00, 103),
(304, 'Artificial Intelligence', 12, 30000.00, 104),
(305, 'Machine Learning', 10, 28000.00, 105),
(306, 'Cyber Security', 8, 20000.00, 106),
(307, 'Mobile App Development', 6, 16000.00, 107);

Practice Questions with Solutions

Section A: Basic SELECT Queries

Q1. Display all students from Delhi.

SELECT * FROM STUDENTS WHERE City = 'Delhi';

Expected Output:

StudentIDNameClassSectionDOBCityMarks
101Aarav Sharma12A2007-05-15Delhi92
104Ananya Singh12A2007-11-05Delhi78
107Karan Mehta12C2007-02-14Delhi82

Q2. Display names of students who scored more than 85 marks.

SELECT Name, Marks FROM STUDENTS WHERE Marks > 85;

Answer: This query uses the WHERE clause to filter records based on the condition.

Q3. Display book names with price between ₹500 and ₹700.

SELECT BookName, Price FROM LIBRARY 
WHERE Price BETWEEN 500 AND 700;

💡 Itxperts Tip: The BETWEEN operator is inclusive, meaning it includes both boundary values (500 and 700).

Q4. Display student names in alphabetical order.

SELECT Name FROM STUDENTS ORDER BY Name;

Q5. Display unique cities from the STUDENTS table.

SELECT DISTINCT City FROM STUDENTS;

Explanation: The DISTINCT keyword removes duplicate values from the result set.

Section B: Aggregate Functions

Need expert guidance? Itxperts offers personalized tutoring for CBSE Class 12 IP students!

Q6. Count the total number of students.

SELECT COUNT(*) AS TotalStudents FROM STUDENTS;

Output: 8

Q7. Find the average marks of all students.

SELECT AVG(Marks) AS AverageMarks FROM STUDENTS;

Output: 87.5

Q8. Find the highest and lowest book price.

SELECT MAX(Price) AS HighestPrice, MIN(Price) AS LowestPrice 
FROM LIBRARY;

Q9. Calculate total fees collected from all courses.

SELECT SUM(Fees) AS TotalFees FROM COURSES;

Q10. Count the number of books issued to students.

SELECT COUNT(StudentID) AS BooksIssued FROM LIBRARY 
WHERE StudentID IS NOT NULL;

Explanation: We use IS NOT NULL to count only issued books (excluding books in stock).

Section C: GROUP BY and HAVING Clause

Q11. Display city-wise count of students.

SELECT City, COUNT(*) AS StudentCount 
FROM STUDENTS 
GROUP BY City;

Expected Output:

CityStudentCount
Delhi3
Mumbai2
Bangalore1
Hyderabad1
Pune1

Q12. Display section-wise average marks.

SELECT Section, AVG(Marks) AS AvgMarks 
FROM STUDENTS 
GROUP BY Section;

Q13. Display publisher-wise total book quantity.

SELECT Publisher, SUM(Quantity) AS TotalBooks 
FROM LIBRARY 
GROUP BY Publisher;

Q14. Display cities having more than 1 student.

SELECT City, COUNT(*) AS StudentCount 
FROM STUDENTS 
GROUP BY City 
HAVING COUNT(*) > 1;

📌 Itxperts Note: HAVING is used with GROUP BY to filter grouped records, while WHERE filters individual records.

Q15. Display publishers having average price more than ₹600.

SELECT Publisher, AVG(Price) AS AvgPrice 
FROM LIBRARY 
GROUP BY Publisher 
HAVING AVG(Price) > 600;

Section D: String Functions

Q16. Display student names in uppercase.

SELECT UPPER(Name) AS NameInCaps FROM STUDENTS;

Q17. Display the first 4 characters of book names.

SELECT LEFT(BookName, 4) AS ShortName FROM LIBRARY;

Q18. Display the length of each student’s name.

SELECT Name, LENGTH(Name) AS NameLength FROM STUDENTS;

Q19. Display book names containing the word ‘Data’.

SELECT BookName FROM LIBRARY WHERE BookName LIKE '%Data%';

Explanation: The % wildcard matches any sequence of characters.

Q20. Concatenate student name with their city.

SELECT CONCAT(Name, ' - ', City) AS StudentInfo FROM STUDENTS;

Sample Output: “Aarav Sharma – Delhi”

Section E: Date Functions

Struggling with date functions? Join Itxperts online coaching program for detailed explanations!

Q21. Display the current date.

SELECT CURDATE() AS CurrentDate;

Q22. Display the year of birth for all students.

SELECT Name, YEAR(DOB) AS BirthYear FROM STUDENTS;

Q23. Display the month name when books were issued.

SELECT BookName, MONTHNAME(IssueDate) AS IssueMonth 
FROM LIBRARY 
WHERE IssueDate IS NOT NULL;

Q24. Calculate the age of students.

SELECT Name, YEAR(CURDATE()) - YEAR(DOB) AS Age FROM STUDENTS;

Q25. Display books issued in October 2024.

SELECT BookName, IssueDate FROM LIBRARY 
WHERE MONTH(IssueDate) = 10 AND YEAR(IssueDate) = 2024;

Section F: Mathematical Functions

Q26. Display book prices rounded to the nearest integer.

SELECT BookName, ROUND(Price) AS RoundedPrice FROM LIBRARY;

Q27. Calculate 10% discount on course fees.

SELECT CourseName, Fees, ROUND(Fees * 0.9, 2) AS DiscountedFees 
FROM COURSES;

Q28. Display the square of marks (marks²).

SELECT Name, Marks, POW(Marks, 2) AS MarksSquare FROM STUDENTS;

Q29. Display the absolute difference between marks and 100.

SELECT Name, Marks, ABS(100 - Marks) AS Difference FROM STUDENTS;

Q30. Display the square root of book prices.

SELECT BookName, Price, ROUND(SQRT(Price), 2) AS SqrtPrice 
FROM LIBRARY;

Section G: JOINS (Most Important for CBSE!)

This section is crucial for scoring well in your IP exam. Itxperts specializes in making complex concepts simple!

Understanding JOINS

Joins are used to combine rows from two or more tables based on a related column. There are different types of joins:

  1. INNER JOIN – Returns matching records from both tables
  2. LEFT JOIN – Returns all records from the left table and matching records from the right
  3. RIGHT JOIN – Returns all records from the right table and matching records from the left

Q31. Display student names with the books they have issued. (INNER JOIN)

SELECT S.Name, L.BookName, L.IssueDate
FROM STUDENTS S
INNER JOIN LIBRARY L ON S.StudentID = L.StudentID;

Expected Output:

NameBookNameIssueDate
Aarav SharmaPython Programming2024-10-15
Rohan KumarData Structures2024-10-20
Diya PatelDatabase Management2024-10-18
Arjun ReddyWeb Development2024-11-01
Rohan KumarArtificial Intelligence2024-10-25
Priya GuptaComputer Networks2024-11-05

Explanation: INNER JOIN returns only students who have issued books.

Q32. Display all students and their issued books (if any). (LEFT JOIN)

SELECT S.Name, L.BookName, L.IssueDate
FROM STUDENTS S
LEFT JOIN LIBRARY L ON S.StudentID = L.StudentID;

Explanation: LEFT JOIN returns all students, even those who haven’t issued any books (NULL values).

Q33. Display student names with their enrolled courses.

SELECT S.Name, C.CourseName, C.Duration, C.Fees
FROM STUDENTS S
INNER JOIN COURSES C ON S.StudentID = C.StudentID;

Q34. Display students who have issued books along with their marks and book prices.

SELECT S.Name, S.Marks, L.BookName, L.Price
FROM STUDENTS S
INNER JOIN LIBRARY L ON S.StudentID = L.StudentID
ORDER BY S.Marks DESC;

Q35. Display all books and the students who issued them (if any). (RIGHT JOIN)

SELECT L.BookName, S.Name, L.IssueDate
FROM STUDENTS S
RIGHT JOIN LIBRARY L ON S.StudentID = L.StudentID;

Explanation: This shows all books, including those not issued to anyone (Operating Systems and Machine Learning).

Q36. Display student names from Delhi who have enrolled in courses.

SELECT S.Name, S.City, C.CourseName
FROM STUDENTS S
INNER JOIN COURSES C ON S.StudentID = C.StudentID
WHERE S.City = 'Delhi';

Q37. Display students who scored more than 85 and the books they issued.

SELECT S.Name, S.Marks, L.BookName
FROM STUDENTS S
INNER JOIN LIBRARY L ON S.StudentID = L.StudentID
WHERE S.Marks > 85;

🎯 Itxperts Pro Tip: When using joins with WHERE clause, apply the WHERE condition after the JOIN to filter the combined result.

Q38. Display total fees paid by each student for courses.

SELECT S.Name, SUM(C.Fees) AS TotalFees
FROM STUDENTS S
INNER JOIN COURSES C ON S.StudentID = C.StudentID
GROUP BY S.Name;

Q39. Display students with their course names and book names.

SELECT S.Name, C.CourseName, L.BookName
FROM STUDENTS S
INNER JOIN COURSES C ON S.StudentID = C.StudentID
INNER JOIN LIBRARY L ON S.StudentID = L.StudentID;

Explanation: This is a three-table join showing complete student information.

Q40. Count how many books each student has issued.

SELECT S.Name, COUNT(L.BookID) AS BooksIssued
FROM STUDENTS S
LEFT JOIN LIBRARY L ON S.StudentID = L.StudentID
GROUP BY S.Name;

Section H: Advanced Queries (Challenge Questions)

Ready to take your skills to the next level? Itxperts offers advanced MySQL courses for competitive programming!

Q41. Display students who have not issued any books.

SELECT S.Name
FROM STUDENTS S
LEFT JOIN LIBRARY L ON S.StudentID = L.StudentID
WHERE L.BookID IS NULL;

Q42. Display the most expensive book issued to students.

SELECT S.Name, L.BookName, L.Price
FROM STUDENTS S
INNER JOIN LIBRARY L ON S.StudentID = L.StudentID
WHERE L.Price = (SELECT MAX(Price) FROM LIBRARY WHERE StudentID IS NOT NULL);

Q43. Display students whose course fees are higher than average.

SELECT S.Name, C.CourseName, C.Fees
FROM STUDENTS S
INNER JOIN COURSES C ON S.StudentID = C.StudentID
WHERE C.Fees > (SELECT AVG(Fees) FROM COURSES);

Q44. Display section-wise total course fees.

SELECT S.Section, SUM(C.Fees) AS TotalFees
FROM STUDENTS S
INNER JOIN COURSES C ON S.StudentID = C.StudentID
GROUP BY S.Section;

Q45. Display publisher-wise book count and average price for books priced above ₹500.

SELECT Publisher, COUNT(*) AS BookCount, AVG(Price) AS AvgPrice
FROM LIBRARY
WHERE Price > 500
GROUP BY Publisher;

Section I: Nested Queries (Subqueries)

Q46. Display students who scored more than the average marks.

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

Q47. Display books more expensive than ‘Python Programming’.

SELECT BookName, Price FROM LIBRARY
WHERE Price > (SELECT Price FROM LIBRARY WHERE BookName = 'Python Programming');

Q48. Display students enrolled in the most expensive course.

SELECT S.Name, C.CourseName, C.Fees
FROM STUDENTS S
INNER JOIN COURSES C ON S.StudentID = C.StudentID
WHERE C.Fees = (SELECT MAX(Fees) FROM COURSES);

Q49. Display books issued by students from Mumbai.

SELECT L.BookName, S.Name, S.City
FROM LIBRARY L
INNER JOIN STUDENTS S ON L.StudentID = S.StudentID
WHERE S.StudentID IN (SELECT StudentID FROM STUDENTS WHERE City = 'Mumbai');

Q50. Display students who have issued more than one book.

SELECT S.Name, COUNT(L.BookID) AS BooksIssued
FROM STUDENTS S
INNER JOIN LIBRARY L ON S.StudentID = L.StudentID
GROUP BY S.Name
HAVING COUNT(L.BookID) > 1;

Important Exam Tips from Itxperts

1. Understand the Table Structure First

Before attempting any query, carefully read the table structure, column names, and relationships.

2. Master the Order of Execution

SQL queries execute in this order:

  • FROM and JOINs
  • WHERE
  • GROUP BY
  • HAVING
  • SELECT
  • ORDER BY

3. Practice JOIN Concepts Thoroughly

Joins carry significant marks in CBSE exams. Make sure you understand:

  • When to use INNER JOIN vs LEFT JOIN
  • How to join multiple tables
  • Combining JOINs with WHERE and GROUP BY

4. Remember Function Syntax

Common functions you must know:

  • Aggregate: COUNT(), SUM(), AVG(), MAX(), MIN()
  • String: UPPER(), LOWER(), LEFT(), RIGHT(), CONCAT(), LENGTH()
  • Date: CURDATE(), YEAR(), MONTH(), MONTHNAME(), DATEDIFF()
  • Math: ROUND(), POW(), SQRT(), ABS()

5. Use Aliases for Better Readability

Always use table aliases (S, L, C) when working with joins to make queries cleaner.

Common Mistakes to Avoid

❌ Forgetting the WHERE clause when filtering after JOIN ❌ Using HAVING instead of WHERE for non-aggregated conditions ❌ Not using GROUP BY when using aggregate functions with other columns ❌ Incorrect JOIN syntax – Remember: table1 JOIN table2 ON condition ❌ Missing IS NULL or IS NOT NULL when checking for null values

Practice Exercise for Self-Assessment

Try these questions on your own before checking the answers:

  1. Display students from Section ‘A’ who have issued books worth more than ₹500.
  2. Find the total value of all books in the library (Price × Quantity).
  3. Display course names with duration more than the average duration.
  4. Show student names who have not enrolled in any courses.
  5. Display the second-highest marks from the STUDENTS table.

Download Complete SQL Practice Set

Want more practice questions? Itxperts offers:

  • ✅ 200+ MySQL practice questions
  • ✅ Chapter-wise tests with solutions
  • ✅ Previous year CBSE question papers
  • ✅ Mock exams with detailed explanations
  • ✅ Video tutorials for complex concepts
  • ✅ One-on-one doubt clearing sessions

Visit Itxperts.co.in or contact us for personalized coaching!

Conclusion

Mastering MySQL for CBSE Class 12 IP requires consistent practice and understanding of core concepts. Focus on:

  1. Basic SELECT queries with WHERE, ORDER BY, DISTINCT
  2. Aggregate functions and their usage
  3. GROUP BY and HAVING clauses
  4. String, Date, and Math functions
  5. JOINS – especially INNER JOIN and LEFT JOIN
  6. Nested queries or subqueries

Remember, practice is the key to success! Work through these questions multiple times, and don’t hesitate to experiment with variations.

At Itxperts, we’re committed to helping students excel in their IP exams. Our expert faculty has years of experience in teaching CBSE curriculum and can guide you through every concept with real-world examples.

About Itxperts

Itxperts is India’s leading online platform for Computer Science and Informatics Practices education. We specialize in:

  • CBSE Class 11 & 12 IP/CS coaching
  • Python programming tutorials
  • Database management training
  • Web development courses
  • Competitive exam preparation

Join thousands of successful students who trusted Itxperts for their IP exam preparation!

📧 Contact: info@itxperts.co.in
🌐 Website: www.itxperts.co.in
📱 WhatsApp: +91-8966968576

Share This Post

Found this guide helpful? Share it with your classmates and help them ace their IP exam too!

#CBSE #Class12 #InformaticsPractices #MySQL #Itxperts #DatabaseManagement #SQLQueries #ExamPreparation

Last Updated: November 2024

Happy Learning! 📚💻