DML (Data Manipulation Language) Commands – Records Related

Introduction

In the world of databases, manipulating data is a fundamental task. Data Manipulation Language (DML) commands play a pivotal role in this arena, allowing us to interact with data within database tables. In this article, we will explore these commands in depth, along with practical examples to understand their usage better.

DML commands are SQL statements used to interact with data stored in database tables. They include INSERT, UPDATE, DELETE, and SELECT statements, each serving a specific purpose.

INSERT INTO - Adding New Records
UPDATE - Modifying Records
DELETE FROM - Deleting Records
SELECT - Retrieving Data
Advanced Condition Handling
Ways to specify conditions in WHERE clause
Conclusion
FAQs

INSERT INTO - Adding New Records

Used to add a new Record to the existing table. Here fieldnames is optional. The rules for specifying the value is:


INSERT INTO tablename(fieldnames) VALUES(values);

All char, varchar, and date field values must be enclosed in either single (' ') or double (" ") quotes.
All date values must be in YYYY-MM-DD format viz. if we have to write 25-Jan-2018 then it will be written as 2018-01-25
If no value is to be inserted then we can use the word NULL for it and the word NULL will not be in ‘ ‘ or “ “. NULL means no value.

To add a new record to the students table created above the command will be:-

example.sql


INSERT INTO students VALUES(120,”Aman Kumar”,”XII”);

You can also specify the columns explicitly: (Or if we just want to add admno and name then the command will be written as:)

example.sql


INSERT INTO students VALUES(121,”Sajay Kumar”,NULL);

example.sql


INSERT INTO students(admno,name) VALUES(121,”Sajay Kumar”);

UPDATE - Modifying Records

The UPDATE command allows you to modify existing records in a table. You can change the values of specific fields based on a given condition. Here's how it works:

example.sql


UPDATE table SET fieldname=newValue WHERE condition;

Example if we want to change the name from Aman kumar to Ashish Kumar then we will be using update

example.sql


UPDATE students SET name=”Ashish Kumar” WHERE name=”Aman Kumar”;

In the Update command WHERE condition is optional

For example if we want to make all the fees of students to be 5000 in students table:-

example.sql


UPDATE students SET fees = 5000;

If want to increase the fees of all students by 500:-

example.sql


UPDATE students SET fees = fees + 500;

If we want to increase the fees by 5%:-

example.sql


UPDATE students SET fees = fees + fees*5/100;

example.sql


UPDATE students SET fees = fees + fees*0.05;

DELETE FROM - Deleting Records

The DELETE FROM command is used to delete records from a table based on a specified condition.

example.sql


DELETE FROM table
WHERE condition;

Example, to delete all the records in library table:-

example.sql


DELETE FROM library;

To delete all the records in library table whose price is more than 500:-

example.sql


DELETE FROM library WHERE price>500;

SELECT - Retrieving Data

example.sql


SELECT *|fieldnames|DISTINCT fieldname
AS “new heading”
FROM tablename
WHERE condition
ORDER BY fieldnames DESC;

Select command is used to display existing records in the table. The command have four parts:-

What you want to display?

If you want to display all fields then specify *.
If you want only specific fields to be displayed like, to display name and address from students write SELECT name,address
If you want to display different or distinct values in a field then write SELECT DISTINCT fieldname.

For example: If we want to display different bookname in library table then write SELECT DISTINCT bookname

Here the AS keyword is optional to change the heading while displaying output.

Example: SELECT DISTINCT bookname AS “different book names”

The table from which to retrieve data.
Conditions to filter records.
Sorting order using ORDER BY.

From which table you want to display records? This is specified using FROM clause

example.sql


SELECT * FROM students;

Which all records needs to be displayed? This is specified using WHERE clause

example.sql


SELECT * FROM students WHERE fees>500;

Ascending/descending order of the records, that is to arrange the records we use ORDER BY clause.

example.sql


SELECT * FROM students ORDER BY name;

To arrange the records in descending order we use DESC word after fieldname:-

example.sql


SELECT * FROM students ORDER BY name DESC;

Advanced Condition Handling

In DML commands, you can use various conditions to filter records effectively. Here are some common ones:

Using relational operators (e.g., >, <, =, !=).
Specifying a range of values using BETWEEN.
Matching specific values using IN.
Checking for NULL or non-NULL values.
Pattern matching using LIKE and wildcard characters (% and _).

Ways to specify conditions in WHERE clause


WHERE fieldname>|<|>=|<=|=|!= Value

To put a condition using relational operator like more than (>), less than (<), more than or equal to (>=), less than or equal to (<=), equal to (=), not equal to (!=)

Example:-

Fees more than 10,000:-


SELECT * FROM students WHERE fees>10000;

Whose name is Vikas:-


SELECT * FROM students WHERE name=”Vikas”;

Of English subject:-


SELECT * FROM students WHERE subject=”English”;


WHERE fieldname BETWEEN val1 AND val2;

WHERE fieldname NOT BETWEEN val1 AND val2;

To put a condition on a range of values like fees is more than or equal to 10000 and less than or equal to 15000 then we use between clause.

WHERE fees BETWEEN 10000 AND 15000 It is equivalent to WHERE fees>=10000 and fees<=15000


SELECT * from students WHERE fees BETWEEN 10000 AND 15000;


WHERE fieldname IN(values);

WHERE fieldname NOT IN(values);

To match for specific values in a field. like subject is English or hindi then we can write:-

It is equivalent to WHERE subject=”English” or subject=”Hindi”


SELECT * FROM students WHERE subject IN(“English”,”Hindi”)


WHERE fieldname IS NULL;

WHERE fieldname IS NOT NULL;

To find the records which don’t have any value or a NULL value. Like to find all records whose bookname is not entered.


SELECT * FROM students WHERE bookname IS NULL;


WHERE fieldname LIKE “value containing % or _”;

WHERE fieldname NOT LIKE “value containing % or _”;

To put condition on pattern matching we use LIKE. Here we can use either % (for any or no character) and _ ( for exactly one character).

For example we want to display records whose

name starts with AM then we will use WHERE name LIKE “AM%”


SELECT * FROM student WHERE name LIKE “AM%”;

Name ends with singh WHERE name LIKE “%singh”


SELECT * FROM student WHERE name LIKE “%singh”;

Name contains the letter A WHERE name LIKE “%A%”


SELECT * FROM student WHERE name LIKE “%A%”;

Name contains second letter as A WHERE name LIKE “_A%”


SELECT * FROM student WHERE name LIKE “_A%”;

Name contains A as second last character WHERE name LIKE “%A_”


SELECT * FROM student WHERE name LIKE “%A_”;

Conclusion

Data Manipulation Language (DML) commands are essential tools for working with data in relational databases. These commands, including INSERT, UPDATE, DELETE, and SELECT, allow you to add, modify, delete, and retrieve records from database tables. By understanding and using these commands effectively, you can manage and manipulate your database data with precision and efficiency.

FAQs

What is the purpose of the INSERT INTO command?

The INSERT INTO command is used to add new records to a database table. It allows you to specify the values you want to insert into each field of the table, and you can optionally specify the column names if you don't want to insert values into all columns.

How do you update records in a database using the UPDATE command?

The UPDATE command is used to modify existing records in a database table. You can specify which fields to update and set new values for those fields. You can also use the WHERE clause to apply the update to specific records based on a condition.

What is the purpose of the DELETE FROM command?

The DELETE FROM command is used to remove records from a database table based on a specified condition. It allows you to delete one or more records that meet the criteria you define in the WHERE clause.

How does the SELECT command work in SQL?

The SELECT command is used to retrieve data from a database table. You can specify which columns you want to retrieve, apply conditions using the WHERE clause to filter the records, and use the ORDER BY clause to control the sorting order of the results.

What are some common conditions used in SQL WHERE clauses?

SQL WHERE clauses can include various conditions such as:

Using relational operators (e.g., >, <, =, !=).
Specifying a range of values using BETWEEN.
Matching specific values using IN.
Checking for NULL or non-NULL values.
Pattern matching using LIKE and wildcard characters (% and _).

These conditions allow you to filter and retrieve specific records from the database based on your requirements.

By mastering these DML commands and understanding how to use conditions effectively, you can efficiently manage and manipulate data in your database, ensuring that it meets your application's needs.