SQL: Structured Query Language
The language of data.
Understand the standard file format for storing database schemas and data manipulation scripts across the industry.
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.
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
Makes a new space or box for data.
Puts brand new data into the box.
Changes data that is already inside the box.
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.
-- 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.
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.
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.
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
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
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.
More in this series
Master more skills with other tutorials from the Data Engineering series.
Data Engineering
- DB: Generic Database File: The universal data container.
- SQLite: Portable Database: A database in a single file.
- SQL: Structured Query Language: The language of data.
- CSV: Comma-Separated Values: The universal data exchange format.
Spreadsheets
- NUMBERS: Apple Numbers Spreadsheet: Visual data storytelling.
- XLSM: Macro-Enabled Spreadsheet: Excel with automation power.
- ODS: OpenDocument Spreadsheet: The open spreadsheet standard.
- XLS: Legacy Excel Spreadsheet: The foundation of digital spreadsheets.
- XLSX: Modern Excel Spreadsheet: The data powerhouse.
Presentations
- ODP: OpenDocument Presentation: The open standard for slides.
- PPT: Legacy Presentation: The classic slide deck format.
- PPTX: Modern Presentation: The gold standard for slide decks.
Word Processing
- WPS: WPS Office Document: The lightweight contender.
- PAGES: Apple Pages Document: The designer's word processor.
- ODT: OpenDocument Text: The open text standard.
- DOC: Legacy Word Document: The classic document format.
- DOCX: Modern Word Document: The standard for office documents.
- RTF: Rich Text Format: The universal translator.
Digital Publishing
- TEX: LaTeX Document: The mathematician's choice.
- PDF: Portable Document Format: The digital paper standard.
Software Engineering
- TSX: TypeScript XML: The modern web component.
Markdown & Text
- MD: Markdown: The programmer's writer.
- TXT: Plain Text: The simplest file in the world.