Create table in SQL

We can create a table in SQL by using the following syntax.

SQL Syntax

Example:

SQL

create table employeedetails1 (
ID INT,
name VARCHAR(20),
age int,
salary VARCHAR(10)
);          

Explanation:

• ID INT: Here the column name is ID, and it will store the employee ID as an integer.
• name VARCHAR(20): Here the column name is “name” will store the employee name as a variable-length string with a maximum of 20 characters.
• age INT: Here the column name is age and will store the employee age as an integer.
• salary VARCHAR(10): Here the column name is salary and will store the employee salary as a variable-length string with a maximum of 10 characters.

Create table as select syntax in sql

In SQL, the CREATE TABLE AS SELECT (CTAS) statement is used to create a new table from the result of a SELECT query. The syntax is:

SQL

CREATE TABLE new_table AS
SELECT column1, column2, ...
FROM existing_table
WHERE condition;

Example:

SQL

CREATE TABLE employees_backup AS
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 10;

This creates a new table “employees_backup” containing the employee_id, first_name, and last_name of employees from department 10.

If we want to create an empty table with the same structure as another, but without copying the data, we can do:

SQL

CREATE TABLE new_table AS
SELECT *
FROM existing_table
WHERE 1 = 0;

This will create new_table with the same structure as existing_table, but no data will be inserted.