Data Manipulation in SQL Tutorial

Data Manipulation in SQL Tutorial

Last updated on 25th Sep 2020, Blog, Tutorials

About author

Balaji (Team Lead - Data Engineer )

High level Domain Expert in TOP MNCs with 8+ Years of Experience. Also, Handled Around 16+ Projects and Shared his Knowledge by Writing these Blogs for us.

(5.0) | 16445 Ratings 401

DML stands for Data Manipulation Language.It is a language used for selecting, inserting, deleting and updating data in a database.It is used to retrieve and manipulate data in a relational database.DML performs read-only queries of data.

What is Data Manipulation

A data manipulation language (DML) is a computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database. A DML is often a sublanguage of a broader database language such as SQL, with the DML comprising some of the operators in the language. Read-only selecting of data is sometimes distinguished as being part of a separate data query language (DQL), but it is closely related and sometimes also considered a component of a DML; some operators may perform both selecting (reading) and writing.

Data-Manipulation-Language

A popular data manipulation language is that of Structured Query Language (SQL), which is used to retrieve and manipulate data in a relational database. Other forms of DML are those used by IMS/DLI, CODASYL databases, such as IDMS and others.Databases can be found in almost all software applications. SQL is the standard language to query a database. This course will teach you database design. Also, it teaches you basic to advanced SQL.

Purpose of Data Manipulation

Data manipulation is a crucial function for business operations and optimisation. To properly use data and transform it into useful insights like analysing financial data, customer behaviour and performing trend analysis, you have to be able to work with the data in the way you need it. As such, data manipulation provides many benefits to a business, including:

Consistent data

Having data in a consistent format allows it to be organised, read and better understood. When you take data from different sources, you may not have a unified view, but with data manipulation and commands, you can make sure that your data is consistently organised and stored.

Project data

Being able to use historical data to project the future and provide more in-depth analysis is paramount for businesses, especially when it comes to finances. Data manipulation makes this function possible.

Create more value from the data

Overall, being able to transform, edit, delete and insert data into a database means that you can do more with your data. By having information that stays static, it becomes useless. But, when you know how to use data to your benefit, you can have clear insights to make better business decisions.

Remove or ignore unneeded data

Frequently, there is data that is unusable and can interfere with what matters. Unnecessary or inaccurate data should be cleaned and deleted. With data manipulation, you can quickly cleanse your records so that you can work with the information that matters.

Steps to Manipulate Data

To get started with data manipulation, you’ll want to understand the general steps and order of operations.

  • To begin, you’ll need a database, which is created from your data sources.
  • You then need to cleanse your data, with data manipulation, you can clean, rearrange and restructure data.
  • Next, import and build a database that you will work from.
  • You can combine, merge and delete information
  • Then analyse the data, to make all of this information come to life, and glean useful insights.

DML commands 

  1. 1. USE
  2. 2. INSERT INTO
  3. 3. UPDATE
  4. 4. DELETE
  5. 5. SELECT

USE

The USE statement is used to select the database on which you want to perform operations.

Syntax

  • USE DatabaseName;

Example

  • USE Employee;

INSERT INTO

This statement is used to insert new records into the table.

Subscribe For Free Demo
[contact-form-7 404 "Not Found"]

Syntax

  • INSERT INTO TableName (Column1, Column2, Column3, …,ColumnN)
  • VALUES (value1, value2, value3, …);

If you don’t want to mention the column names then use the below syntax

  • INSERT INTO TableName
  • VALUES (Value1, Value2, Value3, …);

Example

  • INSERT INTO Employee_Info(EmployeeID, EmployeeName, Emergency ContactName, PhoneNumber, Address, City, Country)
  • VALUES (’06’, ‘Sanjana’,’Jagannath’, ‘9921321141’, ‘Camel Street House No 12’, ‘Chennai’, ‘India’);
  • INSERT INTO Employee_Info
  • VALUES (’07’, ‘Sayantani’,’Praveen’, ‘9934567654’, ‘Nice Road 21’, ‘Pune’, ‘India’);

UPDATE

This statement is used to modify the records already present in the table.

Syntax

  • UPDATE TableName
  • SET Column1 = Value1, Column2 = Value2, …
  • WHERE Condition;

Example

  • UPDATE Employee_Info
  • SET EmployeeName = ‘Aahana’, City= ‘Ahmedabad’
  • WHERE EmployeeID = 1;

DELETE

This statement is used to delete the existing records in a table.

Syntax

  • DELETE FROM TableName WHERE Condition;

Example

  • DELETE FROM Employee_Info
  • WHERE EmployeeName=’Preeti’;

SELECT

This statement is used to select data from a database and the data returned is stored in a result table, called the result-set.

Syntax

  • SELECT Column1, Column2, …ColumN
  • FROM TableName;

(*) is used to select all from the table

  • SELECT * FROM table_name;

To select the number of records to return use:

  • SELECT TOP 3 * FROM TableName;

Example

  • SELECT EmployeeID, EmployeeName
  • FROM Employee_Info;
  • SELECT * FROM Employee_Info;
  • SELECT TOP 3 * FROM Employee_Info;

Apart from just using the SELECT keyword individually, you can use the following keywords with the SELECT statement:

  • DISTINCT
  • ORDER BY
  • GROUP BY
  • HAVING Clause
  • INTO

‘SELECT DISTINCT’

This statement is used to return only different values.

Syntax

  • SELECT DISTINCT Column1, Column2, …ColumnN
  • FROM TableName;

Example

  • SELECT DISTINCT PhoneNumber FROM Employee_Info;

‘ORDER BY’ 

The ‘ORDER BY’ statement is used to sort the required results in ascending or descending order. The results are sorted in ascending order by default. Yet, if you wish to get the required results in descending order, you have to use the DESC keyword.

Syntax

  • SELECT Column1, Column2, …ColumnN
  • FROM TableName
  • ORDER BY Column1, Column2, … ASC|DESC;

Example

Select all employees from the ‘Employee_Info’ table sorted by EmergencyContactName:

  • SELECT * FROM Employee_Info
  • ORDER BY EmergencyContactName;

Select all employees from the ‘Employee_Info’ table sorted by EmergencyContactName and EmployeeName:

  • SELECT * FROM Employee_Info
  • ORDER BY EmergencyContactName, EmployeeName;

Select all employees from the ‘Employee_Info’ table sorted by EmergencyContactName in Descending order and EmployeeName in Ascending order:

  • SELECT * FROM Employee_Info
  • ORDER BY EmergencyContactName ASC, EmployeeName DESC;

‘GROUP BY’ 

This ‘GROUP BY’ statement is used with the aggregate functions to group the result-set by one or more columns.

Syntax

  • SELECT Column1, Column2,…, ColumnN
  • FROM TableName
  • WHERE Condition
  • GROUP BY ColumnName(s)
  • ORDER BY ColumnName(s);

Example

To list the number of employees from each city.

  • SELECT COUNT(EmployeeID), City
  • FROM Employee_Info
  • GROUP BY City;

‘HAVING’ Clause

The ‘HAVING’ clause is used in SQL because the WHERE keyword cannot be used everywhere.

Syntax

  • SELECT ColumnName(s)
  • FROM TableName
  • WHERE Condition
  • GROUP BY ColumnName(s)
  • HAVING Condition
  • ORDER BY ColumnName(s);

Example

To list the number of employees in each city. The employees should be sorted high to low and only those cities must be included who have more than 5 employees:

SQL Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download
  • SELECT COUNT(EmployeeID), City
  • FROM Employee_Info
  • GROUP BY City
  • HAVING COUNT(EmployeeID) > 2
  • ORDER BY COUNT(EmployeeID) DESC;

‘SELECT INTO’ 

The ‘SELECT INTO’ statement is used to copy data from one table to another.

Syntax

  • SELECT *
  • INTO NewTable [IN ExternalDB]
  • FROM OldTable
  •  WHERE Condition;

Example

To create a backup of database ‘Employee

  • SELECT * INTO EmployeeBackup
  • FROM Employee;

To select only few columns from Employee

  • SELECT EmployeeName, PhoneNumber INTO EmployeeContactDetails
  • FROM Employee;
  • SELECT * INTO BlrEmployee
  • FROM Employee
  • WHERE City = ‘Bangalore’;

Advantages of Data Manipulation Language

  1. 1. Data stored in the database can easily be modified anytime and that too very easily using the DML commands. DML provides the facility of the efficient and fast interaction of the human (end-user using the system) with the system or the backend data stored in the database.
  1. 2. That concludes our brief introduction to the SQL Data Manipulation Language. Each database has its own flavor of SQL and things improve with each version. If you get a chance check out the Row Limiting SQL Clause section in my PluralSight course OCP 12c Enhancements for the DBA.

Are you looking training with Right Jobs?

Contact Us

Popular Courses