Data Manipulation Language (DML)

Data Manipulation Language (DML)

A data manipulation language (DML) is a family of syntax elements similar to a computer programming language used for Selecting , inserting, deleting and updating data in a database. Performing read-only queries of data is sometimes also considered a component of DML.

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.

A data manipulation language (DML) is a family of computer languages including commands permitting users to manipulate data in a database. This manipulation involves inserting data into database tables, retrieving existing data, deleting data from existing tables and modifying existing data. DML is mostly incorporated in SQL databases.

Data manipulation language comprises the SQL data change statements, which modify stored data but not the schema or database objects. Manipulation of persistent database objects, e.g., tables or stored procedures, via the SQL schema statements, rather than the data stored within them, is considered to be part of a separate data definition language. In SQL these two categories are similar in their detailed syntax, data types, expressions etc., but distinct in their overall function.

DML resembles simple English language and enhances efficient user interaction with the system. The functional capability of DML is organized in manipulation commands like SELECT, UPDATE, INSERT INTO and DELETE FROM, as described below:

SELECT: This command is used to retrieve rows from a table. The select syntax is SELECT [column name(s)] from [table name] where [conditions]. Select is the most widely used DML command in SQL.

UPDATE: This command modifies data of one or more records. An update command syntax is UPDATE table name SET column name = value where [condition].

INSERT: This command adds one or more records to a database table. The insert command syntax is INSERT INTO table name [column(s)] VALUES [value(s)].

DELETE: This command removes one or more records from a table according to specified conditions. Delete command syntax is DELETE FROM table name where [condition]. 74

Data manipulation languages have their functional capability organized by the initial word in a statement, which is almost always a verb. In the case of SQL, these verbs are:

SELECT ... FROM ... WHERE ...

INSERT INTO ... VALUES ...

UPDATE ... SET ... WHERE ...

DELETE FROM ... WHERE ...

1.Select Command

Syntax

SELECT [DISTINCT|ALL]{*|column|column_expression [AS new_name][,…]}

 FROM table_name [alias] [, … ]

 [WHERE condition]

 [GROUP BY column_list]

 [HAVING condition]

 [ORDER BY column_list [ASC|DESC]];

 column represents a column name.

 column_expression represents an expression on a column.

 table_name is the name of an existing database table or view.

 FROM specifies the table(s) to be used.

 WHERE filters the rows subject to some condition.

 GROUP BY forms groups of rows with the same column name.

 SELECT specifies which column are to appear in the output.

 ORDER BY specifies the order of the output.

 Order of the clauses in the SELECT statement can not be changed.

 The result of a query is another table.

 Asterisk (*) means all columns.

Five aggregation functions defined in SQL:

COUNT returns the number of rows in a specified column.

SUM returns the sum of the values in a specified column.

AVG returns the average of the values in a specified column.

MIN returns the smallest value in a specified column.

MAX returns the largest value in a specified column.

Examples:

Property (PropertyNo, Street, City, postcode, Type, OwnerNo, Rooms, Rent)

How many properties cost more than 350 per month to rent?

SELECT COUNT(*) AS count FROM property WHERE rent > 350;

Subqueries

 A complete SELECT statement can be embedded (subselect) within another SELECT statement.

 A subselect can be used in the WHERE and HAVING clauses of the outer SELECT statement (nested query).

 A subquery can be used immediately following a relational operator.

 Subquery always enclosed in parentheses.

Type of subquery:

 A scalar subquery returns a single column and a single row (singlevalue).

 A row subquery returns multiple columns, but a single row.

 A table subquery returns one or more columns and multiple rows.

STAFF (sno, fname, lname, position, sex, DOB, salary, bno)

BRANCH (bno, street, city, postcode)

Example:

List the staff who work in the branch at ‗163 Main St‘.

SELECT sno, fname, lname, position FROM staff WHERE bno = (SELECT bno FROM branch WHERE street = ‗163 Main St‘);

Modifying Data in the DB (UPDATE)

Syntax

 UPDATE table_name

 SET column_name1 = data_value1 [, column_namei = data_valuei ...]

 [WHERE search_condition]

 table_name may be either a base table or an updatable view.

 The SET clause specifies the names of one or more columns that are updated for all rows in the table.

 Only rows that satisfy the search_condition are updated.

 data_values must be compatible with the data types for the corresponding columns.