SQL create table

Create Table

Tables are a basic unit of organization and storage of data in SQL. Each table has a name such as an author, book_mast, purchase or orders. A table is similar to a file in a non-database system.

Tables are organized into rows and columns. Each row represents a record, and each column can be thought of as representing a component of that record.

Syntax:

CREATE TABLE <table_name>(
column1    data_type[(size)],
column2    data_type[(size)],
...);

Parameters:

Best Practices for SQL CREATE TABLE Statements

Here are some important points and tips about the "SQL CREATE TABLE" statement:

Table Name: Choose a descriptive and relevant name for your table. It should reflect the entity or concept it represents.

Column Definitions: Define each column in the table with its name and data type. Consider the nature of the data being stored and choose appropriate data types (e.g., INTEGER, VARCHAR, DATE, etc.).

Constraints: Use constraints to enforce rules or conditions on the data in your table. Common constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK constraints.

Primary Key: Choose a primary key for your table to uniquely identify each row. This is typically a column (or combination of columns) with a unique value for each row.

Indexes: Consider adding indexes on columns frequently used in search conditions to improve query performance.

Normalization: Design your table structure to follow normalization principles to reduce redundancy and improve data integrity.

Comments: Use comments to document the purpose of the table, as well as individual columns and constraints. This helps other developers understand the schema and its intended usage.

Data Integrity: Ensure data integrity by enforcing constraints and validating data at the database level. This prevents the insertion of incorrect or inconsistent data.

Table Options: Some database systems offer additional table options like storage parameters, compression settings, or partitioning options. Familiarize yourself with these options and use them as needed for performance optimization and data management.

Version Control: Store your SQL scripts, including CREATE TABLE statements, in version control systems. This helps track changes over time and facilitates collaboration among team members.

Testing: Test your CREATE TABLE statements thoroughly before deploying them to production environments. Check for syntax errors, constraint violations, and performance implications.

Permissions: Ensure that the user executing the CREATE TABLE statement has the necessary permissions to create tables in the target database schema.

Backup: Regularly back up your database schema definition, including CREATE TABLE statements, to prevent data loss in case of accidental deletion or corruption.

SQL create table basic statement

The following will describe, how a simple table can be created.

Example:

The following example creates a table. Here is the field name and data types:

the following SQL statement can be used:

SQL Code:

-- Creating a table named 'mytest'
CREATE TABLE mytest (
    -- Column: agent_code with a data type CHAR(6)
    agent_code CHAR(6),
    
    -- Column: agent_name with a data type CHAR(40)
    agent_name CHAR(40),
    
    -- Column: working_area with a data type CHAR(35)
    working_area CHAR(35),
    
    -- Column: commission with a decimal data type (10 digits, 2 decimal places)
    commission DECIMAL(10, 2),
    
    -- Column: phone_no with a data type CHAR(15) that allows NULL values
    phone_no CHAR(15) NULL
);

Explanation:

• CREATE TABLE mytest: This line initiates the creation of a new table named 'mytest'.
• agent_code CHAR(6): This defines a column named 'agent_code' with a data type CHAR(6). CHAR is a fixed-length character data type, and in this case, it allows up to 6 characters.
• agent_name CHAR(40): This line defines a column named 'agent_name' with a data type CHAR(40). It's similar to 'agent_code', but it allows up to 40 characters.
• working_area CHAR(35): This line defines a column named 'working_area' with a data type CHAR(35), allowing up to 35 characters.
• commission DECIMAL(10, 2): This defines a column named 'commission' with a DECIMAL data type. DECIMAL is a numeric data type that can store fixed-point numbers. Here, it's specified to have 10 digits in total, with 2 of those being reserved for decimal places.
• phone_no CHAR(15) NULL: This line defines a column named 'phone_no' with a data type CHAR(15) and allows NULL values. The 'NULL' keyword means that this column can have missing or unknown values. It can store up to 15 characters.

To see the structure of the created table:

SQL Code:

DESCRIBE mytest; 

Output:

 Name                         Null?    Type
 --------------------------- -------- ------
 AGENT_CODE                           CHAR(6)
 AGENT_NAME                           CHAR(40)
 WORKING_AREA                         CHAR(35)
 COMMISSION                           NUMBER(10,2)
 PHONE_NO                             CHAR(15)

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Create/Alter Database
Next: Primary Key