DRL/DQL - Data Retrieval Language / Data Querying Language

DRL/DQL commands are used to select the data or Retrieve the data from database.

SELECT

Select command is used to select the data or Retrieve the data from database

Syntax:

    Select * from <table_name>;              -- here * indicates all columns

or

    Select col1, col2, … coln from <table_name>;

Ex:

    SQL> select * from student;

   

        NO NAME            MARKS

        ---  ------             --------

         1   Sudha             100

         2   Saketh            200

         1   Jagan             300

         2   Naren             400

         3   Ramesh

         4   Madhu

         5   Visu

         6   Rattu

    SQL> select no, name, marks from student;

        NO NAME            MARKS

        ---  ------             --------

         1   Sudha             100

         2   Saketh            200

         1   Jagan             300

         2   Naren             400

         3   Ramesh

         4   Madhu

         5   Visu

         6   Rattu

    SQL> select no, name from student;

        NO NAME

        ---  -------

         1   Sudha

         2   Saketh

         1   Jagan

         2   Naren

         3   Ramesh

         4   Madhu

         5   Visu

         6   Rattu

CREATE WITH SELECT

We can create a table using existing table [along with data].

Syntax:

    Create table <new_table_name> [col1, col2, col3 ... coln] as select * from

                                                               <old_table_name>;

Ex:

    SQL> create table student1 as select * from student;

   

    Creating table with your own column names.

    SQL> create table student2(sno, sname, smarks) as select * from student;

   

    Creating table with specified columns.

    SQL> create table student3 as select no,name from student;

    Creating table with out table data.

    SQL> create table student2(sno, sname, smarks) as select * from student where 1 = 2;

    In the above where clause give any condition which does not satisfy.

   

INSERT WITH SELECT

Using this we can insert existing table data to a another table in a single trip. But the table structure should be same.

Syntax:

     Insert into <table1> select * from <table2>;   

Ex:

     SQL> insert into student1 select * from student;

     Inserting data into specified columns

     SQL> insert into student1(no, name) select no, name from student;