Tutorialspoint.dev

SQL | Create Table Extension

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

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.



This article is attributed to GeeksforGeeks.org

leave a comment

code

0 Comments

load comments

Subscribe to Our Newsletter