Creating a Table from Another Table in SQL

Creating a Table from Another Table

• To create a table based on another table, use the CREATE TABLE command followed by the new table name, AS, and a SELECT statement that specifies the columns to be copied from the original table.
• The new table will have the same structure as the original table, including the data types of the columns.
• The SELECT statement can specify a subset of columns or use * to copy all columns.

Example: Creating a Table with a Subset of Columns

• To create a table with only two columns, title and author_name, from the book table, use the command: CREATE TABLE book_two AS SELECT title, author_name FROM book
• The new table book_two will have only two columns, title and author_name, with the same data types as the original table.
• The DESCRIBE command can be used to verify the structure of the new table.

Example: Creating a Table with All Columns

• To create a table with all columns from the book table, use the command: CREATE TABLE book_three AS SELECT * FROM book
• The new table book_three will have the same structure as the original table, including all columns and data types.

Deleting a Table

• To delete a table, use the DROP TABLE command followed by the table name.
• The table will be removed from the database, along with all its data and structure.

Creating a Table from Another Table

• Create a table based on another table using the CREATE TABLE command followed by the new table name, AS, and a SELECT statement.
• The new table has the same structure as the original table, including column data types.

Specifying Columns

• Use a SELECT statement to specify columns to copy from the original table.
• Specify a subset of columns or use * to copy all columns.

Example: Creating a Table with a Subset of Columns

• Create a table with a subset of columns using a SELECT statement with specified column names (e.g., title and author_name).
• The new table will have only the specified columns with the same data types as the original table.

Example: Creating a Table with All Columns

• Create a table with all columns using a SELECT statement with *.
• The new table will have the same structure as the original table, including all columns and data types.

Deleting a Table

• Delete a table using the DROP TABLE command followed by the table name.
• The table will be removed from the database, along with all its data and structure.

Learn how to create a new table based on an existing table in SQL, including selecting a subset of columns and preserving data types.