SQL Workout 2 - Filtering with the WHERE Clause

Workouts SQL
1

The WHERE clause in SQL is used to filter records based on specific conditions. Understand its syntax and usage in this quick workout.

Scenario:

Imagine you have access to a database table named employees with the columns: id, name, position, salary, and department. Use the given SQL snippets to answer the questions below.

Objectives:

By the end of this workout, you should be able to:

  1. Understand the syntax of the WHERE clause.

  2. Filter records based on single and multiple conditions.

Interactive Task:

Given the following SQL snippets, identify the expected output or fill in the blanks:


SELECT name FROM employees WHERE position = 'Manager';

Expected output:

  • i) List of all employees.

  • ii) Names of employees who are managers.

  • iii) Names of employees who are not managers.

  • iv) Names of employees in the ‘HR’ department.


SELECT ____ FROM employees WHERE salary > 50000 AND department = 'IT';

Fill in the blank to get the names and positions of employees in the ‘IT’ department earning more than $50,000.


SELECT name, salary FROM employees WHERE department = 'Sales' OR ____;

Fill in the blank to also include employees from the ‘Marketing’ department.

Questions:

  1. What is the primary purpose of the WHERE clause in SQL?

    • i) To join tables.

    • ii) To order results.

    • iii) To group results.

    • iv) To filter records based on conditions.

  2. If you want to filter records based on multiple conditions that all need to be true, which operator would you use?

    • i) OR

    • ii) AND

    • iii) NOT

    • iv) XOR

Duration: 15 minutes

Difficulty: Beginner

Period
This workout will be released on Tuesday, September 5, 2023, and will end on Thursday, September 28, 2023. But you can always come back to any of the workouts and solve them.

3

Hello Team - Thanks for the workout. Please find responses below.

Interactive Task:

Given the following SQL snippets, identify the expected output or fill in the blanks:

SELECT name FROM employees WHERE position = 'Manager';

Expected output:

  • i) List of all employees.
  • ii) Names of employees who are managers.
  • iii) Names of employees who are not managers.
  • iv) Names of employees in the ‘HR’ department.

ii) Names of employees who are managers

2

SELECT name , position FROM employees WHERE salary > 50000 AND department = ‘IT’;

Fill in the blank to get the names and positions of employees in the ‘IT’ department earning more than $50,000.

3
SELECT name, salary FROM employees WHERE department = ‘Sales’ OR department = ‘Marketing’;

Can use “IN” also


Fill in the blank to also include employees from the ‘Marketing’ department.

**Questions**:

1. What is the primary purpose of the `WHERE` clause in SQL?
  * i) To join tables.
  * ii) To order results.
  * iii) To group results.
  * iv) To filter records based on conditions.

iv) To filter records based on conditions.

2. If you want to filter records based on multiple conditions that all need to be true, which operator would you use?
  * i) OR
  * ii) AND
  * iii) NOT
  * iv) XOR

ii) AND

Thanks
Ankit J
4

Thanks for the workout, please see my answers below:

  1. ii) Names of employees who are managers.

  2. name, position

  3. department = ‘Marketing’

  4. iv) To filter records based on conditions.

  5. ii) AND

5

Interactive Task:
Answer 1-Names of employees who are managers.

Answer 2- Select names,positions from employees where salary > 50000 and department=‘IT’.

Answer 3-select name,salary form where employees where department=‘Sales’ or ‘Marketing’

Answer 4- To filter with records base on conditions

Answer 5- And