A Comprehensive Guide to Complex SQL Queries and Subqueries

Photo by Campaign Creators on Unsplash

A Comprehensive Guide to Complex SQL Queries and Subqueries

Isuri Balasooriya's photo
Isuri Balasooriya
·

3 min read

In todays article I’m going to be talking about advanced querying in SQL. I’m going to be using the same database schema we created in our previous article which you can find here - https://themathlab.hashnode.dev/mysql-fundamentals-learn-the-basics

  • DISTINCT - Removing duplicate values in a column

Lets say we want to see what are all the countries we have users from,

select distinct country from users;

This will return all the unique countries where we have user accounts created from.

  • CASE - Create a conditional logic

This works similar to a IF-ELSE statement in programming. Lets say we want to categorize our users to tiers based on the subscription model that they have purchased,

select name, country,
case
    when SubscriptionID = 1 THEN "Tier 1"
    when SubscriptionID between 2 and 4 THEN "Tier 2"
    else "Tier 3"
end as "Tier"
from users;

This will retrieve the name and country of the user and an additional column called “Tier” based on the result of the conditional logic defined with case.

  • HAVING - Filtering aggregated results

Now we talked about the HAVING clause in our previous article. It is used to filter out results already grouped with the GROUP BY statement.

select ReleaseYear, avg(Duration) as "Average Duration"
from content
group by ReleaseYear
having avg(Duration) > 100;

Subqueries

A subquery is a query inside another query. There are different places a subquery can be located. Lets take a look at some of these clauses,

  • Subquery in WHERE clause

Lets see how many users are currently watching content more than the average progress % on the platform,

select UserID
from watchhistory
where Progress > (Select avg(Progress) from watchhistory);
  • Subquery in SELECT clause
 select USERID, CONTENTID, Progress, (SELECT avg(Progress) from watchhistory) AS "Average Progress"
 from watchhistory;

  • Subquery in the FROM clause
SELECT department, avg_salary
FROM (SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department) AS dept_avg
WHERE avg_salary > 4000;
  • EXISTS - Check if data exists

Lets find out the subscription types in the subscription table with at least one user who has purchased it,

SELECT PlanName FROM subscription 
WHERE EXISTS (SELECT 1 FROM users WHERE subscription.SubscriptionID = users.SubscriptionID);

This will return subscription plans with at least one user.

  • IN - Checking multiple values from a subquery

Lets find out what are the reviews received for TV shows,

SELECT Review FROM reviews 
WHERE ContentID IN (SELECT ContentID FROM content WHERE Type="TV" );

I hope you got an idea about some advanced querying in SQL with todays article. These queries along with what we covered under the foundations of SQL, would have given you a solid understanding of querying your database with SQL. This knowledge is enough for you to handle basic level of SQL tasks academically or professionally. From here onwards we will be looking at some more advanced features of SQL,

  • Indexing and Performance Optimization

  • Views and Stored Procedures

  • Transactions and Error Handling

  • SQL vs NoSQL

SQLadvanced sqlsubqueries