Ravindrababu_SQL

Question

Consider the above table A. How many tuples does the result of the following SQL queries contain ?
Q1: select Location from A where AreaCode in (101, 103);
Q2: Select Location from A where AreaCode = 101
UNION
Select Location from A where AreaCode = 103;( Marks: 0.00 )

1.   4, 6
2.   4, 4
3.   5, 5
4.   6, 4
   Explanation:
   Q1: We have to select location where Areacode is either 101 or 103
   Output of the query is Hyderabad
   NULL
   Kolkata
   Chennai
   Hyderabad 
   NULL
   ∴ The above query output contains 6 tuples
   
   
   (We know that UNION operation considers two NULL values as same and so it eliminates duplicates in the final result)
   So, Q2 output consists of 4 tuples.

Is it true that NULL values are treated as same.when it is told that NULL IS a unknown value then how it is compared TWO NULL value to be same and count distinctly as one value itself.Whereas in contrary in SELECT COUNT(*) NULL value is counted as many times it occur. please guide me how deal with NULL values in diferent occassions.THANKS in ADVANCE 

Answer 1

unknown u unknown is unknown

unknown u T= T

unknown  u unknown =unknown

unknown $\Delta$ F= F

so null as unknown
null U null is still null

which is counted as 1
i hope u got my point

Answer 2

Ideally, any sql operation must be considered as a relation where duplicates arent allowed. But, since eliminating duplicates is a costly operation, SQL allows duplicates in the result. So, to find distict tuples, we use the 'distinct' keyword.

So, count(*) counts all the tuples irrespective of the duplicates.

and, count(distict(*)) will count the no. of unique tuples.