in Databases edited by
7,255 views
37 votes
37 votes

A table T1 in a relational database has the following rows and columns: 
$$\begin{array}{|c|c|c|} \hline \text {Roll no. } & \text {Marks} \\\hline 1&  10 \\\hline 2 & 20 \\\hline3 & 30  \\\hline 4 & \text{NULL}\\\hline \end{array}$$
The following sequence of SQL statements was successfully executed on table T1.

Update T1 set marks = marks + 5
Select avg(marks) from T1


What is the output of the select statement?

  1. $18.75$
  2. $20$
  3. $25$
  4. $\text{Null}$
in Databases edited by
7.3k views

2 Comments

 

null  =  unknown 

unknown+5  =  unknown

 

5
5
Expect for count all other aggregate functions will ignore null values and count counts the number of rows its not considered about the value within it it just counts and adds to the result if it remains after being filtered out by the where conditions
0
0

5 Answers

49 votes
49 votes
Best answer

Update on null gives null. Now, avg function ignores null values. So, here avg will be $(15 + 25 + 35) / 3 = 25.$

http://msdn.microsoft.com/en-us/library/ms177677.aspx

Correct Answer: $C$

edited by
by

4 Comments

count(∗) won't ignore NULL, count(k)  if k is an attribute ignores NULL.

0
0
So all aggregate functions except count will ignore NULL values ?
0
0
40 votes
40 votes

Must know CONCEPTS from Korth before attempting this:--

  1. Since the special value null indicates “value unknown or nonexistent,” any arithmetic operations (such as +,−, ∗, /) involving null values must return a null result. i.e. means 5+null = null.
  2. IMP for this question:-Unlike arithmetic expression handling of null for aggregated attributes(here avg) is done in different way in sql  , the operation deletes null values at the outset, before applying aggregation.
  3. Extra Note:- Here after removal of null from outset If the resultant multiset is empty, the aggregate result is null. (means if every entry of marks column is null then result will be null)

Step1:-we run update query successfully so we left with Marks(15,25,35,null)

Step2:- Select avg(marks) from T1

Explanation:- Follow Concept#2

Here avg(marks) will first remove the null value from the multiset before applying aggregation.
so we left with Marks(15,25,35)
so avg will be (15+25+35) / 3 =25 Hence Option C is ans.

4 Comments

Count(*) will return 4.
1
1
Count(marks) =3 right??
1
1
Yes.
1
1
2 votes
2 votes
update on null will be null. but u can not say that  null +sommething = null  that is wrong bcz .we do sum of marks thats on it we have null . but we dont have sum as null

for compute avg we count the rows which are not having null. means in count jis row m null h wo hm count nhi krenge . and null value never be  assumed to be 0 . bcz null is unknown
edited by
by

2 Comments

@rajan ...plz see my ans.
0
0
thnx man :)
0
0
0 votes
0 votes
Answer:

Related questions