CREATE Table
Creating
a basic table involves naming the table and defining its columns and each column's
data type.
The
SQL CREATE TABLE statement is used to create a new table.
Syntax:
CREATE
TABLE table_name(
column1
datatype constraint,
column2
datatype constraint,
column3
datatype constraint,
.....
columnN
datatype constraint,
PRIMARY
KEY( one or more columns )
);
CREATE
TABLE is the keyword telling the database system what you want to do. In this
case, you want to create a new table. The unique name or identifier for the
table follows the CREATE TABLE statement.
Then
in brackets comes the list defining each column in the table and what sort of
data type it is. The syntax becomes clearer with an example below.
A copy
of an existing table can be created using a combination of the CREATE TABLE
statement and the SELECT statement. You can check complete details at Create
Table Using another Table.
Create
Table Using another Table
A copy
of an existing table can be created using a combination of the CREATE TABLE
statement and the SELECT statement.
The
new table has the same column definitions. All columns or specific columns can
be selected.
When
you create a new table using existing table, new table would be populated using
existing values in the old table.
Syntax:
The
basic syntax for creating a table from another table is as follows:
CREATE
TABLE NEW_TABLE_NAME AS
SELECT
[ column1, column2...columnN ]
FROM
EXISTING_TABLE_NAME
[
WHERE ]
Here,
column1, column2...are the fields of existing table and same would be used to
create fields of new table.
Example:
Following
is an example, which creates a CUSTOMERS table with ID as primary key and NOT
NULL are the constraints showing that these fileds can not be NULL while
creating records in this table:
SQL>
CREATE TABLE CUSTOMERS(
ID INT
NOT NULL,
NAME
VARCHAR (20) NOT NULL,
AGE
INT NOT NULL,
ADDRESS
CHAR (25) ,
SALARY
DECIMAL (18, 2),
PRIMARY
KEY (ID)
);
You
can verify if your table has been created successfully by looking at the
message displayed by the SQL server, otherwise you can use DESC command as
follows:
SQL>
DESC CUSTOMERS;
+-------------+------------------+------------+----------+------------+-----------+
|
Field | Type | Null | Key | Default
| Extra |
+-------------+------------------+------------+----------+------------+-----------+
| ID
| int(11) | NO
| PRI | | |
|
NAME | varchar(20) | NO | | | |
| AGE
| int(11) | NO |
| | |
|
ADDRESS | char(25) | YES | | NULL | |
|
SALARY | decimal(18,2) | YES | | NULL | | |
+-------------+------------------+------------+----------+------------+-----------+
5 rows
in set (0.00 sec)
Now,
you have CUSTOMERS table available in your database which you can use to store
required information related to customers
Following is an
example, which
would create a table SALARY using CUSTOMERS table and having fields customer ID
and customer SALARY:
SQL>
CREATE TABLE SALARY AS
SELECT
ID, SALARY
FROM
CUSTOMERS;
This
would create new table SALARY, which would have the following records:
+----+---------------+
|
ID | SALARY |
+----+---------------+
|
1 | 2000.00 |
|
2 | 1500.00 |
|
3 | 2000.00 |
|
4 | 6500.00 |
|
5 | 8500.00 |
|
6 | 4500.00 |
|
7 | 10000.00 |
+----+--------------+
0 Comments