Retrieving Data Using the SQL SELECT Statement

SELECT statement is very simple and yet powerful tool to retrieve data out of database. The complexity of this command increases as we start to fetch data from multiple tables. Anyways no need to go in depth of SELECT statement right now. Lets check the basic format of the SELECT statement.

SYNTAX:
SELECT column1, column2,.....,columnn 
FROM Table_Name
WHERE <Conditions>;

Example:
SELECT first_name, last_name
FROM employees
WHERE last_name='Ranjan';

The above example will look into the employees table and get the list of employees whose last name is Ranjan.

Note:
1. SQL statements are case-insensitive, however I prefer to write the SQL reserved key words in upper case.
2. Semi-colons(:) are used as statement terminators.

DESCRIBE or DESC command is used to view the structural metadata of an object. The information include column definitions for the specified table, view or synonym, or the specifications for the specified function or procedure.

SYNTAX:
DESC[RIBE] {[schema.]object[@db_link]}

The three concepts of projection, selection, and joining, which form the underlying basis for the capabilities of the SELECT statement.
Projection refers to the restriction on columns. We may select one or more columns depending upon the requirement.
Selection refers to the restriction of the rows selected from a relation(table).
Joining refers to the interaction of tables with each other in a query.

Basic SELECT statements:

SELECT * FROM employees;
The above sql statement fetches all columns and rows from the employee table. The symbol '*' is used to select all columns from the table.

SELECT DISTINCT last_name FROM EMPLOYEES;
The above statement fetches the last_name column and all the last names are distinct and non-repeating. In short it eliminates the duplicate rows.

SELECT table_name FROM user_tables;
The above statement queries the data dictionary for the list of tables belong to the current user.

SQL Expressions & Operators:

Arithmetic Operators:

Precedence level	Operator Symbol	Operation
Highest	()	Brackets or parentheses
Medium	/	Division
Medium	*	Multiplication
Lowest	-	Subtraction
Lowest	+	Addition

The operators with the same precedence are evaluated from left to right.

In the above example first the operation inside the parenthesis is performed i.e. END_DATE-START_DATE. One more day is added to the result and displayed as DAYS_OF_EMPLOYMENT. The column DAYS_OF_EMPLOYMENT appearing in the result is known as ALIAS. Aliasing is done for the better representation of the result.

Note: If an alias contains more than one word or if the case of an alias must be preserved, then it should be  enclosed in double quotation marks.

Character and String Concatenation Operator:
The double pipe symbols(||) represents the character concatenation operator. It is used to join character expressions or columns together.

In the above example, the concatenation operator has been used to concatenate the FIRST_NAME & LAST_NAME from the EMPLOYEES table. The where clause has been used for the restriction on the number of rows. It displays only those columns for which the FIRST_NAME begins with the alphabet 'R'.

Dual Table:
It is a special one row table available in oracle database. This table has one column named DUMMY of type VARCHAR2(1).

SQL>DESC DUAL
Name  Null Type       
----- ---- -----------
DUMMY      VARCHAR2(1)

SQL>SELECT SYSDATE FROM DUAL;
SYSDATE
---------------
13-AUG-13

SQL>SELECT 3+5 FROM DUAL;
3+5
--------------
8

The above few examples shows the usage of the dual table. In first example we saw that it was used to display the system date and was used with the SYSDATE function. Function always needs select statement to return the value, it cannot be used as stand alone. So, we have used the dual table to show the result of the function SYSDATE.

 In the second example we saw that the dual table was used to hold the result of the sum of number 3 & 5.

NULL
NULL is nothing. It differs from '0' and blank space as both of them occupies some space but NULL does not.
The NULL should be handled carefully as it may lead to incorrect result in complex SQL statement. To replace the NULL from the result, we user a NVL function which will be discussed in the later section.

Few take away points:
1. Columns in tables store different types of data using various data types, the most common of which are NUMBER, VARCHAR2, DATE, and TIMESTAMP.
2. The data type NUMBER(x,y) implies that numeric information stored in this column can have at most x digits, but at least y of these digits must appear on the right hand side of the decimal point.
3. The DESCRIBE command lists the names, data types, and nullable status of all columns in a table.
4. Mandatory columns are also referred to as NOT NULL columns