Week 4 - SQL Chips and Chunks Newsletter

1. SQL Chip: Using IN for Multiple Conditions

Need to filter data based on multiple values? Use IN to check for multiple possible matches in a single column. Here’s an example where we retrieve employees who belong to either the "Sales" or "Marketing" departments:

SELECT
name,
department
FROM employees
WHERE department
IN ('Sales', 'Marketing');

2. SQL Chunk: Filtering with LIKE and Wildcards

Sometimes, you may need to filter data based on patterns or partial matches. The LIKE operator is used for this, and it works in conjunction with wildcards to create flexible search criteria. Here’s an example that finds employees whose names start with "J":

SELECT
name,
department
FROM employees
WHERE name LIKE 'J%';

• % is a wildcard that matches any sequence of characters.

• _ matches a single character.

You can use these wildcards to fine-tune your searches and find data that fits your needs, whether it’s partial text matches or specific patterns.

3. SQL Challenge: Find Employees with Specific Job Titles

This week’s challenge: Write a query to find employees who have the job titles "Manager" or "Director".

Hint: Use the IN operator to filter for multiple job titles.

SELECT
name,
job_title
FROM employees
WHERE job_title IN ('Manager', 'Director');

Try to extend this query by adding more job titles or incorporating other filtering techniques!

4. Common SQL Mistake: Misusing NULL in Comparisons

A common mistake in SQL is misusing NULL in comparisons. When you compare a column to NULL, the result is always unknown (NULL), even if the column has a value. Instead of using standard comparison operators (=, !=), use IS NULL or IS NOT NULL for checking NULL values.

Example:

SELECT name FROM employees WHERE department IS NULL; -- Correct SELECT name FROM employees WHERE department = NULL; -- Incorrect

Avoid these errors to ensure your queries work as expected!

5. SQL in Practice: Calculating Sales Performance

In sales analytics, it’s crucial to evaluate the performance of each sales representative. Here’s a query that calculates the total sales for each employee:

SELECT
employee_id,
SUM(sales_amount) AS total_sales
FROM sales
GROUP BY employee_id;

This query helps summarize each employee’s performance by calculating their total sales. What other aggregations could you use to enhance this query (e.g., average sales per employee)?

6. SQL Resource: Data Engineer Academy (Free & Paid Resources)

As part of your journey into SQL and data engineering, don’t forget about Data Engineer Academy. They offer free resources for beginners and intermediate learners, covering SQL, data pipelines, and data warehouses. For those looking to dive deeper, paid members get access to structured courses, hands-on projects, mentorship, one-on-one coaching, mock interviews, job preparation resources, and even guaranteed interviews to help transition into a data engineering career. If you’re serious about leveling up your skills and landing a job, this is a fantastic place to explore!