Data Manipulation Language in SQL With Example

Greetings!
We have recently published 100+ articles on android tutorials with kotlin and java. If you need, you may visit Android Tutorial for beginners page. You can also check Kotlin Tutorial for beginners. Also, if you are interested in content writing, you can mail us at tutorialwing@gmail.com.

Data Manipulation Language in SQL is SQL commands that deals with manipulation of data present in database.

In this post, SQL commands that we are going to learn –

  1. SELECT command
  2. INSERT command
  3. UPDATE command
  4. DELETE command

1. SELECT command

This is one of the most commonly used SQL statement and you have to be proficient with how to construct the SELECT statement. SELECT is used to fetch the records that are present in the table. We will see the usage of SELECT statement with some examples.

Example 1

SELECT statement to retrieve all the records from the particular table. Let’s say we want to retrieve all the columns of a table named department.

mysql> SELECT * FROM department;

Result

Dno Dname Mgreid
1 Research E103
2 Production E102
3 Administration E101
4 Advertisement E104

4 rows in set (0.00 sec)

Example 2

To retrieve only specific columns.

mysql> SELECT eid, dob, address FROM employee;

Result

 eid dob address
 E101 1982-02-10 Dhayari, Pune
 E102 1985-12-20 Balaji nagar, Pune
 E103 1974-11-13 shivaji nagar Aurangabad
 E104 1963-12-09 VIP road, Nanded
 E105 1955-04-19 Telli galli, yeotmal
 E106 1983-02-02 Monda, Nashik
 E107 1972-01-18 Nagar Road, Aurangabad

7 rows in set (0.00 sec)

Example 3

Retrieving specific rows and columns depending upon the condition.

mysql> SELECT eid, dob, address FROM employee WHERE fname= 'Sharada';

Result

eid dob address
E103 1974-11-13 Shivaji nagar, Aurangabad
E107 1972-01-18 Nagar Road, Aurangabad
Example 4

Total number of records in a table.

mysql> SELECT count(*) FROM employee;

Result

count(*)
7
Example 5

Retrieve all fnames including duplicates if exists.

mysql> SELECT all fname FROM employee;
fname
Neelima
Alok
Sharada
Vaishnavi
Ashwini
Abhishek
Sharada

7 rows in set (0.00 sec)

Example 6

Retrieve all fnames excluding duplicates if exists.

mysql> SELECT distinct fname FROM employee;
fname
Neelima
Alok
Sharada
Vaishnavi
Ashwini
Abhishek

6 rows in set (0.00 sec)

Example 7

Total number of distinct values in a specified column.

mysql> SELECT count(distinct fname) FROM employee;
count(distinct fname)
6

1 row in set (0.05 sec)

Example 8

Same above example but aliasing the column name while retrieving.

mysql> SELECT count(distinct fname) "Co_Di_Fn" FROM employee;

Result

Co _Di_Fn
6

Next command in data manipulation language is INSERT command.

2. INSERT command

  • Once the table is created, you load the table with data to be manipulated. The INSERT INTO statement is used to do this.
  • When inserting a single row of data into the table, the insert operation creates a new empty row in the table and then loads the values passed by the INSERT statement into the specified columns.
  • In the INSERT INTO statement, columns and values have one to one relationship i.e. the first value described is inserted into the first column and the second value into the second column and so on.
  • If you are inserting similar number of values as the number of columns and the sequence of values is exactly in accordance with the data type of the columns, then there is no need to specify the column names in the INSERT INTO statement.
  • But if there are fewer values than the number of columns, then it is necessary to specify the column names and its corresponding values in the INSERT INTO statement.

There are two ways to insert values, first is by specifying the column names and second is without specifying the column names.

Example 1

Specify the column names as the value is not entered into the column dnum.

mysql> INSERT into employee(eid, fname, lname, DOB, address, sex, salary) values(‘101’, ‘Mohit’, ‘Kalokhe’, ‘1982-02-10’, ‘Akurdi, Pune’, ‘M’, 30000);

Result
Query OK, 1 row affected (0.06 sec)

Example 2

Insert values in all columns.

mysql> INSERT into department values (1, ‘Research’, ‘103’);

Result
Query OK, 1 row affected (0.02 sec)

Next command in data manipulation language is UPDATE command.

3. UPDATE command

This command is used to change or modify the data values. The SET clause indicates which column data should be modified and the new values that they should hold. The WHERE clause if given, specifies which rows should be updated. Otherwise all table rows are updated.

Example 1

Updating information using “=” in where.

mysql> UPDATE employee SET dnum=1 WHERE eid= ‘e105’;

Result
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Example 2

Updating information using like operator

mysql> UPDATE studentDetails SET gender= ‘M’ WHERE name like ‘P%’

Result
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Next command in data manipulation language is DELETE command.

4. DELETE command

The delete statement allows you to delete records from the table.

Example 1

Delete all records (Empty the table completely)

mysql> DELETE from employee;

Or

Mysql> TRUNCATE employee;
Example 2

Delete specific records.

mysql> DELETE from employee WHERE fname= "Arnav"

That’s end of post on Data Manipulation Language in SQL.

Support Us

If you like Tutorialwing and would like to contribute, you can email an article on any educational topic at tutorialwing@gmail.com. We would love to publish your article. See your article on Tutorialwing and help others with your knowledge. Follow Facebook, LinkedIn, Google+, Twitter, Youtube for latest updates.
Greetings!
We have recently published 100+ articles on android tutorials with kotlin and java. If you need, you may visit Android Tutorial for beginners page. You can also check Kotlin Tutorial for beginners