Creating a table from another existing table
Create table command helps us to create a new table and it also helps us to create a table from another existing table.
(i)
Syntax:
Create table tablename(new/target)
(col1,col2,col3)
as select
col1,col2,col3
from tablename(old/source);
Example:
old table
std (roll,name,age)
new table
create table std1 (roll,name)
as select roll,name from std;
A new table named std1 with columns roll and name will get created and data of the columns will also be copied from the table std.
to create a duplicate of the table.
Create table std1 as select * from std;
Creating a table from another existing table with selected records
Syntax:
Create table tablename(new/target)
(col1,col2,col3)
as select
col1,col2,col3
from tablename(old/source) where condition;
create table std1 (roll,name)
as select roll,name
from std where roll>=500;
create table std1 (roll,name)
as select roll,name
from std where age>=18;
create table std1 as select * from std where age>=18;
to copy only the structure of the table:
(we have to mention such a condition which is not satisfied by any record)
Create table std1 as select * from std where roll=0;
inserting data into an existing table from another existing table
(i).
syntax:
insert into tablename
select col1,col2,col3
from tablename;
(ii).
syntax:
insert into tablename
select col1,col2,col3
from tablename
where condition;
student(roll,name,age,m1,m2,m3)
std(roll,name,age)
insert into std select roll,name,age from student;
insert into std select roll,name,age from student where roll>=100;
