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
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 |
7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 |
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
Table: DEPARTMENT
DEPTNO | DNAME | LOC |
10 | ACCOUNTING | NEW YORK |
20 | RESEARCH | DALLAS |
30 | SALES | CHICAGO |
40 | OPERATIONS | BOSTON |
Table: GRADE (Represents Salary Grade)
GRADE | LOSAL | HISAL |
1 | 700 | 1200 |
2 | 1201 | 1400 |
3 | 1401 | 2000 |
4 | 2001 | 3000 |
5 | 3001 | 9999 |
Section 1: Basic SELECT Queries
- Q: Display all information about all employees.
A:SELECT * FROM EMPLOYEE;
- Q: Display the employee number, name, and salary of all employees.
A:SELECT EMPNO, ENAME, SAL FROM EMPLOYEE;
- Q: Display the unique job titles from the
EMPLOYEE
table.
A:SELECT DISTINCT JOB FROM EMPLOYEE;
- Q: Display the employee name and their annual salary (SAL * 12).
A:SELECT ENAME, SAL * 12 AS AnnualSalary FROM EMPLOYEE;
- Q: Display the details of all employees from department number 10.
A:SELECT * FROM EMPLOYEE WHERE DEPTNO = 10;
- Q: Display the name and job of employees who are ‘CLERK’.
A:SELECT ENAME, JOB FROM EMPLOYEE WHERE JOB = 'CLERK';
- Q: Display the name of employees whose name starts with ‘S’.
A:SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE 'S%';
- Q: Display the name of employees where the second letter of their name is ‘L’.
A:SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE '_L%';
- Q: Display the name of employees who have ‘AR’ in their name.
A:SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE '%AR%';
- Q: Display the details of employees who were hired in the year 1981.
A:SELECT * FROM EMPLOYEE WHERE HIREDATE LIKE '1981%';
- Q: Display the name and salary of employees who earn more than 2500.
A:SELECT ENAME, SAL FROM EMPLOYEE WHERE SAL > 2500;
- Q: Display the details of employees who are not ‘MANAGER’.
A:SELECT * FROM EMPLOYEE WHERE JOB != 'MANAGER';
orSELECT * FROM EMPLOYEE WHERE JOB <> 'MANAGER';
- Q: Display the details of employees who have a commission.
A:SELECT * FROM EMPLOYEE WHERE COMM IS NOT NULL;
- Q: Display the details of employees who do not have a manager.
A:SELECT * FROM EMPLOYEE WHERE MGR IS NULL;
- Q: Display the names of all employees from department 30 who are ‘SALESMAN’.
A:SELECT ENAME FROM EMPLOYEE WHERE DEPTNO = 30 AND JOB = 'SALESMAN';
- Q: Display the names of all ‘CLERK’s and ‘ANALYST’s.
A:SELECT ENAME, JOB FROM EMPLOYEE WHERE JOB IN ('CLERK', 'ANALYST');
- 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
- Q: Display employee details sorted by their name in alphabetical order.
A:SELECT * FROM EMPLOYEE ORDER BY ENAME;
- Q: Display employee details sorted by department number, and then by salary in descending order.
A:SELECT * FROM EMPLOYEE ORDER BY DEPTNO, SAL DESC;
- Q: Display the total number of employees.
A:SELECT COUNT(*) FROM EMPLOYEE;
- Q: Find the total salary paid to all employees.
A:SELECT SUM(SAL) FROM EMPLOYEE;
- Q: Find the average salary of all employees.
A:SELECT AVG(SAL) FROM EMPLOYEE;
- Q: Find the maximum and minimum salary from the
EMPLOYEE
table.
A:SELECT MAX(SAL), MIN(SAL) FROM EMPLOYEE;
- Q: Count the number of employees in each department.
A:SELECT DEPTNO, COUNT(*) FROM EMPLOYEE GROUP BY DEPTNO;
- Q: Find the average salary for each job type.
A:SELECT JOB, AVG(SAL) FROM EMPLOYEE GROUP BY JOB;
- Q: Find the total salary for each department.
A:SELECT DEPTNO, SUM(SAL) FROM EMPLOYEE GROUP BY DEPTNO;
- Q: Display the department numbers that have more than 3 employees.
A:SELECT DEPTNO, COUNT(*) FROM EMPLOYEE GROUP BY DEPTNO HAVING COUNT(*) > 3;
- 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
- 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;
orSELECT E.ENAME, D.DNAME FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DEPTNO = D.DEPTNO;
- 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;
- 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;
- 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;
- 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';
- 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';
- 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');
- 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');
- 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');
- 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)
- Q: Display the employee names in lowercase.
A:SELECT LOWER(ENAME) FROM EMPLOYEE;
orSELECT LCASE(ENAME) FROM EMPLOYEE;
- Q: Display the first three characters of each employee’s name.
A:SELECT SUBSTRING(ENAME, 1, 3) FROM EMPLOYEE;
orSELECT LEFT(ENAME, 3) FROM EMPLOYEE;
- Q: Display the length of each employee’s name.
A:SELECT ENAME, LENGTH(ENAME) FROM EMPLOYEE;
- Q: Display the salary of each employee rounded to the nearest thousand.
A:SELECT ENAME, SAL, ROUND(SAL, -3) FROM EMPLOYEE;
- Q: Display the remainder of dividing the salary by 1000 for each employee.
A:SELECT ENAME, SAL, MOD(SAL, 1000) FROM EMPLOYEE;
- Q: Display the current date and time.
A:SELECT NOW();
- Q: Display the year each employee was hired.
A:SELECT ENAME, YEAR(HIREDATE) FROM EMPLOYEE;
- Q: Display the name of the month each employee was hired.
A:SELECT ENAME, MONTHNAME(HIREDATE) FROM EMPLOYEE;
- 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
- Q: Write the SQL command to create the
DEPARTMENT
table withDEPTNO
as the primary key. A:SQLCREATE TABLE DEPARTMENT ( DEPTNO INT PRIMARY KEY, DNAME VARCHAR(50), LOC VARCHAR(50) );
- Q: Add a new column
PINCODE
of typeINT
to theDEPARTMENT
table.
A:ALTER TABLE DEPARTMENT ADD PINCODE INT;
- 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');
- Q: Update the location of the ‘SALES’ department to ‘ATLANTA’.
A:UPDATE DEPARTMENT SET LOC = 'ATLANTA' WHERE DNAME = 'SALES';
- 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.