SQL Data Manipulation Language (DML)

One of the most utilized ways to communicate with relational database management systems (RDBMS) is through Structured Query Languages (SQL). As noted in What Is SQL, there are several SQL sub-languages. The statements that these sub-languages encompass may vary depending who is talking about them. Two of the broadest categorize are:

• Data Description Language (DDL) statements for management of objects within the database.

• Database Manipulation Language (DML) statements for data management within the database.

Table 1 shows the DML keywords for four popular databases. This list may not be complete depending on the version of the database you are utilizing. It does however, give you an idea how each vendor implementation of SQL varies.

Table 1: Data Manipulation Language (DML) statements per RDBMS

The rest of this guide will focus on the DML statements that are available across the databases listed in Table 1 that allow users to select (query), insert (add), update (modify), and delete data.

There are several sites that allow you to practice utilizing SQL. I will be utilizing SQL Fiddle which, at the time of the writing of this guide, supports several different databases and versions. The syntax I will show was tested using the Oracle database. This guide does not cover DDL statements but to be able to do DML you have to execute some DDL. To create tables within SQL Fiddle, execute the following in the "Build Schema" window:

Other example statements within this guide will need to be executed within the "Run SQL" window.

A basic INSERT statement can take on two forms. The first is to specify columns names and the values to be inserted.

NOTE: The commit here is a transactional control key word that saves the data entered.

When specifying columns, you should be specific in the data you enter but the order does not matter.

If you are going to insert data into all columns, then you can use the second form and just specify the values. The order of the values must match the table column order.

If you are following along in SQL Fiddle, you will need to do some additional inserts into the EMPLOYEES table to have data in it as well.

The SELECT statement is used to retrieve data from one or more objects within a database. The simplest form that a SELECT statement can take in Oracle is along the lines of:

This statement retrieves all the data from the table employees, unfiltered.

NOTE: If your SELECT statements start to return no rows, put all the insert statements into the "Build Schema" window with the create table statements and build the schema again.

The following statement only returns the last name and department ID from the employees table.

You can go further and filter the data based on other criteria using the WHERE clause. This statement filters that data based on the DEPTID column and only returns the data for when the DEPTID=1.

You do not have to display the column you are filtering on.

You can use the select statement to display information from multiple tables when there is some type of connection between them. In our examples, the employees table only has the department ID. If you want to display the department name, you have to include the departments table and use the WHERE clause join the tables together on the DEPTID field.

These are very simple SELECT statements. Depending on the vendor specification, things can get a lot more complicated but, at the same time, very useful. For example, Oracle provides analytic functions that enable calculation of rankings, percentiles, doing moving window calculations, linear regression statistics, lag/lead analysis, and first/last analysis.

Update statements allow you to change the data in specific columns for rows stored in a table. You can choose a specific row to update by including theWHERE clause and utilize a unique set of values. In the employees table the EMPLOYEEID is unique. If Fredrick decided to change his last name then we can update the LASTNAME column based on the EMPLOYEEID of 3.

You can also update multiple rows at the same time, based on the WHERE clause.

WARNING: If you don't use the WHERE clause, you will be updating all the rows.

NOTE: The ROLLBACK here is a transactional control key word that returns the data to its original state. If you find an error in your UPDATE or DELETE statement, you use ROLLBACK to reverse the changes. If you COMMIT the results, then ROLLBACK is not possible.

If there is data in a table that you no longer need, then you execute a DELETE statement to remove the data. Similar to the UPDATE statement, you need to use the WHERE clause to be specific on the data being deleted.

If you don not use the WHERE clause you will delete all the rows within the table.

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.

Tim Boles is an Oracle Database Administrator who has published multiple courses with Pluralsight. Check out the library of his courses here: https://app.pluralsight.com/profile/author/tim-boles.

He can be reached at tim2boles@gmail.com and twitter @timboles_dba.