🧠 SQL Cheatsheet (In Progress...)

📌 What is a Database?

• A database is a collection of related information or data that can be stored, managed, and retrieved.

📌 SQL vs NoSQL

• SQL: Structured Query Language used to manage Relational Databases (RDBMS).
• NoSQL: Used for Non-Relational Databases (NRDBMS) like MongoDB, Redis, etc.

📌 Arch Linux Tip

• In Arch, MariaDB is a drop-in replacement for MySQL.

🏗 Table Creation & Management

✅ CREATE TABLE

CREATE TABLE users (
  id INT AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

✅ DESCRIBE

DESCRIBE users;

✅ ALTER TABLE

• Add column

ALTER TABLE users ADD age INT;

• Modify column

ALTER TABLE users MODIFY age DECIMAL(5,2);

• Drop column

ALTER TABLE users DROP COLUMN age;

✅ DROP TABLE

DROP TABLE users;

🔑 Keys in SQL

🔹 Primary Key

A column (or set of columns) that uniquely identifies each row in a table.

• Must be unique
• Cannot be NULL

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

🔹 Foreign Key

A column that references the primary key of another table to establish a relationship between them.

• Maintains referential integrity
• Can be NULL (if optional relationship)

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

🔹 Surrogate Key

An artificial key used as a unique identifier (usually AUTO_INCREMENT or UUID)

• Has no business meaning
• Used instead of natural keys to avoid complications

CREATE TABLE products (
  product_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

🔹 Composite Key

A primary key made of multiple columns. Used when a single column can’t uniquely identify a row.

CREATE TABLE enrollment (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id)
);

🔹 Candidate Key

Any column (or set of columns) that could be a primary key (i.e., unique and non-null).

• One becomes the primary key, others are alternate keys.

Alternate Key

A candidate key that was not chosen as the primary key but can still uniquely identify rows.

📋 Data Types

🔍 Querying & Data Insertion

✅ SELECT

SELECT * FROM users;

✅ INSERT

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

🔒 Constraints

📝 UPDATE & DELETE in SQL (MySQL)

🔄 UPDATE – Modify Existing Records

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

✅ Example:

-- Update a student's name where ID is 1
UPDATE students
SET name = 'Tanvir Ahmed'
WHERE id = 1;

⚠️ Always use WHERE with UPDATE to avoid updating all rows!

❌ DELETE – Remove Records from a Table

DELETE FROM table_name
WHERE condition;

✅ Example:

-- Delete a student where ID is 3
DELETE FROM students
WHERE id = 3;

⚠️ DELETE without a WHERE clause removes all rows! Use carefully.

🧪 Pro Tips

• 🔒 Always SELECT the rows first before doing an UPDATE or DELETE:

  SELECT * FROM students WHERE id = 1;
  

• 🧯 Use transactions if you're unsure:

  START TRANSACTION;
  UPDATE students SET name = 'Test' WHERE id = 1;
  ROLLBACK; -- or COMMIT;
  

🔍 Basic Queries

• Retrieve all columns: SELECT * FROM table_name;
• Retrieve specific columns: SELECT name, age FROM users;
• Filtering: SELECT * FROM users WHERE age > 18;
• Sorting: SELECT * FROM users ORDER BY name ASC;
• Limiting: SELECT * FROM users LIMIT 5;

🧱 Creating a Simple Schema

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT,
    email VARCHAR(100) UNIQUE
);

🧮 Functions & Aggregates

• COUNT(*) – total rows
• AVG(column) – average
• SUM(column) – total sum
• MAX(column) / MIN(column) – max/min values
• NOW() – current datetime

SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM students;

🔗 JOINS

• INNER JOIN: Only matching rows

SELECT * FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

• LEFT JOIN: All from left + matched from right
• RIGHT JOIN: All from right + matched from left

🥚 Nested Queries

SELECT name FROM students
WHERE id IN (
    SELECT student_id FROM enrollments WHERE course_id = 1
);

🧬 UNION

• Combine result sets (must have same number of columns)

SELECT name FROM teachers
UNION
SELECT name FROM students;

🌟 Wildcards

• % = any number of characters
• _ = a single character

SELECT * FROM users WHERE name LIKE 'A%';
SELECT * FROM products WHERE code LIKE '_23%';

🔐 ON DELETE SET NULL / CASCADE

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE SET NULL
);

-- or
ON DELETE CASCADE
-- deletes all related orders when user is deleted

📘 Advanced SQL & Database Design Concepts

🧩 DELIMITER in SQL

By default, SQL statements end with a semicolon ;. But when writing complex statements like stored procedures or triggers, we temporarily change the delimiter to avoid premature termination.

🔧 Usage:

DELIMITER $$

CREATE PROCEDURE example_proc()
BEGIN
    -- Multiple SQL statements
    SELECT "Hello";
    SELECT "World";
END $$

DELIMITER ;

DELIMITER $$ tells the MySQL interpreter to treat $$ as the end of the command block instead of ;. This is useful for functions, procedures, and triggers.

⚡ Triggers

Triggers are special stored procedures that automatically run in response to specific events on a table.

🧠 Types of Triggers:

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE

✅ Syntax:

DELIMITER $$

CREATE TRIGGER before_insert_user
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END $$

DELIMITER ;

NEW and OLD are special keywords to access values during trigger execution.

🧬 ER Diagrams (Entity-Relationship Diagrams)

ER diagrams visually represent how entities (tables) relate to one another in a database.

🧱 Key Concepts:

🧾 Entities:

• Represent real-world objects (e.g., Student, Course).
• Become tables in the database.

🔑 Attributes:

• Properties of an entity (e.g., name, age, ID).
• Types:
  • Simple Attribute: Cannot be divided (e.g., age).
  • Composite Attribute: Can be divided (e.g., name → first, last).
  • Derived Attribute: Computed (e.g., age from DOB).
  • Multivalued Attribute: Has multiple values (e.g., phone numbers).

🔗 Relationships:

• Connects two or more entities.
• Cardinality types:
  • One-to-One
  • One-to-Many
  • Many-to-Many

🧩 Keys:

• Primary Key: Uniquely identifies a record.
• Foreign Key: Creates a link between tables.

🔄 Participation:

• Total Participation: Every entity must participate in a relationship.
• Partial Participation: Optional participation.

🔖 Example ER Scenario:

Entities: Student, Course, Enrollment
Relations:

• A Student can enroll in multiple Courses.
• A Course can have many Students.

This forms a many-to-many relationship, usually resolved with an intermediate Enrollment table.

ER diagrams help with planning, normalization, and identifying relationships before creating the schema.