CSDL - SQL Từ Cơ Bản Đến Nâng Cao

Database là gì: RDBMS, Tables, Rows, Columns

SQL vs NoSQL comparison

RDBMS phổ biến: MySQL, PostgreSQL, SQL Server, Oracle

Cài đặt MySQL/PostgreSQL

Kết nối database: Command line, GUI tools (MySQL Workbench, pgAdmin, DBeaver)

Database basics: CREATE DATABASE, DROP DATABASE, USE

Basic data types: INT, VARCHAR, TEXT, DATE, DATETIME, BOOLEAN

SELECT statement basics: SELECT * FROM table

Selecting specific columns: SELECT col1, col2

Column aliases: AS keyword

DISTINCT: Remove duplicates

ORDER BY: ASC, DESC, Multiple columns

LIMIT/OFFSET: Pagination

WHERE clause basics: =, !=, <, >, <=, >=

Logical operators: AND, OR, NOT

NULL handling: IS NULL, IS NOT NULL

WHERE với nhiều điều kiện

IN operator: WHERE column IN (value1, value2)

BETWEEN operator: Range filtering

LIKE operator: Pattern matching với %, _

Wildcard patterns

REGEXP/RLIKE: Regular expressions trong SQL

CASE WHEN: Conditional logic

Filtering best practices

COUNT(): Đếm rows

SUM(): Tổng giá trị

AVG(): Trung bình

MIN(), MAX(): Giá trị nhỏ nhất, lớn nhất

GROUP BY: Nhóm dữ liệu

HAVING clause: Filtering sau GROUP BY

GROUP BY với nhiều columns

Aggregate functions với DISTINCT

Relationship giữa tables: One-to-One, One-to-Many, Many-to-Many

INNER JOIN: Matching rows from both tables

LEFT JOIN (LEFT OUTER JOIN): All from left + matching right

RIGHT JOIN (RIGHT OUTER JOIN): All from right + matching left

FULL OUTER JOIN: All rows from both tables

JOIN với nhiều tables

Self JOIN: Join table với chính nó

CROSS JOIN: Cartesian product

Natural JOIN: JOIN based on same column names

JOIN conditions: ON vs USING

Multiple JOIN conditions

Set Operations

Subquery là gì

Subquery trong WHERE clause

Subquery với IN, NOT IN

Subquery với comparison operators

Subquery trả về single value

Subquery trả về multiple values

EXISTS và NOT EXISTS

Correlated subqueries

Subquery trong FROM clause (Derived tables)

Subquery trong SELECT clause

Nested subqueries

Subquery performance considerations

Replacing subqueries với JOINs

When to use subqueries vs JOINs

CONCAT(), CONCAT_WS(): Nối chuỗi

SUBSTRING()/SUBSTR(): Cắt chuỗi

LENGTH()/CHAR_LENGTH(): Độ dài chuỗi

UPPER(), LOWER(): Chuyển đổi case

TRIM(), LTRIM(), RTRIM(): Xóa khoảng trắng

REPLACE(): Thay thế chuỗi

LEFT(), RIGHT(): Lấy ký tự từ trái/phải

INSTR(), LOCATE(): Tìm vị trí

String comparison và sorting

Numeric Functions

Date/Time Functions

INSERT INTO: Thêm dữ liệu

UPDATE: Cập nhật dữ liệu

DELETE: Xóa dữ liệu

REPLACE: Insert or Update

INSERT ... ON DUPLICATE KEY UPDATE

CREATE TABLE: Tạo bảng

PRIMARY KEY: Khóa chính

FOREIGN KEY: Khóa ngoại, References

UNIQUE constraint: Duy nhất

CHECK constraint: Điều kiện validation

AUTO_INCREMENT: Tự động tăng

ALTER TABLE

DROP TABLE, TRUNCATE TABLE

CREATE TABLE AS SELECT

Temporary tables

CTE là gì: WITH clause

Simple CTE syntax

Multiple CTEs trong một query

CTE với complex queries

Recursive CTEs

Recursive CTE applications

CTE vs Subqueries vs Temporary Tables

Window Functions là gì

OVER() clause

PARTITION BY: Chia nhóm

ORDER BY trong window functions

ROW_NUMBER(): Đánh số thứ tự

RANK(): Xếp hạng với gaps

DENSE_RANK(): Xếp hạng không gaps

NTILE(n): Chia thành n groups

Ranking functions applications

Aggregate window functions

Running totals (Cumulative sum)

Moving averages

ROWS vs RANGE frame specification

Frame clause applications

Analytic functions

LAG/LEAD applications

Complex window function queries

Combining multiple window functions

PIVOT operations: Rows to Columns

UNPIVOT operations: Columns to Rows

Dynamic SQL: Building queries programmatically

JSON functions

Full-text search

Advanced CASE expressions

Complex conditional logic

Execution plans: EXPLAIN, EXPLAIN ANALYZE

Reading execution plans

Query optimization principles

Avoiding common pitfalls

Query refactoring techniques

Using covering indexes

Index hints

Database design process

Requirements gathering

Conceptual design

Entity-Relationship (ER) Diagrams

ER diagram tools

Converting ER to tables

Primary keys selection strategies

Foreign keys and referential integrity

Normalization là gì: Mục đích, Benefits

Functional dependencies

Normal Forms

Normalization process step-by-step

Denormalization: When and why

Trade-offs: Normalization vs Performance

Designing for scalability

Partitioning strategies

Surrogate keys vs Natural keys

Composite keys

Database design patterns

Handling many-to-many relationships

Audit tables and history tracking

Soft delete vs Hard delete

Data integrity types

Constraint enforcement

CASCADE options

CHECK constraints best practices

Triggers for complex integrity rules

Validation at database level vs application level

Database documentation

Index là gì: Purpose, How they work

Index types

Creating indexes

Index selection guidelines

Column order trong composite indexes

Index benefits và costs

When to use indexes

When NOT to use indexes

Covering indexes

Index maintenance

Analyzing index usage

Identifying missing indexes

Removing unused indexes

Clustered vs Non-clustered indexes

Index fragmentation

Performance monitoring tools

Slow query log

Query profiling

Common performance issues

Optimization techniques

Batch operations vs Single operations

LIMIT optimization

COUNT(*) optimization

Database caching

Connection pooling

Prepared statements và parameter binding

Analyzing query patterns

Database statistics và maintenance

VACUUM, ANALYZE (PostgreSQL)

OPTIMIZE TABLE (MySQL)

Table statistics update

Database monitoring và alerting

Performance testing strategies

Stored Procedures là gì: Benefits, Use cases

Creating stored procedures

Parameters: IN, OUT, INOUT

Variable declaration và assignment

Control flow

Cursors: Iterating through result sets

Error handling: DECLARE HANDLER

Calling stored procedures

Stored procedures best practices

When to use stored procedures vs application logic

User-Defined Functions (UDFs)

Triggers

Views

Transaction là gì

ACID properties

Transaction commands

Transaction best practices

Transaction scope management

Nested transactions

Long-running transactions

Concurrency issues

Isolation levels

Locking mechanisms

Deadlocks

Optimistic vs Pessimistic locking

Multi-Version Concurrency Control (MVCC)

Concurrency best practices