# SQL Cheat Sheet

## Connect to a Database

```sql
-- MySQL
mysql -u username -p database_name

-- PostgreSQL
psql -U username -d database_name
```

---

# Create Database

```sql
CREATE DATABASE company;
```

```sql
DROP DATABASE company;
```

---

# Use Database

```sql
USE company; -- MySQL
```

---

# Create Table

```sql
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

PostgreSQL:

```sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

---

# Insert Data

```sql
INSERT INTO users (name, email, age)
VALUES
('John', 'john@example.com', 25),
('Alice', 'alice@example.com', 31);
```

---

# Select

```sql
SELECT * FROM users;
```

```sql
SELECT name, email
FROM users;
```

---

# WHERE

```sql
SELECT *
FROM users
WHERE age > 18;
```

```sql
SELECT *
FROM users
WHERE age BETWEEN 18 AND 30;
```

```sql
SELECT *
FROM users
WHERE name = 'John';
```

---

# LIKE

```sql
SELECT *
FROM users
WHERE name LIKE 'A%';
```

Starts with A.

```sql
WHERE name LIKE '%son';
```

Ends with son.

```sql
WHERE name LIKE '%abc%';
```

Contains abc.

---

# IN

```sql
SELECT *
FROM users
WHERE age IN (18, 21, 25);
```

---

# NULL

```sql
SELECT *
FROM users
WHERE email IS NULL;
```

```sql
SELECT *
FROM users
WHERE email IS NOT NULL;
```

---

# ORDER BY

```sql
SELECT *
FROM users
ORDER BY age ASC;
```

```sql
ORDER BY age DESC;
```

---

# LIMIT

```sql
SELECT *
FROM users
LIMIT 10;
```

---

# OFFSET

```sql
SELECT *
FROM users
LIMIT 10 OFFSET 20;
```

---

# UPDATE

```sql
UPDATE users
SET age = 30
WHERE id = 1;
```

---

# DELETE

```sql
DELETE FROM users
WHERE id = 1;
```

---

# TRUNCATE

```sql
TRUNCATE TABLE users;
```

---

# DROP TABLE

```sql
DROP TABLE users;
```

---

# COUNT

```sql
SELECT COUNT(*)
FROM users;
```

---

# DISTINCT

```sql
SELECT DISTINCT age
FROM users;
```

---

# MIN / MAX

```sql
SELECT MIN(age), MAX(age)
FROM users;
```

---

# AVG

```sql
SELECT AVG(age)
FROM users;
```

---

# SUM

```sql
SELECT SUM(price)
FROM orders;
```

---

# GROUP BY

```sql
SELECT country,
       COUNT(*)
FROM users
GROUP BY country;
```

---

# HAVING

```sql
SELECT country,
       COUNT(*)
FROM users
GROUP BY country
HAVING COUNT(*) > 10;
```

---

# INNER JOIN

```sql
SELECT users.name,
       orders.total
FROM users
INNER JOIN orders
ON users.id = orders.user_id;
```

---

# LEFT JOIN

```sql
SELECT users.name,
       orders.total
FROM users
LEFT JOIN orders
ON users.id = orders.user_id;
```

---

# RIGHT JOIN

```sql
SELECT users.name,
       orders.total
FROM users
RIGHT JOIN orders
ON users.id = orders.user_id;
```

---

# FULL OUTER JOIN (PostgreSQL)

```sql
SELECT *
FROM users
FULL OUTER JOIN orders
ON users.id = orders.user_id;
```

---

# Aliases

```sql
SELECT name AS username
FROM users;
```

---

# UNION

```sql
SELECT email
FROM customers

UNION

SELECT email
FROM employees;
```

---

# UNION ALL

```sql
SELECT email
FROM customers

UNION ALL

SELECT email
FROM employees;
```

---

# EXISTS

```sql
SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);
```

---

# Subquery

```sql
SELECT *
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
);
```

---

# CASE

```sql
SELECT name,
CASE
    WHEN age >= 18 THEN 'Adult'
    ELSE 'Minor'
END AS status
FROM users;
```

---

# CREATE INDEX

```sql
CREATE INDEX idx_email
ON users(email);
```

---

# DROP INDEX

```sql
DROP INDEX idx_email;
```

(PostgreSQL)

---

# Transactions

```sql
BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;
```

Rollback:

```sql
ROLLBACK;
```

---

# View

```sql
CREATE VIEW active_users AS
SELECT *
FROM users
WHERE active = TRUE;
```

---

# Foreign Key

```sql
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id)
);
```

---

# Upsert

PostgreSQL

```sql
INSERT INTO users(id, name)
VALUES (1, 'John')
ON CONFLICT(id)
DO UPDATE
SET name = EXCLUDED.name;
```

MySQL

```sql
INSERT INTO users(id, name)
VALUES (1, 'John')
ON DUPLICATE KEY UPDATE
name = VALUES(name);
```

---

# Common Date Functions

```sql
SELECT NOW();
```

```sql
SELECT CURRENT_DATE;
```

```sql
SELECT CURRENT_TIMESTAMP;
```

---

# String Functions

```sql
UPPER(name)
LOWER(name)
LENGTH(name)
TRIM(name)
CONCAT(first_name, ' ', last_name)
```

---

# Numeric Functions

```sql
ROUND(price, 2)
ABS(number)
CEIL(number)
FLOOR(number)
```

---

# Pagination

```sql
SELECT *
FROM products
ORDER BY id
LIMIT 20 OFFSET 40;
```

---

# Find Duplicates

```sql
SELECT email,
COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
```

---

# Delete Duplicates

```sql
DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id)
    FROM users
    GROUP BY email
);
```

---

# Top 10 Highest Salaries

```sql
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 10;
```

---

# Random Row

PostgreSQL

```sql
SELECT *
FROM users
ORDER BY RANDOM()
LIMIT 1;
```

MySQL

```sql
SELECT *
FROM users
ORDER BY RAND()
LIMIT 1;
```

---

# Show Tables

MySQL

```sql
SHOW TABLES;
```

PostgreSQL

```sql
\dt
```

---

# Describe Table

MySQL

```sql
DESCRIBE users;
```

PostgreSQL

```sql
\d users
```

---

# Backup

MySQL

```bash
mysqldump -u root -p database > backup.sql
```

PostgreSQL

```bash
pg_dump database > backup.sql
```

---

# Restore

MySQL

```bash
mysql database < backup.sql
```

PostgreSQL

```bash
psql database < backup.sql
```