GATE CSE 2015 | Set 1 | Question: 27

Question

Consider the following relation:

$$\overset{\text{Student}}{\begin{array}{|c|c|}\hline\\
\underline{\textbf{Roll_No}}&    \textbf{Student_Name}\\\hline
1&     \text{Raj}  \\ \hline   
2&     \text{Rohit}\\     \hline
3&     \text{Raj}      \\\hline
\end{array}} \qquad\overset{\text{Performance}}{\begin{array}{|c|l|r|c|}\hline\\
\underline{\textbf{Roll_No}}&   \underline{\textbf{Course}}&  \textbf{Marks} \\\hline
1&     \text{Math}&   80 \\ \hline   
1&     \text{English}& 70 \\     \hline
2&     \text{Math}&  75         \\\hline
3&     \text{English}& 80      \\\hline
2&     \text{Physics}&     65 \\\hline    
3&     \text{Math}& 80     \\\hline   
\end{array}}$$

Consider the following SQL query.

SELECT S.Student_Name, Sum(P. Marks) 
FROM Student S, Performance P
WHERE S.Roll_No= P.Roll_No
GROUP BY S.STUDENT_Name

The numbers of rows that will be returned by the SQL query is_________________.

Comments

Detailed Video Solution, with a Quick Trick: https://youtu.be/zb6vXpZVo1E?t=5275&feature=shared

---

GROUP BY student_name so roll no. doesnot matters
Raj
Rohit
2  tuples

---

sneaky question

Answer 1

For answering there is no need to execute the query, we can directly answer this as $2$

How?

Group by Student_Names

It means all name that are same should be kept in one row.

There are $3$ names. But in that there is a duplicate with Raj being repeated $\implies$ Raj produces one row and Rohit produces one row $\implies$ Total $2$ rows.

---

For better understanding, I'll just analyze the whole query

1$^{\text{st}}$ statement which is executed from the query is From Clause $:$ From Student S, Performance P

$\implies$ cross product of those two tables will be
$$\begin{array}{|l|l|l|l|l|} \hline \textbf{S.RollNo} & \textbf{S.Student_name} & \textbf{P.Roll_no} & \textbf{P.Course} & \textbf{P.marks} \\\hline 1 & \text{Raj} & 1 & \text{Maths} & 80 \\\hline  1 & \text{Raj} & 1 & \text{English} & 70 \\\hline 1 & \text{Raj} & 2 & \text{Maths} & 75 \\\hline 1 & \text{Raj} & 3 & \text{English} & 80 \\\hline  1 & \text{Raj} & 2 & \text{Physics} & 65 \\\hline 1 & \text{Raj} & 3 & \text{Maths} & 80 \\\hline 2 & \text{Rohit} & 1 & \text{Maths} & 80 \\\hline 2 & \text{Rohit} & 1 & \text{English} & 70 \\\hline 2 & \text{Rohit} & 2 & \text{Maths} & 75 \\\hline 2 & \text{Rohit} & 3 & \text{English} & 80 \\\hline 2 & \text{Rohit} & 2 & \text{Physics} & 65 \\\hline 2 & \text{Rohit} & 3 & \text{Maths} & 80 \\\hline 3 & \text{Raj} & 1 & \text{Maths} & 80 \\\hline 3 & \text{Raj} & 1 & \text{English} & 70 \\\hline 3 & \text{Raj} & 2 & \text{Maths} & 75 \\\hline 3 & \text{Raj} & 1 & \text{English} & 80 \\\hline 3 & \text{Raj} & 2 & \text{Physics} & 65 \\\hline 3 & \text{Raj} & 3 & \text{Maths} & 80 \\\hline \end{array}$$

2$^{\text{nd}}$ statement which is executed from the query is Where Clause $:$ Where S.Roll_no = P.Roll_no

$\implies$ delete those rows which does not satisfy the WHERE condition. Then the result will be

$$\begin{array}{|l|l|l|l|l|} \hline \textbf{S.RollNo} & \textbf{S.Student_name} & \textbf{P.Roll_no} & \textbf{P.Course} & \textbf{P.marks} \\\hline 1 & \text{Raj} & 1 & \text{Maths} & 80 \\\hline  1 & \text{Raj} & 1 & \text{English} & 70 \\\hline 2 & \text{Rohit} & 2 & \text{Maths} & 75 \\\hline 2 & \text{Rohit} & 2 & \text{Physics} & 65  \\\hline  3 & \text{Raj} & 3 & \text{English} & 80 \\\hline 3 & \text{Raj} & 3 & \text{Maths} & 80 \\\hline  \end{array}$$

3$^{\text{rd}}$ statement which is executed from the query is Group by Clause $:$ Group by S.Student_Name

$\implies$ Merge those rows which are having same name, then result will be
$$\begin{array}{|l|l|l|l|l|} \hline \textbf{S.RollNo} & \textbf{S.Student_name} & \textbf{P.Roll_no} & \textbf{P.Course} & \textbf{P.marks} \\\hline \{1,1,3,3\} & \text{Raj} & \{1,1,3,3\} & \text{\{Maths, English\}} & \{80,70,80,80\} \\\hline  2 & \text{Rohit} & 2 & \text{\{Maths, Physics\}} &\{75,65\} \\\hline  \end{array}$$

Note that, this can't be used as final result as it violates 1NF (multiple values in each tuple for $\text{S.Roll_no}, \text{P.Roll_no}, \text{P.Course}$ and $\text{P.marks})$

4$^{\text{th}}$ statement which is executed from the query is Select Clause $:$ Select S.Student_Name, SUM(P.marks)

$\implies$ Delete un-necessary columns and calculate the aggregate functions, then result will be$$\begin{array}{|l|l|} \hline \textbf{S.Student_name} & \textbf{P.marks} \\\hline \text{Raj} & 310 \\\hline \text{Rohit} & 140 \\\hline \end{array}$$

Comments

The Answer is 2.
2 rows will be returned .

---

Have u run it?

---

yes, i run it....2 rows will be returned...6 is not answer,thank you...

---

For s.roll n o= p.roll no 6 rows will be returned ,then because of group by clause,2 rows will be returned..raj and rohit.

---

@arjun sir GROUP BY allows duplicates so why not 3 here?

---

@habibkhan

Sir, can you please explain how this query is actually working in some detail ?

---

What would be the answer if there were no Group By

---

The query will be wrong since we need no use aggregate function sum. means here aggregate function using by grouping name.

---

Are u sure about this?

---

SELECT S.studentName, Sum(p.marks) 
FROM student S, performance P
WHERE S.rollno= P.rollno

This query will return only 1 Row. Confused ?

SELECT distinct S.studentName, Sum(p.marks) 
FROM student S, performance P
WHERE S.rollno= P.rollno

This will also retun 1 row

SELECT S.studentName 
FROM student S, performance P
WHERE S.rollno= P.rollno

This will return 6 rows.

SELECT distinct S.studentName 
FROM student S, performance P
WHERE S.rollno= P.rollno

This will return 2 rows

Excuted on MySQL 5.6. Hope this will clear all doubts

---

why do first two queries return 1 row ? because of the sum ryt?

and yes only on using the keyword distinct , the query should return 2 rows.

---

What is the explanation for this?

---

@ Arjun  sir.

Can you give a brief explanation of this answer? Thanks in advance..

---

@Shaik Masthan

@Magma

can u pls expalin this?

i am not getting it;

am getting 3 as answer.

---

i edited the answer !

---

The output is fine but can anyone tell me why after group by the first item of tuples like rollno is always selected?

---

Love this answer, thanks

---

@naresh1845 

table of 2nd statement , last Row of 1st column R.Roll_No should be 3 instead of 1 . please correct this .

---

If we were printing (Roll no) also along with other 2 then 3 rows will be returned.

If we were printing both (Roll no.,Course number) along with others , then 6 rows will be printed.

 

Correct me if i m wrong?

---

Amcodes

how can you do that while maintaining sum(Marks)?

---

hats of brother for the answer  such clearly explained

---

Excellent explanation

---

Thank u vro for the detailed explanation.

---

GROUPBY removes duplicate tuples

---

trick here is the number of grps will be the answer no need to evaluate the whole query

---

we only get atmost one tuple for one group in table

Answer 2

Below is result of given query. Note that there are only two student names and query prints sum(P.Marks) for every student.

  Student_Name     Marks
      Raj           310
     Rohit          140

Comments

@Regina Phalange Perfect Answer, Ma'am!

---

superb @phalanay ji

---

Are you Phoebe Buffay or Regina Phalange ???

---

Manjyot Singh Nanra LOL, I knew I had heard this name before.

Answer 3

ANS: 2

AND NOT 3.

Even though there is NO ' select distinct ' is used in this query, it will return two rows because its grouping the rows by 'student_Name'  and hence it will take sum according to Student_name. and we will get this:

Raj 310

Rohit 140

If query is like this:

SELECT S. student_Name, Sum(p. Marks) 
FROM student S, performance P
WHERE S.Roll_No= P.Roll_No
GROUP BY S.Roll_No  <--- changing here 

Then it will definitely return 3 rows and it will contain duplicates as

Raj 150

Rohit 140

Raj 160

Please correct me if I wrong.

Comments

No, Group by must have all the attributes present in the Select clause other than aggregate function, if aggregate function is present in select.

---

Bhushan Your query is not correct. ((reason- mentioned in above comment.

Answer 4

2 Rows
one for each unique name grouped

Answer 5

2 rows will be returned

Best Answer

Comments

"Best Answer"- is opinion. Sometimes it is unanimous too. However by writing "Best Answer" in the answer, no answer will be treated as best answer.