7,255 views

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

null  =  unknown

unknown+5  =  unknown

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

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$

by

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

So all aggregate functions except count will ignore NULL values ?

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

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.

Count(*) will return 4.
Count(marks) =3 right??
Yes.
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
by

@rajan ...plz see my ans.
thnx man :)