1. Introduction

In this tutorial, we'll dive into the powerful technique of using nested queries for advanced data retrieval, often referred to as subqueries. Subqueries are SQL queries that are nested inside other SQL queries and can return data that will be used in the main query.

What you will learn:
- Understanding of nested queries and their applications
- How to write subqueries in SELECT, FROM, and WHERE clauses
- Best practices for writing and optimizing nested queries

Prerequisites:
- Basic understanding of SQL
- Familiarity with SELECT, FROM, and WHERE clauses

2. Step-by-Step Guide

A subquery can be used in the SELECT, FROM, and WHERE clauses, and it's executed once before the main query. The result of the subquery is then passed on to the main query for its execution.

Using Subquery in SELECT clause:
The subquery in the SELECT clause is used when you want to perform operations on a single column and return the output in the main select query.

Using Subquery in FROM clause:
The subquery in the FROM clause is used when you want to perform operations on multiple columns and return the result in the main select query.

Using Subquery in WHERE clause:
The subquery in the WHERE clause is used when you want to filter the results of the main query based on the results of the subquery.

3. Code Examples

Example 1: Subquery in SELECT clause

SELECT employee_name, 
       (SELECT count(*) 
        FROM employee_table 
        WHERE department_id = 1) AS count_of_department_1
FROM employee_table;

In this example, the subquery counts the number of employees in department 1 and returns this value for each row of the main query.

Expected Result:
A table with the name of each employee and the count of employees in department 1.

Example 2: Subquery in FROM clause

SELECT employee_name, AVG(salary) 
FROM 
   (SELECT employee_name, salary 
    FROM employee_table 
    WHERE department_id = 1) AS subquery_table
GROUP BY employee_name;

In this example, the subquery returns the name and salary of employees in department 1. The main query then calculates the average salary of these employees.

Expected Result:
A table with the name of each employee in department 1 and their average salary.

Example 3: Subquery in WHERE clause

SELECT employee_name, salary 
FROM employee_table 
WHERE salary > 
   (SELECT AVG(salary) 
    FROM employee_table);

In this example, the subquery calculates the average salary of all employees. The main query then returns the names and salaries of employees who earn more than this average.

Expected Result:
A table with the name and salary of each employee who earns above the average salary.

4. Summary

In this tutorial, we have learned how to use subqueries in SELECT, FROM, and WHERE clauses to perform advanced data retrieval tasks.

Next steps:
- Practice writing subqueries with different datasets
- Learn about correlated subqueries and how they differ from the subqueries we've discussed in this tutorial

Additional resources:
- SQL Subqueries - W3Schools
- Subqueries in SQL - GeeksforGeeks

5. Practice Exercises

Exercise 1:
Write a query that returns the name and department of employees who earn more than the average salary in their department.

Solution:

SELECT employee_name, department_id 
FROM employee_table 
WHERE salary > 
   (SELECT AVG(salary) 
    FROM employee_table 
    GROUP BY department_id);

This query first calculates the average salary for each department. It then returns the name and department of employees who earn more than this average.

Exercise 2:
Write a query that returns the name of the department with the highest average salary.

Solution:

SELECT department_name 
FROM department_table 
WHERE department_id = 
   (SELECT department_id 
    FROM employee_table 
    GROUP BY department_id 
    ORDER BY AVG(salary) DESC 
    LIMIT 1);

This query first calculates the average salary for each department and returns the ID of the department with the highest average salary. It then returns the name of this department.

Tips for further practice:
- Try writing subqueries in the HAVING clause
- Write a subquery that returns a list of employees who earn more than their manager.