Filter the data, return only the rows we need

SELECT * | <value1>, <value2>
FROM <table_name>
WHERE <field1> = 'abc';

It's like saying: "Hey, PostgreSQL; please can you find this information for me? It's in this table in this schema. When you find it, please only read back the records that match my criteria"

For example:

Return a list of all products and all attributes but only if popularity is more than 80

SELECT product_id, product_category, product_item, product_variety, popularity
FROM "sequel-mart-schema"."Products"
WHERE popularity > 80;

You should see a much smaller set of results, each with a popularity of more than 80

In this case, we are viewing the 12 most popular products out of our inventory of 129

Comparison Operators

We've used arithmetic operators (+, -, *, / and %) already to do maths on static numbers

Just like in programming languages, we can use comparison operators to filter results

These include:

• = - Equal to
• > - Greater than
• >= - Greater than or equal to
• < - Less than
• <= - Less than or equal to
• <> or != - Not equal to

Multiple clauses

We can combine filters together using the following keywords:

• AND - Both statements have to be true
• OR - One of the statements has to be true

For example:

SELECT product_id, product_category, product_item, product_variety, popularity
FROM "sequel-mart-schema"."Products"
WHERE product_category = 'cut_flowers' AND popularity > 70;

Returns:

• All cut flowers that also have a popularity of more than 70

Whereas:

SELECT product_id, product_category, product_item, product_variety, popularity
FROM "sequel-mart-schema"."Products"
WHERE product_category = 'cut_flowers' OR popularity > 85;

Returns:

• All cut flowers regardless of popularity
• Any other product with a popularity of more than 85
  • In this case, raspberries and strawberries qualify because while they are not cut flowers, their popularity is above 85