Playing as a guest? to earn XP and track your progress.
🔗 Grouping Records, Joins in SQL Quiz
🔗 Grouping Records, Joins in SQL
Which category of SQL functions operates on a group of rows and returns a single summary value?
Single-row functions
Scalar functions
Aggregate functions
String functions
Aggregate functions (COUNT, SUM, AVG, MAX, MIN) operate on a GROUP of rows and return ONE summary value for the entire group. Single-row functions process one row at a time.
Which of the following is NOT an aggregate function in MySQL?
SUM()
UPPER()
COUNT()
AVG()
UPPER() is a single-row string function — it processes one row at a time. SUM, COUNT, and AVG are all aggregate functions that work on groups of rows.
What is the purpose of the GROUP BY clause in SQL?
To sort the result in ascending order
To filter individual rows before aggregation
To group rows with the same value into summary rows
To join two tables together
GROUP BY groups rows that have the same value in the specified column(s) into summary rows, allowing aggregate functions to produce one result per group.
Based on the Employee table (Dept: Sales×2, IT×2, HR×2), what does the following query return?
SELECT Dept, COUNT(*) FROM Employee GROUP BY Dept;
One row with total count 6
Three rows: Sales-2, IT-2, HR-2
Six rows, one per employee
An error — COUNT cannot be used with GROUP BY
GROUP BY Dept creates three groups (Sales, IT, HR). COUNT(*) counts the rows in each group. Result: Sales→2, IT→2, HR→2 — three summary rows.
Which query correctly displays the average salary for each department?
SELECT Dept, AVG(Salary) FROM Employee;
SELECT AVG(Salary) FROM Employee GROUP BY Dept;
SELECT Dept, AVG(Salary) FROM Employee GROUP BY Dept;
SELECT Dept, AVERAGE(Salary) FROM Employee GROUP BY Dept;
To get the average salary per department, you need Dept in the SELECT list AND in the GROUP BY clause. AVG() is the correct aggregate function — AVERAGE() does not exist in MySQL.
The Employee table has salaries: Sales(45000, NULL), IT(62000, 71000), HR(38000, 41000). What does SELECT Dept, AVG(Salary) FROM Employee GROUP BY Dept; return for Sales?
22500.00
45000.00
NULL
0.00
AVG() ignores NULL values. For Sales: only 45000 is non-NULL. AVG = 45000 / 1 = 45000.00. The NULL salary (Anjali Gupta) is excluded from the calculation.
Which of the following queries will produce an ERROR in MySQL (ONLY_FULL_GROUP_BY mode)?
SELECT Dept, COUNT(*) FROM Employee GROUP BY Dept;
SELECT Dept, AVG(Salary) FROM Employee GROUP BY Dept;
SELECT Dept, Name, COUNT(*) FROM Employee GROUP BY Dept;
SELECT City, MAX(Salary) FROM Employee GROUP BY City;
Name is neither in an aggregate function nor in the GROUP BY clause. MySQL cannot determine which employee's name to show for each department group. This violates the GROUP BY rule and causes an error.
The Golden Rule of GROUP BY states that every column in the SELECT list that is NOT inside an aggregate function must:
Be enclosed in single quotes
Appear in the WHERE clause
Appear in the GROUP BY clause
Be of VARCHAR data type
Any non-aggregated column in SELECT must be in GROUP BY. Otherwise MySQL cannot determine which value to display for the group (ambiguity error).
Which query correctly groups employees by BOTH department AND city?
SELECT Dept, City, COUNT(*) FROM Employee GROUP BY Dept;
SELECT Dept, City, COUNT(*) FROM Employee GROUP BY Dept AND City;
SELECT Dept, City, COUNT(*) FROM Employee GROUP BY Dept, City;
SELECT Dept, City, COUNT(*) FROM Employee GROUP BY (Dept, City);
Multiple columns in GROUP BY are separated by commas: GROUP BY Dept, City. This creates a separate group for each unique combination of (Dept, City). AND is not used in GROUP BY.
Based on Employee table, how many rows does SELECT Dept, City, COUNT(*) FROM Employee GROUP BY Dept, City; return? (Sales: Delhi×1, Mumbai×1; IT: Mumbai×1, Bangalore×1; HR: Delhi×2)
3
4
5
6
GROUP BY Dept, City creates a group for each unique (Dept, City) combination: Sales-Delhi, Sales-Mumbai, IT-Mumbai, IT-Bangalore, HR-Delhi. That is 5 distinct combinations = 5 rows.
What is the purpose of the HAVING clause in SQL?
To sort groups in ascending order
To filter individual rows before grouping
To filter groups after the GROUP BY clause is applied
To join tables based on a condition
HAVING filters groups AFTER GROUP BY is applied. It is the group-level equivalent of WHERE. Unlike WHERE, HAVING can use aggregate functions in its condition.
Which query correctly shows only departments where the average salary is more than 50000?
SELECT Dept FROM Employee WHERE AVG(Salary) > 50000 GROUP BY Dept;
SELECT Dept, AVG(Salary) FROM Employee GROUP BY Dept HAVING AVG(Salary) > 50000;
SELECT Dept, AVG(Salary) FROM Employee HAVING AVG(Salary) > 50000 GROUP BY Dept;
SELECT Dept FROM Employee GROUP BY Dept WHERE AVG(Salary) > 50000;
HAVING must come AFTER GROUP BY. AVG cannot be used in WHERE. The correct order is: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY.
Based on the Employee table (IT avg=66500, Sales avg=45000, HR avg=39500), what is the output of:
SELECT Dept, AVG(Salary) FROM Employee GROUP BY Dept HAVING AVG(Salary) > 50000;
All three departments
Only IT department
IT and Sales departments
No rows returned
Only IT has AVG Salary (66500) above 50000. Sales (45000) and HR (39500) do not meet the HAVING condition, so they are filtered out.
What is the key difference between WHERE and HAVING?
WHERE works with numbers; HAVING works with text
WHERE filters rows before grouping; HAVING filters groups after grouping
WHERE filters groups after grouping; HAVING filters rows before grouping
WHERE and HAVING do exactly the same thing
WHERE filters individual rows BEFORE the GROUP BY operation. HAVING filters groups AFTER GROUP BY is applied. WHERE cannot use aggregate functions; HAVING can.
Which of the following is INVALID SQL?
SELECT Dept, COUNT(*) FROM Employee GROUP BY Dept HAVING COUNT(*) > 1;
SELECT City, SUM(Salary) FROM Employee GROUP BY City HAVING SUM(Salary) > 100000;
SELECT Dept, AVG(Salary) FROM Employee WHERE AVG(Salary) > 50000 GROUP BY Dept;
SELECT Dept FROM Employee GROUP BY Dept HAVING COUNT(*) >= 2;
AVG(Salary) cannot be used in a WHERE clause. Aggregate functions can only appear in SELECT, HAVING, or ORDER BY — not in WHERE. This query must use HAVING instead.
A query uses both WHERE and HAVING. In what order do these clauses execute?
HAVING runs first, then WHERE
Both run simultaneously
WHERE runs first (before grouping), then HAVING (after grouping)
ORDER BY determines which runs first
Execution order: FROM → WHERE (filter rows) → GROUP BY (create groups) → HAVING (filter groups) → SELECT → ORDER BY. WHERE always runs before GROUP BY; HAVING always runs after.
What is the correct order of clauses in a complete SELECT statement?
SELECT → FROM → GROUP BY → WHERE → HAVING → ORDER BY
SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
SELECT → WHERE → FROM → GROUP BY → HAVING → ORDER BY
SELECT → FROM → HAVING → WHERE → GROUP BY → ORDER BY
The mandatory clause order is: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY. A helpful mnemonic: SF-WG-HO (Select From Where GroupBy Having OrderBy).
Which query shows cities where the maximum salary exceeds 60000?
SELECT City FROM Employee WHERE MAX(Salary) > 60000;
SELECT City, MAX(Salary) FROM Employee GROUP BY City HAVING MAX(Salary) > 60000;
SELECT City, MAX(Salary) FROM Employee HAVING MAX(Salary) > 60000;
SELECT City FROM Employee GROUP BY City WHERE MAX(Salary) > 60000;
MAX() is an aggregate function so it must be in HAVING (not WHERE). GROUP BY City creates one row per city. HAVING MAX(Salary) > 60000 then keeps only qualifying cities.
What is a Cartesian Product in SQL?
A join that returns only matching rows from both tables
A join that returns all rows from the left table plus matching rows
The result of combining tables WITHOUT a join condition — every row paired with every row
A join based on equal values in a common column
A Cartesian Product (Cross Join) pairs every row of the first table with every row of the second table. If Table A has m rows and Table B has n rows, the result has m × n rows — no condition is used.
Employee table has 6 rows and Department table has 4 rows. How many rows does SELECT * FROM Employee, Department; return?
6
4
10
24
A Cartesian Product pairs every row of Employee (6) with every row of Department (4). Result = 6 × 4 = 24 rows.
Why is a Cartesian Product generally considered harmful in SQL queries?
It only works with primary key columns
It produces meaningless data by pairing all rows with all rows, creating a huge result
It cannot be undone with ROLLBACK
It permanently changes the data in the tables
A Cartesian Product produces every possible combination of rows — most of which are meaningless (e.g., Priya paired with every department including the ones she doesn't belong to). It also causes severe performance problems with large tables.
What is a Table Alias in SQL?
A permanent new name given to a table using ALTER TABLE
A temporary short name given to a table within a query
A copy of a table created for backup purposes
A constraint placed on a table's columns
A Table Alias gives a table a temporary, short name valid only within that query. It does NOT change the actual table name in the database. Example: FROM Employee E — 'E' is the alias.
Which syntax correctly creates an alias 'E' for the Employee table?
FROM Employee ALIAS E
FROM Employee RENAME E
FROM Employee AS E OR FROM Employee E
FROM Employee = E
Table aliases can be created either with the AS keyword (FROM Employee AS E) or simply with a space (FROM Employee E). Both are valid MySQL syntax.
In a join query, why are table aliases particularly useful?
They speed up query execution significantly
They allow you to avoid writing long table names and resolve column name ambiguity
They are required by MySQL for joins to work
They permanently simplify the table name in the database
Table aliases shorten long table names and help resolve ambiguity when two tables have columns with the same name (e.g., both Employee and Department have DeptId). E.DeptId vs D.DeptId removes the confusion.
What type of join combines rows from two tables based on an equality condition on a common column?
Cartesian Join
Natural Join
Equi-Join
Cross Join
An Equi-Join links two tables using an equality (=) condition, typically matching a Foreign Key in one table with the Primary Key in another (e.g., WHERE E.DeptId = D.DeptId).
Which query correctly performs an Equi-Join between Employee (alias E) and Department (alias D) on DeptId?
SELECT E.Name, D.DeptName FROM Employee E, Department D;
SELECT E.Name, D.DeptName FROM Employee E JOIN Department D;
SELECT E.Name, D.DeptName FROM Employee E, Department D WHERE E.DeptId = D.DeptId;
SELECT E.Name, D.DeptName FROM Employee E EQUI JOIN Department D ON E.DeptId = D.DeptId;
An Equi-Join requires a WHERE clause with an equality condition linking the two tables. Without WHERE E.DeptId = D.DeptId, it becomes a Cartesian Product. EQUI JOIN is not a valid MySQL keyword.
Employee table has DeptId as a Foreign Key referencing Department(DeptId). Department D04 (Finance) has no employees. In an Equi-Join query, will Finance appear in the output?
Yes, with NULL for employee columns
Yes, with 0 for employee columns
No — Equi-Join only returns rows where the join condition is satisfied in BOTH tables
It depends on the ORDER BY clause
An Equi-Join (which is an Inner Join) only returns rows where matching values exist in BOTH tables. Since no employee has DeptId = D04, Finance has no matching employee row and does not appear in the output.
Which of the following is the modern alternative syntax for an Equi-Join?
FROM Employee, Department WHERE Employee.DeptId = Department.DeptId
FROM Employee JOIN Department ON Employee.DeptId = Department.DeptId
FROM Employee EQUI JOIN Department USING DeptId
FROM Employee MATCH Department ON DeptId
The modern JOIN...ON syntax (FROM Employee JOIN Department ON Employee.DeptId = Department.DeptId) is equivalent to the traditional comma + WHERE syntax. Both produce identical results in MySQL.
What is a Natural Join?
A join that uses the WHERE clause to match rows manually
A join that automatically links tables on all columns with the same name and data type
A join that pairs every row of one table with every row of another
A join that only works with PRIMARY KEY columns
Natural Join automatically identifies all columns that share the same name and data type in both tables and joins on those columns. No explicit join condition needs to be written.
Employee and Department tables both have a column named DeptId. Which query performs a Natural Join?
SELECT * FROM Employee, Department WHERE Employee.DeptId = Department.DeptId;
SELECT * FROM Employee NATURAL JOIN Department;
SELECT * FROM Employee AUTO JOIN Department ON DeptId;
SELECT * FROM Employee JOIN Department USING (DeptId);
NATURAL JOIN is the correct keyword. MySQL automatically finds DeptId as the common column name and joins on it without needing a WHERE or ON condition.
How does Natural Join handle duplicate columns (columns with the same name in both tables)?
It shows the column twice — once from each table
It removes the duplicate — the common column appears only once in the result
It throws an error when duplicate column names are found
It uses the value from the left (first) table only
Natural Join eliminates duplicate columns from the result. If both tables have DeptId, the output contains DeptId only once. This is a key difference from Equi-Join, where DeptId appears twice (once from each table).
What is a risk of using Natural Join compared to Equi-Join?
Natural Join is much slower than Equi-Join
Natural Join might join on unexpected columns if two tables share multiple column names
Natural Join cannot be used with aggregate functions
Natural Join requires both tables to have the same number of rows
Natural Join joins on ALL shared column names automatically. If two tables unexpectedly share multiple column names, the join might produce incorrect results. Equi-Join is safer because you explicitly control which column(s) to join on.
In a join query, how do you add an additional filter condition besides the join condition?
Use a second WHERE keyword for the filter
Use FILTER keyword after the join condition
Extend the WHERE clause using AND with the additional condition
Use HAVING for the additional condition
Additional search conditions are simply added to the WHERE clause using AND. Example: WHERE E.DeptId = D.DeptId AND E.City = 'Delhi'. The first condition joins the tables; the second filters the result.
Which query displays the name, salary, and department name of employees working in Delhi?
SELECT E.Name, E.Salary, D.DeptName FROM Employee E, Department D WHERE E.City = 'Delhi';
SELECT E.Name, E.Salary, D.DeptName FROM Employee E, Department D WHERE E.DeptId = D.DeptId AND E.City = 'Delhi';
SELECT Name, Salary, DeptName FROM Employee WHERE City = 'Delhi';
SELECT E.Name, E.Salary, D.DeptName FROM Employee E JOIN Department D WHERE E.City = 'Delhi';
A join query needs BOTH the join condition (E.DeptId = D.DeptId) AND the filter condition (E.City = 'Delhi') connected with AND. Option A is missing the join condition (produces a Cartesian Product filtered by city). Option C cannot get DeptName without joining Department.
Based on the Employee and Department tables, the query SELECT E.Name, D.DeptName FROM Employee E, Department D WHERE E.DeptId = D.DeptId AND E.Salary > 60000; returns which employees?
Priya Sharma and Vikram Singh
Arjun Kumar and Rohan Das
Only Rohan Das
All 6 employees
Employees with Salary > 60000: Arjun Kumar (62000, IT) and Rohan Das (71000, IT). The join condition links them to their department names. Result: Arjun→IT, Rohan→IT.
Which clause CANNOT use aggregate functions like AVG() or SUM()?
SELECT
HAVING
WHERE
ORDER BY
Aggregate functions CANNOT be used in the WHERE clause. They can appear in SELECT, HAVING, and ORDER BY. WHERE filters individual rows before aggregation, so aggregate values are not yet computed at that stage.
Which query finds departments with more than 1 employee?
SELECT Dept FROM Employee WHERE COUNT(*) > 1;
SELECT Dept, COUNT(*) FROM Employee GROUP BY Dept HAVING COUNT(*) > 1;
SELECT Dept, COUNT(*) FROM Employee HAVING COUNT(*) > 1;
SELECT Dept FROM Employee GROUP BY Dept WHERE COUNT(*) > 1;
COUNT(*) > 1 is a group-level condition, so it goes in HAVING (not WHERE). GROUP BY Dept must come before HAVING. WHERE cannot use COUNT(). HAVING without GROUP BY would treat the whole table as one group.
What is the output of the query: SELECT City, COUNT(*) AS Total FROM Employee GROUP BY City HAVING COUNT(*) > 1;
(Delhi:3, Mumbai:2, Bangalore:1)
All three cities
Only Delhi
Delhi and Mumbai
Only Bangalore
HAVING COUNT(*) > 1 keeps only groups with more than 1 row. Delhi has 3 employees (>1 ✅), Mumbai has 2 (>1 ✅), Bangalore has 1 (not >1 ❌). So Delhi and Mumbai are returned.
In a GROUP BY query, which combination is used to both group AND filter groups?
GROUP BY and WHERE
GROUP BY and ORDER BY
GROUP BY and HAVING
WHERE and HAVING
GROUP BY creates the groups; HAVING filters those groups based on a condition (usually involving an aggregate function). This is the standard pairing for grouped queries with conditions.
What does the following query do?
SELECT Dept, COUNT(*) FROM Employee WHERE City = 'Delhi' GROUP BY Dept;
Counts all employees, then filters by Delhi
First removes non-Delhi employees (WHERE), then groups and counts Delhi employees by department
Groups all employees by department, then filters Delhi employees from each group
Shows only departments in Delhi
Execution order: WHERE City='Delhi' runs first and removes Mumbai and Bangalore employees. The remaining Delhi employees are then grouped by Dept and counted. WHERE runs BEFORE GROUP BY.
Equi-Join is also known as which type of join?
Outer Join
Cross Join
Inner Join
Self Join
Equi-Join is a type of Inner Join — it returns only rows where the join condition is satisfied in BOTH tables. Rows without a match in either table are excluded from the result.
A student writes: SELECT Name, DeptName FROM Employee, Department; without any WHERE clause. What is the result?
Names matched with their correct department
Only employees who have a department
A Cartesian Product — every employee paired with every department
An error — you must always use WHERE with multiple tables
Without a WHERE condition linking the tables, this produces a Cartesian Product. Every employee is paired with every department regardless of actual department membership. For 6 employees and 4 departments: 24 rows.
Which of the following correctly uses an alias to write a self-contained Equi-Join?
SELECT Name, DeptName FROM Employee, Department WHERE DeptId = DeptId;
SELECT E.Name, D.DeptName FROM Employee AS E, Department AS D WHERE E.DeptId = D.DeptId;
SELECT Employee.Name, Department.DeptName WHERE Employee.DeptId = Department.DeptId;
SELECT E.Name, D.DeptName FROM Employee E EQUIJOIN Department D;
Using aliases AS E and AS D, then qualifying columns as E.Name and D.DeptName with the join condition E.DeptId = D.DeptId is the correct and unambiguous syntax for an Equi-Join with table aliases.
What is nested grouping in SQL?
Writing a GROUP BY inside another GROUP BY
Using GROUP BY with more than one column to create sub-groups
Nesting a SELECT inside a GROUP BY clause
Using HAVING inside a WHERE clause
Nested grouping means using multiple columns in GROUP BY (e.g., GROUP BY Dept, City). This creates finer sub-groups — each unique combination of (Dept, City) forms its own group.
Which query displays the total salary paid in each city, for cities where more than 2 employees work?
SELECT City, SUM(Salary) FROM Employee WHERE COUNT(*) > 2 GROUP BY City;
SELECT City, SUM(Salary) FROM Employee GROUP BY City HAVING COUNT(*) > 2;
SELECT City, SUM(Salary) FROM Employee GROUP BY City WHERE COUNT(*) > 2;
SELECT City, SUM(Salary) FROM Employee HAVING COUNT(*) > 2 GROUP BY City;
COUNT(*) > 2 is a group condition — it goes in HAVING, not WHERE. GROUP BY comes before HAVING. Correct order: FROM → GROUP BY → HAVING. Option D has HAVING before GROUP BY which is invalid.
What will SELECT MAX(Salary) FROM Employee GROUP BY Dept; return?
One maximum salary for the entire table
The maximum salary for each department — one row per department
The names of employees with the highest salary
An error — MAX cannot be used with GROUP BY
GROUP BY Dept creates three groups. MAX(Salary) finds the highest salary within each group. Result: 3 rows — one maximum salary value per department.
In the Equi-Join result of Employee and Department, how many rows are returned if every employee has a valid DeptId?
Number of rows in Department table
Number of rows in Employee table
Product of both table row counts
Number of unique departments
In an Equi-Join (Inner Join), each Employee row matches exactly one Department row (since DeptId in Employee is a Foreign Key). So the result has the same number of rows as the Employee table — one row per employee.
Which statement is TRUE about the difference between Equi-Join and Natural Join?
Equi-Join is automatic; Natural Join requires explicit conditions
Natural Join can join on multiple common columns at once; Equi-Join joins on one column only
Both always produce identical results regardless of the tables
Natural Join is faster but less precise; Equi-Join gives more explicit control
Natural Join is convenient (no condition to write) but less precise — it joins on ALL shared column names which can sometimes produce unexpected results. Equi-Join gives the programmer explicit control over exactly which columns to join on.
Which query shows each department name with the count of employees, but only for departments with at least 2 employees, sorted by count in descending order?
SELECT D.DeptName, COUNT(*) AS Total FROM Employee E, Department D WHERE E.DeptId = D.DeptId GROUP BY D.DeptName HAVING COUNT(*) >= 2 ORDER BY Total DESC;
SELECT D.DeptName, COUNT(*) AS Total FROM Employee E, Department D GROUP BY D.DeptName HAVING COUNT(*) >= 2 ORDER BY Total DESC;
SELECT D.DeptName, COUNT(*) AS Total FROM Employee E, Department D WHERE E.DeptId = D.DeptId AND COUNT(*) >= 2 ORDER BY Total DESC;
SELECT D.DeptName, COUNT(*) AS Total FROM Employee E, Department D WHERE E.DeptId = D.DeptId ORDER BY Total DESC HAVING COUNT(*) >= 2;
This query needs: a join condition (WHERE E.DeptId = D.DeptId), grouping (GROUP BY D.DeptName), group filtering (HAVING COUNT(*) >= 2), and sorting (ORDER BY Total DESC). Option A has all in the correct order.
A student wants to display each department name and its average salary. They write: SELECT DeptName, AVG(Salary) FROM Employee GROUP BY DeptName; — What is wrong with this query?
AVG cannot be used with GROUP BY
DeptName is not in the Employee table — it is in the Department table, so a join is needed
GROUP BY DeptName should be GROUP BY Salary
There is nothing wrong — this query is correct
DeptName is stored in the Department table, not in the Employee table. To display DeptName with salary data from Employee, you need an Equi-Join: FROM Employee E, Department D WHERE E.DeptId = D.DeptId GROUP BY D.DeptName.
What does the following query display?
SELECT Dept, MIN(Salary), MAX(Salary) FROM Employee GROUP BY Dept;
One row with the overall minimum and maximum salary
The minimum and maximum salary for each department — three rows
Only the department with the lowest minimum salary
An error — MIN and MAX cannot be used together
GROUP BY Dept creates 3 groups (Sales, IT, HR). MIN(Salary) and MAX(Salary) are both computed for each group. Result: 3 rows, each showing a department with its min and max salary.
How many columns does SELECT * FROM Employee NATURAL JOIN Department; return compared to SELECT * FROM Employee, Department WHERE Employee.DeptId = Department.DeptId;?
Natural Join returns more columns
Both return the same number of columns
Natural Join returns fewer columns — it removes the duplicate DeptId column
Equi-Join returns fewer columns
Natural Join removes duplicate columns — DeptId (common to both tables) appears only once in the Natural Join result. The Equi-Join with * returns DeptId twice (once from each table). So Natural Join has one fewer column.
Which query correctly displays the department name and total salary for departments where total salary exceeds 100000?
SELECT D.DeptName, SUM(E.Salary) FROM Employee E, Department D WHERE E.DeptId = D.DeptId GROUP BY D.DeptName HAVING SUM(E.Salary) > 100000;
SELECT D.DeptName, SUM(E.Salary) FROM Employee E, Department D WHERE E.DeptId = D.DeptId AND SUM(E.Salary) > 100000 GROUP BY D.DeptName;
SELECT D.DeptName, SUM(E.Salary) FROM Employee E, Department D GROUP BY D.DeptName HAVING SUM(E.Salary) > 100000;
SELECT D.DeptName, SUM(E.Salary) FROM Employee E, Department D WHERE SUM(E.Salary) > 100000 GROUP BY D.DeptName;
The query needs: join condition in WHERE, GROUP BY for the department name, and HAVING for the aggregate condition (SUM > 100000). SUM cannot be used in WHERE. Option C is missing the join condition.
In SQL, the execution order of clauses in a grouped query is:
SELECT → WHERE → FROM → GROUP BY → HAVING → ORDER BY
FROM → GROUP BY → WHERE → HAVING → SELECT → ORDER BY
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
WHERE → FROM → GROUP BY → SELECT → HAVING → ORDER BY
The logical execution order is: FROM (identify tables) → WHERE (filter rows) → GROUP BY (create groups) → HAVING (filter groups) → SELECT (compute output) → ORDER BY (sort). This differs from the writing order.
A query joins Employee and Department tables without any WHERE condition and returns 30 rows. If the Department table has 5 rows, how many rows does the Employee table have?
25
35
6
150
Without a WHERE condition, the result is a Cartesian Product. If the result is 30 rows and Department has 5 rows, then Employee must have 30 ÷ 5 = 6 rows.