Heartfulness International School, Omega Branch, Chennai:600128 Academic Year:2025-26

Database Assignment
Subject: Informatics
Practices
Class: XII Section: C / KCC-NEET-A /
H
Date: 01.07.2025
MySQL – Basics and Functions
Write an SQL command for the following:

(i) To create the table employee for the given Description.

+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| EMP_NO | char(5) | NO | PRI | NULL | |
| EMP_NAME | varchar(20) | YES | | NULL | |
| BASIC | decimal(7,2) | YES | | NULL | |
| DEPARTMENT | varchar(25) | YES | | NULL | |
| DATEOFAPP | date | YES | | NULL | |
| AGE | int | YES | | NULL | |
| GENDER | char(1) | YES | | NULL | |
+ + + + + + +
(ii) To insert the 1st, 2nd and 7th records using both syntax.

+ + + + + + + +
| EMP_NO | EMP_NAME | BASIC | DEPARTMENT | DATEOFAPP | AGE | GENDER |
+ + + + + + + +
| AC001 | DIVYA | 20000.00 | ACCOUNTS | 1997-02-19 | 34 | F |
| AC002 | SABINA | 25000.00 | ACCOUNTS | 1996-01-12 | NULL | F |
| CP001 | DIVAKAR | 29000.00 | COMPUTER | 1998-05-01 | 34 | M |
| CP002 | VIKAS | 35600.00 | COMPUTER | 1994-01-22 | 41 | M |
| FN001 | JOHN | 30500.00 | FINANCE | 1997-02-05 | NULL | M |
| MN001 | ROBERT | 32000.00 | MAINTENANCE | 1997-02-20 | 39 | M |
| MN002 | MOHAN | 29000.00 | MAINTENANCE | 1998-02-19 | 37 | M |
| PR001 | KARAN | 23000.00 | PERSONNEL | 1997-03-27 | | M |
| PR002 | ARUN | 25500.00 | PERSONNEL | 1995-01-01 | 33 | M |
| PR003 | RUBINA | 19000.00 | PERSONNEL | 1998-01-13 | 37 | F |
+ + + + + + + +

(iii) To display the employee Number and name who have joined in the year 1997.
Page 1 of 8
Heartfulness International School, Omega Branch, Chennai:600128 Academic Year:2025-26

(iv) To display the details of all the employees from personnel and computer
department

(v) To display the employee Number, name, salary, and age who earns more than
25000 and age above 35 in the decreasing order of age.

(vi) To display the all the female employee’s name and department in the increasing
order of their appointed date.

Page 2 of 8
Heartfulness International School, Omega Branch, Chennai:600128 Academic Year:2025-26

(vii) To display all the employees Name and number whose age is not filled.

(viii) To display all the employees Number, Name, Department and Age whose all
details are available.

(ix) To display the details of all the employees name starting with ‘D’.
sql> select * from employee where emp_name like 'd%';
+--------+----------+----------+------------+------------+------+--------+
| emp_no | emp_name | basic | department | dateofapp | age | gender |
+--------+----------+----------+------------+------------+------+--------+
| ac001 | divya | 20000.00 | accounts | 1999-02-19 | 34 | f |
| cp001 | divakar | 29000.00 | computer | 1998-05-01 | 34 | m |
+--------+----------+----------+------------+------------+------+--------+
2 rows in set (0.10 sec)

Page 3 of 8
Heartfulness International School, Omega Branch, Chennai:600128 Academic Year:2025-26

(x) To display all the employees Number, Name, and Salary with name containing ‘A’
as second last character.

(xi) List all the employees ID, Name and Age who have joined in second half of the
years 1997 and 1998.

(xii) Display department wise count of employees.

(xiii) Display the average salary rounded to integer spent for each department
where average salary not more than 7000.

(xiv) Display the maximum and minimum salary among the employees.

Page 4 of 8
Heartfulness International School, Omega Branch, Chennai:600128 Academic Year:2025-26
mysql> select min(basic),max(basic) from employee;
+------------+------------+
| min(basic) | max(basic) |
+------------+------------+
| 19000.00 | 35600.00 |
+------------+------------+
1 row in set (0.13 sec)

(xv) Generate a report projecting the total expenditure towards salary on each
department.

(xvi) Display the first 3 characters from name and last 4 characters from department
with ID of the employees have name length more than 6 characters.

(xvii) An increment to all employees is updated into their records with MOD(AGE,5)
percentage of basic.

(xviii) Display the exponential value of day raised to month of in appointment date.

Page 5 of 8
Heartfulness International School, Omega Branch, Chennai:600128 Academic Year:2025-26

(xix) Display the employees ID, name and age with the month name of appointment.

(xx) Department of Accounts and Finance in the company is merged and named
as Accounting, update the same in the relation.

(xxi) Insert a column HRA into the table with appropriate data type filling with
default amount 3000.

Page 6 of 8
Heartfulness International School, Omega Branch, Chennai:600128 Academic Year:2025-26

(xxii) Update the HRA with 15% of basic for all the employees who have HRA less
than the new HRA amount.

(xxiii) Display the presence of the gender character in their name with is position.

Page 7 of 8
Heartfulness International School, Omega Branch, Chennai:600128 Academic Year:2025-26

(xxiv) Display the Name and Department name of all the employees as First character
of both the names in upper case and rest all in lowercase.

(xxv) To display the size of employee’s name and the character from
Department positioned in the name size index.
Example: Emp_Name - KARAN hence size is 5
Department of Karan is ACCOUNTS therefore character in 5th position is ‘U’.

Page 8 of 8