SQL DML Commands: Delete and Select

Overview

In this presentation, we focus on delete and select commands within SQL's Data Manipulation Language (DML).

SQL Sublanguages

SQL consists of four sublanguages:

• DDL: Data Definition Language
• DML: Data Manipulation Language
• DCL: Data Control Language
• TCL: Transaction Control Language

Focus on DML

DML Commands

1. Insert
2. Update
3. Delete: Removes rows from a table.
4. Select: Retrieves data from a table.

Delete Command

• Syntax: DELETE FROM table_name WHERE condition
• Example:

  DELETE FROM student WHERE ID = 101;
  

• Explanation: Deletes rows that match the specified condition.
• Note: Unlike truncate, delete affects the data but not the table structure.

Select Command

• Syntax: SELECT column1, column2 FROM table_name WHERE condition
  • To select all columns: SELECT * FROM table_name
• Example:

  SELECT * FROM student WHERE gender = 'F';
  

• Explanation: Retrieves data based on specified conditions. SELECT * retrieves all columns.

Points to Consider

• Delete vs. Truncate:

  • Truncate is faster but does not use conditions.
  • Delete can be selective using conditions.

• Select Command:

  • Can be used with WHERE to filter data.
  • Allows changing the order of selected columns.
  • Does not affect the original table, only retrieves data.

Practical Applications in Front End

• SQL queries interface between frontend interfaces (like ATMs) and backend databases.
• Withdrawal Example: An update query adjusts account balance after money is withdrawn.
• Balance Check: A select query retrieves current balance.

Conclusion

Understanding and using delete and select commands are essential for manipulating data within SQL databases effectively. They allow for practical applications in real-world scenarios, notably in frontend-backend communication in systems like ATM machines.