6. Data Manipulation Language

One of the functions of a RDBMS is to provide a means of manipulating data within the database. This includes operations such as

• Insert
• Delete
• Update
• Process data

This is the role of the data manipulation language (DML) built in to the system.

However, writing commands by hand can be slow and error prone. So to help with this, many systems allow the user to set up a task by using 'Query by Example'. The users are presented with a graphical view of the tables and they then use a number of icons such as 'filter' to manipulate the data. With some systems you can drag fields graphically into appropriate areas on the screen to set up the query.

Behind the scenes, the QBE tool is compiling and running the required DML commands.

It means that users do not need to have a sophisticated understanding of database command languages in order to use the database.

Case study

This author used to work in a large corporation that had a vast data warehouse. It had millions of records going back many years. One of the day-to-day tasks of the staff was to generate comparison reports with like-for-like sales.

In order to do this, a number of QBE tools had been set up by the IT database staff for us to use. We opened up the QBE tool and simply entered a date range, graphically pulled across the fields we were interested in and then told it to run. The run itself could take several hours which meant tens of millions of records were being manipulated to produce the final report.

If a more complicated or non-standard query had to be run, then a meeting was arranged with an in-house database analyst. They would elicit details of the report needed, then the analyst would go and set up a specific DML query.