Data definition in SQL

The structure and characteristics of a database item, such as a table, view, index, or stored procedure, are defined using a Data Definition Language (DDL) in SQL (Structured Query Language).                                                                                                                               

SQL has the following data definition statements:

Creates new database objects including tables, views, indexes, and stored procedures with the CREATE command.

A database object's structure or characteristics can be changed using the ALTER command.

DROP: Deletes a current database object.

TRUNCATE: Removes every row of data from a table.

RENAME: Modifies a database object's name.

Adds comments to a database object in order to provide clarification or more information.

Some examples of data definition statements in SQL are:

• CREATE TABLE: This command creates a new table with the columns and data types of your choosing. A table can be altered with the ALTER TABLE command by adding or removing columns, changing the data types of existing columns, or altering constraints. DROP TABLE: Removes a table from existence together with all of its data. CREATE INDEX: Creates an index on one or more table columns to speed up query execution. ALTER INDEX: Changes an index's structure or characteristics. Dropping an index removes it from the system. CREATE VIEW: Constructs a fictitious table using a SELECT statement's results. A view can be altered to change its attributes or structure. A view is deleted when it is dropped.

Creating Tables The CREATE TABLE statement is used to create a new table in SQL.

Here is the syntax for creating a table:

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

For example, create a table named "students" with the columns "id", "name", "age", and "email".

CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT, email VARCHAR(50) );

In the above example, the "id" column is set as the primary key, which means it will have a unique value for each row in the table. The "name" column is set as "NOT NULL" which means it cannot contain a null value.

Modifying Tables The ALTER TABLE statement is used to modify an existing table.

Here is the syntax for modifying a table:

ALTER TABLE table_name ADD column_name datatype; ALTER TABLE table_name DROP COLUMN column_name;

For example, let's add a new column "address" to the "students" table.

ALTER TABLE students ADD address VARCHAR(100);

Let's also remove the "email" column from the "students" table.

ALTER TABLE students DROP COLUMN email;

Creating Indexes

Indexes are used to improve the performance of database queries. The CREATE INDEX statement is used to create a new index in SQL.

Here is the syntax for creating an index:

CREATE INDEX index_name ON table_name (column1, column2, ...);

For example, create an index named "age_index" on the "students" table for the "age" column.

CREATE INDEX age_index ON students (age);

Creating Views

Views are virtual tables that are created by combining data from one or more tables. The CREATE VIEW statement is used to create a new view in SQL.

Here is the syntax for creating a view:

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

For example, create a view named "young_students" that only includes students who are under the age of 18.

CREATE VIEW young_students AS SELECT id, name FROM students WHERE age < 18;