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