Lecture 5: SQL Commands

1. The Structure of SQL Commands

SQL (Structured Query Language) is not a monolithic language; it is categorized into five highly specific sub-languages based on their functional intent.

2. Data Definition Language (DDL)

DDL commands interact exclusively with the structural architecture of the database, not the raw data itself.

• CREATE TABLE: Used to construct brand new database objects. You define the columns, data types, and structural constraints during this phase.

  CREATE TABLE Projects (ProjectID INT PRIMARY KEY, ProjectName VARCHAR(50));

• DROP TABLE: The most destructive command. It permanently obliterates the table structure and all the data residing inside it.
• ALTER TABLE: Modifies the skeleton of an existing table. It can ADD new columns, DROP existing columns, or apply new CHECK or FOREIGN KEY constraints.
• TRUNCATE TABLE: A rapid deletion command. It wipes out absolutely every data row inside the table instantly but cleanly preserves the empty table structure for future use.
• RENAME TABLE: Modifies the name of the object without interacting with the data.

3. Data Manipulation Language (DML)

DML commands act directly on the data rows residing inside the tables.

• INSERT INTO: Appends brand new records into the table.

  INSERT INTO Employees (EmpID, Name, Age) VALUES (1, 'Alice', 25);

• UPDATE: Modifies existing data values. This command should almost always be paired with a WHERE clause; otherwise, it will blindly overwrite every single row in the table.

  UPDATE Employees SET Age = 26 WHERE EmpID = 1;

• DELETE FROM: Removes specific rows based on a given condition. Unlike TRUNCATE, DELETE allows for surgical, targeted removal of data.

4. Data Query Language (DQL)

• SELECT: The singular, omnipotent command used to fetch, filter, sort, and retrieve data from one or multiple tables based on highly specific conditions.

5. Data Control Language (DCL) & Transaction Control Language (TCL)

• DCL: Dictates database security. GRANT endows a user with specific access rights, while REVOKE strips those rights away.
• TCL: Manages the reliability of data modifications to ensure ACID compliance. COMMIT permanently writes and saves the current transaction to the disk. ROLLBACK acts as an emergency undo button, reverting any uncommitted DML changes if an error is detected. SAVEPOINT allows developers to set a bookmark within a massive transaction to rollback to a specific intermediate step without losing the whole operation.