You are currently viewing SQL 1.40 SQL CREATE 

SQL 1.40 SQL CREATE 

Sure, let’s dive into the basics of SQL CREATE statements, which are fundamental for database management. SQL (Structured Query Language) is a powerful language used for managing and manipulating relational databases. In this tutorial, we’ll focus on understanding the syntax and usage of the SQL CREATE statement to create tables in a database.

1. Understanding SQL CREATE Statement:

The SQL CREATE statement is used to create database objects such as tables, views, indexes, etc. Here, we’ll focus on creating tables.

2. Syntax of SQL CREATE TABLE Statement:

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

Explanation:

  • CREATE TABLE: This is the SQL keyword used to create a new table.
  • table_name: This is the name of the table you want to create.
  • (column1, column2, ..., columnN): These are the columns that you want to define for the table.
  • datatype: Specifies the data type for each column.
  • [constraints]: Optional constraints such as PRIMARY KEY, NOT NULL, UNIQUE, etc., which enforce rules on the data stored in the table.

3. Example of SQL CREATE TABLE Statement:

Let’s create a simple table called Employees with three columns: EmployeeID, Name, and Salary.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Salary DECIMAL(10,2)
);

Explanation:

  • EmployeeID: This column is defined as an integer data type and is specified as the primary key, meaning each value in this column must be unique and not null.
  • Name: This column is defined as a variable character data type (VARCHAR) with a maximum length of 50 characters. It’s also specified as NOT NULL, meaning it cannot contain null values.
  • Salary: This column is defined as a decimal data type with precision 10 and scale 2, meaning it can store decimal numbers with up to 10 digits in total, with 2 digits after the decimal point. It does not have any constraints specified, so it can contain null values.

4. Additional Examples:

Let’s create a few more tables with different scenarios:

a. Creating a table with a composite primary key:

CREATE TABLE Orders (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

Explanation:

In this example, the Orders table has a composite primary key consisting of two columns: OrderID and ProductID. This means that each combination of OrderID and ProductID must be unique.

b. Creating a table with foreign key constraints:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    ProductID INT,
    Quantity INT,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

Explanation:

In this example, the Orders table has a foreign key constraint on the ProductID column, referencing the ProductID column in the Products table. This ensures that every value in the ProductID column of the Orders table must exist in the ProductID column of the Products table.

Conclusion

In this tutorial, we covered the basics of SQL CREATE statements for creating tables in a database. We learned about the syntax of the CREATE TABLE statement, how to define columns with data types and constraints, and saw examples of creating tables with different scenarios including primary keys and foreign key constraints. Practice creating tables with different structures and constraints to strengthen your understanding of SQL CREATE statements.

Leave a Reply