Data Manipulation Language

A DML or refers to a computer programming language that allows you to add, delete, and alter data in a database. A DML is typically a sub language of a larger database language like SQL, with the DML containing some of the language's operators.

Up to this point we have learned how to retrieve data from a database using every selection criterion imaginable. After this data is retrieved, we can use it in an application program or edit it. However, we may have wondered how to enter data into the database in the first place.

We may also be wondering what to do with data that has been edited. We discuss three SQL statements that enable we to manipulate the data within a database’s table. The three statements are as follows:

  • The INSERT statement

  • The UPDATE statement

  • The DELETE statement

We may have used a PC-based product such as Access, dBASE IV, or FoxPro to enter our data in the past. These products come packaged with excellent tools to enter, edit, and delete records from databases.

One reason that SQL provides data manipulation statements is that it is primarily used within application programs that enable the user to edit the data using the application’s own tools.

The SQL programmer needs to be able to return the data to the database using SQL. In addition, most large-scale database systems are not designed with the database designer or programmer in mind. Because these systems are designed to be used in high-volume, multi-user environments, the primary design emphasis is placed on the query optimizer and data retrieval engines.

INSERT Statement

The INSERT statement enables us to enter data into the database. It can be broken down into two statements:

INSERT...VALUES

And

INSERT...SELECT

The INSERT…VALUES Statement

The INSERT…VALUES statement enters data into a table one record at a time. It is useful for small operations that deal with just a few records. The syntax of this statement is as follows:

Syntax:

SQL> INSERT INTO table_name
(col1, col2...)
VALUES(value1, value2...)

The basic format of the INSERT…VALUES statement adds a record to a table using the columns we give it and the corresponding values we instruct it to add. We must follow three rules when inserting data into a table with the INSERT…VALUES statement:

  • The values used must be the same datatype as the fields they are being added to.

  • The data’s size must be within the column’s size. For instance, we cannot add an 80-character string to a 40-character column.

  • The data’s location in the VALUES list must correspond to the location in the column list of the column it is being added to. (That is, the first value must be entered into the first column, the second value into the second column, and so on.)

Inserting NULL Values

  • We learn how to create tables using the CREATE TABLE statement.

  • For now, all we need to know is that when a column is created, it can have several different limitations placed upon it.

  • One of these limitations is that the column should (or should not) be allowed to contain NULL values.

  • A NULL value means that the value is empty.

  • It is neither a zero, in the case of an integer, nor a space, in the case of a string. Instead, no data at all exists for that record’s column.

  • If a column is defined as NOT NULL (that column is not allowed to contain a NULL value), we must insert a value for that column when using the INSERT statement.

  • The INSERT is canceled if this rule is broken, and we should receive a descriptive error message concerning our error.

Warning: We could insert spaces for a null column, but these spaces will be treated as a value. NULL simply means nothing is there.

SQL> INSERT INTO collection VALUES
('SPORES MILDEW FUNGUS', 50.00, ' ');
1 row inserted.

Inserting Unique Values

Many database management systems also allow us to create a UNIQUE column attribute. This attribute means that within the current table, the values within this column must be completely unique and cannot appear more than once. This limitation can cause problems when inserting or updating values into an existing table, as the following exchange demonstrates:

SQL> INSERT INTO COLLECTION VALUES('STRING', 50, 'MORE STRING');
INSERT INTO COLLECTION VALUES('STRING', 50, 'MORE STRING')
*
ERROR at line 1:
ORA-00001: unique constraint (PERKINS.UNQ_COLLECTION_ITEM) violated

The INSERT…SELECT Statement

  • The INSERT…VALUES statement is useful when adding single records to a database table, but it obviously has limitations.

  • Would we like to use it to add 25,000 records to a table? In situations like this, the INSERT…SELECT statement is much more beneficial.

  • It enables the programmer to copy information from a table or group of tables into another table. We will want to use this statement in several situations.

  • Lookup tables are often created for performance gains. Lookup tables can contain data that is spread out across multiple tables in multiple databases.

  • Because multiple-table joins are slower to process than simple queries, it is much quicker to execute a SELECT query against a lookup table than to execute a long, complicated joined query.

  • Lookup tables are often stored on the client machines in client/server environments to reduce network traffic.

Many database systems also support temporary tables. Temporary tables exist for the life of our database connection and are deleted when our connection is terminated. The INSERT…SELECT statement can take the output of a SELECT statement and insert these values into a temporary table.

Example:

SQL> INSERT INTO tmp_tbl SELECT * FROM TABLE;
19,999 rows inserted.

UPDATE Statement

The purpose of the UPDATE statement is to change the values of existing records.

Syntax:

SQL> UPDATE table_name
SET columnname1 = value1
[, columname2 = value2]...
WHERE search_condition

This statement checks the WHERE clause first. For all records in the given table in which the WHERE clause evaluates to TRUE, the corresponding value is updated.

DELETE Statement

In addition to adding data to a database, we will also need to delete data from a database. The syntax for the DELETE statement is

Syntax:

SQL> DELETE FROM tablename WHERE condition

The first thing we will probably notice about the DELETE command is that it doesn’t have a prompt. Users are accustomed to being prompted for assurance when, for instance, a directory or file is deleted at the operating system level. Are we sure? (Y/N) is a common question asked before the operation is performed. Using SQL, when we instruct the DBMS to delete a group of records from a table, it obeys our command without asking. That is, when we tell SQL to delete a group of records, it will really do it!

Depending on the use of the DELETE statements WHERE clause, SQL can do the following:

  • Delete single row

  • Delete multiple rows

  • Delete all rows

  • Delete no rows

Here are several points to remember when using the DELETE statement:

  • The DELETE statement cannot delete an individual field’s values(use UPDATE instead). The DELETE statement deletes entire records from a single table

  • Like INSERT and UPDATE, deleting records from one table can cause referential integrity problems within other tables. Keep this potential problem area in mind when modifying data within a database

  • Using the DELETE statement deletes only records, not the table itself. Use the DROP TABLE statement to remove an entire table.

⌖ sql rdbms data-manipulation-language
← Data Definition Language
Security and Access →

Subscribe For More Content