In order to retrieve the information stored in the database, the SQL query language is used. In
the following we restrict our attention to simple SQL queries and defer the discussion of more
complex queries to Section 1.5
In SQL a query has the following (simplified) form (components in brackets [ ] are optional):
select [distinct]
from
[ where ]
[ order by ]
1.2.1 Selecting Columns
The columns to be selected from a table are specified after the keyword select. This operation
is also called projection. For example, the query
select LOC, DEPTNO from DEPT;
lists only the number and the location for each tuple from the relation DEPT. If all columns
should be selected, the asterisk symbol “” can be used to denote all attributes. The query
select from EMP;
retrieves all tuples with all columns from the table EMP. Instead of an attribute name, the select
clause may also contain arithmetic expressions involving arithmetic operators etc.
select ENAME, DEPTNO, SAL 1.55 from EMP;
3
For the different data types supported in Oracle, several operators and functions are provided:
• for numbers: abs, cos, sin, exp, log, power, mod, sqrt, +,−, , /, . . .
• for strings: chr, concat(string1, string2), lower, upper, replace(string, search string,
replacement string), translate, substr(string, m, n), length, to date, . . .
• for the date data type: add month, month between, next day, to char, . . .
The usage of these operations is described in detail in the SQL*Plus help system (see also
Section 2).
Consider the query
select DEPTNO from EMP;
which retrieves the department number for each tuple. Typically, some numbers will appear
more than only once in the query result, that is, duplicate result tuples are not automatically
eliminated. Inserting the keyword distinct after the keyword select, however, forces the
elimination of duplicates from the query result.
It is also possible to specify a sorting order in which the result tuples of a query are displayed.
For this the order by clause is used and which has one or more attributes listed in the select
clause as parameter. desc specifies a descending order and asc specifies an ascending order
(this is also the default order). For example, the query
select ENAME, DEPTNO, HIREDATE from EMP;
from EMP
order by DEPTNO [asc], HIREDATE desc;
displays the result in an ascending order by the attribute DEPTNO. If two tuples have the same
attribute value for DEPTNO, the sorting criteria is a descending order by the attribute values of
HIREDATE. For the above query, we would get the following output:
ENAME DEPTNO HIREDATE
FORD 10 03-DEC-81
SMITH 20 17-DEC-80
BLAKE 30 01-MAY-81
WARD 30 22-FEB-81
ALLEN 30 20-FEB-81
...........................
No comments:
Post a Comment