Data Analytics: SQL Fundamentals Filtering

What Is This?

Filtering is the process of selecting, sorting, or removing data, content, or information based on specific criteria or rules. This topic appears in exams to test your ability to apply these rules accurately and efficiently.

Why It Matters

Filtering is a crucial skill in various fields, including data analysis, software development, and quality control. It appears in exams such as the Certified Data Analyst (CDA) and the Certified Software Development Associate (CSDA) exams. Filtering typically carries 20-30% of the total marks and tests your understanding of the underlying logic and rules.

Core Concepts

To master filtering, you must understand the following core concepts:

• Criteria: The rules or conditions used to select or remove data.
• Attributes: The characteristics or properties of the data used to apply the criteria.
• Operators: The logical operators used to combine criteria and attributes (e.g., AND, OR, NOT).
• Data types: The different types of data (e.g., numerical, text, date) and how they are handled in filtering.

Prerequisites

Before tackling filtering, you should have a solid understanding of:

• Data structures: The organization and storage of data in various formats (e.g., tables, lists).
• Logical operators: The basic logical operators (e.g., AND, OR, NOT) and how they are used in filtering.
• Data types: The different types of data and how they are handled in filtering.

The Rule-Book (How It Works)

The primary rule of filtering is:

• Apply the criteria: Use the specified criteria to select or remove data based on the attributes and operators.

Sub-rules and exceptions include:

• Case sensitivity: Some filters may be case-sensitive, while others may be case-insensitive.
• Wildcards: Some filters may use wildcards (e.g., *) to match multiple characters.
• Date and time formats: Filters may use specific date and time formats (e.g., YYYY-MM-DD HH:MM:SS).

Exam / Job / Audit Weighting

Difficulty Level

Intermediate

Must-Know Rules, Formulas, Standards, or Principles

The following rules and formulas are essential for filtering:

• AND operator: The AND operator is used to combine multiple criteria (e.g., A AND B).
• OR operator: The OR operator is used to combine multiple criteria (e.g., A OR B).
• NOT operator: The NOT operator is used to negate a criterion (e.g., NOT A).

Worked Examples (Step-by-Step)

Example 1: Easy

Question: Filter the list of employees who are older than 30 and earn more than $50,000.
Criteria: Age > 30, Salary > $50,000 Attributes: Age, Salary Operators: AND Data types: Numerical

Step-by-step solution:

1. Apply the criteria: Use the specified criteria to select employees who meet both conditions.
2. Result: List of employees who are older than 30 and earn more than $50,000.

Example 2: Medium

Question: Filter the list of customers who have purchased products in the last 6 months and have a credit score above 700.
Criteria: Purchase date > 6 months ago, Credit score > 700 Attributes: Purchase date, Credit score Operators: AND Data types: Date, Numerical

Step-by-step solution:

1. Apply the criteria: Use the specified criteria to select customers who meet both conditions.
2. Result: List of customers who have purchased products in the last 6 months and have a credit score above 700.

Example 3: Hard

Question: Filter the list of employees who are either older than 40 or earn more than $75,000, but not both.
Criteria: Age > 40 OR Salary > $75,000, NOT (Age > 40 AND Salary > $75,000) Attributes: Age, Salary Operators: OR, NOT Data types: Numerical

Step-by-step solution:

1. Apply the criteria: Use the specified criteria to select employees who meet the conditions.
2. Result: List of employees who are either older than 40 or earn more than $75,000, but not both.

Common Exam Traps & Mistakes

Trap 1: Incorrect use of operators

Mistake: Using the AND operator instead of the OR operator.
Wrong answer: List of employees who are older than 30 and earn more than $50,000.
Correct approach: Use the OR operator to combine the criteria.

Trap 2: Incorrect handling of data types

Mistake: Using a numerical operator on a date attribute.
Wrong answer: List of employees who are older than 30 and earn more than $50,000.
Correct approach: Use a date operator on the date attribute.

Trap 3: Incorrect use of wildcards

Mistake: Using a wildcard to match a single character.
Wrong answer: List of employees who have a name starting with "A".
Correct approach: Use a wildcard to match multiple characters (e.g., *).

Trap 4: Incorrect handling of case sensitivity

Mistake: Using a case-sensitive filter on a case-insensitive attribute.
Wrong answer: List of employees who have a name starting with "A".
Correct approach: Use a case-insensitive filter on the attribute.

Trap 5: Incorrect use of NOT operator

Mistake: Using the NOT operator to negate a criterion that is already false.
Wrong answer: List of employees who are older than 30 and earn more than $50,000.
Correct approach: Use the NOT operator to negate a criterion that is already true.

Shortcut Strategies & Exam Hacks

Hack 1: Use the "AND" trick

Use the AND operator to combine multiple criteria, even if they are not mutually exclusive.

Hack 2: Use the "OR" trick

Use the OR operator to combine multiple criteria, even if they are mutually exclusive.

Hack 3: Use the "NOT" trick

Use the NOT operator to negate a criterion that is already true.

Question-Type Taxonomy

Practice Set (MCQs)

Question 1: Easy

Question: Which of the following criteria would select employees who are older than 30 and earn more than $50,000? A) Age > 30 AND Salary > $50,000 B) Age > 30 OR Salary > $50,000 C) Age > 30 AND Salary < $50,000 D) Age < 30 AND Salary > $50,000

Correct answer: A) Age > 30 AND Salary > $50,000 Explanation: The AND operator is used to combine multiple criteria.
Why the distractors are tempting: B) Age > 30 OR Salary > $50,000 is a plausible answer, but it would select employees who are older than 30 or earn more than $50,000, but not both.

Question 2: Medium

Question: Which of the following criteria would select customers who have purchased products in the last 6 months and have a credit score above 700? A) Purchase date > 6 months ago AND Credit score > 700 B) Purchase date < 6 months ago OR Credit score > 700 C) Purchase date > 6 months ago OR Credit score < 700 D) Purchase date < 6 months ago AND Credit score < 700

Correct answer: A) Purchase date > 6 months ago AND Credit score > 700 Explanation: The AND operator is used to combine multiple criteria.
Why the distractors are tempting: B) Purchase date < 6 months ago OR Credit score > 700 is a plausible answer, but it would select customers who have purchased products in the last 6 months or have a credit score above 700, but not both.

Question 3: Hard

Question: Which of the following criteria would select employees who are either older than 40 or earn more than $75,000, but not both? A) Age > 40 OR Salary > $75,000, NOT (Age > 40 AND Salary > $75,000) B) Age > 40 AND Salary > $75,000 C) Age < 40 OR Salary < $75,000 D) Age < 40 AND Salary < $75,000

Correct answer: A) Age > 40 OR Salary > $75,000, NOT (Age > 40 AND Salary > $75,000) Explanation: The OR operator is used to combine multiple criteria, and the NOT operator is used to negate the combined criterion.
Why the distractors are tempting: B) Age > 40 AND Salary > $75,000 is a plausible answer, but it would select employees who are older than 40 and earn more than $75,000, but not both.

30-Second Cheat Sheet

• AND operator: Used to combine multiple criteria.
• OR operator: Used to combine multiple criteria.
• NOT operator: Used to negate a criterion.
• Wildcards: Used to match multiple characters.
• Case sensitivity: Some filters may be case-sensitive, while others may be case-insensitive.

Learning Path

1. Beginner foundation: Understand the basic concepts of filtering, including criteria, attributes, operators, and data types.
2. Core rules: Learn the primary rules of filtering, including the use of AND, OR, and NOT operators.
3. Practice: Practice filtering using sample data and scenarios.
4. Timed drills: Practice filtering under timed conditions to improve speed and accuracy.
5. Mock tests: Take mock tests to assess your knowledge and identify areas for improvement.

Related Topics

• Data analysis: Filtering is an essential skill in data analysis, used to select and summarize data.
• Data visualization: Filtering is used to create visualizations that show only the relevant data.
• Data mining: Filtering is used to identify patterns and trends in large datasets.