Data Language Types in SQL

What Are SQL Data Language Types?

SQL consists of different types of languages designed for defining, manipulating, and managing data.

The two primary SQL data language types are:

Data Definition Language (DDL): Defines and modifies database structures.
Data Manipulation Language (DML): Manages and manipulates existing data.

Data Definition Language (DDL)

DDL commands are used to define and modify database schema structures.

Key DDL Commands

CREATE: Creates a new database object (table, index, view).
ALTER: Modifies an existing database object.
DROP: Deletes a database object permanently.

Creating a Table

        CREATE TABLE Students (

          StudentID INT PRIMARY KEY,

          Name VARCHAR(100),

          Age INT

        );

Other DDL Examples

        -- Create a database

        CREATE DATABASE SchoolDB;

        -- Create a view

        CREATE VIEW Teenagers AS

        SELECT * FROM Students WHERE Age BETWEEN 13 AND 19;

        -- Create an index

        CREATE INDEX idx_student_name ON Students(Name);

        -- Alter a table

        ALTER TABLE Students ADD Email VARCHAR(100);

        -- Drop a view

        DROP VIEW Teenagers;

Data Manipulation Language (DML)

DML commands are used to manipulate existing records in a database.

Key DML Commands

INSERT: Adds new records into a table.
UPDATE: Modifies existing records.
DELETE: Removes records from a table.
SELECT: Retrieves records from a table.

Inserting Data

        INSERT INTO Students (StudentID, Name, Age)

        VALUES (1, 'Alice', 20);

Updating Data

        UPDATE Students

        SET Age = 21

        WHERE StudentID = 1;

Deleting Data

        DELETE FROM Students

        WHERE StudentID = 1;

Selecting Data

        SELECT Name, Age

        FROM Students

        WHERE Age >= 18;

Comparison of DDL and DML

SQL Type	Purpose	Example Commands
Data Definition Language (DDL)	Defines, alters, and deletes database structures	`CREATE, ALTER, DROP`
Data Manipulation Language (DML)	Manipulates and retrieves existing data	`INSERT, UPDATE, DELETE, SELECT`

Why Are SQL Language Types Important?

DDL Commands: Help create and maintain the database structure.
DML Commands: Enable efficient data retrieval and modification.
Combining DDL and DML: Ensures both schema definition and data manipulation are well managed.

Key Takeaways

SQL is divided into DDL and DML: DDL defines the structure, while DML manipulates data.
DDL commands include CREATE, ALTER, and DROP: Used for managing database objects.
DML commands include INSERT, UPDATE, DELETE, and SELECT: Used for handling records.