PL/SQL>SQL>Data Retrieval Language

Data Retrieval Language(select)

SELECT

      Syntax:  Select * from table_name; -- here * indicates all columns (or)
                    Select col1, col2, coln from table_name;

      Example:  SQL> select * from Graduates;

      NO NAME MARKS
       --- ------ --------
       1    Adiya    100
       2    Aahil     200
       1    Bella     300
       2    Garry    400
       3    Darsh
       4    Grace
       5    Jaison
       6    Niket

SQL> select no, name, marks from Graduates;
      NO NAME MARKS
       --- ------ --------
       1    Adiya    100
       2    Aahil     200
       1    Bella     300
       2    Garry    400
       3    Darsh
       4    Grace
       5    Jaison
       6    Niket

SQL> select no, name from Graduates;
      NO NAME
       --- ------
       1    Adiya
       2    Aahil
       1    Bella
       2    Garry
       3    Darsh
       4    Grace
       5    Jaison
       6    Niket

CONDITIONAL SELECTIONS AND OPERATORS

We have two clauses used in this.
      1) Where
      2) Order by

Using WHERE:

Syntax:       select * from where ;

The following are the different types of operators used in where clause.

a) Arithmetic operators -- highest precedence
       i) +, -, *, /

b) Comparison operators
       i) ? =, !=, >, <, >=, <=, <>
       ii) between, not between
       iii) in, not in
       iv) null, not null
       v) like

c) Logical operators
       i) And
       ii) Or -- lowest precedence
       iii) not

a)Using =, >, <, >=, <=, !=, <>

Example:  SQL> select * from Graduates where no = 2;
      NO NAME MARKS
      --- ------- ---------
      2    Aahil    200
      2    Garry    400

SQL> select * from Graduates where no < 2;
      NO NAME MARKS
      --- ------- ---------
      1    Adiya    100
      1    Bella    300

SQL> select * from Graduates where no > 2;
      NO NAME MARKS
      --- ------- ---------
      3    Darsh
      4    Grace
      5    Jaison
      6    Niket

SQL> select * from Graduates where no <= 2;
      NO NAME MARKS
      --- ------- ---------
      1    Adiya    100
      2    Aahil    200
      1    Bella    300
      2    Garry    400

SQL> select * from Graduates where no >= 2;
      NO NAME MARKS
      --- ------- ---------
      2    Aahil    200
      2    Garry    400
      3    Darsh
      4    Grace
      5    Jaison
      6    Niket

SQL> select * from Graduates where no != 2;
      NO NAME MARKS
      --- ------- ---------
      1    Adiya    100
      1    Bella    100
      3    Darsh
      4    Grace
      5    Jaison
      6    Niket

SQL> select * from Graduates where no <> 2;
      NO NAME MARKS
      --- ------- ---------
      1    Adiya    100
      1    Bella    100
      3    Darsh
      4    Grace
      5    Jaison
      6    Niket

b)Using AND

      This will gives the output when all the conditions become true.

      Syntax:  select * from table_name where condition1 and condition2 and ..conditionn;

      Example:  SQL> select * from Graduates where no = 2 and marks >= 200;
         NO NAME MARKS
         --- ------- --------
         2    Aahil    200
         2    Garry    400

c)Using OR

      This will gives the output when either of the conditions become true.

      Syntax:  select * from table_name where condition1 or condition2 or .. conditionn;

      Example:  SQL> select * from Graduates where no = 2 or marks >= 200;
         NO NAME MARKS
         --- ------- --------
         2    Aahil    200
         1    Bella    300
         2    Garry    400

d)Using BETWEEN

      This will gives the output based on the column and its lower bound, upperbound.

      Syntax:  select * from table_name where between lower bound and upper bound;

      Example:  SQL> select * from Graduates where marks between 200 and 400;
         NO NAME MARKS
         --- ------- --------
         2    Aahil    200
         1    Bella    300
         2    Garry    400

e)Using NOT BETWEEN

      This will gives the output based on the column which values are not in its lower bound and upperbound.

      Syntax:  select * from table_name where not between lower bound and upper bound;

      Example:  SQL> select * from Graduates where marks not between 200 and 400;
         NO NAME MARKS
         --- ------- --------
         1    Adiya    100

f)Using IN

      This will gives the output based on the column and its list of values specified.

      Syntax:  select * from table_name where in ( value1, value2, value3 valuen);

      Example:  SQL> select * from Graduates where no in (1, 2, 3);
         NO NAME MARKS
         --- ------- --------
         1    Adiya    100
         2    Aahil    200
         1    Bella    300
         2    Garry    400
         3    Darsh

g)Using NOT IN

      This will gives the output based on the column which values are not in the list of values specified.

      Syntax:  select * from table_name where not in ( value1, value2, value3 valuen);

      Example:  SQL> select * from Graduates where no not in (1, 2, 3);
         NO NAME MARKS
         --- ------- --------
         4    Grace
         5    Jaison
         6    Niket

h)Using NULL

      This will gives the output based on the null values in the specified column.

      Syntax:  select * from table_name where is null;

      Example:  SQL> select * from Graduates where marks is null;
         NO NAME MARKS
         --- ------- --------
         3    Darsh
         4    Grace
         5    Jaison
         6    Niket

i)Using NOT NULL

      This will gives the output based on the not null values in the specified column.

      Syntax:  select * from table_name where is not null;

      Example:  SQL> select * from Graduates where marks is not null;
         NO NAME MARKS
         --- ------- --------
         1    Adiya    100
         2    Aahil    200
         1    Bella    300
         2    Garry    400

j)Using LIKE

      This will be used to search through the rows of database column based on the pattern you specify.

      Syntax:  select * from table_name where like pattern;

      Example:

      i) This will give the rows whose marks are 100.

         SQL> select * from Graduates where marks like 100;
         NO NAME MARKS
         --- ------- --------
         1    Adiya    100

      ii) This will give the rows whose name start with A.

         SQL> select * from Graduates where name like 'A%';
         NO NAME MARKS
         --- ------- --------
         1    Adiya    100
         2    Aahil    200

      iii) This will give the rows whose name ends with a.

         SQL> select * from Graduates where name like '%a';
         NO NAME MARKS
         --- ------- --------
         1    Adiya    100
         1    Bella    300

      iV) This will give the rows whose names second letter start with a.

         SQL> select * from Graduates where name like '_a%';
         NO NAME MARKS
         --- ------- --------
         2    Aahil    200
         2    Garry    400
         3    Darsh
         5    Jaison

      V) This will give the rows whose names third letter start with r.

         SQL> select * from Graduates where name like '__r%';
         NO NAME MARKS
         --- ------- --------
         2    Garry    400
         3    Darsh

      Vi) This will give the rows whose names second letter start with e from ending.

         SQL> select * from Graduates where name like '%_e%';
         NO NAME MARKS
         --- ------- --------
         6    Niket

      Vii) This will give the rows whose names third letter start with h from ending.

         SQL> select * from Graduates where name like '%h__%';
         NO NAME MARKS
         --- ------- --------
         2    Aahil    200

      Viii) This will give the rows whose name cotains 2 ls.

         SQL> select * from Graduates where name like '%l% l %';
         NO NAME MARKS
         --- ------- --------
         1    Bella    300

* You have to specify the patterns in like using underscore ( _ ).

Using ORDER BY:

This will be used to ordering the columns data (ascending or descending).

Syntax:  Select * from table_name order by desc;
       By default oracle will use ascending order.
       If you want output in descending order you have to use desc keyword after the column.

Example:   SQL> select * from Graduates order by no;

      NO NAME MARKS
       --- ------- ---------
       1     Adiya     100
       1     Bella     300
       2     Aahil     200
       2     Garry     400
       3     Darsh
       4     Grace
       5     Jaison
       6     Niket

SQL> select * from Graduates order by no desc;
       NO NAME MARKS
       --- ------- ---------
       6     Niket
       5     Jaison
       4     Grace
       3     Darsh
       2     Aahil     200
       2     Garry     400
       1     Adiya     100
       1     Bella     300

Go Back