1. Creating Tables

The CREATE TABLE statement is used to create a new table in SQL. Here is the format of a simple CREATE TABLE statement:

CREATE TABLE "tablename"
(
  "column1" "data type",
  "column2" "data type",
  "column3" "data type"
);

To include optional constraints, the format is as follows:

CREATE TABLE "tablename"
(
  "column1" "data type" [constraint],
  "column2" "data type" [constraint],
  "column3" "data type" [constraint]
);

Note: You may have as many columns as you like, and the constraints are optional.

1.1. Example

To create a table named employee with columns for first name, last name, age, address, city, and state:

CREATE TABLE employee
(
  first VARCHAR(15),
  last VARCHAR(20),
  age NUMBER(3),
  address VARCHAR(30),
  city VARCHAR(20),
  state VARCHAR(20)
);

1.2. Syntax Details

To create a new table:

• Enter the keywords CREATE TABLE followed by the table name.
• Follow this with an open parenthesis (.
• Specify the column name, data type, and optional constraints for each column.
• Separate each column definition with a comma.
• End with a closing parenthesis ).

All SQL statements should end with a semicolon ;.

1.2.1. Naming Conventions

• Table and column names must start with a letter and can include letters, numbers, or underscores, not exceeding 30 characters in length.
• Avoid using SQL reserved keywords as table or column names (e.g., "select", "create", "insert").

1.2.2. Data Types

• char(size): Fixed-length character string. Size specified in parentheses. Max 255 bytes.
• varchar(size): Variable-length character string. Max size specified in parentheses.
• number(size): Number value with a max number of digits specified in parentheses.
• date: Date value.
• number(size,d): Number value with a max of size total digits, and d digits to the right of the decimal.

1.3. Constraints

Constraints are rules applied to columns to enforce data integrity. Common constraints include:

• UNIQUE: Ensures all values in a column are unique.
• NOT NULL: Ensures a column cannot have a NULL value.
• PRIMARY KEY: Uniquely identifies each record in a table.

Constraints will be covered in detail in advanced SQL tutorials.

1.4. Create Table Exercise

To create a table for storing employee information (firstname, lastname, title, age, salary):

CREATE TABLE myemployees_dm0905
(
  firstname VARCHAR(30),
  lastname VARCHAR(30),
  title VARCHAR(30),
  age NUMBER(2),
  salary NUMBER(8,2)
);

Use your initials and birth date digits to create a unique table name, e.g., "myemployees_dm0905".

1.5. Column Definitions

Each column in a table must be defined with a name and a data type. Column definitions determine the type of data that can be stored in each column and enforce data integrity.

• Column Name: The name assigned to a column must follow the naming conventions and be unique within the table.
• Data Type: The data type specifies the type of data that can be stored in the column (e.g., VARCHAR, NUMBER, DATE).
• Constraints: Constraints are optional rules that enforce data integrity (e.g., NOT NULL, UNIQUE).

1.6. Understanding Data Types

Data types define the kind of data that can be stored in a column. Commonly used data types include:

• CHAR(size): A fixed-length character string. Size is specified in parentheses.
• VARCHAR(size): A variable-length character string. Maximum size is specified in parentheses.
• NUMBER(size): A numeric value with a maximum number of digits specified in parentheses.
• NUMBER(size, d): A numeric value with a total of size digits, including d digits to the right of the decimal point.
• DATE: A date value.

1.7. Adding Constraints

Constraints enforce rules on the data in a table to ensure data integrity and accuracy. Common constraints include:

• PRIMARY KEY: Uniquely identifies each row in a table.
• FOREIGN KEY: Ensures referential integrity by linking to a primary key in another table.
• UNIQUE: Ensures all values in a column are distinct.
• NOT NULL: Ensures a column cannot have a NULL value.
• CHECK: Ensures all values in a column satisfy a specific condition.

1.8. Example with Constraints

Creating a table with constraints:

CREATE TABLE employees_dm0905
(
  employee_id NUMBER(5) PRIMARY KEY,
  firstname VARCHAR(30) NOT NULL,
  lastname VARCHAR(30) NOT NULL,
  title VARCHAR(30),
  age NUMBER(2),
  salary NUMBER(8,2) CHECK (salary > 0)
);

This example includes a primary key, not null constraints, and a check constraint.

1.9. Dropping Tables

The DROP TABLE statement is used to delete a table and all of its data permanently. Use this statement with caution, as it cannot be undone.

DROP TABLE employees_dm0905;

This command deletes the employees_dm0905 table.

1.10. Modifying Tables

To modify an existing table, use the ALTER TABLE statement. This can be used to add, modify, or drop columns and constraints.

1.10.1. Adding a Column

ALTER TABLE employees_dm0905
ADD email VARCHAR(50);

This command adds an email column to the employees_dm0905 table.

1.10.2. Modifying a Column

ALTER TABLE employees_dm0905
MODIFY salary NUMBER(10,2);

This command modifies the salary column to allow for a larger number of digits.

1.10.3. Dropping a Column

ALTER TABLE employees_dm0905
DROP COLUMN title;

This command removes the title column from the employees_dm0905 table.

1.11. Practice Exercise

Using the concepts covered, create a table named departments_dm0905 with the following columns:

• department_id: Number, primary key
• department_name: Variable-length character, not null, unique
• manager_id: Number
• location_id: Number

CREATE TABLE departments_dm0905
(
  department_id NUMBER(5) PRIMARY KEY,
  department_name VARCHAR(50) NOT NULL UNIQUE,
  manager_id NUMBER(5),
  location_id NUMBER(5)
);