To create a new table from the results of a SELECT statement in SQL, you can use the CREATE TABLE ... AS syntax. This allows you to define a new table and populate it with the results of a query. Here's the general syntax:
CREATE TABLE new_table_name AS
SELECT column1, column2, ...
FROM existing_table
WHERE condition;
Example
Assume you have an existing table called employees and you want to create a new table called high_salary_employees that includes only those employees with a salary greater than 50000. You would do the following:
CREATE TABLE high_salary_employees AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 50000;
Important Notes:
- The new table (high_salary_employees in the example) will inherit the data types of the selected columns but not the constraints, indices, or default values of the original table.
- Different database systems might have variations in how the CREATE TABLE AS command is implemented, so it's worth checking the specific documentation for the database system you're working with (e.g., MySQL, PostgreSQL, Oracle, SQL Server, etc.).
- If you want to create a table with the same structure and constraints as the original, you would typically have to define the table schema explicitly before using INSERT INTO to populate it.