SQL

Structured Query Language reference for database operations

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases.

This page is currently being developed. Check back soon for comprehensive SQL documentation.

Overview

SQL is used to:

• Query data from databases
• Insert, update, and delete records
• Create and modify database structures
• Control access to data
• Manage transactions

Coming Soon

This documentation will cover:

Basic Queries

SELECT statements, filtering with WHERE, sorting with ORDER BY

Data Manipulation

INSERT, UPDATE, DELETE operations and best practices

Joins & Relations

INNER JOIN, LEFT JOIN, RIGHT JOIN, and complex relationships

Advanced Topics

Subqueries, CTEs, window functions, and optimization

Quick Reference

Basic SELECT

Query all columns

SELECT * FROM users;

Query specific columns

SELECT name, email FROM users;

Filter results

SELECT * FROM users WHERE age > 18;

Sort results

SELECT * FROM users ORDER BY name ASC;

Data Manipulation

Insert data

INSERT INTO users (name, email, age) 
VALUES ('John Doe', '[e-mail]', 25);

Update data

UPDATE users 
SET email = '[e-mail]' 
WHERE id = 1;

Delete data

DELETE FROM users WHERE id = 1;

Table Operations

Create table

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  age INTEGER,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Alter table

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

Drop table

DROP TABLE IF EXISTS users;

Common Patterns

Pagination

Paginate results

SELECT * FROM users 
ORDER BY id 
LIMIT 10 OFFSET 20;

This retrieves 10 users starting from the 21st record (useful for pagination).

Count Records

Count all records

SELECT COUNT(*) FROM users;

Count with condition

SELECT COUNT(*) FROM users WHERE age > 18;

Group by and count

SELECT country, COUNT(*) as user_count 
FROM users 
GROUP BY country;

Search Patterns

Exact match

SELECT * FROM users WHERE name = 'John Doe';

Pattern matching

SELECT * FROM users WHERE name LIKE 'John%';

Case-insensitive search (PostgreSQL)

SELECT * FROM users WHERE name ILIKE '%john%';

Database-Specific Dialects

SQL syntax can vary slightly between database systems (PostgreSQL, MySQL, SQLite, SQL Server). Always check your specific database documentation for exact syntax.

PostgreSQL

PostgreSQL is a powerful, open-source object-relational database system.

PostgreSQL-specific features

-- Serial auto-increment
CREATE TABLE users (
  id SERIAL PRIMARY KEY
);

-- JSON support
SELECT data->>'name' FROM users;

-- Array types
SELECT * FROM posts WHERE 'sql' = ANY(tags);

MySQL

MySQL is one of the most popular open-source databases.

MySQL-specific features

-- Auto-increment
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY
);

-- Limit syntax
SELECT * FROM users LIMIT 10;

SQLite

SQLite is a lightweight, file-based database.

SQLite-specific features

-- Auto-increment
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT
);

-- Simpler syntax, fewer data types

Best Practices

Use Indexes

Create indexes on frequently queried columns to improve performance.

Avoid SELECT *

Select only the columns you need to reduce data transfer and improve performance.

Use Transactions

Wrap related operations in transactions to ensure data consistency.

Parameterize Queries

Always use parameterized queries to prevent SQL injection attacks.

Security

Never concatenate user input directly into SQL queries. This opens your application to SQL injection attacks.

Bad - SQL Injection Vulnerable

// DON'T DO THIS!
const query = `SELECT * FROM users WHERE email = '${userInput}'`;

Resources

• PostgreSQL Documentation
• MySQL Documentation
• SQLite Documentation
• SQL Tutorial - W3Schools

This page will be expanded with more comprehensive examples, advanced queries, performance optimization tips, and database-specific features. Stay tuned for updates!