HTML
JAVASCRIPT
CSS
PYTHON
SQL
JAVA
GO
REACT
NODEJS
AWS
GENERATIVE-AI
AI
GIT

SQL Tables

Understanding SQL Tables

A table in SQL is a structured collection of data organized in rows and columns. It acts as a container where records are stored systematically, ensuring easy retrieval, manipulation, and management of data. Each column in a table defines the type of data it holds, while each row represents an individual data entry.

Creating a Table in SQL

To establish a table within a database, we use the CREATE TABLE statement. This defines the table structure, including column names and data types.

Syntax:

    CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints,
    ...
    );

Explanation:

  • table_name: The name assigned to the table.
  • column1, column2, ...: The names of the columns in the table.
  • datatype: Specifies the type of data allowed in a column (e.g., INT, VARCHAR, DATE).
  • constraints: Optional rules like PRIMARY KEY, NOT NULL, UNIQUE, etc.

Example:

  CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  FullName VARCHAR(100) NOT NULL,
  Age INT CHECK (Age >= 18),
  EnrollmentDate DATE DEFAULT CURRENT_DATE
);

Explanation:

  • StudentID INT PRIMARY KEY: Ensures each student has a unique identifier.
  • FullName VARCHAR(100) NOT NULL: Stores names up to 100 characters and ensures a name is always provided.
  • Age INT CHECK (Age >= 18): Accepts only students 18 years or older.
  • EnrollmentDate DATE DEFAULT CURRENT_DATE: Automatically assigns the current date when a new record is inserted.

Inserting Data into the Table

Once a table is created, data can be added using the INSERT INTO statement.

Syntax:

  INSERT INTO table_name (column1, column2, column3, ...)
  VALUES (value1, value2, value3, ...);

Example:

  INSERT INTO Students (StudentID, FullName, Age)
  VALUES (1, 'Alice Johnson', 20);

Explanation: Since EnrollmentDate has a default value (CURRENT_DATE), we don’t need to specify it explicitly.

Retrieving Data from the Table

To view stored records, the SELECT statement is used.

Syntax:

  SELECT column1, column2 FROM table_name WHERE condition;

Example:

  SELECT FullName, Age FROM Students WHERE Age > 18;

Explanation: SELECT FullName, Age FROM Students WHERE Age > 18;

Modifying an Existing Table

If modifications are required, the ALTER TABLE statement helps update the structure.

Syntax:

  ALTER TABLE table_name ADD column_name datatype constraints;

Example:

  ALTER TABLE Students ADD Email VARCHAR(255);

Explanation: This appends an Email column to the Students table.

Removing a Table

To delete an entire table along with its data, the DROP TABLE statement is utilized.

Syntax:

    DROP TABLE table_name;

Example:

  DROP TABLE Students;

Explanation: This permanently erases the Students table from the database.

Conclusion

SQL tables serve as the backbone of relational databases, ensuring structured and efficient data storage. Understanding how to create, modify, insert, retrieve, and delete tables is fundamental for database management and optimization.

Previous Next