Course 9.2Lesson 2 of 3
WHERE keeps only the records that meet a condition. You write conditions with the comparison operators, put text in quotes, use TRUE and FALSE for a Boolean field, and work out exactly which records a script outputs.
Try the code and the simulations beside the lesson.
The librarian now wants the titles of the nature books only. SELECT chooses the fields. To choose the records, we add WHERE and a condition.
SELECT Title
FROM Books
WHERE Genre = 'Nature';The output:
Title
Green Hills
Deep Blue
Snow FoxThe database looks at each record in turn. If the condition is true for that record, the record is output. If it is false, the record is left out. Here three books pass.
Notice that Genre is in the condition but not in the output. The field in a WHERE condition does not have to be selected.
= equal to<> not equal to> greater than, and < less than>= greater than or equal to, and <= less than or equal toThese are the same operators as in pseudocode.
SELECT Title, Pages
FROM Books
WHERE Pages > 250;The output:
Title Pages
Night Train 320
Lost Key 256
Deep Blue 410
Iron Bridge 288Look at Lost Key with pages. It is greater than , so it passes. A book with exactly pages would not pass > 250, but it would pass >= 250.
WHERE Author = 'Silva'. This course uses single quotes, which every database accepts.WHERE Pages < 200.WHERE OnLoan = TRUE.SELECT BookID
FROM Books
WHERE OnLoan = TRUE;The output:
BookID
K1
K4
K5Not equal to works for text too. The script below outputs every book whose author is not Silva.
SELECT Title
FROM Books
WHERE Author <> 'Silva';The output:
Title
Night Train
Lost Key
Deep Blue
Iron BridgeWhen nothing matches. A condition that no record meets gives no output at all. That is not an error.
The program runs three scripts with WHERE on the Books table and prints the output of each, with the number of records.
WHERE Genre = Nature makes the database look for a field called Nature.Pages > 250, not Pages > '250'.> when the question says "at least". "At least " is >= 300.WHERE keeps the records for which the condition is true.=, <>, >, <, >= and <=.Try each one on your own first. If you are stuck, open a hint. Then check with Show the answer.
SELECT Title FROM Books WHERE Author = 'Adeyemi'; output?SELECT BookID FROM Books WHERE Pages < 200; output?Title of every book that is not on loan.WHERE Genre <> 'Mystery' let through?SELECT Title FROM Books WHERE Genre = History;Every lesson is open to read. Enrolling also lets you:
Log in to join the discussion.
Loading…
This lesson is free to read. Enroll free to keep your place and take the tests.