To retrieve the data from Oracle database, you need to use the SQL SELECT statement. There are 3 basic steps to retrieve the data using SQL SELECT statement.
- Choose the columns
- Choose the rows
- Join the tables
Below is the sample basic SQL statement using SELECT command:-
| SELECT * FROM table; |
SELECT and FROM are the keywords. (*) mean select all columns.
If you need to retrieve selected columns from a table, then as below:-
| SELECT column1, column2, … column10 FROM table; |
(column1, column2, …) is a clause for a SQL statement. If you are not sure the column names for a specific table, then you can use the DESCRIBE command to display the structure.
| DESC tablename; |
You need to take note few things to write a SQL statements:-
- Not a case-sensitive.
- Can be more than one lines.
- Keywords cannot be abbreviated or split across lines.
- Clauses can put in separate lines.
- SQL statement end with semicolons (;) to terminate it before write another SQL statement. This is compulsory to use in SQL plus, and after (;) press ENTER key to run the command.
You can use the arithmetic operators (+, –, *, /) in SQL statement.
| SELECT column1, column2, column3 + 100 FROM table; |
| SELECT column1, 50*column2+500 FROM table; |
To understand a Null value:-
If a row of a column without any data, mean the value is null. Null value is not same as zero or a space, null value is mean nothing at all. Zero is a number, and a space is a character. In order to prevent the null value for that column, then we can use the ‘NOT NULL’ and ‘PRIMARY KEY’ to ensure no null value.
If you are using the arithmetic expression for the null value column, then your result is null.
To use Column Aliases:-
The purpose to use the Column Aliases in your SQL statement, is to rename a column heading to ease for understanding.
| SELECT column1 AS “First Name”, column2 “Last Name”, 50*column2+500 “Monthly Profit” FROM table; |
‘AS’ is an optional keyword, but if you have spaces or special characters (i.e. # or $), or need to be case-sensitive for the alias, then you need to use the double quotation marks (“ “) in your SQL statement.
To use Concatenation Operator:-
You can join the columns data value into one line result using the concatenation operator (||) in your SQL statement.
| SELECT column1||column2 AS “Full Name” FROM table; |
If you need to put a space between two columns as your result, then use the (‘ ‘) as below:-
| SELECT column1||’ ‘||column2 AS “Full Name” FROM table; |
To use Literal Character Strings:-
You need to add the number or characters or date to make it like a full sentence in your result, then you can use the single quotation mark (‘ ‘) in your SQL statement.
| SELECT column1 || ‘ is the First Name of ‘ || column2 FROM table; |
To use Alternative Quote (q) Operator:-
If you need to use a quote (q) in your Literal character String, then you can refer below example:-
| SELECT column1 || q'[ isn’t his First Name!]‘ || column2 FROM table; |
To remove the Duplicate Rows:-
When you retrieve more than one duplicate rows in your SQL SELECT statement, then you can use the DISTINCT command with your SQL SELECT statement to make sure display the unique records only.
| SELECT DISTINCT column1, column2 FROM table; |
