GATE Overflow Test Series | Databases | Test 1 | Question: 19

Question

Consider the following relation.
$$\textbf{Emp}$$$$\begin{array}{|c|c|c|c|c|c|c|}
\hline
\underline{\textbf{EmpId}} & \textbf{DeptNo} & \textbf{EmpName} & \textbf{Salary} \\
\hline
1 & 5 & \text{Alex}&10,000 \\
\hline
2 & 2 & \text{Steve}&20,000 \\
\hline
3 & 1 & \text{Bob} &15,000 \\
\hline
4 & 2 & \text{Smith}&18,000 \\
\hline
5 & 4 & \text{Rakul}&24,000 \\
\hline
6 & 3 & \text{Rhea}&17,000 \\
\hline
\end{array}$$
$$\textbf{Dept}$$$$\begin{array}{|c|c|c|c|c|c|c|}
\hline
\underline{\textbf{DeptId}} & \textbf{DeptName} \\
\hline
1 & \text{CSE} \\
\hline
2 & \text{ECE} \\
\hline
3 & \text{ECE} \\
\hline
4 & \text{IT} \\
\hline
5 & \text{ME} \\
\hline
\end{array}$$
Here, Emp(DeptNo) is the foreign key referring to Dept(DeptId).

The number of tuples returned by the following SQL query is _____

SELECT max(Salary) FROM Emp, Dept
    WHERE Emp.DeptNo = Dept.DeptId group by Dept.DeptId;

Comments

mistake : grouping Id not the Dept name

Answer 1

We are grouping by DeptId and so 5 tuples will be returned one for each distinct DeptId.

Comments

Referring to this question, I concluded that two statements,  

https://gateoverflow.in/347488/gate-overflow-test-series-databases-test-1-question-18 

During a group by, we cannot include any attribute in the select which is not part of the group by. So, select EmpId is wrong here so is select EmpName. But we can do select max(EmpId), DeptId.

This SELECT and GROUPBY clause creates a lot of confusion, 

https://gateoverflow.in/47/gate-cse-2012-question-15 → In one of the comments @Deepak Poonia is saying

All attributes used in the GROUP BY clause must appear in the SELECT clause (Ref :here)

 

---

• A column used in an expression of the SELECT clause must be in the GROUP BY clause. Otherwise, the expression using that column is an aggregate function.
  I think reason for this is straightforward and well explained here though recent SQL standards have relaxed this.

• A GROUP BY expression can only contain column names from the select list, but not those used only as arguments for vector aggregates.
  I don’t think this statement is meaning that all column names in select clause must be in group by clause. I guess by “select list” it is meaning the list of columns from the table and not the ones we are filtering through the “select clause”. They are trying to emphasize that operations like “sum, average” etc must not be used in GROUP BY expression (they can be in HAVING expression). But they should not have used the words “select list” in the sentence to avoid confusion.

---

Got it, Thanks a lot!