edited by
17,039 views
49 votes
49 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_________________.

edited by

4 Answers

Best answer
108 votes
108 votes

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

edited by
21 votes
21 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
11 votes
11 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.

6 votes
6 votes
2 Rows
one for each unique name grouped
Answer:

Related questions

80 votes
80 votes
7 answers
3
makhdoom ghaya asked Feb 13, 2015
28,737 views
The least number of temporary variables required to create a three-address code in static single assignment form for the expression $q + r / 3 + s - t * 5 + u * v/w$ is_...
32 votes
32 votes
9 answers
4
makhdoom ghaya asked Feb 13, 2015
24,372 views
Let G be a connected planar graph with 10 vertices. If the number of edges on each face is three, then the number of edges in G is_______________.