SQL Tutorial for Beginners with Real Examples

A list of anything works in a spreadsheet until two people have to change it at the same time, or until one list has to point at rows in another. That is where the list moves into a database, and SQL is the language you type to work with it. Four statements do most of the work: one reads rows, one adds them, one changes them, one removes them.

What a database and SQL are

A database is a program that stores data on disk and hands it back to whatever asks for it. A relational database keeps that data in tables, and a table is a grid. The columns say what each field holds, and every row is one record with a value in each column.

SQL is what you type at a relational database, both to define those tables and to work with the rows inside them. The language is standardized, and MySQL, PostgreSQL, Oracle and SQL Server all use it. Here is a table and four rows in it:

CREATE TABLE books (
    book_id        INTEGER PRIMARY KEY,
    title          VARCHAR(60),
    author         VARCHAR(40),
    published_year INTEGER,
    copies         INTEGER
);

INSERT INTO books VALUES (1, 'Blue Horizon',     'Ibarra', 1998, 3);
INSERT INTO books VALUES (2, 'The Quiet Ledger', 'Ibarra', 2004, 1);
INSERT INTO books VALUES (3, 'Salt and Iron',    'Novak',  2011, 5);
INSERT INTO books VALUES (4, 'Winter Grammar',   'Petrov', 2019, 2);

CREATE TABLE names the table and its five columns, and says what kind of value each column accepts: INTEGER for whole numbers, VARCHAR for text up to the length in brackets. PRIMARY KEY on book_id tells the database that no two rows may carry the same book_id, which is how you point at one row and mean exactly one row. Each INSERT adds a record.

Real-world applications of SQL and databases

A bank keeps customers, balances, loans and transactions in tables shaped like the one above. The balance its app shows you is the answer to a query it sent while the screen was loading, and the transaction list under the balance is a second query with a condition on the account number.

An online shop keeps products, customers, orders and stock. The line that says how many are left reads a stock column each time somebody opens the product page, and placing an order writes a row into the orders table and subtracts one from that same stock column. A social network stores users, posts, comments and likes, and the likes under a post are counted rather than stored on the post itself. A clinic stores patients, appointments, prescriptions and bills, with each appointment pointing at the patient it belongs to the way book_id points at one book.

None of those systems does anything the four rows above cannot show you. What grows is the count of tables, the rows inside each one, and the number of programs reading and writing them at the same moment.

What SQL can do

SELECT reads rows. You name the columns you want and the table they live in:

SELECT title, author FROM books;

Add a WHERE clause and the database tests every row against your condition, returning only the rows that pass. Text values go inside single quotes, numbers go without them:

SELECT title, published_year FROM books WHERE author = 'Ibarra';

UPDATE changes values in rows that already exist. The condition decides which rows it touches, and an UPDATE with no condition changes every row in the table, so the WHERE clause is the part to write first:

UPDATE books SET copies = 4 WHERE book_id = 3;

SELECT title, copies FROM books WHERE book_id = 3;

DELETE removes whole rows and takes the same kind of condition:

DELETE FROM books WHERE book_id = 4;

SELECT book_id, title FROM books;

SELECT, INSERT, UPDATE and DELETE are what people mean by CRUD, an acronym for create, read, update and delete. INSERT is the create and SELECT is the read, and the other two keep their own names. Each of the four has a tutorial of its own in this series, starting with SQL syntax and commands.

SQL also decides who is allowed to read and change each table. GRANT gives a permission to a database user, one statement per permission:

GRANT SELECT ON books TO librarian;
GRANT UPDATE ON books TO librarian;

The librarian user can now read the books table and change rows in it. Neither statement returns rows. What they change is the list of permissions the database checks every time that user sends a statement. REVOKE takes a permission back:

REVOKE UPDATE ON books FROM librarian;

Reading is all the librarian user can do now, which is how a reporting account gets the data without being able to damage it.

A query does not have to hand back stored values unchanged. A column in the SELECT list can be an expression, and AS gives the computed column a name:

SELECT title, 2026 - published_year AS years_in_print FROM books;

Nothing in the table changed. The subtraction happened while the answer was being assembled, and the stored published_year values are still 1998, 2004 and 2011.

A query can also be saved under a name and then read as though it were a table. CREATE VIEW stores the statement, and selecting from the view runs it:

CREATE VIEW low_stock AS SELECT title, copies FROM books WHERE copies < 4;

SELECT * FROM low_stock;

A view holds no rows of its own, which is why it is called a virtual table. low_stock holds the query, so the rows it hands back follow whatever is in books at the moment you select from it.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

The two types of database, relational and non-relational

Everything on this page is a relational database: data in rows and columns, and tables that point at each other through shared values such as book_id. MySQL, PostgreSQL, Oracle and SQL Server are relational databases, and the model they implement was set out by Edgar Codd.

Non-relational databases, usually grouped under the name NoSQL, hold data that does not sit comfortably in a fixed grid. There are four kinds: document databases such as MongoDB, key-value stores such as Redis, wide-column stores such as Cassandra, and graph databases such as Neo4j. Each one has its own query language rather than SQL, though the borrowing runs deep in places. Cassandra reads its data with a CQL statement built from SELECT, FROM and WHERE, so the shape above carries over; what the Cassandra documentation rules out is joining two tables or nesting one query inside another.

Reading a database you did not design is the fastest way to see how far the four statements go. DbSchema connects to MySQL, PostgreSQL, Oracle, SQL Server and the rest, reverse-engineers the tables into a diagram that draws the links between them, and opens an SQL Editor where Execute Query runs a statement against the live database and shows the rows it returned. Connecting, the diagram and the SQL Editor are all in the free Community Edition: download DbSchema at https://dbschema.com/download.html, point it at a database, and run your first SELECT against a table somebody else filled.