Subqueries (nested query) and GROUP BY in SQL: Advance commands

In this article at OpenGenus, we have covered two advanced concepts in SQL that is using Subqueries (nested query) and nested commands along with GROUP BY. We have explained the concepts with SQL examples.

Table of contents:

1. Subqueries (nested query)- a query within a query
2. Nested SQL commands
3. Using two nested commands
4. GROUP BY

Subqueries (nested query)- a query within a query

Suppose you possess a database consisting of several tables containing varying data such as personal details, interests, likes, interactions, etc. If you want to access specific data from any or all of these tables pertaining to a particular user, you would need to recall the user ID that connects all the tables in the dataset. In such a scenario, a nested query can come in handy. With a nested query, you can retrieve the user ID in one loop along with their name and other information in the outer loop using just a single command line.

SELECT "id" FROM "publishers" WHERE "publisher" = 'Fitzcarralo Editions';
SELECT "title" FROM "books" WHERE "publisher_id" = 5;

In a typical command, you need to use two different command lines to access the title data. However, with the command below, you can use nested commands to retrieve all the data in a single go.

Nested SQL commands

SELECT "title" FROM "books"
WHERE "Publisher_id" = (
	SELECT "id" FROM "publishers"
	WHERE "publisher" = 'Fitzcarroldo Editions'
);

Since we don't have the Publisher ID, we used a nested command to locate it by utilizing the publisher name. Initially, we selected the ID of all publishers and then filtered it according to the name 'Fitzcarroldo Editions.' Eventually, we obtained the ID of the publisher named 'Fitzcarroldo Editions.'

---

SELECT "name" FROM "authors" WHERE "id" = (
	SELECT "author_id" FROM "authored" where "book_id" = (
		SELECT "id" FROM "publishers"
		WHERE "title" = 'The Birthday Party'
);

This SQLite command is a nested query that retrieves the name of the author(s) of a book titled 'The Birthday Party.' The command selects the ID of the publisher that published the book and uses it to locate the author ID(s) in the 'authored' table, finally returning the corresponding author name(s) from the 'authors' table.

---

SELECT "book_id" FROM "authored" WHERE "author_id" = (
	SELECT "id" FROM "authors" WHERE "name" = 'OpenGenus'
);

Using two nested commands

select books of an author

SELECT "title" FROM "books" WHERE "id" IN (
	SELECT "book_id" FROM "authored"
	WHERE "author_id" = (
		SELECT "id" FROM "authors" WHERE "name" = 'OpenGenus'
	)
);

In this scenario, we have utilized two nested commands to search for two different IDs based on the data available. Firstly, we searched for the author ID by their name, 'OpenGenus.' Subsequently, we used that ID to look up the book ID of the author and finally retrieved the book title that we were interested in.

---

GROUP BY

Rows in a table can be grouped based on a defined column or set of columns using the GROUP BY method in SQLite. To determine values for each group, this function is generally used in conjunction with aggregate methods like COUNT, SUM, AVG, MAX, and MIN. It makes it possible to analyse data more thoroughly, leading to more insightful conclusions and better decision-making. In SQLite, GROUP BY is a crucial tool for data analysis and is utilised extensively across numerous industries.

SELECT "book_id", AVG("rating") AS "average rating"
FROM "ratings"
GROUP BY "book_id";

When it comes to rounding up and filtering average ratings above 4 in SQLite, using the WHERE clause won't work because it creates a group. Instead, we must utilize the HAVING clause to filter groups based on aggregate functions like AVG. With the HAVING clause, we can filter grouped data according to the result of aggregate functions, which is perfect for filtering data based on computed averages, sums, or counts.

SELECT "book_id", ROUND(AVG("rating"), 2) AS "average rating"
FROM "ratings"
GROUP BY "book_id"
HAVING "average rating" > 4.0;

The HAVING clause is used literally to check whether a rating is above 4, while counting the number of ratings and grouping them by book ID is a common use case for data analysis in SQLite.

---

This SQL query retrieves the book ID and rounds up the average of ratings from the 'ratings' table, grouping them by book ID and ordering them in descending order only if the rating is greater than 4

SELECT "book_id", ROUND(AVG("rating"), 2) AS "average rating"
FROM "ratings"
GROUP BY "book_id"
HAVING "average rating" > 4.0
ORDER BY "average rating" DESC;