Standard comparison operators in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the = operator do in SQL?

The = operator checks if two values are equal. It returns true if they are the same, otherwise false.

beginner

What is the difference between <> and != in PostgreSQL?

Both <> and != mean "not equal to" in PostgreSQL. They work the same way and return true if values are different.

beginner

How does the < operator work in SQL?

The < operator checks if the value on the left is less than the value on the right. It returns true if yes, otherwise false.

beginner

What does the >= operator check?

The >= operator checks if the left value is greater than or equal to the right value. It returns true if either condition is met.

intermediate

Can you use comparison operators with text values in PostgreSQL?

Yes, you can use comparison operators like =, <, > with text. They compare strings based on alphabetical order.

Which operator checks if two values are NOT equal in PostgreSQL?

A <>

B =

C <=

D >=

The operator <> means NOT equal in PostgreSQL.

What does the <= operator mean?

A Less than or equal to

B Greater than

C Less than

D Equal to

The <= operator means less than or equal to.

If you want to check if a value is greater than another, which operator do you use?

A <

B >

C =

D <>

The > operator checks if the left value is greater than the right.

Which operator would return true if two values are exactly the same?

A !=

B <>

C =

D >=

The = operator checks for equality.

Can you use < to compare text values in PostgreSQL?

A Only boolean values

B No, only numbers

C Only dates

D Yes, it compares alphabetically

Text values can be compared alphabetically using < and other comparison operators.

Explain how the standard comparison operators work in PostgreSQL and give examples.

Think about how you compare numbers or words in real life.

Describe the difference between equality and inequality operators in SQL.

Equality means same, inequality means different.

Practice

1. Which of the following operators in PostgreSQL means "not equal to"?

easy

A. <=

B. =

C. <> or !=

D. >=

Solution

1. Step 1: Understand the meaning of operators

   The operator '=' means equal, so it cannot mean not equal.

2. Step 2: Identify the not equal operator

   In PostgreSQL, both '<>' and '!=' are used to mean not equal.

3. Final Answer:

   <> or != -> Option C

4. Quick Check:

   Not equal operator = <> or != [OK]

Hint: Remember: <> or != means not equal in SQL [OK]

Common Mistakes:

• Confusing '=' with not equal
• Using '<' or '>' as not equal
• Thinking '!=' is invalid in PostgreSQL

2. Which of the following is the correct syntax to check if column age is greater than or equal to 18 in PostgreSQL?

easy

A. age >> 18

B. age >= 18

C. age =< 18

D. age => 18

Solution

1. Step 1: Recall the correct operator for greater than or equal

   The correct operator is '>=' which means greater than or equal.

2. Step 2: Check each option's syntax

   age >= 18 uses '>=', which is correct. Options B and C use invalid operators '=>' and '=<' which are not valid in PostgreSQL. age >> 18 uses '>>' which is a bitwise operator, not comparison.

3. Final Answer:

   age >= 18 -> Option B

4. Quick Check:

   Greater or equal operator = >= [OK]

Hint: Use >= for greater or equal, not => or =< [OK]

Common Mistakes:

• Swapping operator symbols like => instead of >=
• Using bitwise operators instead of comparison
• Confusing <= and >= syntax

3. Given a table products with a column price, what will the following query return?

SELECT * FROM products WHERE price < 50;

medium

A. All products with price less than 50

B. All products with price greater than 50

C. All products with price equal to 50

D. Syntax error in query

Solution

1. Step 1: Understand the comparison operator used

   The operator '<' means less than, so the query filters rows where price is less than 50.

2. Step 2: Interpret the query result

   The query returns all rows from products where price is strictly less than 50.

3. Final Answer:

   All products with price less than 50 -> Option A

4. Quick Check:

   Operator '<' means less than [OK]

Hint: Remember: < means less than in SQL [OK]

Common Mistakes:

• Thinking '<' means less than or equal
• Confusing '<' with '>'
• Assuming syntax error due to operator

4. Identify the error in the following PostgreSQL query:

SELECT * FROM users WHERE age <> 30 AND age != 25;

medium

A. The comparison operators should be = instead of <> and !=

B. Using both <> and != together causes syntax error

C. The AND operator should be OR

D. No error; query is valid and works correctly

Solution

1. Step 1: Check the use of comparison operators

   Both '<>' and '!=' are valid not equal operators in PostgreSQL and can be used interchangeably.

2. Step 2: Analyze the logical operator usage

   The AND operator correctly combines two conditions requiring age to be not 30 and not 25 simultaneously.

3. Final Answer:

   No error; query is valid and works correctly -> Option D

4. Quick Check:

   Both <> and != are valid not equal operators [OK]

Hint: Both <> and != work for not equal in PostgreSQL [OK]

Common Mistakes:

• Assuming <> and != cannot be mixed
• Thinking AND should be OR without reason
• Confusing not equal with equal operator

5. You want to select all employees from a table employees whose salary is between 3000 and 7000 inclusive. Which of the following WHERE clauses correctly uses standard comparison operators to achieve this?

hard

A. salary >= 3000 AND salary <= 7000

B. salary > 3000 AND salary < 7000

C. salary = 3000 OR salary = 7000

D. salary <> 3000 AND salary <> 7000

Solution

1. Step 1: Understand the requirement for inclusive range

   Inclusive means salary can be equal to 3000 or 7000 or any value in between.

2. Step 2: Analyze each option's logic

   salary >= 3000 AND salary <= 7000 uses '>=' and '<=' which include the boundary values. salary > 3000 AND salary < 7000 excludes boundaries with '>' and '<'. salary = 3000 OR salary = 7000 only selects salaries exactly 3000 or 7000, missing values in between. salary <> 3000 AND salary <> 7000 excludes salaries equal to 3000 and 7000, opposite of requirement.

3. Final Answer:

   salary >= 3000 AND salary <= 7000 -> Option A

4. Quick Check:

   Use >= and <= for inclusive range [OK]

Hint: Use >= and <= for inclusive ranges in WHERE clause [OK]

Common Mistakes:

• Using > and < excludes boundary values
• Using OR instead of AND for range
• Using not equal operators instead of range