# SQL
SQL (Structured Query Language) is a domain-specific language for managing and querying relational [[Database|databases]]. Developed in the 1970s at IBM, it has become the standard language for interacting with relational database systems.
SQL is declarative—you describe what data you want, not how to get it. The database engine determines the optimal execution plan.
## Core Operations (CRUD)
### SELECT (Read)
```sql
-- Basic query
SELECT name, email FROM users WHERE active = true;
-- With sorting and limiting
SELECT * FROM products ORDER BY price DESC LIMIT 10;
-- Aggregation
SELECT category, COUNT(*), AVG(price)
FROM products
GROUP BY category
HAVING COUNT(*) > 5;
```
### INSERT (Create)
```sql
-- Single row
INSERT INTO users (name, email) VALUES ('Alice', '
[email protected]');
-- Multiple rows
INSERT INTO users (name, email) VALUES
('Bob', '
[email protected]'),
('Charlie', '
[email protected]');
```
### UPDATE
```sql
UPDATE users SET active = false WHERE last_login < '2024-01-01';
```
### DELETE
```sql
DELETE FROM users WHERE id = 123;
```
## Joins
Combine data from multiple tables:
```sql
-- INNER JOIN: Only matching rows
SELECT orders.id, users.name
FROM orders
INNER JOIN users ON orders.user_id = users.id;
-- LEFT JOIN: All left rows, matching right
SELECT users.name, COUNT(orders.id)
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id;
```
| Join Type | Description |
|-----------|-------------|
| INNER JOIN | Only matching rows from both tables |
| LEFT JOIN | All rows from left, matching from right |
| RIGHT JOIN | All rows from right, matching from left |
| FULL JOIN | All rows from both tables |
| CROSS JOIN | Cartesian product of both tables |
## Common Clauses
```sql
SELECT columns -- What to return
FROM table -- Source table
JOIN other_table ON ... -- Combine tables
WHERE condition -- Filter rows
GROUP BY column -- Aggregate groups
HAVING condition -- Filter groups
ORDER BY column -- Sort results
LIMIT n OFFSET m -- Pagination
```
## Data Definition (DDL)
### Create Table
```sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```
### Alter Table
```sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME COLUMN name TO full_name;
```
### Indexes
```sql
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_email ON users(email);
DROP INDEX idx_users_email;
```
## Useful Functions
### String Functions
```sql
CONCAT(first, ' ', last) -- Concatenate
UPPER(name), LOWER(name) -- Case conversion
TRIM(value) -- Remove whitespace
SUBSTRING(text, 1, 10) -- Extract substring
```
### Date Functions
```sql
NOW() -- Current timestamp
CURRENT_DATE -- Current date
DATE_TRUNC('month', date) -- Truncate to month
AGE(date) -- Time since date
```
### Aggregate Functions
```sql
COUNT(*), COUNT(DISTINCT x) -- Count rows
SUM(amount), AVG(amount) -- Sum, average
MIN(value), MAX(value) -- Min, max
```
## Advanced Features
### Subqueries
```sql
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);
```
### Common Table Expressions (CTEs)
```sql
WITH active_users AS (
SELECT * FROM users WHERE active = true
)
SELECT * FROM active_users WHERE created_at > '2024-01-01';
```
### Window Functions
```sql
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) as rank,
AVG(salary) OVER () as avg_salary
FROM employees;
```
## SQL Dialects
Different databases have variations:
- **[[PostgreSQL]]**: Most standards-compliant, rich features
- **[[SQLite]]**: Simplified, dynamic typing
- **MySQL**: Different syntax for limits, dates
- **SQL Server**: T-SQL with procedural extensions
- **Oracle**: PL/SQL with proprietary features
## References
- https://en.wikipedia.org/wiki/SQL
- PostgreSQL docs: https://www.postgresql.org/docs/current/sql.html
## Related
- [[Database]]
- [[Database Management Systems (DBMS)]]
- [[Relational Databases (RDBMS)]]
- [[SQLite]]
- [[PostgreSQL]]
- [[DuckDB]]
- [[Dolt]]
- [[ACID]]
- [[Edgar F. Codd]]