CREATE Statement in SQL

The CREATE statement in SQL is used to create databases, tables, indexes, and other database objects. It is part of the Data Definition Language (DDL) and helps define the structure of a database system.

1. Creating a Database

The CREATE DATABASE statement is used to create a new database.

Example:

      -- Create a database named 'SchoolDB'
      CREATE DATABASE SchoolDB;

      -- Use the database
      USE SchoolDB;

2. Creating a Table

The CREATE TABLE statement is used to define the structure of a table, including its columns, data types, and constraints.

Example:

      -- Create a table named 'Students'
      CREATE TABLE Students (
          StudentID INT PRIMARY KEY,
          Name VARCHAR(100),
          Age INT,
          Grade CHAR(2),
          EnrollmentDate DATE
      );

      -- Insert data into the table
      INSERT INTO Students (StudentID, Name, Age, Grade, EnrollmentDate)
      VALUES (1, 'Alice Johnson', 20, 'A+', '2024-01-15');

      -- Retrieve data from the table
      SELECT * FROM Students;

3. Creating an Index

The CREATE INDEX statement is used to create an index on a table. Indexes improve the performance of queries by allowing faster data retrieval.

Example:

      -- Create an index on the 'Name' column in the 'Students' table
      CREATE INDEX idx_name ON Students (Name);

      -- Query to use the index
      SELECT * FROM Students WHERE Name = 'Alice Johnson';

4. Creating a Unique Index

The CREATE UNIQUE INDEX statement ensures that all values in the indexed column are unique.

Example:

      -- Create a unique index on the 'StudentID' column
      CREATE UNIQUE INDEX idx_unique_studentid ON Students (StudentID);

5. Creating a Table with Constraints

Constraints are used to define rules for data in a table. Common constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK.

Example:

      -- Create a table with constraints
      CREATE TABLE Courses (
          CourseID INT PRIMARY KEY,
          CourseName VARCHAR(100) NOT NULL,
          Credits INT CHECK (Credits > 0),
          DepartmentID INT,
          FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
      );

6. Creating a Database Schema

You can organize database objects into schemas for better management.

Example:

      -- Create a schema named 'Academic'
      CREATE SCHEMA Academic;

      -- Create a table in the 'Academic' schema
      CREATE TABLE Academic.Teachers (
          TeacherID INT PRIMARY KEY,
          Name VARCHAR(100),
          Subject VARCHAR(100)
      );

Conclusion

The CREATE statement is essential for defining the structure of a database system. By using it to create databases, tables, and indexes, you can effectively organize and manage data. Understanding how to use CREATE ensures a solid foundation for database design and development.




Advertisement