close

Top 50+ MySQL Practice Questions and Answers for CBSE CS/IP Board Exam – Itxperts

July 10, 2025 By @mritxperts
Top 50+ MySQL Practice Questions and Answers  for CBSE CS/IP Board Exam – Itxperts

Your one-stop shop for MySQL practice. This guide covers all the essential commands and functions you need to know, complete with clear, verified answers.

Scenario-Based Questions

For the following questions, consider this database schema with four tables: EMPLOYEE, DEPARTMENT, GRADE, and PROJECT.

Table: EMPLOYEE

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7369SMITHCLERK79021980-12-17800NULL20
7499ALLENSALESMAN76981981-02-20160030030
7521WARDSALESMAN76981981-02-22125050030
7566JONESMANAGER78391981-04-022975NULL20
7654MARTINSALESMAN76981981-09-281250140030
7698BLAKEMANAGER78391981-05-012850NULL30
7782CLARKMANAGER78391981-06-092450NULL10
7788SCOTTANALYST75661987-04-193000NULL20
7839KINGPRESIDENTNULL1981-11-175000NULL10
7844TURNERSALESMAN76981981-09-081500030
7876ADAMSCLERK77881987-05-231100NULL20
7900JAMESCLERK76981981-12-03950NULL30
7902FORDANALYST75661981-12-033000NULL20
7934MILLERCLERK77821982-01-231300NULL10

Table: DEPARTMENT

DEPTNODNAMELOC
10ACCOUNTINGNEW YORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON

Table: GRADE (Represents Salary Grade)

GRADELOSALHISAL
17001200
212011400
314012000
420013000
530019999

Section 1: Basic SELECT Queries

  1. Q: Display all information about all employees.
    A: SELECT * FROM EMPLOYEE;
  2. Q: Display the employee number, name, and salary of all employees.
    A: SELECT EMPNO, ENAME, SAL FROM EMPLOYEE;
  3. Q: Display the unique job titles from the EMPLOYEE table.
    A: SELECT DISTINCT JOB FROM EMPLOYEE;
  4. Q: Display the employee name and their annual salary (SAL * 12).
    A: SELECT ENAME, SAL * 12 AS AnnualSalary FROM EMPLOYEE;
  5. Q: Display the details of all employees from department number 10.
    A: SELECT * FROM EMPLOYEE WHERE DEPTNO = 10;
  6. Q: Display the name and job of employees who are ‘CLERK’.
    A: SELECT ENAME, JOB FROM EMPLOYEE WHERE JOB = 'CLERK';
  7. Q: Display the name of employees whose name starts with ‘S’.
    A: SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE 'S%';
  8. Q: Display the name of employees where the second letter of their name is ‘L’.
    A: SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE '_L%';
  9. Q: Display the name of employees who have ‘AR’ in their name.
    A: SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE '%AR%';
  10. Q: Display the details of employees who were hired in the year 1981.
    A: SELECT * FROM EMPLOYEE WHERE HIREDATE LIKE '1981%';
  11. Q: Display the name and salary of employees who earn more than 2500.
    A: SELECT ENAME, SAL FROM EMPLOYEE WHERE SAL > 2500;
  12. Q: Display the details of employees who are not ‘MANAGER’.
    A: SELECT * FROM EMPLOYEE WHERE JOB != 'MANAGER'; or SELECT * FROM EMPLOYEE WHERE JOB <> 'MANAGER';
  13. Q: Display the details of employees who have a commission.
    A: SELECT * FROM EMPLOYEE WHERE COMM IS NOT NULL;
  14. Q: Display the details of employees who do not have a manager.
    A: SELECT * FROM EMPLOYEE WHERE MGR IS NULL;
  15. Q: Display the names of all employees from department 30 who are ‘SALESMAN’.
    A: SELECT ENAME FROM EMPLOYEE WHERE DEPTNO = 30 AND JOB = 'SALESMAN';
  16. Q: Display the names of all ‘CLERK’s and ‘ANALYST’s.
    A: SELECT ENAME, JOB FROM EMPLOYEE WHERE JOB IN ('CLERK', 'ANALYST');
  17. Q: Display the names of employees whose salary is between 1000 and 2000.
    A: SELECT ENAME, SAL FROM EMPLOYEE WHERE SAL BETWEEN 1000 AND 2000;

Section 2: Ordering and Grouping

  1. Q: Display employee details sorted by their name in alphabetical order.
    A: SELECT * FROM EMPLOYEE ORDER BY ENAME;
  2. Q: Display employee details sorted by department number, and then by salary in descending order.
    A: SELECT * FROM EMPLOYEE ORDER BY DEPTNO, SAL DESC;
  3. Q: Display the total number of employees.
    A: SELECT COUNT(*) FROM EMPLOYEE;
  4. Q: Find the total salary paid to all employees.
    A: SELECT SUM(SAL) FROM EMPLOYEE;
  5. Q: Find the average salary of all employees.
    A: SELECT AVG(SAL) FROM EMPLOYEE;
  6. Q: Find the maximum and minimum salary from the EMPLOYEE table.
    A: SELECT MAX(SAL), MIN(SAL) FROM EMPLOYEE;
  7. Q: Count the number of employees in each department.
    A: SELECT DEPTNO, COUNT(*) FROM EMPLOYEE GROUP BY DEPTNO;
  8. Q: Find the average salary for each job type.
    A: SELECT JOB, AVG(SAL) FROM EMPLOYEE GROUP BY JOB;
  9. Q: Find the total salary for each department.
    A: SELECT DEPTNO, SUM(SAL) FROM EMPLOYEE GROUP BY DEPTNO;
  10. Q: Display the department numbers that have more than 3 employees.
    A: SELECT DEPTNO, COUNT(*) FROM EMPLOYEE GROUP BY DEPTNO HAVING COUNT(*) > 3;
  11. Q: Display the jobs where the average salary is more than 2000.
    A: SELECT JOB, AVG(SAL) FROM EMPLOYEE GROUP BY JOB HAVING AVG(SAL) > 2000;

Section 3: Joins and Subqueries

  1. Q: Display the employee name and their department name.
    A: SELECT E.ENAME, D.DNAME FROM EMPLOYEE E, DEPARTMENT D WHERE E.DEPTNO = D.DEPTNO; or SELECT E.ENAME, D.DNAME FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DEPTNO = D.DEPTNO;
  2. Q: Display the employee name, department name, and location for all employees.
    A: SELECT E.ENAME, D.DNAME, D.LOC FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DEPTNO = D.DEPTNO;
  3. Q: Display the name of the employee and the name of their manager.
    A: SELECT E1.ENAME AS Employee, E2.ENAME AS Manager FROM EMPLOYEE E1 JOIN EMPLOYEE E2 ON E1.MGR = E2.EMPNO;
  4. Q: Display the employee name, salary, and salary grade.
    A: SELECT E.ENAME, E.SAL, G.GRADE FROM EMPLOYEE E JOIN GRADE G ON E.SAL BETWEEN G.LOSAL AND G.HISAL;
  5. Q: Display the employee name for those working in ‘NEW YORK’.
    A: SELECT E.ENAME FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DEPTNO = D.DEPTNO WHERE D.LOC = 'NEW YORK';
  6. Q: Find the name of the department where ‘SMITH’ works.
    A: SELECT D.DNAME FROM DEPARTMENT D JOIN EMPLOYEE E ON D.DEPTNO = E.DEPTNO WHERE E.ENAME = 'SMITH';
  7. Q: Display the names of employees who earn more than ‘ALLEN’. (Subquery)
    A: SELECT ENAME FROM EMPLOYEE WHERE SAL > (SELECT SAL FROM EMPLOYEE WHERE ENAME = 'ALLEN');
  8. Q: Display the names of employees who work in the same department as ‘SCOTT’.
    A: SELECT ENAME FROM EMPLOYEE WHERE DEPTNO = (SELECT DEPTNO FROM EMPLOYEE WHERE ENAME = 'SCOTT');
  9. Q: Display the names of employees who have the same job as ‘JONES’.
    A: SELECT ENAME FROM EMPLOYEE WHERE JOB = (SELECT JOB FROM EMPLOYEE WHERE ENAME = 'JONES');
  10. Q: Display the names of managers who manage more than 2 employees.
    A: SELECT MGR, COUNT(*) FROM EMPLOYEE GROUP BY MGR HAVING COUNT(*) > 2;

Section 4: Functions (String, Numeric, Date)

  1. Q: Display the employee names in lowercase.
    A: SELECT LOWER(ENAME) FROM EMPLOYEE; or SELECT LCASE(ENAME) FROM EMPLOYEE;
  2. Q: Display the first three characters of each employee’s name.
    A: SELECT SUBSTRING(ENAME, 1, 3) FROM EMPLOYEE; or SELECT LEFT(ENAME, 3) FROM EMPLOYEE;
  3. Q: Display the length of each employee’s name.
    A: SELECT ENAME, LENGTH(ENAME) FROM EMPLOYEE;
  4. Q: Display the salary of each employee rounded to the nearest thousand.
    A: SELECT ENAME, SAL, ROUND(SAL, -3) FROM EMPLOYEE;
  5. Q: Display the remainder of dividing the salary by 1000 for each employee.
    A: SELECT ENAME, SAL, MOD(SAL, 1000) FROM EMPLOYEE;
  6. Q: Display the current date and time.
    A: SELECT NOW();
  7. Q: Display the year each employee was hired.
    A: SELECT ENAME, YEAR(HIREDATE) FROM EMPLOYEE;
  8. Q: Display the name of the month each employee was hired.
    A: SELECT ENAME, MONTHNAME(HIREDATE) FROM EMPLOYEE;
  9. Q: Display the number of years each employee has been with the company (assuming the current year is 2025).
    A: SELECT ENAME, 2025 - YEAR(HIREDATE) AS YearsOfService FROM EMPLOYEE;

Section 5: DDL and DML

  1. Q: Write the SQL command to create the DEPARTMENT table with DEPTNO as the primary key. A:SQLCREATE TABLE DEPARTMENT ( DEPTNO INT PRIMARY KEY, DNAME VARCHAR(50), LOC VARCHAR(50) );
  2. Q: Add a new column PINCODE of type INT to the DEPARTMENT table.
    A: ALTER TABLE DEPARTMENT ADD PINCODE INT;
  3. Q: Insert a new record for department 50, ‘HR’, located in ‘HOUSTON’ into the DEPARTMENT table.
    A: INSERT INTO DEPARTMENT (DEPTNO, DNAME, LOC) VALUES (50, 'HR', 'HOUSTON');
  4. Q: Update the location of the ‘SALES’ department to ‘ATLANTA’.
    A: UPDATE DEPARTMENT SET LOC = 'ATLANTA' WHERE DNAME = 'SALES';
  5. Q: Delete the ‘OPERATIONS’ department from the table.
    A: DELETE FROM DEPARTMENT WHERE DNAME = 'OPERATIONS';

Top Search Query:

MySQL practice questions
CBSE Class 12 computer science questions
SQL queries for class 12
MySQL questions for class 11
Database practice questions
CBSE computer science question bank
SQL practice for beginners
MySQL commands practice
MySQL questions for CBSE board exam
CBSE Class 12 informatics practices important questions
Previous year MySQL questions CBSE class 12
MySQL practical exam questions
CBSE Class 12 Computer Science sample paper questions SQL
How to practice SQL for CBSE exam
Most important MySQL queries for board exam
CBSE Class 11 final exam MySQL questions
MySQL join queries practice for class 12
group by and having clause questions with answers
SQL functions practice questions (string, numeric, date)
DDL and DML commands practice questions
MySQL subquery questions for students
Create table query practice questions
MySQL order by clause exercises
SQL queries on multiple tables for class 12 practice
MySQL practice questions with answers for class 12
SQL queries for practice with solutions pdf
CBSE Class 12 computer science question bank with answers
MySQL solved questions for board exam
Free MySQL question bank for students
MySQL exercises with solutions
Where can I find practice questions for MySQL class 12?
What type of SQL questions are asked in CBSE board exams?
How to get good marks in MySQL practical?
Can you give me some practice questions for SQL joins?
Examples of MySQL GROUP BY queries for practice.