School: Alpine Public School, Khurja
Subject: Information Technology (802)
Section: MySQL Practical Work
Session: 2025-2026
About This Page
This page presents the MySQL practicals in proper sequence with clean formatting,
corrected SQL syntax, and easy readability. Each practical includes the question,
query, and a short explanation for better understanding.
Database
Table
Insert
Select
Update
Alter
Drop
Aggregate Functions
MySQL Practicals
Practical 1
Create a database Company, show the list of databases and select Company database.
Query:
CREATE DATABASE Company;
SHOW DATABASES;
USE Company;
This creates a new database named Company, displays all databases, and selects it for further work.
Practical 2
Create table Employee(empid int(4) primary key, ename varchar(15) not null, dob date, designation varchar(20), department varchar(20), hiredate date, salary decimal(8,2) check (salary > 10000)).
Query:
CREATE TABLE Employee (
empid INT(4) PRIMARY KEY,
ename VARCHAR(15) NOT NULL,
dob DATE,
designation VARCHAR(20),
department VARCHAR(20),
hiredate DATE,
salary DECIMAL(8,2) CHECK (salary > 10000)
);
This creates the Employee table with proper datatypes and a salary validation condition.
Practical 3
Show the structure of Employee table.
Query:
DESC Employee;
This command displays the table structure, including field names, types, null values, and keys.
Practical 4
Insert five records in Employee table.
Query:
INSERT INTO Employee (empid, ename, dob, designation, department, hiredate, salary)
VALUES
(101, 'Amit', '2000-05-12', 'Manager', 'HR', '2021-01-10', 45000.00),
(102, 'Anjali', '1999-07-25', 'Clerk', 'Admin', '2022-03-15', 22000.00),
(103, 'Rohit', '1998-09-18', 'Salesman', 'Sales', '2020-11-20', 25000.00),
(104, 'Arun', '2001-02-10', 'Clerk', 'Accounts','2023-06-05', 21000.00),
(105, 'Neha', '1997-12-30', 'Manager', 'Sales', '2019-08-01', 50000.00);
This inserts five employee records into the table.
Practical 5
Display all the information of employees whose designation is NEITHER Manager nor Salesman.
Query:
SELECT *
FROM Employee
WHERE designation NOT IN ('Manager', 'Salesman');
This shows employees whose designation is not Manager and not Salesman.
Practical 6
Display the details of all the employees whose hiredate is after December 2020.
Query:
SELECT *
FROM Employee
WHERE hiredate > '2020-12-31';
This fetches employees hired after 31 December 2020.
Practical 7
Display all information of Clerk and Manager in descending salary wise.
Query:
SELECT *
FROM Employee
WHERE designation IN ('Clerk', 'Manager')
ORDER BY salary DESC;
This shows Clerks and Managers, sorted from highest salary to lowest salary.
Practical 8
List all employees whose name starts with character 'A'.
Query:
SELECT *
FROM Employee
WHERE ename LIKE 'A%';
This displays employees whose names begin with the letter A.
Practical 9
Display ename and salary of those employees whose salary is in the range 15000 and 28000.
Query:
SELECT ename, salary
FROM Employee
WHERE salary BETWEEN 15000 AND 28000;
The BETWEEN operator includes both values 15000 and 28000.
Practical 10
Display average salary, highest salary and total number of employees for each department.
Query:
SELECT department,
AVG(salary) AS average_salary,
MAX(salary) AS highest_salary,
COUNT(*) AS total_employees
FROM Employee
GROUP BY department;
This uses aggregate functions department-wise using GROUP BY.
Practical 11
Increase the salary of Clerk by 5% and verify the updated rows.
Query:
UPDATE Employee
SET salary = salary + (salary * 0.05)
WHERE designation = 'Clerk';
SELECT *
FROM Employee
WHERE designation = 'Clerk';
First query updates the salary. Second query verifies the updated Clerk records.
Practical 12
Add a new column named Address.
Query:
ALTER TABLE Employee
ADD Address VARCHAR(50);
This adds a new column named Address to the Employee table.
Practical 13
Drop the column Address.
Query:
ALTER TABLE Employee
DROP COLUMN Address;
This removes the Address column from the table.
Practical 14
Display ename, designation, salary and hiredate of Clerk whose salary is below 28000.
Query:
SELECT ename, designation, salary, hiredate
FROM Employee
WHERE designation = 'Clerk'
AND salary < 28000;
This shows selected fields for Clerk employees whose salary is less than 28000.
Practical 15
Display unique designation.
Query:
SELECT DISTINCT designation
FROM Employee;
The DISTINCT keyword shows only unique designation values without repetition.
Complete Flow for Practical Execution
Suggested Order to Run Queries
1. CREATE DATABASE Company;
2. SHOW DATABASES;
3. USE Company;
4. CREATE TABLE Employee (...);
5. DESC Employee;
6. INSERT INTO Employee ...;
7. Run all SELECT queries one by one.
8. Run UPDATE query.
9. Verify updated rows.
10. ALTER TABLE ADD Address;
11. ALTER TABLE DROP COLUMN Address;
12. Run DISTINCT query.
This order helps students execute all practicals smoothly in the lab.