How to Write SQL Queries on Linux: The Complete Beginner’s Guide

Working with data is a huge part of modern computing, and Linux is arguably the best platform to learn and practice essential database skills. Among those skills, knowing how to write SQL queries is near the top of the list—whether you want to analyze logs, power a website, or simply store your own projects in an organized way.

But how do you actually get started writing SQL queries on Linux? What tools do you need? And what does a typical workflow look like? If you’re new to databases or just want to brush up on your skills, this guide is for you. I’ll walk you through every step, from installation to running real queries, with plenty of practical examples and troubleshooting tips along the way.

What is SQL, and Why Should You Care?

SQL stands for Structured Query Language, and it’s the standard way of talking to relational databases. If you’ve worked with spreadsheets before, think of SQL as a way to do everything you’d do in Excel—filtering, sorting, searching, and updating data—but with much more power, and on much larger datasets.

On Linux, SQL comes into play in all sorts of ways:

• Managing users and permissions for web servers
• Analyzing server logs or IoT data
• Powering web applications (think WordPress, Nextcloud, or your own projects)
• Working with open data sets for research or reporting

Learning SQL isn’t just for programmers. System administrators, scientists, and even hobbyists benefit from knowing how to pull information from a database using SQL.

Choosing a Database System on Linux

You have several great options for running SQL databases on a Linux system. Here are the three most popular choices, and why you might choose each:

SQLite

• Best for: Beginners, local development, small projects, and scripting.
• Why: No server required, just a single file. Very lightweight.
• How to use: Directly from the command line.

MySQL/MariaDB

• Best for: Websites, networked apps, and situations where multiple users connect.
• Why: Fast, reliable, widely supported, and familiar to many hosting providers.
• How to use: Client-server model; run the server in the background and connect from your app or terminal.

PostgreSQL

• Best for: Advanced analytics, complex queries, GIS (geospatial) work, or if you want to learn “SQL done right.”
• Why: Open source, extremely powerful, strict about standards, and excellent for large or complex data.
• How to use: Like MySQL, as a client-server setup.

For absolute beginners, I recommend starting with SQLite. It’s the simplest to install and use, and you can move up to the others later with very little relearning.

Step 1: Installing a Database on Linux

Installing SQLite

On most distributions, it’s as simple as:

sudo apt update
sudo apt install sqlite3

If you’re on Fedora, use sudo dnf install sqlite. For Arch Linux, use sudo pacman -S sqlite.

Installing MySQL/MariaDB

MariaDB is a drop-in replacement for MySQL. For most uses, you can treat them as the same.

sudo apt update
sudo apt install mariadb-server
sudo systemctl enable mariadb
sudo systemctl start mariadb
sudo mysql_secure_installation

Follow the prompts during mysql_secure_installation to set the root password and secure your installation.

Installing PostgreSQL

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl enable postgresql
sudo systemctl start postgresql

You can also use the package manager for your distribution to install either of these databases.

Step 2: Creating a Database and Getting to the Prompt

SQLite

Just type:

sqlite3 myfirstdb.db

This opens a new database file called myfirstdb.db (or opens it if it exists), and drops you into the SQLite prompt. If you ever want to “reset” your learning, just delete the .db file and start over.

MySQL/MariaDB

Start the client as root (for learning):

sudo mariadb

Or, with a user and password:

mysql -u youruser -p

Create a database:

CREATE DATABASE myfirstdb;
USE myfirstdb;

PostgreSQL

Switch to the postgres user and start the prompt:

sudo -i -u postgres
psql

Create and connect to a database:

CREATE DATABASE myfirstdb;
\c myfirstdb

Anatomy of a SQL Query

A SQL query is just a command you send to the database. The most common actions are:

• SELECT: Get data out of the database
• INSERT: Add new data
• UPDATE: Modify existing data
• DELETE: Remove data

Let’s see how it works with practical examples.

Step 3: Creating Your First Table

In all three databases, you’ll start by defining a table. Suppose you want to store a list of books:

CREATE TABLE books (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  year_published INTEGER
);

In MySQL, use AUTO_INCREMENT instead of AUTOINCREMENT, and you might use VARCHAR(255) instead of TEXT.
In PostgreSQL, you can use SERIAL for the id.

Step 4: Inserting Data

Add a few books:

INSERT INTO books (title, author, year_published) VALUES
('The Hobbit', 'J.R.R. Tolkien', 1937),
('1984', 'George Orwell', 1949),
('Dune', 'Frank Herbert', 1965);

Step 5: Querying Data (SELECT)

See everything in your table:

SELECT * FROM books;

Find only the titles and authors:

SELECT title, author FROM books;

Find books published after 1950:

SELECT title, year_published FROM books WHERE year_published > 1950;

Sort them by year:

SELECT * FROM books ORDER BY year_published ASC;

Step 6: Updating Data

Let’s say you need to fix a typo in an author’s name:

UPDATE books SET author = 'J. R. R. Tolkien' WHERE title = 'The Hobbit';

Step 7: Deleting Data

To remove a book from the table:

DELETE FROM books WHERE title = '1984';

Be careful with DELETE! If you run DELETE FROM books; with no WHERE clause, you’ll erase the entire table.

Step 8: Searching with LIKE

Want to find all books with “Dune” in the title?

SELECT * FROM books WHERE title LIKE '%Dune%';

The percent signs are wildcards.

Using SQL in Linux Shell Scripts

SQL on Linux gets even more powerful when you combine it with the shell. For example, you can automate queries with bash scripts:

Using SQLite:

#!/bin/bash
sqlite3 myfirstdb.db "SELECT title FROM books WHERE year_published > 1950;"

Using MySQL:

mysql -u user -p -e "SELECT * FROM books;" myfirstdb

Using PostgreSQL:

psql -U user -d myfirstdb -c "SELECT * FROM books;"

You can also redirect query output to a file, or chain commands together to build automated reports.

Real-World Example: Log Analysis with SQL

Suppose you have a large log file that you want to analyze for errors. You can use tools like awk or sed to process the log and then import the results into SQLite for further analysis:

1. Extract relevant data from your log:
   grep "ERROR" syslog.log > errors.log
2. Format the data so it matches a table structure (e.g., with comma-separated values).
3. Import into SQLite:
   sqlite3 logs.db .mode csv .import errors.log errors
4. Analyze with SQL:
   SELECT COUNT(*) FROM errors;
SELECT message, COUNT(*) FROM errors GROUP BY message ORDER BY COUNT(*) DESC;

SQL makes it easy to get statistics or trends from what would otherwise be a jumble of text.

Connecting to Remote Databases

If you ever need to connect to a database on a different server, you can do so with extra parameters:

MySQL:
mysql -h server.example.com -u user -p

PostgreSQL:
psql -h server.example.com -U user -d myfirstdb

For security, consider tunneling your connection over SSH, especially if you’re on a public network.

Best Practices for Writing and Running SQL Queries

• Always back up your database before running UPDATE or DELETE queries, especially when learning.
• Use transactions to group related changes, so you can roll them back if something goes wrong (BEGIN TRANSACTION; ... COMMIT;).
• Avoid running dangerous commands as root unless necessary.
• Test queries with LIMIT to avoid accidentally pulling huge amounts of data.
• Comment your queries for clarity and maintenance:
  -- This query lists all books published after 1950
• Check your results after each step, so mistakes don’t pile up.

Troubleshooting Common SQL Issues

• Permission errors: Check file permissions for SQLite or user/database privileges for MySQL/PostgreSQL.
• Syntax errors: Read the error message carefully. SQL error messages usually point you to where the problem is.
• Database locked: Close other programs using the database or kill stuck processes.
• Authentication errors: Double-check your username, password, and host.

If you get stuck, check the documentation (man sqlite3, man mysql, or man psql) or search online—chances are, someone else has hit the same snag.

Frequently Asked Questions (FAQ)

Do I need to know programming to use SQL?

No. SQL is designed to be readable and is much closer to plain English than most programming languages.

Which database should I use?

For most beginners: SQLite. For shared or web projects: MySQL/MariaDB or PostgreSQL.

Are there graphical tools for working with SQL?

Yes! DBeaver, pgAdmin (for PostgreSQL), and phpMyAdmin (for MySQL/MariaDB) are all popular and available on Linux.

Can I recover data if I delete something by mistake?

If you backed up your database file, yes. Otherwise, data recovery is difficult. Practice on copies, not production data.

How do I practice advanced SQL?

Try importing open datasets from the web and challenging yourself with real-world questions—like, “Which city had the most rainfall last year?” or “What’s the average sale per customer?”

Conclusion

Learning to write SQL queries on Linux isn’t just for database administrators or web developers—it’s a practical skill for anyone who wants to organize, search, or analyze information efficiently. By starting with a beginner-friendly tool like SQLite and gradually exploring more complex systems like MySQL or PostgreSQL, you’ll build a foundation that opens up all sorts of possibilities.

Remember: the best way to learn SQL is by doing. Create a database, add some data, and start asking questions. The more you experiment, the more you’ll understand. And don’t worry if you make mistakes—each one is a step forward.

If you run into trouble, there’s a big and helpful Linux and SQL community online. Don’t hesitate to ask for help!