Standard comparison operators in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple equality comparison

What is the output of this SQL query?

SELECT 5 = 5 AS result;

PostgreSQL

SELECT 5 = 5 AS result;

A 5

B true

C Syntax error

D false

💡 Hint

The '=' operator checks if two values are equal and returns a boolean.

The '=' operator compares two values. Since 5 equals 5, the result is true.

❓ query_result

intermediate

Result of greater than comparison in WHERE clause

Given a table numbers with a column value, what rows will this query return?

SELECT value FROM numbers WHERE value > 10;

Assuming the table has values: 5, 10, 15, 20.

PostgreSQL

SELECT value FROM numbers WHERE value > 10;

A 10, 15, 20

B 5, 10

C 15, 20

D 5, 15, 20

💡 Hint

The '>' operator selects values strictly greater than 10.

Only values greater than 10 are 15 and 20, so those rows are returned.

📝 Syntax

advanced

Identify the syntax error in comparison

Which option contains a syntax error in using comparison operators in PostgreSQL?

A SELECT * FROM users WHERE age =! 30;

B SELECT * FROM users WHERE age != 30;

C SELECT * FROM users WHERE age > 30;

D SELECT * FROM users WHERE age <> 30;

💡 Hint

Check the order of symbols in the comparison operator.

The operator '=!' is invalid. The correct not equal operators are '<>' or '!='.

❓ query_result

advanced

Output of combined comparison with AND

What is the output of this query?

SELECT (5 > 3) AND (2 = 2) AS result;

PostgreSQL

SELECT (5 > 3) AND (2 = 2) AS result;

A true

B false

C Syntax error

D NULL

💡 Hint

Both comparisons must be true for AND to return true.

5 > 3 is true and 2 = 2 is true, so true AND true is true.

❓ query_result

expert

Understanding NULL in comparison operators

What is the result of this query?

SELECT NULL = NULL AS result;

PostgreSQL

SELECT NULL = NULL AS result;

A true

B false

C Syntax error

D NULL

💡 Hint

In SQL, NULL means unknown, so comparing NULL to NULL is not true or false.

Comparing NULL to NULL returns NULL because NULL represents an unknown value, so equality is unknown.

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