SQL: Structured Query Language

The language of data.

Data EngineeringSQLDatabaseProgramming

Understand the standard file format for storing database schemas and data manipulation scripts across the industry.

getbetterat.work

DATA SYSTEMS GUIDE

FILE EXTENSION

What is an .sql file?

An .sql file is a simple text file. It holds a list of instructions for a database. Think of it like a recipe. The file has the steps, and the database is the chef that cooks the meal.

File: data_setup.sql

It contains plain words.

You can read it like a book.

It tells computers what to do.

Inside the File

An SQL file does not have images, sounds, or hidden code. It only has text. There are two main types of text inside:

Commands

The actual instructions. They tell the database to build a table or add information.

Comments

Notes written by humans, for humans. The computer ignores these words completely.

Where Do They Come From?

Workers do not always type these files by hand. Often, the computer writes the file for them. This is called a "Dump" or an "Export."

Live Database

Full of millions of records.

CLICK "EXPORT"

New .sql File

The computer writes every step needed to rebuild it.

The 3 Big Jobs

Why do workers use these files? They use them for three very important tasks.

1. Backups

A file can hold a copy of everything in your database. If a computer breaks, you use this file to put everything back to normal.

2. Setup

When you buy a new computer, it is empty. An SQL file can give it all the rules and empty boxes it needs to start working.

3. Migrations

Sometimes you need to move data from an old system to a new system. The file acts like a moving truck for your data.

The Words Inside

example.sql

CREATE

Makes a new space or box for data.

INSERT

Puts brand new data into the box.

UPDATE

Changes data that is already inside the box.

DROP

Deletes the box and everything inside it completely.

-- This is a human comment
CREATE TABLE users (
    id INT,
    name VARCHAR
);

-- Put data in
INSERT INTO users (id, name) 
VALUES (1, 'John');

-- Change the data
UPDATE users 
SET name = 'Jane' 
WHERE id = 1;

The Most Dangerous Word

Never ignore the word DROP.

If an SQL file contains the command DROP DATABASE, it will erase everything. It does not ask "Are you sure?". It acts instantly. Always read a file before you let your computer follow its instructions.

The Power of Notes

Because code can be hard to read, workers leave notes for each other inside the file. The computer ignores any line that starts with two dashes --.

A good SQL file tells you why a change is happening, not just what is happening.

-- Adding this box because
-- the boss wants to track
-- user birthdays now.
ALTER TABLE users ADD birthday DATE;

Tools to Open SQL Files

Because it is just text, you do not need expensive software. You can open it with very simple programs.

Notepad

VS Code

TextEdit

Database Tools

Pro Tip: Code editors like VS Code will color the words to make them easier to read.

Not All Files Are Equal Size

The Tiny File

Size: 2 Kilobytes

Just a few lines of instructions. You can open this in Notepad instantly. It is smaller than a single photograph.

The Monster Backup

Size: 50 Gigabytes

A full copy of a giant company. If you try to open this in Notepad, your computer will freeze and crash. You must use special database tools to read it.

LANGUAGE WARNING

The "Funny Letter" Problem

Computers read text using a standard called "Encoding". If you save your SQL file with the wrong encoding, special characters and emojis will break.

Saved Correctly (UTF-8)

INSERT INTO cities ('Café');

Saved Incorrectly

INSERT INTO cities ('CafÃ©');

How Do You "Run" It?

Just opening and reading the file does not change any data. To make the instructions happen, you must give the file to a Database Program.

1. The File

2. Database Engine reads it

3. Action Happens

The Night Shift

Workers do not always click "Run" themselves. They can tell the computer to automatically run an SQL file every night at 3:00 AM while everyone is asleep. This is how companies do daily backups without hiring someone to stay awake.

Scheduled Task

Run backup.sql Daily @ 03:00

Taking it to the Cloud

If you rent a database on the internet (like from Amazon or Google), you can just upload your .sql file through your web browser. Their big internet computers will read the text and set everything up for you in seconds.

The File Lifecycle

Write

A worker types out the text instructions in an editor.

Save

They save the text with a .sql name so the computer knows what it is.

Test

They run it on a safe, fake database first to make sure it does not break anything.

Deploy

They run the file on the real, live database.

Time Travel for Files

Because SQL files are just plain text, workers use tools like Git to save versions of them over time. If a new instruction breaks the database, they can instantly "rewind" to the file from yesterday.

v1.0.sql

v1.1.sql (Added names)

v1.2.sql (Current)

Different Flavors of SQL

Just like English has American and British dialects, databases have different "flavors" (like MySQL, PostgreSQL, or SQL Server). An SQL file written for one flavor might not work perfectly in another without making small changes.

MySQL

LIMIT 10

PostgreSQL

FETCH FIRST 10

SQL Server

TOP 10

.CSV Files

Think of a CSV like a flat spreadsheet. It only holds raw data.

Cannot create tables
Cannot delete spaces
Just lists of words and numbers

.SQL Files

Think of SQL as the builder. It holds actions, rules, AND data.

Can build new tables
Can delete everything
Gives strict orders

The Security Rule

Never put real passwords, credit card numbers, or secret keys inside an SQL file in plain text. Because anyone can read the text file, anyone could steal that secret data. Always use fake data or scrambled data when sharing these files.

Safe Work Habits

ALWAYS

Make a backup of your data before running a new SQL file.
Read the text file to see what it does before you press run.

NEVER

Do not run an SQL file if you do not know who made it. It can delete everything.
Never skip testing. A small spelling mistake can cause big problems.

When Things Break

Because an SQL file is read step-by-step, a single spelling mistake will make the database stop reading. It will show you an error.

ERROR 1064 (42000) at line 5:

You have an error in your SQL syntax near 'UPDAET users SET name = "John"'

How to fix it:

The computer is very helpful. It tells you exactly where the problem is. In the box above, the error is on line 5. Someone spelled UPDATE wrong. You just open the file, fix the spelling, save it, and run it again.

Easy to Share

The best thing about an .sql file is that it is small and easy to move. Because it is only plain text, you can email it to a friend, put it on a USB drive, or save it online. It makes moving huge databases as easy as sharing a text message.

SQL: Structured Query Language

Commands

Comments

The Tiny File

The Monster Backup

Write

Save

Test

Deploy

More in this series

Data Engineering

Spreadsheets

Presentations

Word Processing

Digital Publishing

Software Engineering

Markdown & Text