Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Monday, 28 March 2016

SQL ALIASES

CREATE TABLE 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;

COLUMN ALIASES

Syntax:
     Select <orginal_col> <alias_name> from <table_name>;

Ex:
     SQL> select no sno from student;
or
     SQL> select no “sno” from student;

TABLE ALIASES

If you are using table aliases you can use dot method to the columns.

Syntax:
     Select <alias_name>.<col1>, <alias_name>.<col2> … <alias_name>.<coln> from  <table_name> <alias_name>;

Ex:


     SQL> select s.no, s.name from student s;
Share this article :

0 comments:

Post a Comment