Course 9.3Lesson 2 of 3
Real questions often have two conditions: Saturday sales of evening shows, or members who are under 12 or over 65. AND needs both conditions to be true, OR needs at least one. You join conditions, find a range with AND, and count and add the records that pass.
Try the code and the simulations beside the lesson.
The manager now wants the evening sales on Saturday only. That is two conditions: the day is Saturday, and the show is the evening show. We join them with AND.
SELECT SaleID, Seats
FROM Tickets
WHERE Day = 'Saturday' AND Show = 'Evening';The output:
SaleID Seats
T2 2
T5 5With AND, a record passes only if both conditions are true for it.
AND usually gives fewer records than either condition alone.
With OR, a record passes if at least one condition is true, or both.
SELECT SaleID
FROM Tickets
WHERE Day = 'Saturday' OR Show = 'Evening';The output:
SaleID
T2
T3
T4
T5
T6T3 passes because it is on Saturday. T4 passes because it is an evening show. T2 and T5 pass on both counts, but each is output once. Only T1, a Friday matinee, fails both.
OR usually gives more records than either condition alone.
To find values between two limits, join two comparisons on the same field with AND.
SELECT SaleID
FROM Tickets
WHERE Seats >= 2 AND Seats <= 4;The output:
SaleID
T1
T2
T4Outside a range is the opposite: WHERE Seats < 2 OR Seats > 4. A value cannot be less than and more than at the same time, so that one needs OR.
The conditions work with COUNT and SUM in the same way.
SELECT SUM(Amount)
FROM Tickets
WHERE Day = 'Saturday' AND Show = 'Evening';The output:
SUM(Amount)
105Watch out. Each part of a condition needs its own field name. Write Day = 'Friday' OR Day = 'Sunday', not Day = 'Friday' OR 'Sunday'.
The program runs the same two conditions joined first with AND and then with OR, and prints the records and the count each time.
OR: no sale is on both days.AND.Day = 'Friday' OR 'Sunday' does not test the day twice.AND: both conditions must be true. Usually fewer records.OR: at least one condition must be true. Usually more records.Field >= low AND Field <= high.Try each one on your own first. If you are stuck, open a hint. Then check with Show the answer.
WHERE Day = 'Friday' AND Seats > 2 give?WHERE Day = 'Friday' OR Day = 'Sunday'?WHERE Day = 'Friday' AND Day = 'Sunday' output nothing?SELECT COUNT(*) FROM Tickets WHERE Show = 'Matinee' OR Seats = 5; output?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.