Data Definition Language - SQL

What is Data Definition Language?

Data Definition Language, or DDL, is a set of commands in SQL used to define and structure a database. These commands are used to create, modify, and delete the objects within a database, such as tables, indexes, and views.

Think of DDL as the blueprint for the database, it sets up the structure of how the data will be organized and stored. If you were building a house, DDL would be the architectural plan.

DDL Commands in SQL:

1. CREATE

2. DROP

3. ALTER

4. TRUNCATE

5. RENAME

CREATE Statement:

The CREATE statement in SQL is used to create new objects in a database. These objects can include tables, views, procedures, and functions.

A table is a database object that stores data in a specific format, with rows and columns. With the CREATE TABLE statement, you can define the columns of the table, their data types, and any constraints that should be applied to the table.

A view is a virtual table that is based on the result of a SELECT statement. With the CREATE VIEW statement, you can define a SELECT statement that retrieves data from one or more tables and present them as a single table.

A stored procedure is a predefined set of SQL statements that can be executed with a single command. With the CREATE PROCEDURE statement, you can define the SQL statements and any input parameters that the procedure should take.

Syntax to create a Table :

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

For example, if you want to create a table called "employees" with columns "employee_id", "first_name", "last_name", and "salary", the query would be:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    salary DECIMAL(10,2) NOT NULL
);

Syntax to create a View:

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

For example, if you want to create a view called "employee_details" that selects the "first_name", "last_name", and "salary" columns from the "employees" table, the query would be:

CREATE VIEW employee_details AS
SELECT first_name, last_name, salary
FROM employees;

Syntax to create a Stored Procedure:

CREATE PROCEDURE procedure_name
(
    @parameter1 datatype,
    @parameter2 datatype,
    ...
    @parameter_n datatype
)
AS
BEGIN
    -- SQL statements here
END;

For example, if you want to create a stored procedure called "get_employee_salary" that takes an employee ID as a parameter and returns the salary of the employee, the query would be:

CREATE PROCEDURE get_employee_salary
(
    @employee_id INT
)
AS
BEGIN
    SELECT salary
    FROM employees
    WHERE employee_id = @employee_id;
END;