Content ITV PRO
This is Itvedant Content department
SQL for Testers
Joins, Aggregates, Data Validation with SQL
Learning Outcome
5
Use COUNT, SUM, AVG, MAX, MIN
4
Understand aggregate functions
3
Use INNER, LEFT, RIGHT joins
2
Differentiate between types of joins
1
Understand why joins are required
Recall
What is a table?
What is a primary key?
What is a foreign key?
What does SELECT command do?
What is JOIN?
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
JOIN is used to combine rows from two or more tables based on a related column
SELECT Students.Name, Courses.CourseName FROM Students INNER JOIN Courses ON Students.CourseID = Courses.CourseID;
Common Types:
Example:
Why Need Joins?
- Data is stored in multiple tables
-
Information is divided to reduce redundancy
-
We need to combine tables to get complete information
- Students table
-
Courses table
-
Enrollment table
In real databases:
Example
To get student name with course name → We use JOIN
Attendance List & Exam Marks List
Imagine two lists in a classroom:
List 1: Class Attendance
List 2: Exam Marks
Student ID
1
3
2
Name
Rahul
Aman
Priya
Marks
70
90
85
Student ID
1
3
2
INNER JOIN
Class Attendance
Exam Marks
Student ID
1
3
2
Name
Rahul
Aman
Priya
Student ID
1
3
2
Marks
70
90
85
Show only students who are in both lists
Rahul = 85
Priya = 90
Aman (no marks) is not shown
StudentID 4 (not in class) is not shown
Meaning: Only matching records from both tables
Result:
LEFT JOIN
List 1: Class Attendance
List 2: Exam Marks
Show ALL students from attendance list + their marks (if available)
Result:
Rahul = 85
Priya = 90
Aman = NULL
Meaning: All students, even if they didn’t give exam
Name
Rahul
Aman
Priya
Student ID
1
3
2
Student ID
1
4
2
Marks
70
90
85
RIGHT JOIN
List 1: Class Attendance
List 2: Exam Marks
Student ID
1
3
2
Name
Rahul
Aman
Priya
Student ID
1
3
2
Marks
70
90
85
Show ALL students from marks list
Result:
Rahul = 85
Priya = 90
Unknown (ID 4) = 70
Meaning: All exam records, even if student not in attendance
FULL JOIN
List 1: Class Attendance
List 2: Exam Marks
Student ID
1
3
2
Name
Rahul
Aman
Priya
Show everyone from both lists
Meaning: All matched and unmatched records
Student ID
1
3
2
Marks
70
90
85
Just like we combine attendance and marks using StudentID,
SQL JOIN combines tables using a common column
What are Aggregate Functions?
Common Functions:
-
COUNT()
-
SUM()
-
AVG()
-
MAX()
-
MIN()
SELECT AVG(Marks) FROM Students;
SELECT SUM(Marks) FROM Students;
SELECT MAX(Marks) FROM Students;
SELECT MIN(Marks) FROM Students;
Aggregate functions perform calculations on multiple rows and return a single value
Example:
Class Result Summary
Total marks of class → SUM()
After exams, teacher doesn’t always need individual marks
Teacher may want:
Highest marks → MAX()
Average marks → AVG()
Lowest marks → MIN()
Number of students → COUNT()
Just like teacher calculates total and average marks,
SQL aggregate functions calculate summary values from rows
GROUP BY (With Aggregate Functions)
Used to group rows with same values
SELECT Department, COUNT(*) FROM Employees GROUP BY Department
Example:-
Summary
5
GROUP BY works with aggregate functions
4
COUNT, SUM, AVG, MAX, MIN are common
3
Aggregate functions perform calculations
2
INNER, LEFT, RIGHT, FULL are main types
1
Use COUNT, SUM, AVG, MAX, MIN
Quiz
Which JOIN returns only matching records?
A. LEFT JOIN
B. INNER JOIN
C. RIGHT JOIN
D. FULL JOIN
Quiz-Answer
Which JOIN returns only matching records?
A. LEFT JOIN
B. INNER JOIN
C. RIGHT JOIN
D. FULL JOIN
Joins, Aggregates, Data Validation with SQL
By Content ITV
