Lecture 6: SQL - DDL, DML & Constraints

1. Data Definition Language (DDL)

These commands define the structure (schema) of the database.

• CREATE DATABASE: Creates a new container for tables.

  CREATE DATABASE CompanyDB;

• CREATE TABLE: Defines a new table, its columns, and data types.

  CREATE TABLE Employee (
      EmpID INT PRIMARY KEY,
      Name VARCHAR(255) NOT NULL,
      Dept VARCHAR(50)
  );

  • Common Data Types: INT (Integers), VARCHAR(n) (Text with variable length), DATE, DECIMAL.

• DROP TABLE/DATABASE: Permanently deletes the object and its data.

  DROP TABLE Employee;

2. SQL Constraints

Rules enforced on data columns to ensure accuracy and reliability.

• PRIMARY KEY: Uniquely identifies each record (Combination of NOT NULL + UNIQUE).
• NOT NULL: Column cannot be left empty.
• UNIQUE: All values in the column must be different.
• FOREIGN KEY: Links to another table's Primary Key.
  • Cascading:
    • ON DELETE CASCADE: If parent record is deleted, child records are deleted automatically.
    • ON UPDATE CASCADE: If parent ID changes, child FKs update automatically.

3. Data Manipulation Language (DML)

These commands manage the data inside the tables.

• INSERT INTO: Adds new records.

  INSERT INTO Employee (EmpID, Name, Dept)
  VALUES (101, 'Ahmed', 'IT');

• UPDATE: Modifies existing records. Crucial: Always use WHERE, otherwise all rows change.

  UPDATE Employee
  SET Dept = 'HR'
  WHERE EmpID = 101;

• DELETE FROM: Removes records. Crucial: Always use WHERE, otherwise all data is lost.

  DELETE FROM Employee
  WHERE EmpID = 101;