SQL Presentation: DDL and Create Command

Overview

In this presentation, we delve into the first DDL (Data Definition Language) command: the CREATE command. SQL consists of four sublanguages:

1. DDL - Data Definition Language
2. DML - Data Manipulation Language
3. DCL - Data Control Language
4. TCL - Transaction Control Language

We will focus on the DDL sublanguage and specifically on the CREATE command.

DDL Commands

DDL involves defining the structure of database objects, which are automatically committed. DDL comprises five commands:

1. CREATE
2. ALTER
3. TRUNCATE
4. DROP
5. RENAME

The CREATE Command

Objective

We aim to illustrate how to use the CREATE command to construct a table.

Example Table

• Table Name: Employee
• Attributes/Columns: ID, Name, Department Name, Salary

Syntax for Creating a Table

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

• Columns are defined with their names and data types.
• The use of commas separates multiple columns; the last column does not require a comma before closing the parenthesis.

Example

CREATE TABLE Employee (
  ID INTEGER(4),
  Name VARCHAR(50),
  DepartmentName VARCHAR(40),
  Salary INTEGER(10)
);

• ID: Integer type with a maximum size of 4
• Name: Varchar type with a maximum of 50 characters
• DepartmentName: Varchar type with a maximum of 40 characters
• Salary: Integer type with maximum 10 digits

Data Types in SQL

Commonly Used Data Types

1. CHAR: Stores fixed-length character strings
2. VARCHAR: Stores variable-length character strings, including alphanumeric
3. BOOLEAN: Stores TRUE/FALSE values, suitable for conditional attributes
4. INTEGER: Stores integer numbers
5. FLOAT: Stores decimal numbers with precision
6. DATE: Stores dates
7. TIMESTAMP: Stores date and time details

Important Notes

• BOOLEAN: Used when a column should strictly store TRUE/FALSE, e.g., isPermanentEmployee.
• VARCHAR vs CHAR: VARCHAR is dynamic and allows flexibility in size, whereas CHAR is of fixed length.

Examples with Varying Data Types

1. Example 1: Basic employee table structure

   CREATE TABLE Employee (
     ID INTEGER(4),
     Name VARCHAR(50)
   );
   

2. Example 2: Including department and salary

   CREATE TABLE Employee (
     ID INTEGER(4),
     Name VARCHAR(50),
     DepartmentName VARCHAR(40),
     Salary INTEGER(10)
   );
   

3. Example 3: Different data types including float and date

   CREATE TABLE Employee (
     RollNumber INTEGER(10),
     Name VARCHAR(50),
     DateOfBirth DATE,
     Percentage FLOAT(2)
   );
   

Homework

Write a SQL DDL command to create a table with the following columns and choose appropriate data types:

• Employee ID
• Employee Name
• Date of Birth
• Salary
• City
• ZIP Code

Post your SQL CREATE statement in the comments section.

Conclusion

This presentation covered creating tables in SQL without constraints. Future lessons will explore how to apply conditions or constraints when defining tables.

---

Thank you for watching.

[Applause]