Filtering Data
How to filter rows based on condition on a given column
SELECT * FROM invoice WHERE billing_country = 'France';
or
SELECT * FROM invoice WHERE total >= 20;
or
SELECT * FROM invoice WHERE billing_country ILIKE '%a%a';
case insensitive match
or
SELECT * FROM invoice WHERE billing_country IN ('Germany', 'France', 'Chile');
search in a list
or
SELECT * FROM invoice WHERE customer_id BETWEEN 21 AND 35;
search in a range
SELECT * FROM invoice WHERE customer_id NOT BETWEEN 21 AND 1000;
How to combine multiple conditions
SELECT * FROM invoice WHERE (customer_id NOT BETWEEN 21 AND 1000) AND (billing_country ILIKE '%a%a');
How to filter using null values
SELECT * FROM invoice WHERE billing_state IS NOT NULL;
How to output derived columns
SELECT *, (milliseconds / 1000) AS seconds FROM track limit 5;
As you can see i have derived a new column called seconds which have beeen derived from milliseconds column
Round seconds to 2 decimal places
SELECT *, ROUND(milliseconds / 1000.0, 2) AS seconds FROM track LIMIT 5;
Add new columns based on logical statements
SELECT *, CASE WHEN unit_price > 0.85 THEN 'TRUE' ELSE 'FALSE' END AS more_than_two FROM track limit 5;