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.
CREATE TABLE newTable LIKE pets
CREATE TABLE newTable as (SELECT * FROM pets WHERE pets.BREED = 'German Shepherd')
CREATE TABLE newTable LIKE pets; SELECT * FROM newTable where newTable.GENDER = '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;
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
This article is contributed by Mayank Kumar. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- How to Get the names of the table in SQL
- SQL | Checking Existing Constraints on a Table using Data Dictionaries
- What is Temporary Table in SQL?
- SQL query to find unique column values from table
- SQL | Declare Local Temporary Table
- SQL | Query to select NAME from table using different options
- DUAL table in SQL
- Table operations in MS SQL Server
- Table Variable in SQL Server
- SQL | Procedures in PL/SQL
- SQL | Difference between functions and stored procedures in PL/SQL
- Difference between SQL and T-SQL
- SQL | CREATE
- Performing Database Operations in Java | SQL CREATE, INSERT, UPDATE, DELETE and SELECT
- SQL | CREATE DOMAIN
- Create, Alter and Drop schema in MS SQL Server
- Create login in SQL Server
- Create Database in MS SQL Server
- CREATE and DROP INDEX Statement in SQL