2 rows will be returned .

The Gateway to Computer Science Excellence

+30 votes

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

+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 $:$

$\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 $:$

$\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 $:$

$\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 $:$

$\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}$$

+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

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

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

+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

Student_Name Marks

Raj 310

Rohit 140

+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 BYS.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.

- All categories
- General Aptitude 1.9k
- Engineering Mathematics 7.5k
- Digital Logic 2.9k
- Programming and DS 4.9k
- Algorithms 4.4k
- Theory of Computation 6.2k
- Compiler Design 2.1k
- Databases 4.1k
- CO and Architecture 3.4k
- Computer Networks 4.2k
- Non GATE 1.4k
- Others 1.4k
- Admissions 595
- Exam Queries 573
- Tier 1 Placement Questions 23
- Job Queries 72
- Projects 18

50,737 questions

57,385 answers

198,558 comments

105,370 users