AQ

BlogProjects

YOUR SQL

November 1, 2024 (11mo ago)

20/09/2025: Intro to DBMS, MySQL, Installation Process

Introduction to DBMS

A Database Management System (DBMS) is software that enables users to create, manage, and manipulate databases efficiently. It acts as an intermediary between the user and the database, ensuring data is stored securely, retrieved quickly, and maintained with integrity. The primary purpose of a DBMS is to handle large volumes of data in an organized manner, supporting operations like insertion, updating, deletion, and querying.

DBMS evolved from file-based systems, which were prone to data redundancy and inconsistency. In the 1960s, hierarchical and network models emerged, but the relational model, proposed by Edgar F. Codd in 1970, revolutionized DBMS with its table-based structure using rows and columns. Key components of a DBMS include the database engine (for storage and retrieval), query processor (for executing commands), and transaction manager (for ensuring ACID properties: Atomicity, Consistency, Isolation, Durability).

Types of DBMS include:

  • Relational DBMS (RDBMS): Uses tables with relationships via keys (e.g., MySQL, Oracle).
  • NoSQL DBMS: For unstructured data (e.g., MongoDB for documents, Cassandra for key-value).
  • Object-Oriented DBMS: Integrates with OOP languages.
  • Hierarchical DBMS: Tree-like structure (e.g., IMS).
  • Network DBMS: Graph-like with many-to-many relationships.

Advantages of DBMS: Data independence (logical and physical), reduced redundancy, improved security via access controls, concurrent access with locking mechanisms, and backup/recovery features. Disadvantages include high cost, complexity for small applications, and potential performance overhead.

Example 1: Consider a university system without DBMS—student records in flat files might duplicate data across departments, leading to errors. With DBMS, a single "Students" table ensures consistency.

Example 2: In e-commerce, DBMS handles inventory updates atomically; if a purchase fails midway, the transaction rolls back, preventing stock discrepancies.

Introduction to MySQL

MySQL is an open-source relational database management system (RDBMS) developed by Oracle Corporation. It uses Structured Query Language (SQL) for data manipulation and is known for its speed, reliability, and ease of use. MySQL was first released in 1995 by MySQL AB and acquired by Sun Microsystems in 2008, then Oracle in 2010. It's widely used in web applications, powering sites like Facebook, Twitter, and WordPress via the LAMP stack (Linux, Apache, MySQL, PHP).

Key features:

  • Scalability: Handles large databases with replication and clustering.
  • Storage Engines: Supports InnoDB (transactional, default), MyISAM (fast reads), Memory (in-RAM), etc.
  • Security: User privileges, SSL encryption, and password hashing.
  • Compatibility: Works on Windows, Linux, macOS; integrates with languages like Python, Java.
  • Community and Enterprise Editions: Free community version; paid enterprise with advanced tools.

MySQL follows the client-server model: Clients (e.g., MySQL Workbench) send queries to the server, which processes them against the database files.

Example 1: Creating a simple database in MySQL:

CREATE DATABASE company;
USE company;
CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO employees VALUES (1, 'John Doe');
SELECT * FROM employees;

This demonstrates basic CRUD (Create, Read, Update, Delete) operations.

Example 2: MySQL in a blogging app: A "posts" table stores articles, linked to a "users" table via foreign keys, ensuring referential integrity.

Installation Process

Installing MySQL varies by OS, but the process is straightforward. Always download from the official site (mysql.com) to avoid malware.

On Windows:

  1. Download MySQL Installer (MSI) for Windows from the MySQL website.
  2. Run the installer; choose "Developer Default" for full features including MySQL Server, Workbench, and connectors.
  3. Configure: Set root password, add to PATH, and select port 3306.
  4. Install and start the service via Services app or command: net start mysql.
  5. Verify: Open Command Prompt, run mysql -u root -p, enter password, and execute SHOW DATABASES;.

On Linux (Ubuntu):

  1. Update packages: sudo apt update.
  2. Install: sudo apt install mysql-server.
  3. Secure installation: sudo mysql_secure_installation (set password, remove anonymous users).
  4. Start service: sudo systemctl start mysql.
  5. Verify: mysql -u root -p and run a query.

On macOS:

  1. Download DMG from MySQL site.
  2. Install via the package; set password during setup.
  3. Add to PATH: Edit ~/.bash_profile with export PATH="/usr/local/mysql/bin:$PATH".
  4. Start: sudo /usr/local/mysql/support-files/mysql.server start.
  5. Verify as above.

Example: Post-installation, create a test DB:

CREATE DATABASE testdb;

Common Mistakes:

  • Forgetting to set a strong root password, leading to security vulnerabilities.
  • Installing incompatible versions (e.g., 32-bit on 64-bit OS), causing crashes.
  • Not adding MySQL to system PATH, resulting in "command not found" errors.
  • Ignoring firewall rules, blocking port 3306 and preventing remote access.
  • Skipping post-install security script on Linux, leaving test databases exposed.

(Word count: 812)

22/09/2025: Sub Languages of SQL, Basic Datatypes

Sub Languages of SQL

SQL (Structured Query Language) is divided into sublanguages for specific tasks: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL). Some include Data Query Language (DQL) as a subset of DML.

  • DDL: Defines database structures. Commands: CREATE, ALTER, DROP, TRUNCATE, RENAME. It modifies schema without affecting data.
  • DML: Manipulates data. Commands: INSERT, UPDATE, DELETE, MERGE. Focuses on records.
  • DCL: Manages access. Commands: GRANT, REVOKE. Controls permissions.
  • TCL: Handles transactions. Commands: COMMIT, ROLLBACK, SAVEPOINT. Ensures data integrity.
  • DQL: Queries data. Primarily SELECT.

These sublanguages make SQL versatile for database operations.

Example 1 (DDL):

CREATE TABLE users (id INT); 
ALTER TABLE users ADD COLUMN email VARCHAR(100);

Example 2 (DML):

INSERT INTO users VALUES (1, 'user@example.com'); 
UPDATE users SET email = 'new@example.com' WHERE id=1;

Example 3 (DCL):

GRANT SELECT ON users TO 'viewer'@'localhost'; 
REVOKE SELECT ON users FROM 'viewer'@'localhost';

Example 4 (TCL):

START TRANSACTION; 
INSERT INTO users VALUES (2, 'test'); 
ROLLBACK;

(undoes insert).

Example 5 (DQL):

SELECT * FROM users WHERE id > 0;

Basic Datatypes

MySQL datatypes define how data is stored, affecting storage size and operations.

  • Numeric: INT (integer, 4 bytes), TINYINT (1 byte, -128 to 127), BIGINT (8 bytes), FLOAT (approx. decimal), DOUBLE (precise decimal), DECIMAL(p,s) (exact, e.g., DECIMAL(10,2) for money).
  • String: CHAR(n) (fixed length, padded), VARCHAR(n) (variable, up to 65,535 chars), TEXT (large text, up to 65,535), MEDIUMTEXT (16M), LONGTEXT (4GB).
  • Date/Time: DATE (YYYY-MM-DD), TIME (HH:MM:SS), DATETIME (both), TIMESTAMP (auto-updates).
  • Binary: BLOB (binary large object), BINARY(n) (fixed binary).
  • Boolean: TINYINT(1) as 0/1 for false/true.
  • Enum/Set: ENUM('value1','value2') restricts to list; SET allows multiple.

Choosing the right datatype optimizes storage and performance.

Example 1:

CREATE TABLE products (id INT, name VARCHAR(50), price DECIMAL(5,2), stock TINYINT);

Example 2: Inserting:

INSERT INTO products VALUES (1, 'Laptop', 999.99, 50);

(DECIMAL ensures no rounding errors).

Example 3: Date:

CREATE TABLE events (event_date DATE); 
INSERT INTO events VALUES ('2025-09-22');

Example 4: Enum:

CREATE TABLE status (level ENUM('low','medium','high')); 
INSERT INTO status VALUES ('medium');

Invalid: 'extreme' errors.

Example 5: Text vs VARCHAR: For short descriptions, use VARCHAR(255); for articles, TEXT.

Example 6: FLOAT vs DECIMAL: FLOAT(3.14) might store approx.; DECIMAL(5,2) stores exactly 3.14.

Common Mistakes:

  • Using VARCHAR for fixed-length data like phone numbers, wasting space (use CHAR).
  • Oversizing datatypes, e.g., INT for small IDs, increasing storage unnecessarily.
  • Mixing date formats, causing insertion errors (always YYYY-MM-DD).
  • Forgetting UNSIGNED for positive numerics, limiting range (e.g., INT UNSIGNED: 0 to 4B).
  • Using TEXT for searchable fields without indexes, slowing queries.

(Word count: 678)

23/09/2025: CREATE, DROP (Database, Table), Basic Commands

CREATE Command

CREATE defines new database objects. Syntax for database:

CREATE DATABASE db_name;

For table:

CREATE TABLE table_name (column1 datatype constraints, ...);

It specifies structure, datatypes, and optional constraints.

Example 1:

CREATE DATABASE school;

Example 2:

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

Example 3: Temporary table:

CREATE TEMPORARY TABLE temp (data VARCHAR(50));

(auto-drops on session end).

Example 4: View:

CREATE VIEW active_students AS SELECT * FROM students WHERE age > 18;

Example 5: Database with charset:

CREATE DATABASE blog CHARACTER SET utf8mb4;

DROP Command

DROP removes objects permanently. Syntax:

DROP DATABASE db_name;

or

DROP TABLE table_name;

Use cautiously as data is lost; no rollback without backups.

Example 1:

DROP DATABASE school;

(deletes entire DB).

Example 2:

DROP TABLE students;

Example 3: Multiple:

DROP TABLE table1, table2;

Example 4: If exists:

DROP TABLE IF EXISTS old_table;

(avoids errors).

Example 5: View:

DROP VIEW active_students;

Basic Commands

Basic commands include USE (switch DB:

USE db_name;

), SHOW (list objects:

SHOW DATABASES;

,

SHOW TABLES;

), DESCRIBE (table structure:

DESC table_name;

), HELP (syntax help).

Example 1:

USE school; 
SHOW TABLES;

Example 2:

DESC students;

(shows columns, types).

Example 3:

SHOW CREATE TABLE students;

(full creation script).

Example 4:

SHOW VARIABLES LIKE 'version';

(system info).

Example 5:

HELP 'CREATE TABLE';

Theory: These commands form DDL basics, ensuring schema management. CREATE allocates storage; DROP frees it.

Common Mistakes:

  • Dropping without backups, leading to data loss.
  • Creating tables without primary keys, causing duplicates.
  • Using reserved words as names without backticks (e.g.,
CREATE TABLE `select`;

).

  • Forgetting USE, executing in wrong DB.
  • Ignoring case sensitivity on Windows (DB names case-insensitive) vs Linux.

(Word count: 542)

24/09/2025: ALTER, RENAME, INSERT, UPDATE

ALTER Command

ALTER modifies existing table structure: add/drop columns, change types, add constraints.

Syntax:

ALTER TABLE table_name action;

Example 1: Add column:

ALTER TABLE students ADD COLUMN email VARCHAR(100);

Example 2: Drop:

ALTER TABLE students DROP COLUMN age;

Example 3: Modify type:

ALTER TABLE students MODIFY name VARCHAR(150);

Example 4: Add constraint:

ALTER TABLE students ADD UNIQUE (email);

Example 5: Rename column:

ALTER TABLE students CHANGE old_name new_name INT;

RENAME Command

RENAME changes names. For tables:

RENAME TABLE old TO new;

For DB: Use tools or dump/restore.

Example 1:

RENAME TABLE students TO pupils;

Example 2: Multiple:

RENAME TABLE t1 TO new1, t2 TO new2;

Example 3: Column via ALTER: As above.

Example 4: In views: Recreate with new name.

Example 5: Database rename workaround:

CREATE DATABASE newdb; 
RENAME TABLE olddb.table TO newdb.table; 
DROP DATABASE olddb;

INSERT Command

INSERT adds records (DML). Syntax:

INSERT INTO table (cols) VALUES (vals);

Example 1: Single:

INSERT INTO students (name) VALUES ('Alice');

Example 2: Multiple:

INSERT INTO students (name) VALUES ('Bob'), ('Charlie');

Example 3: From select:

INSERT INTO archive SELECT * FROM students WHERE age > 20;

Example 4: Ignore duplicates:

INSERT IGNORE INTO students VALUES (1, 'Duplicate');

Example 5: On duplicate update:

INSERT INTO students VALUES (1, 'New') ON DUPLICATE KEY UPDATE name='Updated';

UPDATE Command

UPDATE modifies records. Syntax:

UPDATE table SET col=val WHERE condition;

Example 1:

UPDATE students SET age=21 WHERE id=1;

Example 2: Multiple cols:

UPDATE students SET name='Dave', age=22 WHERE id=2;

Example 3: All rows:

UPDATE products SET price=price*1.1;

(10% increase).

Example 4: With join:

UPDATE students s JOIN classes c ON s.class_id=c.id SET s.grade='A' WHERE c.name='Math';

Example 5: Limit:

UPDATE students SET status='active' ORDER BY id LIMIT 10;

Theory: ALTER/RENAME for schema changes; INSERT/UPDATE for data. Use WHERE to avoid mass updates.

Common Mistakes:

  • Omitting WHERE in UPDATE, changing all rows.
  • Inserting wrong datatype, causing truncation (e.g., long string in short VARCHAR).
  • Renaming without updating dependent views/queries.
  • Altering large tables without backups, locking DB.
  • Ignoring auto-increment in INSERT, leading to key conflicts.

(Word count: 612)

25/09/2025: UPDATE, DELETE, TRUNCATE, COPY TABLE, INSERT Records

UPDATE (Continued)

Building on previous, UPDATE can use subqueries or expressions.

Example 6: Subquery:

UPDATE employees SET salary = (SELECT AVG(salary) FROM employees) WHERE dept='HR';

Example 7: Case:

UPDATE products SET category = CASE WHEN price < 10 THEN 'Cheap' ELSE 'Expensive' END;

Example 8: Increment:

UPDATE users SET login_count = login_count + 1 WHERE id=5;

Example 9: With limit/offset:

UPDATE orders SET status='shipped' ORDER BY date LIMIT 5 OFFSET 10;

Example 10: Safe mode off for mass updates:

SET SQL_SAFE_UPDATES=0; 
UPDATE table SET col=val;

DELETE Command

DELETE removes records (DML). Syntax:

DELETE FROM table WHERE condition;

Example 1:

DELETE FROM students WHERE age < 18;

Example 2: All:

DELETE FROM temp_table;

Example 3: With join:

DELETE s FROM students s JOIN inactive i ON s.id=i.id;

Example 4: Limit:

DELETE FROM logs ORDER BY date LIMIT 100;

Example 5: Cascade via FK: If ON DELETE CASCADE, child rows delete automatically.

TRUNCATE Command

TRUNCATE removes all data but keeps structure (DDL). Faster than DELETE as it doesn't log per row.

Syntax:

TRUNCATE TABLE table_name;

Example 1:

TRUNCATE TABLE archive;

(resets auto-increment).

Example 2: Vs DELETE: DELETE can be rolled back in transactions; TRUNCATE cannot in some engines.

Example 3: On FK tables: May fail if referenced.

Example 4: Empty large logs: TRUNCATE for speed.

Example 5: No WHERE: Always all rows.

COPY TABLE

Copy via CREATE...SELECT or INSERT...SELECT.

Example 1:

CREATE TABLE copy AS SELECT * FROM original;

Example 2: Structure only:

CREATE TABLE copy LIKE original;

Example 3: Insert:

INSERT INTO copy SELECT * FROM original WHERE condition;

Example 4: Across DBs:

INSERT INTO db2.table SELECT * FROM db1.table;

Example 5: Dump: Use mysqldump for full copy.

INSERT Records (Continued)

Advanced inserts.

Example 6: From file:

LOAD DATA INFILE 'file.csv' INTO TABLE table FIELDS TERMINATED BY ',';

Example 7: Replace:

REPLACE INTO table VALUES (vals);

(deletes if duplicate key).

Example 8: Delayed:

INSERT DELAYED INTO queue VALUES (data);

(for high-load).

Example 9: Partitioned: For large tables.

Example 10: JSON:

INSERT INTO json_table (data) VALUES ('{"key":"value"}');

Theory: DELETE/TRUNCATE for removal; COPY for duplication. TRUNCATE resets IDs.

Common Mistakes:

  • Using DELETE without WHERE, emptying tables.
  • TRUNCATE on transactional tables expecting rollback (not always).
  • Copying without constraints, leading to invalid data.
  • UPDATE/DELETE in loops instead of batch, slowing performance.
  • Ignoring FKs in DELETE, causing integrity errors.

(Word count: 652)

03/10/2025: Constraints (NOT NULL, UNIQUE, CHECK, DEFAULT, PRIMARY KEY)

Constraints Overview

Constraints enforce data integrity rules at the database level.

  • NOT NULL: Prevents null values.
  • UNIQUE: Ensures unique values (allows null unless combined).
  • CHECK: Validates condition (e.g., age > 18).
  • DEFAULT: Sets default value on insert.
  • PRIMARY KEY: UNIQUE + NOT NULL + indexed; identifies rows uniquely.

Applied during CREATE or ALTER.

NOT NULL

Example 1:

CREATE TABLE users (id INT NOT NULL);

Example 2: Insert fail:

INSERT INTO users (id) VALUES (NULL);

(error).

Example 3: Alter:

ALTER TABLE users MODIFY name VARCHAR(50) NOT NULL;

UNIQUE

Example 1:

CREATE TABLE emails (address VARCHAR(100) UNIQUE);

Example 2: Multi-column:

UNIQUE (col1, col2)

Example 3: Insert duplicate: Errors.

CHECK

Example 1:

CREATE TABLE adults (age INT CHECK (age >= 18));

Example 2: Complex:

CHECK (salary > 0 AND dept IN ('HR','IT'))

Example 3: MySQL 8+ enforces; older ignore.

DEFAULT

Example 1:

CREATE TABLE orders (status VARCHAR(20) DEFAULT 'pending');

Example 2:

INSERT INTO orders () VALUES ();

(uses default).

Example 3: Expression:

DEFAULT CURRENT_TIMESTAMP

PRIMARY KEY

Example 1:

CREATE TABLE books (isbn VARCHAR(13) PRIMARY KEY);

Example 2: Composite:

PRIMARY KEY (col1, col2)

Example 3: Auto:

id INT PRIMARY KEY AUTO_INCREMENT

Example 4: Add:

ALTER TABLE table ADD PRIMARY KEY (id);

Example 5: Drop:

ALTER TABLE table DROP PRIMARY KEY;

Theory: Constraints prevent invalid data, improving reliability. PK clusters data for speed.

Common Mistakes:

  • Adding UNIQUE to existing duplicates, failing.
  • CHECK on unsupported engines (InnoDB ok).
  • DEFAULT mismatch with datatype.
  • Multiple PKs per table (only one allowed).
  • Null in PK, violating rules.

(Word count: 512)

04/10/2025: Add PK and Drop PK from Existing Table, AUTO INCREMENT, Basics of FOREIGN KEY

Add and Drop PK

Add: ALTER TABLE table ADD PRIMARY KEY (col); (col must be UNIQUE/NOT NULL).

Drop: ALTER TABLE table DROP PRIMARY KEY;

Example 1: Add: Ensure no duplicates first.

Example 2: Drop then add new: For changing PK.

Example 3: Composite add: ADD PRIMARY KEY (id, code);

Example 4: On large tables: May lock.

Example 5: Verify: SHOW INDEX FROM table;

AUTO INCREMENT

Auto-generates sequential numbers for PK.

Syntax: id INT AUTO_INCREMENT PRIMARY KEY

Example 1:

INSERT INTO table (name) VALUES ('Test');
``` (id auto-sets).
 
Example 2: Start from: 
 
```sql
ALTER TABLE table AUTO_INCREMENT=100;

Example 3: Reset: After TRUNCATE.

Example 4: Non-PK: Possible but rare.

Example 5: Gaps: Deletes don't reuse numbers.

Basics of FOREIGN KEY

FK links tables, enforcing referential integrity.

Syntax:

FOREIGN KEY (col) REFERENCES parent(col)

Example 1:

CREATE TABLE orders (id INT, cust_id INT FOREIGN KEY REFERENCES customers(id));

Example 2: On delete:

ON DELETE CASCADE
``` (deletes child).
 
Example 3: On update: 
 
```sql
ON UPDATE RESTRICT

Example 4: Add to existing:

ALTER TABLE child ADD FOREIGN KEY (col) REFERENCES parent(col);

Example 5: Drop:

ALTER TABLE child DROP FOREIGN KEY fk_name;
``` (get name from SHOW CREATE TABLE).
 
Theory: AUTO_INCREMENT for surrogate keys; FK maintains relationships.
 
Common Mistakes:
- Adding PK to non-unique data.
- AUTO_INCREMENT on non-integer.
- FK to non-PK/UNIQUE, allowing orphans.
- Dropping PK without handling dependents.
- Ignoring engine (MyISAM doesn't enforce FK).
 
(Word count: 528)
 
---
 
### 06/10/2025: FOREIGN KEY, IN, BETWEEN…AND, LIKE Operator
 
---
 
#### FOREIGN KEY (Continued)
Advanced: Naming FK: `CONSTRAINT fk_name FOREIGN KEY...`
 
Example 6: Named: Easy to drop.
 
Example 7: Self-referential: Employee manager_id FK to id.
 
Example 8: Composite FK: Matches multiple cols.
 
Example 9: No action: Default RESTRICT.
 
Example 10: Set null: 
 
```sql
ON DELETE SET NULL

IN Operator

Checks if value in list. Syntax:

WHERE col IN (val1, val2)

Example 1:

SELECT * FROM products WHERE category IN ('Electronics', 'Books');

Example 2: Subquery:

WHERE id IN (SELECT id FROM archive);

Example 3: NOT IN: Exclude.

Example 4: With strings: Case-sensitive.

Example 5: Performance: Indexes help.

BETWEEN…AND

Ranges inclusive. WHERE col BETWEEN low AND high

Example 1:

SELECT * FROM sales WHERE date BETWEEN '2025-01-01' AND '2025-12-31';

Example 2: Numbers: price BETWEEN 10 AND 50

Example 3: NOT BETWEEN: Outside.

Example 4: Dates: Careful with formats.

Example 5: Strings: Alphabetical, e.g., names A to M.

LIKE Operator

Pattern matching with % (any chars), _ (single).

Example 1: WHERE name LIKE 'A%' (starts with A).

Example 2: LIKE '%end' (ends).

Example 3: LIKE '_a%' (second char a).

Example 4: NOT LIKE: Exclude.

Example 5: Escape: LIKE '10\%' ESCAPE '\' for literal %.

Theory: Operators filter data; FK links.

Common Mistakes:

  • FK cycles without care.
  • IN with large lists, slow without subquery opt.
  • BETWEEN with non-inclusive needs (use > <).
  • LIKE without indexes, full scans.
  • Case insensitivity: Use LOWER().

(Word count: 538)

07/10/2025: ORDER BY, WHERE, Aggregate Functions

ORDER BY

Sorts results. SELECT * FROM table ORDER BY col ASC/DESC

Example 1: ORDER BY name ASC

Example 2: Multiple: ORDER BY dept, salary DESC

Example 3: Expressions: ORDER BY LENGTH(name)

Example 4: Nulls: NULLS FIRST/LAST (MySQL 8+).

Example 5: With LIMIT: Top 10.

WHERE

Filters rows. WHERE condition

Example 1: WHERE age > 18 AND salary < 50000

Example 2: OR: status = 'active' OR 'pending'

Example 3: Subquery:

WHERE id > (SELECT AVG(id))

Example 4: Exists:

WHERE EXISTS (SELECT 1 FROM other WHERE...)

Example 5: IS NULL: WHERE col IS NULL

Aggregate Functions

Summarize: COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT.

Example 1:

SELECT COUNT(*) FROM users;

Example 2: SUM(salary) FROM employees WHERE dept='IT'

Example 3: AVG(price), MIN(price), MAX(price) FROM products

Example 4: DISTINCT: COUNT(DISTINCT name)

Example 5: GROUP_CONCAT(name SEPARATOR ', ')`

Theory: WHERE before aggregates; ORDER after.

Common Mistakes:

  • ORDER BY non-selected col (ok but confusing).
  • WHERE on aggregates (use HAVING).
  • COUNT(*) vs COUNT(col): Latter ignores nulls.
  • Sorting large sets without indexes.
  • Complex WHERE without parentheses, wrong logic.

(Word count: 502)

08/10/2025: GROUP BY Clause

GROUP BY

Groups rows for aggregates. GROUP BY col

Example 1:

SELECT dept, COUNT(*) FROM employees GROUP BY dept;

Example 2: Multiple: GROUP BY dept, role

Example 3: With WHERE: Filter before group.

Example 4: HAVING: GROUP BY dept HAVING COUNT(*) > 5

Example 5: ROLLUP: GROUP BY dept WITH ROLLUP (totals).

Example 6: CUBE: Multi-dimensional summaries.

Example 7: Expressions: GROUP BY YEAR(date)

Example 8: ORDER: GROUP BY dept ORDER BY COUNT(*) DESC

Example 9: Concat:

SELECT dept, GROUP_CONCAT(name) GROUP BY dept

Example 10: Errors: Non-grouped cols must be aggregated.

Theory: Aggregates per group; strict mode requires all non-agg cols in GROUP BY.

Common Mistakes:

  • Selecting non-aggregated cols without GROUP BY.
  • HAVING vs WHERE: HAVING after group.
  • Large groups without limits.
  • Ignoring ONLY_FULL_GROUP_BY mode.
  • Grouping on floats, unpredictable.

(Word count: 512)

09/10/2025: Operators in SQL

Operators Overview

Arithmetic: +, -, *, /, %

Comparison: =, !=, >, <, >=, <=, IS NULL, BETWEEN, IN, LIKE

Logical: AND, OR, NOT

Bitwise: &, |, ^, <<, >>

Others: CASE, COALESCE

Arithmetic

Example 1:

SELECT price * 1.1 AS new_price FROM products;

Example 2:

UPDATE stock SET quantity = quantity - 1;

Comparison

Example 1: WHERE salary > 50000

Example 2: name != 'Admin'

Logical

Example 1: WHERE age > 18 AND status = 'active'

Example 2: NOT (condition)

Bitwise

Example 1: SELECT 5 & 3 (1)

Example 2: Flags: permissions & 4 (check bit).

Others

Example 1: CASE:

SELECT CASE WHEN score > 90 THEN 'A' ELSE 'B' END

Example 2: COALESCE: COALESCE(null_col, 'default')

Example 3: IF: IF(condition, true, false)

Example 4: NULLIF: NULLIF(val, bad_val)

Example 5: DIV/MOD: Integer division.

Theory: Operators build expressions; precedence: () > * / > + - > comparisons > logical.

Common Mistakes:

  • = vs == (SQL uses =).
  • String comparisons case.
  • Division by zero errors.
  • Bitwise on non-ints.
  • NOT without parens, flipping wrong.

(Word count: 518)

10/10/2025: String Functions

String Functions

Manipulate text: CONCAT, SUBSTRING, LENGTH, UPPER/LOWER, TRIM, REPLACE, etc.

Example 1: CONCAT:

SELECT CONCAT(first, ' ', last) AS full_name;

Example 2: SUBSTRING: SUBSTRING(name, 1, 3) (first 3 chars).

Example 3: LENGTH: WHERE LENGTH(name) > 10

Example 4: UPPER: UPPER(name)

Example 5: TRIM: TRIM(' spaced ') removes spaces.

Example 6: REPLACE: REPLACE(text, 'old', 'new')

Example 7: LEFT/RIGHT: LEFT(name, 5)

Example 8: LOCATE: LOCATE('sub', string) position.

Example 9: LPAD/RPAD: Pad strings.

Example 10: REVERSE: REVERSE(string)

Example 11: CHAR_LENGTH vs LENGTH: Chars vs bytes.

Example 12: INSTR: Similar to LOCATE.

Example 13: FORMAT: For numbers as strings.

Example 14: CONVERT/CAST: To string.

Example 15: COLLATION: For case-insensitivity.

Theory: Efficient for data cleaning; chainable.

Common Mistakes:

  • CONCAT with null: Becomes null.
  • SUBSTRING out of bounds: Partial.
  • Encoding issues (UTF8).
  • Performance on large texts without indexes.
  • Forgetting QUOTE for literals.

(Word count: 522)

11/10/2025: Numeric Functions, LIMIT, OFFSET, System Info Functions

Numeric Functions

Math: ABS, CEIL, FLOOR, ROUND, RAND, POW, SQRT, etc.

Example 1: ABS: ABS(-5) =5

Example 2: ROUND: ROUND(3.14159, 2) =3.14

Example 3: RAND: SELECT RAND() random.

Example 4: POW: POW(2,3)=8

Example 5: MOD: 10 MOD 3=1

Example 6: TRUNCATE:

TRUNCATE(3.99,1)=3.9

Example 7: SIGN: Positive/negative.

Example 8: EXP/LOG: Exponential/log.

Example 9: PI(): Constant.

Example 10: DEGREES/RADIANS: Angle conv.

LIMIT and OFFSET

Paginate: LIMIT n top n; OFFSET m skip m.

Example 1:

SELECT * FROM users LIMIT 10;

Example 2: LIMIT 10 OFFSET 20 page 3.

Example 3: With ORDER: Sorted paging.

Example 4: UPDATE/DELETE LIMIT.

Example 5: No OFFSET: LIMIT n,0

System Info Functions

VERSION(), USER(), DATABASE(), FOUND_ROWS(), etc.

Example 1:

SELECT VERSION();

Example 2: CURRENT_USER()

Example 3: CONNECTION_ID()

Example 4: LAST_INSERT_ID()

Example 5: ROW_COUNT()

Theory: Numeric for calcs; LIMIT for efficiency; Sys for meta.

Common Mistakes:

  • RAND without seed: Different results.
  • LIMIT without ORDER: Unpredictable.
  • Offset large: Slow scans.
  • ROUND vs TRUNCATE confusion.
  • Sys functions in wrong context.

(Word count: 532)

13/10/2025: Date and Time, Advanced Functions

Date and Time

Types: DATE, DATETIME, TIMESTAMP.

Functions: NOW(), CURDATE(), CURTIME(), DATE_ADD, DATEDIFF, etc.

Example 1: NOW(): Current datetime.

Example 2: DATE_ADD(date, INTERVAL 1 DAY)

Example 3: DATEDIFF(d1,d2): Days between.

Example 4: EXTRACT(YEAR FROM date)

Example 5: STR_TO_DATE('str', '%Y-%m-%d')

Example 6: TIMESTAMPADD(unit, int, ts)

Example 7: WEEKDAY(date): 0-6

Example 8: LAST_DAY(date)

Example 9: UNIX_TIMESTAMP()

Example 10: FROM_UNIXTIME(ts)

Advanced Functions

Window: ROW_NUMBER(), RANK(), NTILE()

Example 1: ROW_NUMBER() OVER (ORDER BY salary)

Example 2: LAG/LEAD: Previous/next value.

Example 3: JSON functions: JSON_EXTRACT('json', '$.key')

Example 4: REGEXP: WHERE name REGEXP 'pattern'

Example 5: UUID(): Unique ID.

Example 6: BENCHMARK: Performance test.

Example 7: SLEEP(n): Delay.

Example 8: MD5/SHA: Hash.

Example 9: COMPRESS/UNCOMPRESS: Data.

Example 10: IFNULL: Like COALESCE.

Theory: Date for temporal; Advanced for analytics/security.

Common Mistakes:

  • Timezone issues (use UTC).
  • Interval wrong unit.
  • Window without PARTITION: Whole set.
  • REGEXP slow on large.
  • Hash collisions.

(Word count: 542)

14/10/2025: Set Operators, Intro to Joins

Set Operators

Combine queries: UNION (distinct), UNION ALL (duplicates), INTERSECT, EXCEPT.

Example 1:

SELECT col FROM t1 UNION SELECT col FROM t2;

Example 2: UNION ALL: Keeps dups.

Example 3: INTERSECT: Common rows (MySQL emulate with JOIN).

Example 4: EXCEPT: t1 minus t2 (emulate with LEFT JOIN NULL).

Example 5: With ORDER: At end.

Intro to Joins

Combine tables on conditions.

Types: INNER (matching), OUTER (LEFT/RIGHT/FULL: include non-match), CROSS (cartesian), SELF (same table).

Syntax:

SELECT * FROM t1 JOIN t2 ON t1.col=t2.col

Example 1: INNER: Common.

Example 2: LEFT: All left + matching right.

Example 3: USING: JOIN t2 USING (col) if same name.

Example 4: Natural: Auto on same cols.

Example 5: Multi-join: t1 JOIN t2 JOIN t3.

Theory: Sets for vertical combine; Joins horizontal.

Common Mistakes:

  • UNION column mismatch.
  • ALL vs distinct performance.
  • Joins without ON: CROSS.
  • Ambiguous cols without aliases.
  • Large joins without indexes.

(Word count: 508)

15/10/2025: Equi Join, Inner Join

Equi Join

Join on equality (=). Subset of INNER.

Example 1:

SELECT * FROM employees e, departments d WHERE e.dept_id = d.id;
``` (old syntax)
 
Example 2: With JOIN: `FROM e JOIN d ON e.dept_id = d.id`
 
Example 3: Multi-condition: `ON e.id = d.manager_id AND e.active=1`
 
Example 4: Aliases: Shorten.
 
Example 5: Performance: Indexes on join cols.
 
#### Inner Join
Returns matching rows only. Equi is common type.
 
Example 1: 
 
```sql
SELECT e.name, d.name FROM employees e INNER JOIN departments d ON e.dept_id=d.id;

Example 2: Theta join: Non-equality, e.g., >

Example 3: With WHERE: Additional filters.

Example 4: Chain: INNER JOIN projects p ON e.id=p.emp_id

Example 5: Vs OUTER: INNER drops non-matches.

Example 6: Group: COUNT per dept.

Example 7: Subquery in join.

Example 8: Natural inner: NATURAL JOIN (same cols).

Example 9: Using: INNER JOIN USING (dept_id)

Example 10: Explain:

EXPLAIN SELECT...
``` for plan.
 
Theory: Filters to intersections; optimizes queries.
 
Common Mistakes:
- Old comma syntax: Hard to read.
- No indexes: Slow.
- Ambiguous: Use qualifiers.
- Inner when outer needed: Missing data.
- Joining on wrong cols.
 
(Word count: 518)
 
---
 
### 16/10/2025: Left Join, Right Join, Full Outer Join
 
---
 
#### Left Join
All left rows + matching right; nulls for non-match.
 
Example 1: 
 
```sql
SELECT * FROM left_table LEFT JOIN right_table ON condition;

Example 2: Employees and optional bonuses.

Example 3: WHERE on right: Filters matches.

Example 4: Multiple lefts.

Example 5: Vs inner: Includes orphans.

Right Join

All right + matching left; symmetric to left.

Example 1: RIGHT JOIN same as left swap.

Example 2: Departments and employees (all depts even empty).

Example 3: Chain with left.

Example 4: Performance same as left.

Example 5: Use when right is primary.

Full Outer Join

All from both; nulls where no match. MySQL emulates with UNION of LEFT and RIGHT.

Example 1: LEFT JOIN UNION RIGHT JOIN WHERE left.col IS NULL

Example 2: Inventory vs sales: All items/sales.

Example 3: With conditions.

Example 4: Large data: Memory intensive.

Example 5: Alternatives in MySQL.

Theory: Outer for inclusive; full for complete merge.

Common Mistakes:

  • Forgetting null handling: IS NULL for non-matches.
  • Right when left suffices: Readability.
  • Full without emulate: Syntax error.
  • Joins order affecting plan.
  • Nulls in ON: Unexpected.

(Word count: 512)

17/10/2025: Cross Join, Natural Join, Self Join

Cross Join

Cartesian product: Every left with every right. No ON.

Example 1:

SELECT * FROM t1 CROSS JOIN t2;

Example 2: Comma: FROM t1, t2

Example 3: Generate combos: Colors x sizes.

Example 4: With WHERE: Becomes inner.

Example 5: Large: Explosive rows.

Natural Join

Auto joins on same-named cols. Inner type.

Example 1:

SELECT * FROM t1 NATURAL JOIN t2;

Example 2: Assumes equality.

Example 3: Dangerous: If cols change.

Example 4: With USING equivalent.

Example 5: Avoid in prod: Implicit.

Self Join

Join table to itself. Aliases required.

Example 1:

SELECT e1.name, e2.name AS manager FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id;

Example 2: Find duplicates: WHERE e1.id > e2.id AND e1.name=e2.name

Example 3: Hierarchical: Tree traversal.

Example 4: Comparisons: Employees same dept.

Example 5: With outer: Unmanaged.

Theory: Cross for all pairs; Natural implicit; Self for relations within.

Common Mistakes:

  • Cross without intent: Huge results.
  • Natural col mismatches.
  • Self without aliases: Ambiguous.
  • Recursive self without limits.
  • Performance: Indexes crucial.
LinkedIn