Computer Science
Grade 10
20 min
SQL Advanced Queries
SQL Advanced Queries
Tutorial Preview
1
Introduction & Learning Objectives
Learning Objectives
Write SQL queries that combine data from multiple tables using INNER JOIN.
Use aggregate functions (COUNT, AVG, SUM) to summarize data.
Group rows using the GROUP BY clause to perform calculations on subsets of data.
Filter grouped data using the HAVING clause.
Construct a basic subquery to use the result of one query within another.
Differentiate between the WHERE and HAVING clauses and apply them correctly.
Ever wonder how a music app creates a playlist of your most-played songs from your favorite artists? 🤔 They use advanced queries to connect different pieces of data!
In this tutorial, you'll move beyond simple SELECT statements and learn how to combine, group, and filter data in powerful ways. These advanced SQL skills are essential for buildi...
2
Key Concepts & Vocabulary
TermDefinitionExample
JOINA clause used to combine rows from two or more tables based on a related column between them.To get a list of students and the courses they are enrolled in, you would JOIN the `Students` table with the `Enrollments` table on the `StudentID` column.
Aggregate FunctionA function that performs a calculation on a set of values and returns a single, summary value.`COUNT(*)` counts the total number of rows. `AVG(Grade)` calculates the average of all values in the 'Grade' column.
GROUP BYA clause that groups rows that have the same values in specified columns into summary rows. It's often used with aggregate functions.`GROUP BY CourseName` would group all enrollment records together for each unique course, allowing you to `COUNT` the number of students in...
3
Core Syntax & Patterns
INNER JOIN Syntax
SELECT table1.column1, table2.column2
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;
Use this pattern to combine rows from two tables where the values in the common column match. The `ON` keyword specifies the condition that links the tables.
GROUP BY with HAVING Syntax
SELECT column_to_group_by, AGGREGATE_FUNCTION(column)
FROM table_name
GROUP BY column_to_group_by
HAVING condition;
Use this to first group rows based on a column, then apply an aggregate function, and finally filter those groups based on the result of the aggregate function.
Subquery in WHERE Clause
SELECT column1 FROM table1 WHERE column2 IN (SELECT column2 FROM table2 WHERE condition);
Use this pattern when you need to filter the main query based on...
4 more steps in this tutorial
Sign up free to access the complete tutorial with worked examples and practice.
Sign Up Free to ContinueSample Practice Questions
Challenging
You have `Employees` (`EmployeeID`, `Name`, `DeptID`) and `Departments` (`DeptID`, `DeptName`). You need to find the names of departments that have zero employees. An `INNER JOIN` will not work because it only shows departments with matching employees. Which query structure is best suited to solve this?
A.An INNER JOIN with a GROUP BY clause.
B.subquery that finds all departments with employees, used with a `NOT IN` clause in the main query.
C.query that uses `COUNT(EmployeeID) = 0` in the `HAVING` clause.
D.simple `SELECT DeptName FROM Departments WHERE EmployeeID IS NULL;`
Challenging
You have three tables: `Employees` (`EmpID`, `Name`), `Projects` (`ProjID`, `ProjName`), and `Assignments` (`EmpID`, `ProjID`). How would you find the names of all employees working on the 'Phoenix' project?
A.Join `Employees` and `Projects` and filter by `ProjName`.
B.Join `Employees` and `Assignments` and filter by `ProjName`.
C.Join all three tables together, linking them through their respective IDs.
D.Use a subquery to get the `ProjID` for 'Phoenix', then use that ID to find `EmpID`s from `Assignments`, and finally use those `EmpID`s to find names from `Employees`.
Challenging
From an `Orders` table (`OrderID`, `Country`, `Amount`), you want to find countries where the average order amount is over $1000. Which query is structured correctly to do this?
A.SELECT Country FROM Orders GROUP BY Country HAVING AVG(Amount) > 1000;
B.SELECT Country FROM Orders WHERE Amount > 1000 GROUP BY Country;
C.SELECT Country, AVG(Amount) FROM Orders WHERE AVG(Amount) > 1000;
D.SELECT Country FROM (SELECT Country, AVG(Amount) AS AvgAmt FROM Orders GROUP BY Country) WHERE AvgAmt > 1000;
Want to practice and check your answers?
Sign up to access all questions with instant feedback, explanations, and progress tracking.
Start Practicing Free