By the end of this lesson you will be able to
- show everything in a table with
SELECT * FROM - read the result a database gives back
- write several statements one after another, and add comments
- recognise and fix the four mistakes beginners make most often
Every box on this page holds real SQL that you can change. Press Run to run it on the practice database, and the result appears underneath as a table. Reset puts back the original SQL. Tables in this database, above the SQL, lists every table and its columns.
On a phone: the star *, the semicolon ; and the single quote ' are on the symbols keyboard (the ?123 key). If your keyboard types curly quotes ‘ ’ instead of straight ones, this page will offer to fix them for you. A wide result can be scrolled sideways.
On a computer: press Ctrl + Enter (⌘ + Enter on a Mac) to run. Inside a box the Tab key adds spaces; to move out of the box with the keyboard, press Esc and then Tab.
Show me everything
The simplest question you can ask a database is “show me everything in this table”. Here it is in SQL. Press Run.
That one line is a complete query. The database has answered with the whole books table: every row and every column. Underneath the table, the page tells you how many rows came back (22). Scroll sideways to see the columns on the right.
What each part means
Let us look at that line piece by piece.
SELECT“show me…”*every columnFROM“…taken from…”booksthe table's name;end of the statementSELECTstarts a query. It tells the database that you want some information shown to you.- The star
*means “every column”. (In the next lesson you will replace it with just the columns you want.) FROM booksnames the table the information comes from. The name must be spelt exactly as it is in the database.- The semicolon
;marks the end of the statement, like a full stop at the end of a sentence. A statement is one complete instruction to the database; a query is a statement that asks a question.
Words such as SELECT and FROM, which are part of the SQL language itself, are called keywords. SQL does not mind whether you write keywords in capitals or small letters: select * from books; works just as well. But almost everyone writes keywords in capitals and names such as books in small letters, so that you can see at a glance which is which. This course does the same, and it is a good habit to form from your first query.
Now change books to customers in the box above and run it again. You will see the bookshop's twelve customers instead.
Reading the result
A query always gives back a table, called the result. It has column names at the top and one row for each thing that matched your question. A few details are worth noticing now:
- Numbers are lined up on the right, and text on the left, so that numbers are easy to compare.
- Prices such as
350.0end in.0. That is SQLite's way of showing that the column holds decimal numbers (rupees and paise), even when there are no paise. Whole numbers, such as the stock count, have no.0. Lesson 1.4 explains the different kinds of values. - Some cells show
NULLin grey. Look at theemailcolumn ofcustomers, or thepublishedcolumn of the last two books.NULLmeans “no value here”: the database simply does not know that customer's email address. You will learn to work withNULLin Lesson 2.5.
Several statements in one box
A box can hold more than one statement. The semicolons separate them, and the database runs them in order, from top to bottom. Each query gives its own result.
SQL also ignores extra spaces and line breaks, so you can spread a statement over several lines. This makes no difference now, but longer queries in later lessons are much easier to read when each part starts on a new line:
Asking without a table
In SQLite you can also use SELECT on its own, without FROM, to work something out or to show a piece of text. This is handy for quick calculations and for trying things out:
The star * between two numbers means “multiply”. Notice two things. First, text in SQL goes inside single quotes '…', and the quotes are not shown in the result. Second, because this result did not come from a table, the database names each column after what you wrote. In the next lesson you will learn to give columns better names.
MySQL, PostgreSQL and SQL Server also allow SELECT without FROM, and so does Oracle from version 23. In older versions of Oracle you write SELECT 3 * 275 FROM dual;, using a special one-row table called dual.
Comments
Two dashes -- start a comment: a note for people reading the SQL. The database ignores everything from -- to the end of that line. Comments are useful for explaining what a query is for.
The practice databases
This course has three practice databases, and each box says which one it uses in the Tables in this database bar. Most lessons use the bookshop. Some use the school, which has a table of pupils called students, a table of subjects, and a table of marks. This box uses the school database. Run it, then open the bar above the SQL to see all three tables.
Four common mistakes and how to fix them
Everyone who writes SQL makes mistakes, and professionals see error messages every day. What matters is being able to read them. Each box below contains a mistake. Run it to see what the database says, then fix it.
When the database cannot run a statement, it gives back an error message instead of a result, such as no such table: book. Underneath every error, this page explains in plain English what it means and on which line the statement with the problem starts. When a box has several statements, the ones before the faulty statement still run, and the ones after it do not.
1. A misspelt keyword
The database does not know the word FORM, so it reports a syntax error: a mistake in how the statement is written. The message near "FORM": syntax error points at the word where it got confused. Change it to FROM. Typing FORM for FROM is so common that even experienced people do it.
2. A wrong table name
The statement is written correctly, but there is no table called book: it is called books. The message no such table: book says exactly that. Whenever you are unsure of a name, check the Tables in this database bar.
3. A missing quote
Without the closing quote, the database cannot find where the text ends, so it reports an unrecognized token: a piece of the statement it cannot make sense of. Add the missing ' after morning.
4. Curly quotes
Look closely: these quotes are curly (‘ ’) instead of straight (' '). Programs such as Word and Google Docs, chat apps and many phone keyboards change straight quotes into curly ones automatically, and SQL does not accept them. Here SQLite even thinks ‘Good is the name of a column. This is the most common reason that SQL copied from a document or a chat message will not run.
Exercises
Now it is your turn. Each exercise shows the expected result. Type your SQL in the box and press Check my answer: the page runs your SQL and compares its result with the expected one, row by row and value by value. If you get stuck, Show solution becomes available after your first check. Try on your own first, because that is how the learning happens.
Exercise 1 · Every customer
Write a query that shows every column and every row of the customers table.
Exercise 2 · The orders
Every order a customer places is a row in the orders table. Write a query that shows the whole of that table.
Exercise 3 · Let the database do the sum
A customer buys 4 copies of a book that costs 225 rupees. Write a SELECT without a table that works out the total cost. Do not work out the answer yourself: write the calculation 4 * 225 and let the database do it.
Exercise 4 · The school's subjects
This exercise uses the school database. Write a query that shows every column and row of its subjects table, which lists each subject and its teacher.
Exercise 5 · Find and fix the mistakes
This query should show every book, but it contains three mistakes. Fix them all. Run it after each fix: each error message points you to the next problem.
Quick check
Four questions to confirm the main ideas. Choose an answer to see the explanation.
1. What does the star in SELECT * FROM books; mean?
Straight after SELECT, the star means “every column”. The query also returns every row, but that is because nothing in it chooses particular rows; you will learn to do that in Module 2. Between two numbers, as in 3 * 275, the star means multiply.
2. Which of these works exactly the same as SELECT * FROM books;?
Keywords can be written in capitals or small letters. The second has the wrong table name, the third misspells FROM, and the fourth simply shows the text books, because text in quotes is shown as it is.
3. In a result, what does a grey NULL in the email column mean?
NULL is not a word, a zero or an error. It means that no value has been stored in that cell. Lesson 2.5 is all about working with it.
4. A box holds three queries, and the second has a spelling mistake. What happens?
The statements run in order, one at a time. The first one is finished before the database even looks at the second, so its result is shown; when the second fails, the database stops, and the third never runs.
Summary
SELECT * FROM table_name;shows every column and every row of a table.- Keywords such as
SELECTandFROMmay be written in any case; by custom they are written in capitals, and names in small letters. - A semicolon ends each statement. Several statements run in order, and each query gives its own result. Extra spaces and line breaks make no difference.
- Text goes inside straight single quotes
'…'.SELECTwithoutFROMcan calculate or show a value. --starts a comment, which the database ignores.- Read error messages carefully: they name the word, table or column that caused the problem.
Found a mistake on this page, or something unclear? Report a problem and mention “SQL Lesson 1.2”.