As my journey in becoming a competent data analytics, my SQL knowledge continues to deepen and as a result, I also publish few things pick up through the process.
couple of weeks back I published about SQL fundamentals, covering that is DDL, DML and Data Manipulation. Read more about SQL fundamentals from this link Click here to visit dev.to.

Building on SQL skills and data analysis, I have come to know you can work on different tables at the same time through the help of SQL joins and SQL Functions.

What is Join?

A JOIN in SQL is used to link or combine rows from two or more tables based on related column between them and it is usually a Primary Key and Foreign Key.
Think of it like, one table has students and another has scores, JOIN will help you see which student took which course.

Types of Joins

• LEFT JOIN (LEFT OUTER JOIN) This type of returns all records from Left table and matching records from the right table

Example:

SELECT s.name, c.course_name
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id;

All students appear, even if they are not assigned a course.

INNER JOIN
This type of JOIN returns only matching records from both tables.

Example:

SELECT s.name, c.course_name
FROM students s
INNER JOIN courses c
ON s.course_id = c.id;

RIGHT JOIN (RIGHT OUTER JOIN)
The JOIN returns all records from the right table and matching ones from the left.

Example

SELECT s.name, c.course_name
FROM students s
RIGHT JOIN courses c
ON s.course_id = c.id;

All courses appear, even if no student is enrolled.

FULL JOIN(FULL OUTER JOIN)
This type of JOIN returns all records when there is a match in either table.

Example:

SELECT s.name, c.course_name
FROM students s
FULL JOIN courses c
ON s.course_id = c.id;

• JOINs are about relationships between tables
• Without JOINs, databases would be much less powerful

SQL Window Functions

What are Window Functions?
Window functions are used to calculate across rows without collapsing them.

Example:

SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

The output from this query, every employee still appears, which also includes department average.

SQL functions Every Beginner Should Know

COUNT():

The function counts rows present in a given table.

Example:

SELECT COUNT(*) FROM students;

Counts total number of students

SUM():

This function is used to add numeric values.

Example:

SELECT SUM(salary) FROM employees;

This will get the total salary.

AVERAGE():

The function is used to get Average values like school exam result performance.

Example:

SELECT AVG(marks) FROM exams;

The output will give the average marks.

UPPER()/LOWER():

The function is used to get or change text case,
UPPER() is used to change text into upper case while
LOWER() is used to change text into lower case.

Example:

SELECT 
   UPPER(first_name),
   LOWER(last_name) 
FROM students;

The student's first name will be in upper case and second name will be in lower case.

NOW()/CURRENT_DATE():
This function is used to get current date or time. It is useful when filtering recent records.

Example:

SELECT CURRENT_DATE;

In SQL functions are many, the highlighted functions are most common and every beginner should know and understand how they work in order to ease the work as data analysts, data engineer or scientist.

SQL Subqueries

What is Subquery in SQL:
A Subquery is a query written inside another SQL Query, It executes first and its result is used by the outer query.

Example:

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

• The inner query calculates average salary and the outer query compares the employee's salary against average

Types of Subquery
We have different types of subqueries, they include;

Scalar Subquery: It is used to return single value.

Example:

SELECT *
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

Multiple-row Subquery: This subquery returns multiple rows as its name suggests.

Example:

SELECT name
FROM employees
WHERE department_id IN (
    SELECT id
    FROM departments
);

Correlated Subquery: This query references one or more columns from the outer(main) query, it depends on the outer query.

Example:

SELECT e1.name, e1.salary
FROM employees e1
WHERE salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e1.department = e2.department
);

The query compares each employee to the average salary in their department.

Common Table Expression (CTE)

What is CTE?
A CTE (Common Table expression) is a temporary named result set created using the With clause.
CTE makes query easier to organize and read.

Example: same query using a CTE

WITH avg_salary AS (
    SELECT AVG(salary) AS avg_sal
    FROM employees
)

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT avg_sal
    FROM avg_salary
);

Reasons for using CTEs

• The need for better readability
• Simply complex queries
• The need for organized structure
• If you need to reuse intermediate results

CTE with Multiple Steps
One advantage of CTEs is chaining logic

Example: Monthly sales analysis

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', sale_date) AS month,
        SUM(amount) AS revenue
    FROM sales
    GROUP BY month
),

ranked_sales AS (
    SELECT *,
           RANK() OVER (ORDER BY revenue DESC) AS sales_rank
    FROM monthly_sales
)

SELECT *
FROM ranked_sales;

This will calculate monthly revenue, rank months by revenue and return final results.

SQL Learning Roadmap for a Beginner

SQL Fundamentals

• JOINs
• Group BY
• Window Functions
• SQL Functions
• Subqueries
• CTEs

These will be core concepts needed for:

• Preparing for SQL technical interview
• Writing quality SQL scripts
• Dashboard preparation for tools like Ms Power BI
• Data analysis

Conclusion

Since now as a data scientist, data manipulation and analysis is easier now that I have learnt and understood SQL Functions, Window Function and also Joins.

Also the use of CTEs and Subquery makes SQL query to be easier to read and organization.
Note: If a subquery starts to become difficult to read, convert it into a CTE