Data Definition Language (DDL)

Data Definition Language (DDL) is a subset of SQL (Structured Query Language) used to define and modify database schema and structure. It includes commands for creating, altering, and deleting database objects like tables, indexes, and views. 

The basic queries used in DDL are:

• CREATE
• ALTER
• DROP
• TRUNCATE

There are also a couple of SQLite specific queries that you might use:

• .TABLE
• PRAGMA

CREATE

Usually used to create a new table.

CREATE TABLE tablename ( column1 datatype, column2 datatype );

Example

CREATE TABLE Employees (
    EmployeeID int,
    FirstName varchar(255),
    LastName varchar(255),
    BirthDate date
);

ALTER

Used to modify existing database objects.

ALTER TABLE tablename
ADD column_name datatype;

Example - Add email column

ALTER TABLE Employees
ADD Email varchar(255);

DROP

Used to delete objects from the database.

DROP TABLE tablename;

Example: Delete the Employee table

DROP TABLE Employees;

DELETE

Used to delete all data inside a table, but not the table itself.

TRUNCATE TABLE tablename;

Example: Clear the contents of the Employee table

TRUNCATE TABLE Employees;

.TABLE

The .table command in SQLite is used to list all tables in the current database. It's a part of the SQLite command line shell's special commands, which are not standard SQL queries but are specific to the SQLite CLI.

.table

PRAGMA table_info

The PRAGMA table_info statement in SQLite is used to retrieve information about the columns in a specified table. It is particularly useful for understanding the structure of a table, including the names of columns, their data types, whether they can be NULL, and other properties.

PRAGMA table_info(table_name);

• When you execute PRAGMA table_info, it returns a result set with the following columns:
  
  
  • cid: The column ID.
  • name: The name of the column.
  • type: The data type of the column.
  • notnull: A flag indicating if the column can be NULL (0 for false, 1 for true).
  • dflt_value: The default value for the column.
  • pk: A flag indicating if the column is part of the primary key (0 for false, any non-zero value indicates the order of the column in the primary key).