| ☰ See All Chapters |
Oracle SELECT Statement
The Oracle SELECT statement is used to extract data in a readable format according to the user’s request. SELECT statement can retrieve data from one or more than one tables, object tables, views, object views etc... Such inquiry into the database using the SELECT statement is called as query.
Syntax of SELECT Statement
SELECT * / <column1>, <column2> FROM <table-name1>, <table-name2> [WHERE < condition1/expression1 > and / OR <condition2 / expression2 >] [GROUP BY < column-name1 >.< column-name2 > [HAVING condition] [ORDER BY < column-name1 >.< column-name2 > [ ASC | DESC ] ] |
Anything specified in [ ] is optional.
The SELECT statement is used in conjunction with the FROM clause to extract data from the database in an organized, readable format
The SELECT keyword in a query is followed by a list of columns that you want displayed as part of the query output.
The asterisk (*) denotes that all columns in a table should be displayed as part of the output. The ALL option displays all values for a column, including duplicates. It is thought of as the default.
Selecting distinct columns option suppresses duplicate rows from being displayed in the output.
The FROM Clause
FROM clause inform database what table(s) to access to retrieve the desired data for the query.
FROM clause is the mandatory part to be used in conjunction with the SELECT statement.
The FROM clause must always list at least one table.
The FROM clause may contain one or more tables.
The WHERE Clause
WHERE clause is used for selecting the rows from tables by giving conditions using operators.
There can be more than one condition in the WHERE clause connected by the AND and OR operators.
ORDER BY Clause
ORDER BY clause is used for ordering the output of query either by ascending or descending.
If ORDER BY clause is not used results are ordered in a default order. Specify DESC for descending order.
A column listed in the ORDER BY clause can be abbreviated with an integer. The integer is a substitution for the actual column name (an alias for the purpose of the sort operation), identifying the position of the column after the SELECT keyword.
SELECT ITEM_DESC, ITEM _ID, COST FROM ITEMS WHERE COST < 20 ORDER BY 1;
|
In this query, the integer 1 represents the column PROD_DESC. The integer 2 represents the PROD_ID column, 3 represents the COST column, and so on.
We can order by multiple columns in a query, in such situations first result is sorted using column1, then in sorted table if same content is present in column1 then those rows are sorted according to column2.
All Chapters
