WEBVTT

00:00.000 --> 00:04.000
Subcrease and nested queries.

00:04.000 --> 00:07.000
Learning objectives.

00:07.000 --> 00:12.000
Understand what subcrease are and how they work in SQL.

00:12.000 --> 00:17.000
Write select statements with subcrease in where and from clauses.

00:17.000 --> 00:24.000
Recognize when to use subcrease and how they affect performance.

00:24.000 --> 00:27.000
Understanding subcrease.

00:27.000 --> 00:33.000
In SQL, sometimes we need to write a query that depends on the result of another query.

00:33.000 --> 00:37.000
That's where subcrease and nested queries come in.

00:37.000 --> 00:39.000
Think of it like nesting dolls.

00:39.000 --> 00:44.000
One doll fits inside another and together they form a complete set.

00:44.000 --> 00:48.000
In the same way, a subquery fits inside a mean query.

00:48.000 --> 00:53.000
Helping us build a layered question where each part depends on the one inside it.

00:53.000 --> 01:00.000
For example, to find students who scored higher than the average marks of the entire class, we can write.

01:00.000 --> 01:06.000
Select from student where marks greater than select AVG marks from student.

01:06.000 --> 01:11.000
Here, the inner query calculates the overall average marks of all students.

01:11.000 --> 01:16.000
And the outer query compares each student's marks to that average.

01:16.000 --> 01:22.000
This allows the query to automatically compare each student's marks with the overall average.

01:22.000 --> 01:29.000
So only those students who have scored above the average are retrieved from the database.

01:29.000 --> 01:32.000
Subcrease in the where clause.

01:32.000 --> 01:36.000
One of the most common uses of subqueries is in the where clause.

01:36.000 --> 01:42.000
This allows SQL to filter records based on a condition generated by another query.

01:42.000 --> 01:50.000
For example, select from students where marks greater than select AVG marks from students.

01:50.000 --> 01:55.000
In this case, we find all students whose marks are above the overall class average.

01:55.000 --> 02:03.000
Subqueries in the where clause are useful for filtering, ranking, and comparing values within a table.

02:03.000 --> 02:06.000
Subqueries in the from clause.

02:06.000 --> 02:13.000
SQL allows subqueries to appear in the from clause where they act like a temporary or virtual table.

02:13.000 --> 02:17.000
This is useful when you need to calculate intermediate results first.

02:17.000 --> 02:22.000
And then use those results in your main query for further processing or filtering.

02:22.000 --> 02:25.000
For example, we can write.

02:25.000 --> 02:27.000
Select course.

02:27.000 --> 02:29.000
Average marks from.

02:29.000 --> 02:32.000
Select course AVG marks.

02:32.000 --> 02:35.000
As average marks from student group by course.

02:35.000 --> 02:37.000
As course average.

02:37.000 --> 02:41.000
Order by average marks DEC.

02:41.000 --> 02:46.000
Here, the inner query calculates the average marks for each course.

02:46.000 --> 02:49.000
And the outer query selects and sorts those results.

02:49.000 --> 02:53.000
This approach is useful for ranking courses by their performance.

02:53.000 --> 02:59.000
As it allows you to sort the summarized average marks from highest to lowest.

02:59.000 --> 03:03.000
Correlated subqueries and performance considerations.

03:03.000 --> 03:10.000
SQL supports correlated subqueries which rely on data from the outer query to produce results.

03:10.000 --> 03:14.000
They're useful for row by row comparisons but can slow down performance,

03:14.000 --> 03:17.000
especially on large data sets.

03:17.000 --> 03:22.000
To optimize speed, use joins for aggregate queries instead.

03:22.000 --> 03:25.000
They process data more efficiently.

03:25.000 --> 03:28.000
In practice, subqueries offer flexibility.

03:28.000 --> 03:34.000
But understanding when to replace them with joins helps balance clarity, precision,

03:34.000 --> 03:41.000
and performance in real world SQL design.

03:41.000 --> 03:47.000
Subqueries let SQL use one query's result inside another.

03:47.000 --> 03:54.000
They filter data, create temporary tables, or compare rows through correlated subqueries.

03:54.000 --> 03:59.000
This makes SQL more flexible and precise in data handling.

03:59.000 --> 04:04.000
A good grasp of subqueries improves query design and performance.