Selecting rows from a table

Let us see how to retrieve data of a table. SELECT command of SQL is used to retrieve data from one or more tables. It implements operators of relational algebra such as projection, and selection.

The following is the syntax of SELECT command. The syntax given here is incomplete. For complete syntax, please refer to online documentation.


SELECT [DISTINCT | ALL]
{* | table.* | expr } [alias ]
[ {table}.*| expr } [alias ] ] ...

FROM [schema.]object
[, [schema.]object ] ...

[WHERE condition]

[ORDER BY {expr|position} [ASC | DESC]
[, {expr|position} [ASC | DESC]] ...]



schema is the name of the user whose table is being accessed. Schema prefix is not required if the table is in the current account. Schema prefix is required while we are accessing a table of some other account and not ours.

The following is an example of a basic SELECT command.

select * from courses;

CCODE NAME DURATION FEE PREREQUISITE
----- -------------------- --------- --------- -------------------------
ora Oracle database 25 4500 Windows
vbnet VB.NET 30 5500 Windows and programming
c C programming 20 3500 Computer Awareness
asp ASP.NET 25 5000 Internet and programming
java Java Language 25 4500 C language
xml XML Programming 15 4000 HTML,Scripting, ASP/JSP

The simplest SELECT command contains the following:

 Columns to be displayed. If * is given, all columns are selected.
 The name of the table from where rows are to be retrieved.

Projection
Projection is the operation where we select only a few columns out of the available columns. The following is an example of projection.

select name,fee from courses;

NAME FEE
-------------------- ---------
Oracle database 4500
VB.NET 5500
C programming 3500
ASP.NET 5000
Java Language 4500
XML Programming 4000



Using expressions in SELECT command
It is also possible to include expressions in the list of columns. For example, the following SELECT will display discount to be given for each course.

select name,fee, fee * 0.15 from courses;

NAME FEE FEE*0.15
-------------------- --------- ---------
Oracle database 4500 675
VB.NET 5500 825
C programming 3500 525
ASP.NET 5000 750
Java Language 4500 675
XML Programming 4000 600

Column Alias
The column heading of an expression will be the expression itself. However, as it may not be meaningful to have expression as the result of column heading, we can give an alias to the column so that alias is displayed as the column heading.

The following example will use alias DISCOUNT for the expression FEE * 0.15.

select name, fee, fee * 0.15 DISCOUNT from courses

NAME FEE DISCOUNT
-------------------- --------- ---------
Oracle database 4500 675
VB.NET 5500 825
C programming 3500 525
ASP.NET 5000 750
Java Language 4500 675
XML Programming 4000 600


The following are the arithmetic operators that can be used in expressions.

Operator Description
+ Add
- Subtract
* Multiply
/ Divide


ORDER BY clause
It is possible to display the rows of a table in the required order using ORDER BY clause. It is used to sort rows on the given column(s) and in the given order at the time of retrieving rows. Remember, sorting takes place on the row that are retrieved and in no way affects the rows in the table. That means the order of the rows will remain unchanged.

Note: ORDER BY must always be the last of all clauses used in the SELECT command.

The following SELECT command displays the rows after sorting rows on course fee.

select name, fee from courses order by fee;

NAME FEE
-------------------- ---------
C programming 3500
XML Programming 4000
Oracle database 4500
Java Language 4500
ASP.NET 5000
VB.NET 5500


Note: Null values are placed at the end in ascending order and at the beginning in descending order.

The default order for sorting is ascending. Use option DESC to sort in the descending order. It is also possible to sort on more than one column.

To sort rows of COURSES table in the ascending order of DURATION and descending order of FEE, enter:

select name, duration, fee from courses
order by duration , fee desc;

NAME DURATION FEE
-------------------- --------- ---------
XML Programming 15 4000
C programming 20 3500
ASP.NET 25 5000
Oracle database 25 4500
Java Language 25 4500
VB.NET 30 5500


First, all rows are sorted in the ascending order of DURATION column. Then the rows that have same value in DURATION column will be further sorted in the descending order of FEE column.
Using column position
Instead of giving the name of the column, you can also give the position of the column on which you want to sort rows.

For example, the following SELECT sorts rows based on discount to be given to each course.

select name, fee, fee * 0.15
from courses
order by 3;

NAME FEE FEE*0.15
-------------------- --------- ---------
C programming 3500 525
XML Programming 4000 600
Oracle database 4500 675
Java Language 4500 675
ASP.NET 5000 750
VB.NET 5500 825


Note: Column position refers to position of the column in the selected columns and not the position of the column in the table.

The above command uses column position in ORDER BY clause. Alternatively you can use column alias in ORDER BY clause as follows:

select name, fee, fee * 0.15 discount
from courses
order by discount;

NAME FEE DISCOUNT
-------------------- --------- ---------
C programming 3500 525
XML Programming 4000 600
Oracle database 4500 675
Java Language 4500 675
ASP.NET 5000 750
VB.NET 5500 825

Selection
It is possible to select only the required rows using WHERE clause of SELECT command. It implements selection operator of relational algebra.

WHERE clause specifies the condition that rows must satisfy in order to be selected. The following example select rows where FEE is more than or equal to 5000.

select name, fee from courses
where fee >= 5000

NAME FEE
-------------------- ---------
VB.NET 5500
ASP.NET 5000

The following relational and logical operators are used to form condition of WHERE clause. Logical operators – AND, OR – are used to combine conditions. NOT operator reverses the result of the condition. If condition returns true, NOT will make the overall condition false.


Operator
Meaning
= Equal to
!= or <> Not equal to
>= Greater than or equal to
<= Less than or equal to > Greater than
< Less than AND Logical ANDing OR Logical Oring NOT Negates result of condition. The following SELECT command displays the courses where duration is more than 15 days and course fee is less than 4000. select * from courses where duration > 15 and fee < 4000;

CCODE NAME DURATION FEE PREREQUISITE
----- -------------------- --------- --------- -------------------
c C programming 20 3500 Computer Awareness

The following SELECT command retrieves the details of course with code ORA.

select * from courses
where ccode = 'ora';

CCODE NAME DURATION FEE PREREQUISITE
----- -------------------- --------- --------- ----------------
ora Oracle database 25 4500 Windows


Note: When comparing strings, the case of the string must match. Lowercase letters are not equivalent to uppercase letters.