Day 2: SQL Querying Basics - WHERE, ORDER BY, LIMIT, Aggregate Functions, GROUP BY, and HAVING : Mastering

Introduce advanced querying techniques for filtering, aggregating, and grouping data.

i.) Filtering Data:

• Use the WHERE clause to filter rows based on specific conditions, like retrieving students with grades above 80.

ii.) Aggregating Data:

• Use functions like COUNT, SUM, AVG, MIN, and MAX to perform calculations on data, such as finding the total number of students or the average grade.

iii.) Grouping Data:

• Use the GROUP BY clause to organize data into groups based on a column, such as grouping students by grade.
• Use HAVING to filter grouped results, like showing only grades achieved by more than two students.

Querying Techniques:

• WHERE Clause: Filters rows based on conditions.

SELECT * FROM students WHERE grade >= 75;

• ORDER BY Clause: Sorts rows in ascending (ASC) or descending (DESC) order.

SELECT * FROM students ORDER BY grade DESC;

• LIMIT Clause: Limits the number of rows returned.

SELECT * FROM students LIMIT 3;

Aggregate Functions:

• COUNT: Counts the number of rows in a table or meeting a condition.

SELECT COUNT(*) AS total_students FROM students;

• SUM: Calculates the total of a numeric column.

SELECT SUM(grade) AS total_grades FROM students;

• AVG: Computes the average value of a numeric column.

SELECT AVG(grade) AS average_grade FROM students;

• MIN and MAX: Finds the smallest and largest values in a column.

SELECT MIN(grade) AS lowest_grade, MAX(grade) AS highest_grade FROM students;

GROUP BY and HAVING:

• GROUP BY: Groups rows sharing a value in a specified column and applies aggregate functions to each group.

SELECT grade, COUNT(*) AS student_count  
FROM students  
GROUP BY grade;

• HAVING: Filters grouped data based on conditions (similar to WHERE, but for aggregated results).

SELECT grade, COUNT(*) AS student_count  
FROM students  
GROUP BY grade  
HAVING student_count > 1;

Combining All Techniques:

Show how to use WHERE, ORDER BY, LIMIT, GROUP BY, and HAVING together.

SELECT grade, COUNT(*) AS student_count  
FROM students  
WHERE grade >= 80  
GROUP BY grade  
HAVING student_count > 2  
ORDER BY student_count DESC  
LIMIT 3;

Challenge for you!
Find the total number of students grouped by grades.
List grades with an average score above 85.
Show the top 3 grades with the most students.
Encourage readers to share their solutions in the comments!

In the next post, we will dive deeper into SQL commands, including DDL, DML, and more!