relational databases, is used to store data in the form of some structures. These structures are nothing more than simple tables containing data in the form of fields and records. Here, a field is a column that defines the type of data to be stored in a table and the record is a row that contains actual data. SQL provides various queries to interact with the data by creating tables, updating them, deleting them, and so on.
To create a table in SQL, you use the CREATE TABLE statement.
An SQL query to create a table must define the structure of a table. The structure consists of the name of a table and the names of the columns in the table with the data type of each column. Note that each table must have a unique name in a database.
The following is the basic syntax of a
CREATE TABLE statement: CREATE TABLE table_name( column1 data type, column2 data type, column3 data type, ….. columnData type N, PRIMARY KEY( one or more columns ) );
CREATE TABLE is the keyword that tells the database system what it wants to do. In this case, you want to create a new table. The unique name or identifier of the table follows the CREATE TABLE statement.
Then, in parentheses, the list that defines each column in the table and what type of data type it is appears. The syntax becomes clearer with the following example.
The following block of code is an example, which creates a table CLIENTS with an ID as the primary key and NOT NULL are the constraints that show that these fields cannot be NULL when creating records in this table −
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , DECIMAL SALARY (18, 2), PRIMARY KEY (ID) );
You can check if your table has been created correctly by looking at the message displayed by the SQL server, otherwise, you can use the EXEC command sp_help as follows: EXEC sp_help
The displayed table contains the structure of the created table: column names, their respective data types, constraints (if any), etc.
+-+-+-+-+-+-+ | Field | Type | Null | Key | Default | Extra | +-+-+-+-+-+-+ | ID | int(11) | YES | PRI | | | | NAME | varchar(20) | NO | | | | | AGE | int(11) | NO | | | | | ADDRESS | char(25) | YES | | NULL | | | SALARY | decimal(18,2) | YES | | NULL | | +-+-+-+-+-+-+ 5 rows together (0.00 sec) Now, you have the
CUSTOMERS table available in your database that you can use to store the required information related to customers
. Create a table
an existing table
Instead of creating a new table each time, you can also copy an existing table and its contents, including its structure, into a new table. This can be done by using a combination of the CREATE TABLE statement and the SELECT statement. Because its structure is copied, the new table will have the same column definitions as the original table. In addition, the new table would be populated using the existing values from the previous table.
Note: Because this is a completely new table, changes made to it will not be reflected in the original table.
The basic syntax for creating a table from another table is as follows:
CREATE TABLE NEW_TABLE_NAME AS SELECT [column1, column2… columnN] OF EXISTING_TABLE_NAME [WHERE CONDITION];
Here, column1, column2… are the fields in the existing table and would be used to create fields in the new table.
Below is an example, which would create a SALARY table using the CUSTOMERS table and having the fields Customer ID and CUSTOMER SALARY −
CREATE TABLE SALARY AS SELECTION ID, CUSTOMER SALARY;
This would create a new SALARY table that will 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 | +-+-+