You are currently viewing SQL 1.43 SQL – CREATE TABLE

SQL 1.43 SQL – CREATE TABLE

Basics of SQL – CREATE TABLE

Tables are the building blocks of any relational database, and understanding how to create them is crucial for database management and application development.

1. Introduction to CREATE TABLE:

The CREATE TABLE statement is used to create a new table in a database. It allows you to define the structure of the table, including column names, data types, constraints, and other properties.

2. Syntax of CREATE TABLE:

The basic syntax of the CREATE TABLE statement is as follows:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    columnN datatype
);

3. Example of CREATE TABLE:

Let’s create a simple table named employees with columns for employee_id, first_name, last_name, and salary. We will assume that the employee_id is an integer, and the first_name, last_name, and salary are strings.

CREATE TABLE employees (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2)
);

Explanation:

  • CREATE TABLE employees: This line initiates the creation of a table named employees.
  • ( ... ): This block contains the definitions of the table columns.
  • employee_id INT: Defines a column named employee_id with a data type of INT (integer).
  • first_name VARCHAR(50): Defines a column named first_name with a variable-length string data type (VARCHAR) that can hold up to 50 characters.
  • last_name VARCHAR(50): Defines a column named last_name with a variable-length string data type (VARCHAR) that can hold up to 50 characters.
  • salary DECIMAL(10, 2): Defines a column named salary with a fixed-point number data type (DECIMAL) with precision 10 and scale 2, suitable for storing monetary values.

4. Additional Options:

In addition to specifying column names and data types, you can also include various options such as constraints, defaults, and indexes in the CREATE TABLE statement. Here’s an example:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE,
    CONSTRAINT fk_department
        FOREIGN KEY (department_id)
        REFERENCES departments (department_id)
);

Explanation:

  • PRIMARY KEY: Specifies that the student_id column is the primary key for the students table.
  • NOT NULL: Specifies that the first_name and last_name columns cannot contain NULL values.
  • DATE: Defines the date_of_birth column with a data type of DATE.
  • CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments (department_id): Defines a foreign key constraint named fk_department on the department_id column, referencing the department_id column in the departments table.

Conclusion

In this tutorial, we have covered the basics of creating a table in SQL using the CREATE TABLE statement. Understanding how to define the structure of a table is essential for database design and management. Experiment with creating tables in your own database environment to solidify your understanding of this fundamental SQL operation.

Leave a Reply