WEBVTT

00:00.000 --> 00:15.000
In this video, we will understand, refine queries to retrieve precise data using where and pattern matching.

00:15.000 --> 00:21.000
Sort and organize data using order by and combine multiple tables with joins.

00:21.000 --> 00:31.000
Summarize and aggregate data using functions like count, sum, AVG, max, min, group by, and having.

00:31.000 --> 00:39.000
Refining queries with where, the where clause helps us filter data from a table based on specific conditions.

00:39.000 --> 00:45.000
For example, the query select star from students where marks greater than 80.

00:45.000 --> 00:49.000
Retrieves only those students who scored more than 80 marks.

00:49.000 --> 00:53.000
We can make our conditions more specific using logical operators.

00:53.000 --> 01:00.000
For instance, select from students where marks greater than 70 and age greater than 10.

01:00.000 --> 01:04.000
Show students who scored above 70 and are older than 10.

01:04.000 --> 01:10.000
This allows us to extract only the relevant rows instead of displaying the entire table.

01:10.000 --> 01:18.000
By refining our queries with the where clause, we can focus on meaningful data, just like filtering top performers from a class list.

01:18.000 --> 01:26.000
While combining conditions using AND or and NOT to make our filtering even more accurate.

01:26.000 --> 01:29.000
Sorting results with order by.

01:29.000 --> 01:36.000
Once we have retrieved the records from the students table, we can arrange them in a specific order using the order by clause.

01:36.000 --> 01:41.000
In the example shown, the data is sorted based on the marks column.

01:41.000 --> 01:51.000
The query select from students order by marks ASC, arranges the students in ascending order, displaying them from the lowest marks to the highest.

01:51.000 --> 02:04.000
The query select from students, order by marks DESC arranges the students in descending order, displaying them from the highest marks to the lowest.

02:04.000 --> 02:07.000
Aggregating data

02:07.000 --> 02:12.000
This query demonstrates how to summarize data by categories using aggregation functions.

02:12.000 --> 02:17.000
The key is the group by class ID line, which applies all these functions.

02:17.000 --> 02:24.000
Count, sum, AVG, max, and MN, separately to each class group.

02:24.000 --> 02:34.000
In the select clause, we choose class.id, then use count to get the number of students, some to calculate total marks and AVG for the average.

02:34.000 --> 02:40.000
We also use max and MN to find the highest and lowest scores in each class.

02:40.000 --> 02:53.000
Finally, the having clause filters the results to show only classes where the average marks are greater than 80, giving us just the high performing groups for analysis.

02:53.000 --> 02:55.000
Combining clauses

02:55.000 --> 03:00.000
In SQL, we can combine multiple clauses to make queries more powerful.

03:00.000 --> 03:10.000
For example, this query retrieves the students ID, name, age, marks, and their class name by joining the students and classes tables.

03:10.000 --> 03:16.000
The where clause filters only those students who scored above the average calculated using a subquery.

03:16.000 --> 03:21.000
Finally, order by sorts, the results from highest to lowest marks.

03:21.000 --> 03:32.000
By combining where, join, subquaries, and order by, we can efficiently filter, organize, and analyze data all in one query.

03:32.000 --> 03:38.000
Querying data in SQL is about asking the right questions to extract meaningful information.

03:38.000 --> 03:48.000
By mastering clauses like where, order by, join, and group by, we can organize and analyze data effectively.