# Catatan Seekor SQL

SQL (Structured Query Language) adalah bahasa standar untuk mengakses dan memanipulasi database relasional.

## Fundamental

### Data Definition Language (DDL)

```sql
-- Create Database
CREATE DATABASE database_name;

-- Create Table
CREATE TABLE table_name (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Alter Table
ALTER TABLE table_name ADD COLUMN new_column VARCHAR(50);
ALTER TABLE table_name MODIFY COLUMN column_name INT;

-- Drop Table
DROP TABLE table_name;
```

### Data Manipulation Language (DML)

```sql
-- Insert Data
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
INSERT INTO table_name VALUES (value1, value2, value3);

-- Update Data
UPDATE table_name SET column1 = value1 WHERE condition;

-- Delete Data
DELETE FROM table_name WHERE condition;
DELETE FROM table_name; -- Delete all records
```

### Data Query Language (DQL)

```sql
-- Basic Select
SELECT column1, column2 FROM table_name;
SELECT * FROM table_name;

-- Where Clause
SELECT * FROM table_name WHERE column1 = 'value';
SELECT * FROM table_name WHERE column1 > 100;

-- Order By
SELECT * FROM table_name ORDER BY column1 ASC;
SELECT * FROM table_name ORDER BY column1 DESC;

-- Limit
SELECT * FROM table_name LIMIT 10;
SELECT * FROM table_name LIMIT 10 OFFSET 20;
```

## Common Queries

### Joins

```sql
-- Inner Join
SELECT t1.column1, t2.column2
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.table1_id;

-- Left Join
SELECT t1.column1, t2.column2
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.table1_id;

-- Right Join
SELECT t1.column1, t2.column2
FROM table1 t1
RIGHT JOIN table2 t2 ON t1.id = t2.table1_id;

-- Full Outer Join (MySQL doesn't support directly)
SELECT t1.column1, t2.column2
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.table1_id
UNION
SELECT t1.column1, t2.column2
FROM table1 t1
RIGHT JOIN table2 t2 ON t1.id = t2.table1_id
WHERE t1.id IS NULL;
```

### Aggregation Functions

```sql
-- Count
SELECT COUNT(*) FROM table_name;
SELECT COUNT(DISTINCT column1) FROM table_name;

-- Sum, Average, Min, Max
SELECT 
    SUM(column1) as total,
    AVG(column1) as average,
    MIN(column1) as minimum,
    MAX(column1) as maximum
FROM table_name;

-- Group By
SELECT column1, COUNT(*) as count
FROM table_name
GROUP BY column1;

-- Having
SELECT column1, COUNT(*) as count
FROM table_name
GROUP BY column1
HAVING COUNT(*) > 5;
```

### Subqueries

```sql
-- In Subquery
SELECT * FROM table1 
WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);

-- Exists Subquery
SELECT * FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t2.id = t1.id);

-- Scalar Subquery
SELECT column1, (SELECT MAX(column2) FROM table2) as max_value
FROM table1;
```

## Advanced Features

### Window Functions

```sql
-- Row Number
SELECT 
    column1,
    ROW_NUMBER() OVER (ORDER BY column1) as row_num
FROM table_name;

-- Rank
SELECT 
    column1,
    RANK() OVER (ORDER BY column1 DESC) as rank_num
FROM table_name;

-- Partition By
SELECT 
    column1,
    column2,
    AVG(column3) OVER (PARTITION BY column1) as avg_by_group
FROM table_name;
```

### Common Table Expressions (CTE)

```sql
WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT * FROM cte_name;
```

## Best Practices

### Performance Optimization

* Use indexes on frequently queried columns
* Avoid SELECT \* in production
* Use LIMIT for large result sets
* Optimize JOIN operations
* Use appropriate data types

### Security

* Use parameterized queries to prevent SQL injection
* Validate and sanitize input data
* Use least privilege principle for database users
* Regular backup and recovery testing

### Naming Conventions

* Use descriptive table and column names
* Use consistent naming patterns
* Avoid reserved words
* Use lowercase with underscores for readability

## References

### Stack Overflow

* [How can I add minutes and seconds to interval](https://stackoverflow.com/questions/21745125/add-minutes-to-current-timestamp-in-postgresql)

## Database Specific Features

### MySQL

```sql
-- Auto increment
CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY
);

-- Show tables
SHOW TABLES;

-- Describe table
DESCRIBE table_name;
```

### PostgreSQL

```sql
-- Serial (auto increment)
CREATE TABLE table_name (
    id SERIAL PRIMARY KEY
);

-- List tables
\dt

-- Describe table
\d table_name
```

### SQL Server

```sql
-- Identity column
CREATE TABLE table_name (
    id INT IDENTITY(1,1) PRIMARY KEY
);

-- List tables
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES;

-- Describe table
sp_help table_name;
```