Lesson 3 of 10
Combining conditions
- 12 min
- 5 checks
- 3 hints
- Go to task
Goal
Conditions with AND, OR, IN and BETWEEN
Explanation
You can combine several conditions in one query. AND needs both conditions to be true, OR is happy if either one is, and NOT flips a condition. Use IN ('books', 'stationery') to compare against a list of values, and BETWEEN 20 AND 50 to compare against a range that includes both ends.
Watch the order of evaluation: AND is evaluated before OR, the same way multiplication comes before addition. So the condition category = 'books' OR category = 'stationery' AND price < 50 means every book at any price, plus only the cheap stationery.
Parentheses make your meaning clear, as in (… OR …) AND price < 50, though in a case like this one IN saves you from needing OR at all.
Your task
Show the name and price of the products in the 'tech' and 'home' categories priced from 100 to 300, and leave out the ones that are sold out (stock = 0).
Checks
5 checks- Columns are name and price
- Only products from the tech and home categories
- Every price is between 100 and 300
- No sold-out products
- No product is missing, and a price of 100 is in range
Tables in this lesson
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT NOT NULL,
price INTEGER NOT NULL,
stock INTEGER NOT NULL
);
INSERT INTO products (name, category, price, stock) VALUES
('27-inch monitor', 'tech', 899, 4),
('Wireless headphones', 'tech', 249, 12),
('Keyboard', 'tech', 189, 0),
('Fast charger', 'tech', 79, 40),
('Office chair', 'home', 649, 3),
('Bookshelf', 'home', 249, 7),
('Desk lamp', 'home', 100, 18),
('Backpack', 'stationery', 180, 22),
('Notebook', 'stationery', 15, 200),
('Colored pencils', 'stationery', 25, 0),
('Dictionary', 'books', 95, 10),
('Novel', 'books', 45, 30);