SQL | Create Table Extension
Last Updated :
21 Mar, 2018
SQL provides an extension for CREATE TABLE clause that creates a new table with the same schema of some existing table in the database.
- It is used to store the result of complex queries temporarily in a new table.
- The new table created has the same schema as the referencing table.
- By default, the new table has the same column names and the data type of the referencing table.
Syntax:
CREATE TABLE newTable LIKE pets
Example:
CREATE TABLE newTable as
(SELECT *
FROM pets
WHERE pets.BREED = 'German Shepherd')
Queries
pets table:
ID |
Name |
Breed |
Gender |
11441 |
Tommy |
German Shepherd |
Male |
11442 |
Max |
Beagle |
Male |
11443 |
Charlie |
Pug |
Male |
11444 |
Daisy |
Poodle |
Female |
11445 |
Zoe |
Labrador |
Female |
11446 |
Toby |
Bulldog |
Male |
Query 1:
CREATE TABLE newTable LIKE pets;
SELECT *
FROM newTable
where newTable.GENDER = 'Female';
Output:
ID |
Name |
Breed |
Gender |
11444 |
Daisy |
Poodle |
Female |
11445 |
Zoe |
Labrador |
Female |
Explanation: The newTable created is a copy of pets table. So, selecting female pets from newTable returns only two rows in which the pet is a female.
Query 2 :
CREATE TABLE newTable as
(SELECT *
FROM pets
WHERE pets.BREED = 'German Shepherd');
SELECT * from newTable;
Output:
ID |
Name |
Breed |
Gender |
11441 |
Tommy |
German Shepherd |
Male |
Explanation: First the inner query is evaluated and the results are stored in a new temporary relation. Following this, the outer query is evaluated which create newTable as add the output of inner query to newTable.
References :Â Database System Concepts 6th Edition by Silberschatz
Share your thoughts in the comments
Please Login to comment...