Select Statement: 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.
Syntax:
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 Oracledatabas 25 4500 Windows
vbnet VB.NET 30 5500 Windows and programming
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
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
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
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
-------------------- ---------
Oracle database 4500
Java Language 4500
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
-------------------- --------- - --------
Oracle database 25 4500
Java Language 25 4500
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
-------------------- --------- ---------
Oracle database 4500 675
Java Language 4500 675
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
-------------------- --------- ---------
Oracle database 4500 675
Java Language 4500 675
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. 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.
No comments:
Post a Comment