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:
Understand the syntax of the WHERE clause.
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:
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.
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.
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
What is the primary purpose of the WHERE clause in SQL?
Answer:
iv) To filter records based on conditions.
If you want to filter records based on multiple conditions that all need to be true, which operator would you use?
Answer:
ii) AND
Interactive Task:
SQL snippets, identify the expected output or fill in the blanks:
SELECT name FROM employees WHERE position = 'Manager';
Expected output:
ii) Names of employees who are managers.
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.
Answer:
Name, position
SELECT name, position FROM employees WHERE salary > 50000 AND department = ‘IT’;
SELECT name, salary FROM employees WHERE department = 'Sales' OR ____;
Fill in the blank to also include employees from the ‘Marketing’ department.
SELECT name, salary FROM employees WHERE department = ‘Sales’ OR department = ‘Marketing’;