๐Ÿง  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

TypeDescription
INTInteger values
DECIMALFixed-point decimal (money, etc.)
VARCHARVariable-length text
BLOBBinary large object (images, files)
DATEStores year-month-day
TIMESTAMPStores date & time

๐Ÿ” Querying & Data Insertion

โœ… SELECT

SELECT * FROM users;

โœ… INSERT

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

๐Ÿ”’ Constraints

ConstraintUse Case
NOT NULLPrevents null entries in a column
UNIQUEEnsures unique value in a column
DEFAULTSets default value if none is provided
AUTO_INCREMENTAutomatically increments numeric value

๐Ÿ“ 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.