CRUD Operations in SQL

 Let's go into detail about the most fundamental SQL operations: SELECT, WHERE, INSERT, UPDATE, and DELETE, with examples to clarify each.

---

1. SELECT: Retrieving Data from a Database

The SELECT statement is used to retrieve data from one or more tables in a database.

Syntax:

SELECT column1, column2, ...
FROM table_name;            

Example:

Let’s assume we have a table called employees:

id	name	position	salary
1	Alice	Manager	70000
2	Bob	Developer	50000
3	Charlie	Designer	40000

• To retrieve all employee data:

  
  SELECT * FROM employees;
  

  Result 1:

   id | name     | position | salary 
   --------------------------------- 
   1  | Alice    | Manager  | 70000  
   2  | Bob      | Developer| 50000  
   3  | Charlie  | Designer | 40000  

• To retrieve only name and position:

  
  SELECT name, position FROM employees;
  

  Result:

   name    | position 
   ------------------ 
   Alice   | Manager  
   Bob     | Developer
   Charlie | Designer 
  

---

2. WHERE Clause: Filtering Data

The WHERE clause is used to filter records based on specified conditions.

Syntax:

 SELECT column1, column2, ...
 FROM table_name             
 WHERE condition;            

Example:

• To retrieve employees with a salary greater than 45000:

  SELECT * FROM employees WHERE salary > 45000;
  

  Result:

   id | name  | position | salary
   ------------------------------
   1  | Alice | Manager  | 70000 
   2  | Bob   | Developer| 50000 
  

• To retrieve employees who are either Managers or Developers:

  
  SELECT * FROM employees WHERE position IN ('Manager', 'Developer');
  

  Result:

  
   id | name  | position | salary
   ------------------------------
   1  | Alice | Manager  | 70000 
   2  | Bob   | Developer| 50000 
  

---

3. INSERT: Adding Data to a Database

The INSERT statement is used to add new records into a table.

Syntax:

INSERT INTO table_name (column1, column2,)
VALUES (value1, value2, ...);             

Example:

• To insert a new employee:

  
  INSERT INTO employees (id, name, position, salary)
  VALUES (4, 'Daisy', 'Tester', 45000);             
  

  After this INSERT, the table looks like:

  id	name	position	salary
  1	Alice	Manager	70000
  2	Bob	Developer	50000
  3	Charlie	Designer	40000
  4	Daisy	Tester	45000

---

4. UPDATE: Modifying Existing Data

The UPDATE statement is used to modify the existing records in a table.

Syntax:

UPDATE table_name                          
SET column1 = value1, column2 = value2, ...
WHERE condition;                           

Example:

• To update Bob’s salary to 55000:

  
  UPDATE employees   
  SET salary = 55000 
  WHERE name = 'Bob';
  

  After this UPDATE, the table looks like:

  id	name	position	salary
  1	Alice	Manager	70000
  2	Bob	Developer	55000
  3	Charlie	Designer	40000
  4	Daisy	Tester	45000

---

5. DELETE: Removing Data from a Table

The DELETE statement is used to delete existing records in a table.

Syntax:

DELETE FROM table_name
WHERE condition;      

Example:

• To delete the employee record of Charlie:

  
  DELETE FROM employees  
  WHERE name = 'Charlie';
  

  After this DELETE, the table looks like:

  id	name	position	salary
  1	Alice	Manager	70000
  2	Bob	Developer	55000
  4	Daisy	Tester	45000