Create tables from other tables

You can create table from other existing table also. Following syntax is used to create a table from existing table.

CREATE TABLE table_name AS

SELECT [column1…column]

FROM existing_table_name

[WHERE] some condition;

If you don’t specify any columns then it takes all the columns from existing table. Keywords specified in [] are optional.

Let me create employee table.

CREATE TABLE employee(

        id int PRIMARY KEY,

        firstName varchar(15),

        lastName varchar(15),

        city varchar(15)

);

Insert some data to employee table.

INSERT INTO employee VALUES (1, "Hari", "Krishna", "Bangalore");

INSERT INTO employee VALUES (2, "Gopi", "Battu", "Bangalore");

INSERT INTO employee VALUES (3, "Shanmuk", "Kummari", "Chennai");

mysql> CREATE TABLE employee(
    -> id int PRIMARY KEY,
    -> firstName varchar(15),
    -> lastName varchar(15),
    -> city varchar(15)
    -> ); 
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO employee VALUES (1, "Hari", "Krishna", "Bangalore");
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO employee VALUES (2, "Gopi", "Battu", "Bangalore");
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO employee VALUES (3, "Shanmuk", "Kummari", "Chennai");
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select * from employee;
+----+-----------+----------+-----------+
| id | firstName | lastName | city      |
+----+-----------+----------+-----------+
|  1 | Hari      | Krishna  | Bangalore |
|  2 | Gopi      | Battu    | Bangalore |
|  3 | Shanmuk   | Kummari  | Chennai   |
+----+-----------+----------+-----------+
3 rows in set (0.00 sec)

1. Create employee_name table that contains firstName and lastName fields from employee table.

CREATE TABLE employee_name AS

SELECT firstName, lastName

FROM employee;

mysql> CREATE TABLE employee_name AS
    -> SELECT firstName, lastName
    -> FROM employee;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM employee_name;
+-----------+----------+
| firstName | lastName |
+-----------+----------+
| Hari      | Krishna  |
| Gopi      | Battu    |
| Shanmuk   | Kummari  |
+-----------+----------+
3 rows in set (0.00 sec)

2. Create employee_bangalore table, that contains all the records from employee who are living in Bangalore.

CREATE TABLE employee_bangalore AS

SELECT id, firstName, lastName

FROM employee

WHERE city="Bangalore";

mysql> CREATE TABLE employee_bangalore AS
    -> SELECT id, firstName, lastName
    -> FROM employee
    -> WHERE city="Bangalore";
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM employee_bangalore;
+----+-----------+----------+
| id | firstName | lastName |
+----+-----------+----------+
|  1 | Hari      | Krishna  |
|  2 | Gopi      | Battu    |
+----+-----------+----------+
2 rows in set (0.00 sec)

Previous                                                 Next                                                 Home