Skip to content

1.core_CRUD.md

Latest commit

 

History

History
187 lines (151 loc) · 7.29 KB

1.core_CRUD.md

File metadata and controls

187 lines (151 loc) · 7.29 KB

Core CRUD

Basic SQL CRUD Operations Explained

CRUD stands for Create, Read, Update, Delete. These are the fundamental actions you perform on data within a database table.

While CREATE TABLE isn't technically part of data CRUD (it creates the structure, not the data itself), it's the essential first step. The 'Read' part of CRUD is handled by the SELECT statement.

Here's a breakdown:

1. CREATE TABLE (Setting up the Structure)

  • Purpose: To define a new table in your database. You specify the table's name and the columns it will contain, along with the data type for each column (e.g., text, number, date) and any constraints (like whether a value is required or must be unique).
  • Syntax: 
    CREATE TABLE table_name (
    column1_name data_type constraints,
    column2_name data_type constraints,
    ...
    columnN_name data_type constraints
);
  • Example: Create a simple table to store information about employees. 
    CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,  -- Unique identifier for each employee, cannot be null
    FirstName VARCHAR(50) NOT NULL, -- Employee's first name, up to 50 characters, cannot be null
    LastName VARCHAR(50) NOT NULL,  -- Employee's last name, up to 50 characters, cannot be null
    Department VARCHAR(50),       -- Department name, up to 50 characters (can be null)
    Salary DECIMAL(10, 2)         -- Salary, allowing up to 10 digits total, with 2 after the decimal point
);
    • INT: Integer (whole number).
    • VARCHAR(n): Variable-length string (text) up to n characters.
    • DECIMAL(p, s): Exact decimal number with precision p (total digits) and scale s (digits after decimal).
    • PRIMARY KEY: Constraint ensuring EmployeeID is unique for each row and cannot be empty (NULL).
    • NOT NULL: Constraint ensuring a value must be provided for this column.

2. INSERT INTO (Create Data)

  • Purpose: To add new rows (records) of data into an existing table. This is the 'Create' part of CRUD for data.
  • Syntax: 
    -- Option 1: Specify columns and values (recommended for clarity)
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

-- Option 2: If providing values for ALL columns in their defined order
INSERT INTO table_name
VALUES (value1_for_col1, value2_for_col2, ...);
  • Example: Add two employees to our Employees table. 
    -- Using Option 1 (specifying columns)
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (101, 'Alice', 'Smith', 'HR', 60000.00);

-- Using Option 2 (assuming order: EmployeeID, FirstName, LastName, Department, Salary)
INSERT INTO Employees
VALUES (102, 'Bob', 'Johnson', 'IT', 75000.50);

-- Inserting another employee, leaving Department null
INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
VALUES (103, 'Charlie', 'Williams', 55000.00);
    • Notice that string values like names and departments are enclosed in single quotes (').
    • Numbers are not quoted.
    • If a column allows NULL (like Department) and you don't provide a value (like for Charlie), it will be stored as NULL (unknown/missing).

3. UPDATE (Update Data)

  • Purpose: To modify existing data in one or more rows within a table. This is the 'Update' part of CRUD.
  • Syntax: 
    UPDATE table_name
SET column1 = new_value1,
    column2 = new_value2,
    ...
WHERE condition; -- VERY IMPORTANT! Specifies which row(s) to update.
  • WARNING: If you forget the WHERE clause, the UPDATE statement will modify ALL rows in the table! Always be careful and specify which rows you intend to change.
  • Example: Give Alice a raise and move Bob to the 'Development' department. 
    -- Update Alice's salary (EmployeeID 101)
UPDATE Employees
SET Salary = 65000.00
WHERE EmployeeID = 101;

-- Update Bob's department (EmployeeID 102)
UPDATE Employees
SET Department = 'Development'
WHERE EmployeeID = 102;
    • The WHERE clause identifies the specific row(s) based on a condition (in this case, matching the EmployeeID).

4. DELETE (Delete Data)

  • Purpose: To remove one or more existing rows (records) from a table. This is the 'Delete' part of CRUD.
  • Syntax: 
    DELETE FROM table_name
WHERE condition; -- VERY IMPORTANT! Specifies which row(s) to delete.
  • WARNING: Just like UPDATE, if you forget the WHERE clause, the DELETE statement will remove ALL rows from the table! Be extremely cautious.
  • Example: Assume Charlie leaves the company. 
    -- Delete the employee with EmployeeID 103
DELETE FROM Employees
WHERE EmployeeID = 103;
    • The WHERE clause ensures only the row matching EmployeeID = 103 is removed.

Bonus: SELECT (Read Data)

  • Purpose: To retrieve (read) data from one or more tables. This is the 'Read' part of CRUD and how you view the results of your other operations.
  • Syntax: 
    SELECT column1, column2, ... -- Or use * to select all columns
FROM table_name
WHERE condition; -- Optional: filters which rows to retrieve
  • Example: See the current state of our Employees table after the previous operations. 
    -- Select all columns (*) for all remaining employees
SELECT * FROM Employees;

-- Select only FirstName and Department for employees in Development
SELECT FirstName, Department
FROM Employees
WHERE Department = 'Development';

These operations (INSERT, SELECT, UPDATE, DELETE), along with CREATE TABLE, form the foundation of interacting with data in relational databases using SQL.

Practical Session 🔴

-- First employee: Adding Alice to HR department with initial salary
-- Using explicit column names for clarity
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (101, 'Alice', 'Smith', 'HR', 60000.00);


-- Second employee: Adding Bob to IT department
-- Using implicit column order (must match table definition exactly)
INSERT INTO Employees
VALUES (102, 'Bob', 'Johnson', 'IT', 75000.50);


-- Third employee: Adding Charlie without specifying department
-- Department will be NULL since it's not included in the column list
INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
VALUES (103, 'Charlie', 'Williams', 55000.00);


-- Giving Alice a raise: Update salary for specific employee
-- Using WHERE clause to ensure we only update Alice's record
UPDATE Employees
SET Salary = 65000.00 
WHERE EmployeeID = 101;


-- Moving Bob to a different department
-- Using WHERE clause to target only Bob's record
UPDATE Employees
SET Department = 'Development'
WHERE EmployeeID = 102;


-- Removing Charlie's record from the database
-- Using WHERE clause to ensure we only delete Charlie's record
DELETE FROM Employees
WHERE EmployeeID = 103;

-- Viewing all employee records after our changes
-- Shows current state of the table
SELECT * FROM Employees;


-- Filtering to show only employees in Development department
-- Demonstrates how to query specific columns with conditions
SELECT FirstName, Department
FROM Employees
WHERE Department = 'Development';