MySQL Practical File Programs (Class 12 IT 802)

In this lesson: All Class 12 SQL practical file programs on one Employee table — query, output and explanation in Hindi & English. Download-ready.

About these SQL practicals

This page brings all the Class 12 Information Technology (802) SQL practical file programs together, and builds every one of them on a single, simple Employee table. Because the same table is used throughout, you can see clearly how each command — SELECT, WHERE, ORDER BY, GROUP BY, aggregate functions, UPDATE, DELETE — changes the result.

Start by running the first two practicals once (create the table, then insert the sample rows). After that, try any query below. Each one shows the exact SQL and its output, with a short easy explanation in Hindi and English.

1. Create the Employee table

Har SQL practical se pehle ek table chahiye. Ye CREATE TABLE se Employee table banata hai jisme id, name, department, salary aur city columns hain.

CREATE TABLE Employee (
    emp_id     INT PRIMARY KEY,
    name       VARCHAR(40),
    department VARCHAR(30),
    salary     DECIMAL(10,2),
    city       VARCHAR(30)
);

Query OK, table 'Employee' created.

2. Insert records into Employee

Table me data INSERT INTO se aata hai. Yahan hum 5 employees ke records ek saath daal rahe hain.

INSERT INTO Employee VALUES
(1, 'Aarav',  'IT',      55000, 'Delhi'),
(2, 'Diya',   'HR',      42000, 'Mumbai'),
(3, 'Kabir',  'IT',      61000, 'Delhi'),
(4, 'Meera',  'Sales',   38000, 'Pune'),
(5, 'Rohan',  'HR',      47000, 'Mumbai');

Query OK, 5 rows affected.

3. Display all records

SELECT * table ke saare columns aur saari rows dikhata hai. Ye sabse basic query hai.

SELECT * FROM Employee;

+--------+-------+------------+---------+--------+
| emp_id | name  | department | salary  | city   |
+--------+-------+------------+---------+--------+
|      1 | Aarav | IT         | 55000.00| Delhi  |
|      2 | Diya  | HR         | 42000.00| Mumbai |
|      3 | Kabir | IT         | 61000.00| Delhi  |
|      4 | Meera | Sales      | 38000.00| Pune   |
|      5 | Rohan | HR         | 47000.00| Mumbai |
+--------+-------+------------+---------+--------+

4. Show selected columns

Sab columns ki jagah sirf zaroori columns dekhne ke liye unke naam likhte hain — yahan sirf name aur salary.

SELECT name, salary FROM Employee;

+-------+---------+
| name  | salary  |
+-------+---------+
| Aarav | 55000.00|
| Diya  | 42000.00|
| Kabir | 61000.00|
| Meera | 38000.00|
| Rohan | 47000.00|
+-------+---------+

5. Filter rows with WHERE

WHERE ek shart lagata hai — sirf wahi rows aati hain jo shart poori karti hain. Yahan sirf IT department wale.

SELECT name, department FROM Employee
WHERE department = 'IT';

+-------+------------+
| name  | department |
+-------+------------+
| Aarav | IT         |
| Kabir | IT         |
+-------+------------+

6. Employees earning above 45000

WHERE ke saath tulna operator (>) laga kar hum salary ke aadhar par rows chhaant sakte hain.

SELECT name, salary FROM Employee
WHERE salary > 45000;

+-------+---------+
| name  | salary  |
+-------+---------+
| Aarav | 55000.00|
| Kabir | 61000.00|
| Rohan | 47000.00|
+-------+---------+

7. Sort by salary (ORDER BY)

ORDER BY result ko kisi column ke hisaab se lagata hai. DESC se bade-se-chhota.

SELECT name, salary FROM Employee
ORDER BY salary DESC;

+-------+---------+
| name  | salary  |
+-------+---------+
| Kabir | 61000.00|
| Aarav | 55000.00|
| Rohan | 47000.00|
| Diya  | 42000.00|
| Meera | 38000.00|
+-------+---------+

8. Count employees per department

GROUP BY rows ko group me baantta hai aur COUNT() har group ki ginti karta hai.

SELECT department, COUNT(*) AS total
FROM Employee
GROUP BY department;

+------------+-------+
| department | total |
+------------+-------+
| IT         |     2 |
| HR         |     2 |
| Sales      |     1 |
+------------+-------+

9. Total and average salary

Aggregate functions SUM() aur AVG() poore column ka jod aur औsat nikaalte hain.

SELECT SUM(salary) AS total_salary,
       AVG(salary) AS average_salary
FROM Employee;

+--------------+----------------+
| total_salary | average_salary |
+--------------+----------------+
|    243000.00 |    48600.000000|
+--------------+----------------+

10. Highest and lowest salary

MAX() sabse badi value aur MIN() sabse chhoti value deta hai.

SELECT MAX(salary) AS highest,
       MIN(salary) AS lowest
FROM Employee;

+----------+----------+
| highest  | lowest   |
+----------+----------+
| 61000.00 | 38000.00 |
+----------+----------+

11. Update a salary

UPDATE maujooda row ki value badalta hai. WHERE zaroori hai warna saari rows badal jayengi.

UPDATE Employee
SET salary = 50000
WHERE name = 'Diya';

Query OK, 1 row affected.

12. Delete a record

DELETE row hata deta hai. WHERE ke bina saari rows delete ho jayengi — dhyan se.

DELETE FROM Employee
WHERE name = 'Meera';

Query OK, 1 row affected.

13. Search with LIKE

LIKE pattern se khojta hai. 'D%' ka matlab 'D' se shuru hone wale naam.

SELECT name FROM Employee
WHERE name LIKE 'D%';

+------+
| name |
+------+
| Diya |
+------+

14. Employees from selected cities (IN)

IN ek se zyada values me se match karta hai — yahan Delhi ya Mumbai.

SELECT name, city FROM Employee
WHERE city IN ('Delhi', 'Mumbai');

+-------+--------+
| name  | city   |
+-------+--------+
| Aarav | Delhi  |
| Diya  | Mumbai |
| Kabir | Delhi  |
| Rohan | Mumbai |
+-------+--------+

15. Department-wise average salary

GROUP BY aur AVG() milकर har department ki औsat salary nikaalte hain.

SELECT department, AVG(salary) AS avg_salary
FROM Employee
GROUP BY department;

+------------+-------------+
| department | avg_salary  |
+------------+-------------+
| IT         | 58000.000000|
| HR         | 44500.000000|
| Sales      | 38000.000000|
+------------+-------------+

Summary

• All 15 SQL practicals use one Employee table, so concepts build on each other.
• They cover the full Class 12 syllabus: create, insert, select, where, order by, group by, aggregates, update, delete, like and in.
• Always use WHERE with UPDATE and DELETE to avoid changing every row.
• Aggregate functions (COUNT, SUM, AVG, MAX, MIN) turn many rows into one value.

Go deeper with constraints in MySQL, normalization, or the full outer join lesson.