Data Manipulation Language

Overview

The SQL DML consists of syntactic constructs for querying and modifying the data stored in the database. The DML builds on top of the relational algebra.

Clauses

FROM

The FROM clause is used to list the relations to be accessed during query evaluation.

SELECT

The SELECT clause is used to list the attributes desired in the result of a query.

The asterisk symbol (*) can be used to denote "all attributes."

To ensure no duplicate rows are returned in the result, the DISTINCT keyword is used. In contrast, ALL can be used to indicate duplicates are explicitly permitted.

ORDER BY

The ORDER BY clause allows listing tuples returned by a query in a specific order. The ASC or DESC keywords can be appended to an attribute name in order to dictate ascending or descending order respectively.

WHERE

Ther WHERE clause specifies a predicate that rows must satisfy in order to be included in the results of a query. There exist a number of functions and operators for use in this clause:

• BETWEEN. Allows specifying an inclusive range.
  • A BETWEEN X AND Y is equivalent to A >= X AND A <= Y.

Renaming

The AS keyword can be found in the SELECT clause to rename attributes or the FROM clause to rename relations. An identifier used to rename a relation is called a correlation name in the SQL standard. It is sometimes referred to instead as a table alias or correlation variable.

Set Operations

Set operations are used to combine compatible relations.

Union

The UNION operation gathers all tuples from both argument relations with duplicates removed. Duplicates may be preserved by specifying UNION ALL.

Intersection

The INTERSECT operation yields tuples in both argument relations with duplicates removed. Duplicates may be preserved by specifying INTERSECT ALL.

Difference

The EXCEPT operation keeps tuples in one relation not present in the other. Duplicates in the input are removed. Duplicates may be preserved by specifying EXCEPT ALL.

Aggregation

Aggregate functions refer to functions that take a collection of values as input and return a single value. SQL offers five standard aggregate functions:

• AVG. Computes the average value of a collection of numbers.
• MIN. Computes the smallest value of a collection of values.
• MAX. Computes the largest value of a collection of values.
• SUM. Computes the total summation of a collection of numbers.
• COUNT. Computes the number of rows satisfying the specified predicate.
  • An * may be specified to count the number of tuples in a relation.

Other than with COUNT(*), ALL and DISTINCT can be specified alongside the aggregate function's arguments.