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

Question

Consider the following relations.$$\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).

What does the following SQL query give? (Mark all the appropriate options)

SELECT Emp.EmpId, Emp.EmpName FROM Emp,Dept 
    WHERE Emp.DeptNo = Dept.DeptId 
    group by Dept.DeptId having Dept.DeptName = 'ECE'

• A. It gives the id, name of the employees who works for 'ECE' department
• B. It gives the name of the employees whose DeptNo is 2
• C. It gives the id,name of the employees whose DeptNo is either 2 or 3
• D. The query has error

Answer 1

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.

Comments

@gatecse sir,

so we can say , all attributes present in Select must be present in Group By clause?

---

yes we can say but in reverse

any attribute which is in Group by, has to be in Select also

This proposition is valid

---

Hey @StoneHeart please update your comment. ‘Valid’ should be actually ‘invalid’.

---

@gatecse sir even

select max(EmpId), DeptId

will give error too. Because we have used Dept.DeptName in the HAVING clause instead of Dept.DeptId

Answer 2

All non-aggregate functions that appear in SELECT clause must be mentioned in GROUPBY clause,otherwise error,

where as aggregate functions can be applied to any attributes.