💘 Advanced Queries and SQL Functions

Databases aren’t just about storing information. They’re about exploring, analyzing and connecting data.

6.1 Aggregate Functions, Counting the Blessings

Aggregate functions perform calculations on multiple rows and return a single value.
Common Functions :
COUNT() → count the number of rows
SUM() → total of numeric values
AVG() → average of numeric values
MIN() → smallest value
MAX() → largest value

SELECT COUNT(*) AS TotalLovers, AVG(Age) AS GoldenAge
FROM Lovers;

COUNT(*) counts all rows in the Lovers table
AVG( ) calculates the average age of all lovers
AS gives a friendly name to the result column

6.2 GROUP BY and HAVING Clauses

GROUP BY organizes rows into groups based on a column.
HAVING filters groups (like WHERE, but for aggregates).

SELECT City, COUNT(*) AS LoversCount
FROM Lovers
GROUP BY City
HAVING COUNT(*) > 3;

GROUP BY groups lovers by their city
COUNT(*) counts lovers in each city
HAVING COUNT(*) > 3 only shows cities with more than 3 lovers

6.3 JOINS, How Couples Form

Joins combine rows from two or more tables based on related columns.
INNER JOIN 💑 Only matching rows in both tables.
LEFT JOIN : All rows from left table, matched from right if available.
RIGHT JOIN : All rows from right table, matched from left.
FULL OUTER JOIN 🌍 All rows from both sides, unmatched included.
CROSS JOIN : Cartesian product, all possible pairings.

6.4 Secrets within Secrets, Subqueries

A subquery is a query inside another query. It helps uncover hidden insights.

SELECT Name
FROM Lovers
WHERE LoverID IN (
    SELECT LoverID
    FROM LoverEvents
    WHERE EventID = 5
);

Retrieve data based on a condition that depends on another query :
Inner query finds all LoverID attending event 5 Outer query selects the names of those lovers

6.5 Views and Materialized Views

Views : Virtual tables based on queries. They simplify complex queries without storing data physically.
Materialized Views : Store the results physically, faster for repeated access because the data is precomputed.

CREATE VIEW ActiveLovers AS
SELECT Name AS LoverName, City AS LoverCity
FROM Lovers
WHERE Active = TRUE;

ActiveLovers is the name of the view (like a virtual table)
AS after CREATE VIEW does not create a column alias ; it just connects the view name to the query
Column aliases (Name AS LoverName, City AS LoverCity) rename columns inside the view for clarity
WHERE Active = TRUE only includes lovers who are currently active
Using a view allows you to reuse this query easily without writing the full SELECT each time

6.6 Window Functions

Window functions calculate aggregates across a "window" of rows, while keeping individual rows visible.
Common Functions : ROW_NUMBER(), RANK(), DENSE_RANK(), SUM() OVER(), AVG() OVER()

SELECT Name, RANK() OVER(PARTITION BY City ORDER BY Age DESC) AS RankInCity
FROM Lovers;

To see rankings of lovers inside each city without collapsing rows :
RANK() OVER(PARTITION BY City ORDER BY Age DESC) → ranks lovers within each city by age
PARTITION BY defines the group (city)
ORDER BY defines the ranking order

6.7 Triggers and Stored Procedures

Triggers : Automated actions fired before or after events (INSERT, UPDATE, DELETE).
Stored Procedures : Predefined SQL scripts for repeated actions.

CREATE TRIGGER AfterNewLover
AFTER INSERT ON Lovers
FOR EACH ROW
BEGIN
    INSERT INTO Notifications(Message) VALUES ('A new admirer has arrived!');
END;

Automate notifications whenever a new lover joins :
AFTER INSERT trigger runs after a new lover is added
FOR EACH ROW executes for every new record