6 SQL Functions Every Data Analyst Should Know

Mastering the Big Six

SQL is the backbone of data analysis and the language of databases.

If you're serious about a career in data analytics, knowing SQL is a must at some point. While SQL can seem intimidating at first, there are a handful of essential queries that form the foundation of most data analysis tasks.

These are known as the Big 6: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. Understanding how these work will cover a large array of basic queries but will also help create a strong foundation in your knowledge of SQL.

Let's break down each of these essential SQL queries with some examples.

---

1. SELECT: The Starting Point

SELECT is the most fundamental SQL query. It's how you choose what data you want to see from your database.

Example:

SELECT 
   first_name, 
   last_name, 
   salary
FROM employees;

In this example, you're asking the database to show you the first_name, last_name, and salary columns from the employees table. The SELECT statement allows you to specify exactly which columns you want to retrieve.

2. FROM: Defining the Source

The FROM clause tells SQL where, or which table, to find the data you're selecting. It's paired with SELECT and is essential for every query.

Example:

SELECT 
   first_name, 
   last_name, 
   salary
FROM employees;

Here, FROM employees specifies that the data should come from the employees table. Without FROM, SQL wouldn't know where to look for your data.

3. WHERE: Filtering Your Data

Once you’ve selected your data, you often need to filter it down to something more specific. That’s where WHERE comes in.

Example:

SELECT 
   first_name, 
   last_name, 
   salary 
FROM employees 
WHERE salary > 50000;

This query will return only those employees who earn more than $50,000. The WHERE clause is crucial for narrowing down large datasets to the information you really need.

4. GROUP BY: Aggregating Data

GROUP BY is used when you need to group data together, often paired with aggregate functions like COUNT(), SUM(), AVG(), etc.

Example:

SELECT 
   department, 
   AVG(salary) 
FROM employees 
GROUP BY department;

In this case, you're asking the database to calculate the average salary for each department. GROUP BY department tells SQL to aggregate the salary data by department before calculating the average.

5. HAVING: Filtering Groups

The HAVING clause works similarly to WHERE, but it's used to filter groups created by GROUP BY. You use HAVING when you want to filter results after aggregation.

Example:

SELECT 
   department, 
   AVG(salary) 
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 60000;

This query will show only those departments where the average salary is greater than $60,000. Without HAVING, you wouldn’t be able to apply this filter after the grouping has been done.

6. ORDER BY: Sorting Your Results

Finally, ORDER BY allows you to sort the results of your query in ascending or descending order.

Example:

SELECT 
   first_name, 
   last_name, 
   salary 
FROM employees 
WHERE salary > 50000 
ORDER BY salary DESC;

This query will list all employees earning more than $50,000, ordered from highest to lowest salary. ORDER BY salary DESC ensures the results are sorted in descending order of salary.

Putting It All Together

Let’s take a look at a more complex example that combines all the Big 6 queries:

Example:

SELECT 
   department, 
   COUNT(employee_id) AS num_employees, 
   AVG(salary) AS avg_salary 
FROM employees 
WHERE salary > 50000 
GROUP BY department 
HAVING AVG(salary) > 60000 
ORDER BY avg_salary DESC;

Here’s what’s happening in this query:

• SELECT is pulling the department name, the number of employees in that department (COUNT(employee_id)), and the average salary (AVG(salary)).

• FROM specifies that we’re working with the employees table.

• WHERE filters out employees who earn less than $50,000.

• GROUP BY groups the data by department, so we get results per department.

• HAVING filters out departments where the average salary is less than $60,000.

• ORDER BY sorts the results by average salary in descending order.

Mastering these six SQL queries will allow you to handle a large amount of basic querying. They are the building blocks for more advanced SQL techniques, and once you’re comfortable with them, you’ll find that you can tackle almost any data-related problem with confidence.

So, memorize these functions and practice them regularly.

In the next article, we’ll review some more advanced functions to take your SQL skills further.

That’s it for this week.

See you next time ✌️

---

There are 3 more ways I can help you

1. Recommended Courses: The top standalone courses I recommend for learning Excel, SQL, Tableau, Power BI, and Python. Check them out here.

2. My YouTube Channel: Free in-depth video content to take your data career journey even further. Subscribe here.

3. The Data Roadmap: My personalized 1:1 coaching service, designed to give you a curated game plan with tons of additional resources, built in Notion. Take a look.