Data Manipulation Language

Data Manipulation Language

Data Manipulation Language (DML) deals with the basic SQL queries. There are many SQL commands; The SELECT command is one such DML commands, which is used to retrieve information from the database. The basic form of SQL SELECT statement is also called a mapping. The SELECT expression consists of three clauses: SELECT, FROM and WHERE.
  • The SELECT clause corresponds to the projection operation of the relational algebra. It is used to list the attributes desired in the result of a query.
  • The FROM clause correspond to the Cartesian-product operation of the relational algebra. It lists the relations to be scanned in the evaluation of the expression.
  • The WHERE clause corresponds to the selection predicate of the relational algebra. It consists of predicate involving attributes of the relations that appears in the FROM clause.
That the term select has different meaning in SQL and in the relational algebra is an unfortunate historical fact. In general format of SQL statements is as follows
SELECT     < attribute List>
FROM         < table list>
WHERE       <condition>
  • < attribute List> is a list of attribute name whose values are retrieved by the query.
  • < table list> is a list  the relation names required to process the query.
  • <condition> is a conditional search expression that identifies the tuples to be retrieved by the query.

In other words the same thing can be expressed as follows:

Select   A1, A2……An
From r1,r2…….m
Where P.
Each A, represents an attribute, and each r1 a relation- P is a PREDICATE. The query is equivalent to the relational-algebra expression. The query is equivalent to the relational-algebra expression
πA1, A2…….An(sp(r1 X r2 X …… rm)).
If WHERE clause is committed, the predicate P is true

The SIMPLE SELECT statements in DML

  • Write a QUERY to list all the BRANCH NAMES in the loan relation.
Select br-name from loan
The result of this query is a relation consisting of a single attribute with the heading br-name. This query will list each branch by-name once for every tuple in which it appears in the loan relation. In case if we want to eliminate duplicates,  we use the key word DISTINCT after SELECT. We can rewrite the above query to eliminate duplicate value as
Select br-name from loan
Note that SQL allows us to use the keyword ALL to specify explicitly that duplicates are not removed:
Select all br-name from loan
Since duplicate retention is default, we will not use ALL. That symbol* is used to denote “all attributes”. In order to list all the attribute value of any relation we write the query as follows
Select *from loan
If we have specified more than one relation in the FROM clause, then all the attributes of all the relations are listed. The SELECT statement can also contain arithmetic expressions involving the operators +, -, *, /, and operating on constants or attributes of tuples.
Now if we have to write a query to multiply the amount attribute of all tuples in the loan relation, we can achieve the same as follows:
Select br_name, loan_no, amount*200 FROM loan
This will return a relation that is same as loan relation, except that, the attribute amount is multiplied by 200.

Leave a Reply

Your email address will not be published.