SQL for Data Definition

Understanding Why We Define Data First

Before you can store any data, you must first decide how that data will be organised. This is the core idea behind data definition. You cannot just start typing numbers and names into a database — you need a blueprint. That blueprint is called a relation schema (or simply a table structure). Defining this schema involves several clear steps: giving the table a name, listing all the columns (attributes) it will contain, choosing the correct data type for each column (e.g., integer, text, date), and setting any rules or constraints that the data must follow.

Sometimes, after the schema is created, you may need to change it — add a new column, delete an old one, or modify a data type. SQL provides a set of statements specifically for these tasks: creating, modifying, and deleting relation schemas. These statements belong to the Data Definition Language (DDL) part of SQL.

The Core DDL Statement: CREATE

The most fundamental DDL statement is CREATE. It is used to create both the database itself and the tables (relations) inside it.

Planning Before Creating

The textbook emphasises a crucial planning step: before creating a database, you should be clear about:

• How many tables the database will have.
• The columns (attributes) in each table.
• The data type of each column.
• Any constraints on each column (if any).

This planning ensures that the database is well-structured from the start, avoiding messy redesigns later.

The CREATE DATABASE Statement

To create a new database, you use:

CREATE DATABASE database_name;

For example, to create a database for a school:

CREATE DATABASE School;

The CREATE TABLE Statement

Once the database exists, you create tables inside it. The general syntax is:

CREATE TABLE table_name (
    column1_name data_type constraint,
    column2_name data_type constraint,
    ...
);

Each column definition includes:

• Column name: A meaningful identifier (e.g., RollNo, StudentName).
• Data type: Specifies what kind of data the column can hold (e.g., INT, VARCHAR(50), DATE).
• Constraint (optional): Rules that restrict the data (e.g., NOT NULL, PRIMARY KEY).

Modifying and Deleting Schemas

The textbook mentions that SQL also allows you to modify and delete relation schemas. These operations are performed using other DDL statements:

• ALTER TABLE — to modify an existing table (add, drop, or modify columns).
• DROP TABLE — to delete an entire table and its data.
• DROP DATABASE — to delete the entire database.

These are introduced here as part of the DDL family, but their detailed syntax is covered in later sections of the chapter.

Summary of Key Points

| Concept | Explanation |

|---------|-------------| …