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

edited | 4.3k views

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
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
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;

+1
0

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

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

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

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.
2 Rows
one for each unique name grouped
by (71 points)