Introduction of SQL Queries

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.

In SQL, a query has the following (simplified) form (components in brackets [ ] are optional):
select [distinct] from [ where ] [ order by ]

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;

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, . . .

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
………………………

Selection of Tuples

Up to now we have only focused on selecting (some) attributes of all tuples from a table. If one is
interested in tuples that satisfy certain conditions, the where clause is used. In a where clause
simple conditions based on comparison operators can be combined using the logical connectives
and, or, and not to form complex conditions. Conditions may also include pattern matching
operations and even subqueries

Example: List the job title and the salary of those employees whose manager has the
number 7698 or 7566 and who earn more than 1500:
select JOB, SAL
from EMP
where (MGR = 7698 or MGR = 7566) and SAL > 1500;

For all data types, the comparison operators =, != or ,, are allowed in the
conditions of a where clause.

Further comparison operators are:

• Set Conditions: [not] in ()
Example: select * from DEPT where DEPTNO in (20,30);

• Null value: is [not] null,
i.e., for a tuple to be selected there must (not) exist a defined value for this column.
Example: select * from EMP where MGR is not null;
Note: the operations = null and ! = null are not defined!

• Domain conditions: [not] between and
Example: • select EMPNO, ENAME, SAL from EMP
where SAL between 1500 and 2500;

• select ENAME from EMP
where HIREDATE between ’02-APR-81’ and ’08-SEP-81’;