SQL Script

SQL files are plain text and can be opened in any text editor. They are executed against a database server — the file itself contains no data, only the instructions for creating or manipulating it.

How to open SQL files

• MySQL Workbench (Windows, macOS, Linux) — Free GUI for MySQL and MariaDB
• DBeaver (Windows, macOS, Linux) — Free, connects to any database engine
• pgAdmin (Windows, macOS, Linux) — PostgreSQL administration tool
• VS Code (with SQLTools extension) — Syntax highlighting and query execution
• Any text editor — View and edit the raw SQL text

Technical specifications

Common use cases

• Database migrations: Schema changes tracked as versioned SQL files (e.g., Flyway, Liquibase)
• Data dumps: mysqldump and pg_dump export databases as .sql files for backup and transfer
• Seed data: Initial data population for development and testing environments
• Reporting queries: Complex multi-table joins and aggregations for business intelligence
• ETL pipelines: Extract, transform, and load operations in data warehouses

SQL statement categories

-- DDL: Define structure
CREATE TABLE users (
  id   SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email TEXT UNIQUE
);

-- DML: Manipulate data
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
SELECT name, email FROM users WHERE id > 10 ORDER BY name;
UPDATE users SET name = 'Bob' WHERE id = 1;
DELETE FROM users WHERE email IS NULL;

-- DCL: Control access
GRANT SELECT ON users TO readonly_role;

SQL is divided into DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transaction Control Language — COMMIT, ROLLBACK).

SQL dialects and portability

While the ISO standard defines core SQL, each database engine extends it with proprietary syntax. Code written for PostgreSQL may not run on MySQL without changes. Key differences appear in: string functions, date handling, window functions, JSON support, and auto-increment syntax (SERIAL vs AUTO_INCREMENT vs IDENTITY). SQLite is the most portable but lacks features like FULL OUTER JOIN.

Security: SQL injection

SQL injection is the most common and dangerous web vulnerability. It occurs when user input is concatenated directly into SQL queries:

-- DANGEROUS — never do this
"SELECT * FROM users WHERE name = '" + userInput + "'"

If userInput is ' OR '1'='1, the query returns all users. Always use parameterized queries or prepared statements, which separate SQL code from data and prevent injection entirely. Most ORMs (Sequelize, SQLAlchemy, ActiveRecord, Prisma) handle this automatically.

Performance considerations

Slow queries are the most common cause of database bottlenecks. Add indexes on columns used in WHERE, JOIN, and ORDER BY clauses. Use EXPLAIN (PostgreSQL/MySQL) or EXPLAIN QUERY PLAN (SQLite) to see how the database executes a query and whether it uses indexes. Avoid SELECT * in production code — select only the columns you need.