Comparison operators, IN, AND, OR and sub-queries

Overview

Six comparison operators used in an SQL WHERE clause, difference between < and <= in a WHERE clause and term sub-query.

What you need to know

Answer each one before you reveal it.

Questions from this subtopic

Each one with the model answer underneath.

1. 1

   List the six comparison operators used in an SQL WHERE clause.

   Model answer

   The six comparison operators are =, >, >=, <, <= and <>. The last of these tests for inequality.

2. 2

   A table Staff holds Salary. Write SQL to list all columns for staff earning more than 30000.

   Model answer

   SELECT * FROM Staff WHERE Salary > 30000. The operator > excludes a salary of exactly 30000.

3. 3

   What is the difference between < and <= in a WHERE clause?

   Model answer

   The operator < excludes the stated value. The operator <= returns rows equal to the stated value as well.

4. 4

   Staff holds Department. Write SQL to list staff in either Sales or Finance using a single operator.

   Model answer

   SELECT * FROM Staff WHERE Department IN ('Sales', 'Finance'). The IN operator tests membership of a list of values.

5. 5

   Explain why IN is often preferred to a chain of OR conditions.

   Model answer

   The IN operator states a list of acceptable values in one short clause. A long chain of OR tests is harder to read and harder to change.

6. 6

   Staff holds Department and Salary. Write SQL to find staff in Sales earning over 25000.

   Model answer

   SELECT * FROM Staff WHERE Department = 'Sales' AND Salary > 25000. Each condition must be true for a row to be returned.

Other ways to work this

The same content, four other ways.

What is inside Levo

Six ways to work the same material.