Beginner · Databases and SQL basics · topic 17 of 54 · 25 min
SELECT and WHERE: finding rows
Explanation
Choose the columns
SELECT is followed by the columns you want, separated by commas, then FROM and the table name. SELECT * means all columns, but in real apps it is better to name only the columns you need: less data travels and the code is clearer.
SELECT name, marks FROM students;Choose the rows with WHERE
WHERE is followed by a condition: a test that is true or false for each row. Only the rows where it is true come back.
| Operator | Meaning | Example |
|---|---|---|
| = and <> | equal, not equal | city = 'Pune', course <> 'BCom' |
| > < >= <= | bigger, smaller | marks >= 60 |
| AND | both must be true | city = 'Pune' AND marks > 70 |
| OR | at least one is true | city = 'Pune' OR city = 'Delhi' |
| IN (...) | equal to any in the list | course IN ('BCA', 'BSc') |
| BETWEEN a AND b | from a to b, both included | marks BETWEEN 60 AND 80 |
| LIKE | matches a text pattern | name LIKE 'A%' |
| IS NULL | the value is missing | phone IS NULL |
SELECT name, marks
FROM students
WHERE city = 'Pune' AND marks > 70;Patterns with LIKE
In LIKE, % means "any number of any characters" and _ means "exactly one character". 'A%' matches names that start with A, '%Kumar' matches names that end with Kumar, and '%an%' matches names that contain an anywhere.
SELECT name FROM students WHERE name LIKE '%Kumar';Missing values: NULL
NULL means "no value": unknown or not filled in. It is not zero and not an empty text. Because nothing is equal to an unknown value, you must test it with IS NULL or IS NOT NULL.
Watch the lesson
SELECT and WHERE: finding rows · 1/7
Today we learn to ask questions of a table: which columns, and which rows.
Prefer a video by a teacher? Find free videos on “sql select where clause tutorial for beginners” on YouTube ↗
In real life: Scholarship list at a college
A college gives a scholarship to BCA and BSc students from Maharashtra who scored between 60 and 80 and have a phone number on record, so the office can call them. One query does it: SELECT name, phone FROM students WHERE course IN ('BCA', 'BSc') AND marks BETWEEN 60 AND 80 AND phone IS NOT NULL. The clerk gets the call list in a second instead of going through the register page by page.
Practice (0/3 done)
The students table has id, name, city, course, marks and phone. Show the name and marks of every student from Pune.
Show the name of every student who studies BCA or BSc AND has marks from 60 to 80 (both included). Use IN and BETWEEN.
The office wants to call every student, but some have no phone number saved (the value is NULL). Show the name of each student whose phone is missing.
Quick quiz
Read more (free)
Pass the quiz (60% or more) or mark this topic as done when you are ready.
Your progress is saved on this device only.