SQL Lecture Notes: CRUD Operations and Schema Alteration

Key Points

• Recap of previous sessions:
  • Primary Key, Unique Key, Auto Increment
  • Importance of practicing SQL
  • Encouragement to subscribe to the channel

CRUD Operations

Overview

• CRUD stands for Create, Read, Update, Delete.
• Equivalent SQL Commands:
  • Create: INSERT
  • Read: SELECT
  • Update: UPDATE
  • Delete: DELETE

Create (INSERT)

• Syntax: INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
• Example: Creating an employee table and inserting records.

Read (SELECT)

• Basic Syntax: SELECT * FROM table_name;
  • Select specific columns: SELECT column1, column2 FROM table_name;
  • Using WHERE clause for filters: SELECT * FROM table_name WHERE condition;
  • Case insensitivity: SQL is by default case insensitive.
  • Case sensitivity: use BINARY keyword (SELECT * FROM table_name WHERE BINARY column = value;).
  • Alias: renaming columns using AS keyword (SELECT column AS alias FROM table_name;).

Update (UPDATE)

• Syntax: UPDATE table_name SET column1 = value1 WHERE condition;
  • Example: Update last name, change location.
  • Without WHERE clause: updates all records in the table.
  • Using logical operators: Combine multiple conditions with AND, OR.

Delete (DELETE)

• Syntax: DELETE FROM table_name WHERE condition;
  • Example: Delete record based on a specific condition.
  • Without WHERE clause: deletes all records.
  • Caution: Always run a SELECT statement first to verify the records to be deleted.

Alter Command

• Purpose: To change the table structure.
• Add Column: ALTER TABLE table_name ADD COLUMN column_name datatype;
• Drop Column: ALTER TABLE table_name DROP COLUMN column_name;
• Modify Column: Change data type or size: ALTER TABLE table_name MODIFY COLUMN column_name datatype;
• Drop Primary Key: ALTER TABLE table_name DROP PRIMARY KEY;
• Add Primary Key: ALTER TABLE table_name ADD PRIMARY KEY (column_name);

DDL vs DML

• DDL: Data Definition Language (deals with table structure)
  • Examples: CREATE TABLE, ALTER TABLE, DROP TABLE
• DML: Data Manipulation Language (deals with actual data)
  • Examples: INSERT, UPDATE, DELETE

Truncate vs Delete

• Delete: DELETE FROM table_name; (DML command, deletes records one by one)
• Truncate: TRUNCATE TABLE table_name; (DDL command, drops and recreates the table)
  • More efficient for large datasets

Summary

• Overview of CRUD operations
• Usage of SELECT, UPDATE, DELETE, ALTER commands
• Differentiation between DML and DDL
• Introduction to differences between DELETE and TRUNCATE

Next Session Preview

• Continuation of further topics in SQL

Reminder: Practice exercises and subscribe for more content.