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 student121
as select * from student12;
Creating table with your own column names.
SQL> create table student122(sno,
sname, smarks) as select * from student12;
Creating table with specified columns.
SQL> create table student123
as select no,name from student12;
Creating table with out table data.
SQL> create table student122(sno, sname, smarks) as select * from student12
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 student121
select * from student12;
Inserting data into specified columns
SQL> insert into student121(no,
name) select no, name from student12;
COLUMN ALIASES
Syntax:
Select
<orginal_col> <alias_name> from <table_name>;
Ex:
SQL> select no sno
from student12;
or
SQL> select no
“sno” from student12;
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 student12 s;
