SQL Create

The CREATE statement in SQL is used to create a new table, database, index, or other database object.

How to use the CREATE statement for various purposes:

1. Creating a Database

To create a new database, you use the CREATE DATABASE statement:

CREATE DATABASE database_name;

Example:

CREATE DATABASE mydatabase;

Viewing Databases

To view a list of all databases in your SQL environment, you can use:

SHOW DATABASES;  -- MySQL and MariaDB

Selecting a Database

Before performing operations on tables or other objects within a database, you need to select it:

USE database_name;  -- MySQL and MariaDB

 

Dropping a Database

If you need to delete a database, you can use the DROP DATABASE statement. This operation is irreversible and will permanently remove the database along with all its objects:

DROP DATABASE database_name;

2. Creating a Table

To create a new table, you use the CREATE TABLE statement followed by the table name and a definition of its columns and their data types:

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

Example:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    hire_date DATE,
    salary DECIMAL(10, 2)
);

 

Possible column datatypes:

Numeric Data Types

Numeric data types are used to store numbers. Common types include:

• INT: Integer data type.Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. Example: INT, INTEGER
• DECIMAL(p, s): Fixed-point number with precision p and scale s.The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0.  Example: DECIMAL(10, 2)
• FLOAT: Floating-point number. The number of digits after the decimal point can be specified in the parameter. Example: FLOAT, FLOAT(2), REAL
• DOUBLE: Double-precision floating-point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. Example: DOUBLE,DOUBLE(10,25)

Character Data Types

Character data types are used to store text strings. Common types include:

• CHAR(n): Fixed-length character string with length n.The n parameter can be from 0 to 255. Default is 1. Example: CHAR(10)
• VARCHAR(n): Variable-length character string with maximum length n. The n parameter can be from 0 to 65535. Example: VARCHAR(50)
• TEXT: Variable-length character string with a large maximum length. Example: TEXT

Date/Time Data Types

Date/Time data types are used to store date and time values. Common types include:

• DATE: Stores date values. Format: YYYY-MM-DD.
• TIME: Stores time values.  Format: hh:mm:ss.
• TIMESTAMP: Stores date and time values. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss.
• DATETIME: Stores date and time values. Format: YYYY-MM-DD hh:mm:ss. Do not include timezones.
• YEAR: Stores year values. 

Boolean Data Type

Boolean data type is used to store true or false values:

• BOOLEAN: Stores true or false values. Zero is considered as false, nonzero values are considered as true. Example: BOOLEAN, BOOL

Binary Data Types

Binary data types are used to store binary data, such as images or files. Common types include:

• BINARY(n): Fixed-length binary data with length n. Example: BINARY(16)
• VARBINARY(n): Variable-length binary data with maximum length n. Example: VARBINARY(64)

3. Creating an Index

An index is used to speed up the retrieval of rows by using a pointer. Without an index, MySQL must begin with the first row and then read through the entire
table to find the relevant rows. The CREATE INDEX statement is used to create indexes:

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

Example:

CREATE INDEX idx_last_name ON employees (last_name);

4. Creating a View

A view is a virtual table based on the result-set of an SQL statement. You can create a view using the CREATE VIEW statement:

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

Example:

CREATE VIEW employee_view AS
SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date > '2020-01-01';

5. Creating a User

Creating a new user in the database can be done with the CREATE USER statement (specific to systems like MySQL and PostgreSQL):

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Example (MySQL):

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';

Constraints

When creating tables, you can apply various constraints to the columns to enforce rules:

• PRIMARY KEY: Uniquely identifies each record in a table.
• FOREIGN KEY: Uniquely identifies a record in another table.
• NOT NULL: Ensures that a column cannot have a NULL value.
• UNIQUE: Ensures that all values in a column are different.
• CHECK: Ensures that all values in a column satisfy a specific condition.
• DEFAULT: Sets a default value for a column when no value is specified.

Example with constraints:

CREATE TABLE employees (
    id INT PRIMARY KEY,                           
    first_name VARCHAR(50) NOT NULL,        
    last_name VARCHAR(50) NOT NULL,          
    email VARCHAR(100) UNIQUE,                 
    birth_date DATE CHECK (birth_date > '1900-01-01'),  
    hire_date DATE DEFAULT CURRENT_DATE,        
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);