HANDLING NULL VALUES USING DML
ABSTRACT:
In database systems, NULL values represent missing, unknown, or
inapplicable data. Proper handling of NULL values is essential to maintain
data accuracy and avoid incorrect query results. This experiment focuses
on handling NULL values using DML (Data Manipulation Language)
commands such as INSERT, UPDATE, SELECT, and DELETE in MySQL.
The objective is to understand how NULL values behave in database
operations and how SQL functions like IS NULL, IS NOT NULL, IFNULL(),
and COALESCE() help in managing them effectively.
INTRODUCTION:
In real-world databases, all information may not be available at the time
of data entry. For example, a customer may not provide an email address
or a payment date may be unknown. In such cases, NULL values are used.
However, NULL is not equal to zero or an empty string. Special SQL
conditions and functions are required to handle NULL values correctly
using DML operations.
OBJECTIVES:
To understand the concept of NULL values
To insert records containing NULL values
To retrieve and filter NULL values
To update and replace NULL values
To apply SQL functions for NULL handling
SYSTEM / SOFTWARE REQUIREMENTS:
Software Requirements:
MySQL Server
MySQL Workbench
Windows / Linux OS
Hardware Requirements:
Laptop / Desktop
Minimum 4 GB RAM
DATABASE USED:
Sql
CREATE DATABASE CompanyDB;
USE CompanyDB;
TABLE CREATION:
Sql
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
Name VARCHAR(50),
Department VARCHAR(30),
Email VARCHAR(50),
Salary INT
);
DML OPERATIONS FOR HANDLING NULL VALUES
1. INSERTING NULL VALUES
Sql
INSERT INTO Employee VALUES
(1, 'Ravi Kumar', 'HR', 'ravi@[Link]', 30000),
(2, 'Anjali Singh', 'IT', NULL, 40000),
(3, 'Suresh Rao', NULL, 'suresh@[Link]', NULL);
2. VIEW ALL RECORDS
Sql
SELECT * FROM Employee;
3. CHECKING NULL VALUES USING IS NULL
Sql
SELECT * FROM Employee
WHERE Email IS NULL;
4. CHECKING NON-NULL VALUES USING IS NOT NULL
Sql
SELECT * FROM Employee
WHERE Salary IS NOT NULL;
5. REPLACING NULL VALUES USING IFNULL()
Sql
Copy code
SELECT Name, IFNULL(Email, 'Not Provided') AS Email
FROM Employee;
6. USING COALESCE() FUNCTION
Sql
SELECT Name, COALESCE(Department, 'General') AS Department
FROM Employee;
7. UPDATING NULL VALUES
Sql
UPDATE Employee
SET Salary = 25000
WHERE Salary IS NULL;
8. DELETING RECORDS WITH NULL VALUES
Sql
DELETE FROM Employee
WHERE Department IS NULL;
IMPLEMENTATION:
The Employee table was created and populated with records containing
NULL values. DML commands such as INSERT, SELECT, UPDATE, and
DELETE were used to manage NULL values. SQL conditions and built-in
functions ensured correct handling and retrieval of data.
RESULTS:
NULL values were successfully inserted and retrieved
Queries using IS NULL and IS NOT NULL worked correctly
NULL values were replaced using IFNULL() and COALESCE()
Records with NULL values were updated and deleted successfully
CONCLUSION:
Handling NULL values using DML is crucial in database management.
NULL values represent missing data and require special conditions and
functions. This experiment helped in understanding how NULL values
behave in SQL queries and how to manage them effectively using DML
operations.
REFERENCES:
Database System Concepts – Silberschatz
MySQL Documentation
GeeksforGeeks – SQL NULL Values
W3Schools – SQL