Creating a Simple Table with SQL

Creating a Simple Table

Define the Table Structure

To create a table, you need to:

• Specify the table name: The name by which the table will be referred to.
• Define the columns: Each column must have a name and a data type. Optionally, you can set constraints (like PRIMARY KEY, NOT NULL, etc.).

SQL Syntax for Creating a Table

Here’s a generic example of creating a simple table in SQL: 

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

Example Tables

Here are examples for creating a simple table in various popular DBMSs:

PostgreSQL 

CREATE TABLE Employees (
    EmployeeID SERIAL PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    HireDate DATE
);

• SERIAL: Automatically increments the EmployeeID for each new row.
• VARCHAR(50): Specifies a variable-length character string with a maximum of 50 characters.
• DATE: Stores date values.

MySQL 

CREATE TABLE Employees (
    EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    HireDate DATE
);

• AUTO_INCREMENT: Automatically increments the EmployeeID for each new row.
• VARCHAR(50): Specifies a variable-length character string with a maximum of 50 characters.
• DATE: Stores date values.

SQL Server 

CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    HireDate DATE
);

• IDENTITY(1,1): Automatically generates incrementing numbers for EmployeeID.
• NVARCHAR(50): Specifies a variable-length Unicode character string with a maximum of 50 characters.
• DATE: Stores date values.

Oracle 

CREATE TABLE Employees (
    EmployeeID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    FirstName VARCHAR2(50) NOT NULL,
    LastName VARCHAR2(50) NOT NULL,
    HireDate DATE
);

• NUMBER GENERATED BY DEFAULT AS IDENTITY: Automatically generates incrementing numbers for EmployeeID.
• VARCHAR2(50): Specifies a variable-length character string with a maximum of 50 characters.
• DATE: Stores date values.

Considerations When Creating a Table

• Data Types: Choose appropriate data types for each column based on the kind of data you need to store (e.g., VARCHAR for text, INT for integers, DATE for dates).
• Constraints: Add constraints to enforce rules on the data (e.g., NOT NULL to ensure a column cannot have NULL values, PRIMARY KEY to uniquely identify each row).
• Indexes: For tables with large amounts of data, consider creating indexes to improve query performance.

Verifying the Table Structure

After creating the table, you can use SQL queries to verify its structure. For example:

• PostgreSQL and MySQL:

DESCRIBE Employees;

• SQL Server: 

EXEC sp_help 'Employees';

• Oracle: 

SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'EMPLOYEES'