Lesson 1

Creating Modifying Table Structures (DDL)

This module introduces the SQL commands necessary to create and modify database tables, indexes, and constraints^[1]. Here again, Oracle has taken the basic SQL commands and extended them to include parameters specifically tailored for the Oracle database system.

Creating and modifying database tables in SQL involves a series of specific commands. The commands required to create tables include CREATE TABLE, while ALTER TABLE is used for modifying tables. Here is a detailed explanation of these commands:
CREATE TABLE: This command is used to create a new table in a database. The syntax is as follows:

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

Each column in the table is declared with a name and a datatype. For example:

CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE
);

ALTER TABLE: This command is used to add, delete/drop, or modify columns in an existing table. It can also be used to add and drop constraints on an existing table.

ADD: This command is used to add one or more columns in a table. The syntax is as follows:
```
ALTER TABLE table_name
ADD column_name datatype;
```
For example, to add a column Email to the Employees table:
```
ALTER TABLE Employees
ADD Email VARCHAR(100);
```
DROP COLUMN: This command is used to delete a column in a table. The syntax is as follows:
```
ALTER TABLE table_name
DROP COLUMN column_name;
```
For example, to drop the Email column from the Employees table:
```
ALTER TABLE Employees
DROP COLUMN Email;
```
MODIFY: This command is used to change the data type of a column in a table. The syntax is as follows:
```
ALTER TABLE table_name
MODIFY column_name column_type;
```
For example, to change the data type of the EmployeeID column to VARCHAR in the Employees table:
```
ALTER TABLE Employees
MODIFY EmployeeID VARCHAR(10);
```

In addition to these, the PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL commands can be used to impose constraints on tables during creation or modification.
Remember that SQL commands are not case-sensitive, but it is a common convention to write them in uppercase for readability. Additionally, SQL syntax can vary slightly between different database systems, so it is important to consult the specific documentation for the SQL variant you are using.

Module Objectives

By the end of this module, you will know how to:

Identify the components of the CREATE TABLE command
Describe how tables, tablespaces, and datafiles fit together
Identify the parameters that define storage space
Describe the advantages of using primary key constraints
Identify the syntax components of the foreign key, check, and unique constraints
Change columns in an existing table
Describe the effects of dropping a table and disabling or removing a constraint

TABLE Commands

First, you will work with the CREATE TABLE command. There are several lessons on this because there is a vast amount of information you need to know about this feature, including not only the columns to be included in the table, but also the amount of physical space to allocate the table, some validation, and even the relationships between the table and other tables.
Next, you will find out how to modify the table's construction once the table has already been created, using the ALTER TABLE command. Finally, you will discover how to remove a table from the database and either remove or disable a relationship that was defined earlier. The next lesson starts you out on the basics of the CREATE TABLE command.

[1]Constraint: A constraint is a database rule that you define within the database to enforce certain conditions regarding the data that is stored in the database tables.