The Gateway to Computer Science Excellence
+30 votes
4.3k views

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_________________.

in Databases by Boss (30.8k points)
edited by | 4.3k views

4 Answers

+49 votes
Best answer

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$^{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$^{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 1 & \text{Raj} & 3 & \text{Maths} & 80 \\\hline  \end{array}$$

3$^{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$^{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}$$

by Active (1.4k points)
edited by
0
The Answer is 2.
2 rows will be returned .
0
Have u run it?
0
yes, i run it....2 rows will be returned...6 is not answer,thank you...
+1
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.
0
@arjun sir GROUP BY allows duplicates so why not 3 here?
0
@habibkhan

Sir, can you please explain how this query is actually working in some detail ?
0
What would be the answer if there were no Group By
0
The query will be wrong since we need no use aggregate function sum. means here aggregate function using by grouping name.
0
Are u sure about this?
0
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

0
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.
+1
What is the explanation for this?
0

@ Arjun  sir.

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

0

@Shaik Masthan

@Magma

can u pls expalin this?

i am not getting it;

am getting 3 as answer.

+1
i edited the answer !
0

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

+17 votes
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
by Loyal (9.9k points)
+1

@Regina Phalange Perfect Answer, Ma'am!

0
superb @phalanay ji
0
Are you Phoebe Buffay or Regina Phalange ???
+10 votes

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.

by (229 points)
+4
No, Group by must have all the attributes present in the Select clause other than aggregate function, if aggregate function is present in select.
0
Bhushan Your query is not correct. ((reason- mentioned in above comment.
+6 votes
2 Rows
one for each unique name grouped
by (71 points)
Answer:

Related questions

Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
50,737 questions
57,385 answers
198,558 comments
105,370 users