SELECT and WHERE: reading and filtering data

Learn to choose columns with SELECT, filter rows with WHERE and search for patterns with LIKE.

8 min · 3 questions

If you are starting a new session, first run the CREATE TABLE + INSERT script from lesson 1 again. The SELECT query reads data without ever modifying it. After the word SELECT, you list the columns to display: this is called a projection (choosing the columns). After FROM, you indicate the table to read.

SELECT title, price FROM books;

To keep only certain rows, add a WHERE clause followed by a condition. A condition compares a column to a value using an operator: = (equal), <> or != (not equal), < (less than), > (greater than), <= and >=. Reminder: text values are written between single quotes.

SELECT * FROM books WHERE genre = 'sf';

SELECT title, year FROM books WHERE year < 1950;

You can combine several conditions: AND requires both to be true, OR requires at least one, and NOT reverses a condition. When in doubt about precedence, add parentheses.

SELECT title, price FROM books
WHERE genre = 'sf' AND price < 8;

SELECT title FROM books
WHERE genre = 'conte' OR genre = 'aventure';

Finally, the LIKE operator compares text to a pattern: the % wildcard stands for any sequence of characters (even an empty one), and _ stands for exactly one character. For example, 'D%' means "starts with D" and '%an%' means "contains an".

SELECT title FROM books WHERE author LIKE 'A%';

SELECT title FROM books WHERE title LIKE 'd%';

SELECT author FROM books WHERE author LIKE 'V_rne';

Knowledge check

Make sure you remember the key points of this lesson.

1. Which query displays only the titles of the books?
   • SELECT * FROM books;
   • SELECT title FROM books;
   • SELECT books FROM title;
2. What does the condition WHERE price < 7 return?
   • The rows whose price is strictly less than 7
   • The rows whose price is exactly 7
   • The first 7 rows of the table
3. What does the pattern LIKE 'D%' match?
   • Texts that contain the letter D
   • Texts that end with D
   • Texts that start with D