SQL Tutorial

SQL Clauses / Operators

SQL-Injection

SQL Functions

SQL Queries

PL/SQL

MySQL

SQL Server

Misc

SQL | CREATE

The CREATE statement in SQL is used to create objects in the database, such as tables, views, indexes, procedures, and more. The specific syntax and options can vary depending on the object type and the specific relational database management system (RDBMS) being used. Below are some common usages of the CREATE statement:

1. CREATE TABLE

The CREATE TABLE statement is used to create a new table in the database.

Syntax:

CREATE TABLE table_name (
    column1 datatype1 [constraints],
    column2 datatype2 [constraints],
    ...
);

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT
);

2. CREATE INDEX

The CREATE INDEX statement is used to create an index on one or more columns of a table. Indexes are used to speed up query performance.

Syntax:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Example:

CREATE INDEX idx_lastname
ON Employees (LastName);

3. CREATE VIEW

The CREATE VIEW statement allows you to create a virtual table (a view) based on the result set of a SELECT statement. Views do not store data themselves but refer to data present in tables.

Syntax:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

CREATE VIEW View_EmployeeNames AS
SELECT FirstName, LastName
FROM Employees;

4. CREATE DATABASE

This statement is used to create a new database.

Syntax:

CREATE DATABASE database_name;

Example:

CREATE DATABASE EmployeeDB;

5. CREATE PROCEDURE

The CREATE PROCEDURE statement is used to create a stored procedure. Stored procedures are a sequence of SQL statements stored in the database.

Syntax:

CREATE PROCEDURE procedure_name [parameters]
BEGIN
    SQL statements;
END;

Example:

CREATE PROCEDURE sp_GetEmployeeByID @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Notes:

Constraints: When creating tables, you can define constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK to impose rules on the data in the table.
Collation and Character Set: Depending on the RDBMS, you may need to specify collation and character set options, especially when dealing with text data.
Data Types: Each RDBMS supports various data types. You should consult the specific documentation for your RDBMS to know the appropriate data types and their attributes.
Permissions: Creating objects in a database usually requires appropriate permissions. Ensure you have the necessary rights or permissions before executing CREATE statements.

Remember that the exact syntax, supported options, and behaviors can vary across different RDBMSs, so it's essential to consult the specific documentation for the system you're using.

How to Use CREATE in SQL: The CREATE statement is used to create database objects such as tables, indexes, views, etc.

Creating Tables with CREATE TABLE in SQL:

CREATE TABLE your_table (
   column1 INT,
   column2 VARCHAR(50),
   PRIMARY KEY (column1)
);

Creating Indexes with CREATE INDEX:

CREATE INDEX idx_column2
ON your_table (column2);

Using CREATE VIEW in SQL:

CREATE VIEW your_view AS
SELECT column1, column2
FROM your_table
WHERE column1 > 10;

CREATE and PRIMARY KEY Constraint in SQL:

CREATE TABLE your_table (
   user_id INT PRIMARY KEY,
   username VARCHAR(50) NOT NULL
);

Creating Unique Constraints with CREATE:

CREATE TABLE your_table (
   email VARCHAR(50) UNIQUE,
   -- other columns
);

Adding Foreign Key Constraints with CREATE:

CREATE TABLE orders (
   order_id INT PRIMARY KEY,
   product_id INT,
   FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE and CHECK Constraints in SQL:

CREATE TABLE your_table (
   age INT CHECK (age >= 18),
   -- other columns
);

Creating Sequences with CREATE SEQUENCE:
- Sequences are commonly used in databases like Oracle.
```
CREATE SEQUENCE your_sequence
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000;
```

CREATE and TRIGGER in SQL:

CREATE TRIGGER your_trigger
AFTER INSERT ON your_table
FOR EACH ROW
BEGIN
   -- Trigger logic here
END;

Creating Stored Procedures with CREATE PROCEDURE:

CREATE PROCEDURE your_procedure
   @param1 INT,
   @param2 VARCHAR(50)
AS
BEGIN
   -- Procedure logic here
END;

CREATE and USER in SQL:

CREATE USER your_user
IDENTIFIED BY 'your_password';

Creating Database Schemas with CREATE SCHEMA:
```
CREATE SCHEMA your_schema;
```