Chapter 1: Introduction to Advanced SQL

Are you ready to take your data analysis skills to the next level? This guide introduces advanced SQL concepts that will help you uncover deeper insights from your data. As queries grow in complexity, the importance of writing clean, modular, and maintainable code becomes paramount. Mastering these skills will set you apart from your peers. In this tutorial, we will delve into two essential SQL tools: subqueries and Common Table Expressions (CTEs).

Without further delay, let’s get started!

Using Subqueries in SELECT Statements

A significant portion of data analysis is conducted within SELECT statements, so we will begin by examining how subqueries can be applied here.

#### Subqueries as Column Values

The results of subqueries can serve as values in a SELECT statement. For instance, the following SQL code calculates the percentage of orders placed by each customer:

SELECT customer_name,

AVG(

quantity /

(

SELECT SUM(quantity)

FROM orders

) * 100

) AS "Percentage of order"

FROM orders

GROUP BY 1;

#### Subqueries in the FROM Clause

Subqueries can also function as temporary tables in the FROM clause. They are helpful for:

• Performing intricate data manipulations or aggregations before joining with other tables.
• Simplifying complex logic into smaller, manageable parts.
• Generating temporary result sets.

Here’s an example of a subquery in the FROM clause that calculates the difference in gender representation among customers:

SELECT SUM(male) - SUM(female) AS gender_difference

FROM (

SELECT

CASE WHEN customer_gender = 'Male' THEN 1 ELSE 0 END AS male,

CASE WHEN customer_gender = 'Female' THEN 1 ELSE 0 END AS female

FROM customer

) AS gender;

Subqueries in WHERE and HAVING Clauses

Subqueries are often used to refine the results of the primary query, typically placed within the WHERE or HAVING clauses. In the following example, we identify products priced below the average:

SELECT * FROM product

WHERE price >= (

SELECT AVG(price)

FROM product

);

Feel free to share an example of using a subquery within the HAVING clause in the comments!

Subqueries in INSERT Statements

Subqueries can be incorporated into INSERT statements, allowing you to insert data into a table based on another query's results. For example:

INSERT INTO orders(customer_name, quantity)

SELECT customer_name, 1 FROM customer WHERE customer_gender = 'Male';

Subqueries in UPDATE Statements

Similar to INSERT statements, subqueries can also be used in UPDATE statements. Below, we update customer names in the orders table for orders with a quantity of one:

UPDATE orders

SET customer_name = (

SELECT customer_name FROM customer WHERE customer_id = 'C10'

)

WHERE quantity = 1;

Subqueries in DELETE Statements

Finally, subqueries can facilitate DELETE operations by allowing you to remove records based on another query's output. The following code deletes orders from inactive customers:

DELETE FROM orders

WHERE customer_id IN (

SELECT customer_id

FROM customers

WHERE status = 'inactive'

);

Advantages of CTEs

• Improved Readability: CTEs simplify complex queries for easier comprehension.
• Modularity: Defined once, they can be reused multiple times in the same query.
• Recursive Queries: CTEs support recursive operations.
• Performance Enhancement: CTEs can optimize query performance.

Here’s an example of a CTE that cleanses product price data stored as text, removing dollar signs and converting the values to decimal format:

WITH cleansed_product AS (

SELECT

product_id,

category_id,

product_name,

CAST(REPLACE(price,'$','') AS DECIMAL(10,2)) AS price

FROM product

)

SELECT * FROM cleansed_product;

Additionally, CTEs are invaluable for working with partitions and window functions, making complex analytical queries more readable and manageable. We will explore these advanced techniques further in Part Two of this series.