Select syntax
How to query the Oracle database

When you query an object, you could be querying a table, a synonym or a view,
but this will make no difference to the syntax you need to use or to the result you will obtain.

A table consists of a number of columns and rows and your query will be built so that you decide which rows and which columns to return.

The simplified syntax for the queries we'll be using in this tutorial is as follows:

select <column list> from <table> where <row criteria> ;

For example: (log in as scott/tiger)

Table DEPT looks like this :

There are 3 columns : DEPTNO, DNAME and LOC
There are 4 rows.

Column criteria

Let's query all columns and all rows:

SQL> select deptno, dname, loc from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Time saving tip: use * instead of the whole column list if you plan to return each column
in the table:


Let's only query the Location column in the table.

SQL> select loc from dept;

LOC
-------------
NEW YORK
DALLAS
CHICAGO
BOSTON

SQL> select dname, loc from dept;

DNAME          LOC
-------------- -------------
ACCOUNTING     NEW YORK
RESEARCH       DALLAS
SALES          CHICAGO
OPERATIONS     BOSTON

Row criteria

To specify only a subset of rows to be returned,
you will use the WHERE clause.

Let's query only the rows in the dept table belonging to the SALES department.
We will query all columns.

SQL> select * from dept where dname = 'SALES';

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

SQL> select * from dept where deptno = 30;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

SQL> select * from dept where deptno > 20;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

What if you want to make your where clause more complex ?

Let's look for any rows that have 'SALES' as a department name but also any rows that have 'BOSTON' as the location.

This means that
any row contains the value 'SALES' in the DNAME column,
as well as
any row containing the value 'BOSTON' in the LOC column
will be included in the result set.

You can do so by adding another section to the where clause and using the 'OR' term to glue them together.

SQL> select * from dept 
     where dname = 'SALES' or loc = 'BOSTON';

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * from dept 
     where dname = 'SALES' and loc = 'BOSTON';

no rows 

Note on datatypes
Your where clause will have to be aware of which type of data you are using as query criteria.
In other words, you must be sure to use the correct datatype in your where clause.

For example, DNAME is a varchar column, so you must put single quotes around your query criteria.
(Only number datatypes do not need single quotes)

If you try to specify SALES in your where clause like this :

where dname = SALES;

you will receive an error:

ORA-00904: "SALES": invalid identifier

because you are expected to supply single quotes around a varchar datatype like this:

where dname = 'SALES';


How do you know the datatype of a column in a table ?

Use DESCRIBE to see which columns are in a table and what their datatypes are:

SQL> desc dept;
 Name                Null?    Type
 ------------------- -------- -----------------

 DEPTNO                       NUMBER(2)
 DNAME                        VARCHAR2(14)
 LOC                          VARCHAR2(13)

Varchar tip
Oracle is case sensitive to data inside the tables,
unlike column and table names which are usually upper case unless you explicitly create them otherwise.

So, there is a difference between 'SALES', 'sales' and even 'Sales'.

If you are unsure of the case that is used in your data, you can force the result to upper (or lower)
case by calling the upper or lower function to convert the column data, like this:

... where lower(dname) = 'sales';.

Recommended reading: