edited by
10,555 views
43 votes
43 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}$
edited by

5 Answers

Best answer
56 votes
56 votes

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
44 votes
44 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.
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
Answer:

Related questions

38 votes
38 votes
3 answers
1
Ishrat Jahan asked Nov 2, 2014
10,865 views
Consider two tables in a relational database with columns and rows as follows:$$\overset{\text{Table: Student}}{\begin{array}{|c|c|c|} \hline \textbf {Roll_no} & \textbf{...
43 votes
43 votes
6 answers
4
Ishrat Jahan asked Nov 2, 2014
12,144 views
Consider the following schedule $S$ of transactions $T1$ and $T2:$$${\begin{array}{l|l}\textbf{T1}& \textbf{T2} \\\hline\text{Read(A)} \\\text{A = A – 10}\\& \text...