SQL Problem 1

Question

Consider the following relational schema and instance R(A, B, C):

My Answer is 3 because When T1.A = 4 only 1 record will be printed with T2.A = 6 and likewise for when T1.A = 2 only one record will be printed but none for T1.A = 6. Am i right ?

Comments

Not sure if you already know about this or not but it is worth sharing for other readers:

https://stackoverflow.com/questions/6319183/aggregate-function-in-sql-where-clause

Answer 1

Answer is 3 tuples but not because of the reasoning your provided.

In the inner related subquery "count *" is returned, i.e., number of tuples that match the condition T1.A < T2.A

If the subquery returns at least one record in its result set, the EXISTS clause will evaluate to true and the EXISTS condition will be met. If the subquery does not return any records, the EXISTS clause will evaluate to false and the EXISTS condition will not be met.

For T1.A=4, count * returns a single record with value 1. Thus row corresponding to A=4 is printed.

For T1.A=2, count * returns a single record with value 2. Thus row corresponding to A=2 is printed.

For T1.A=6, count * returns a single record with NULL. But since it is a record, even if it contains NULL, the EXISTS clause becomes true. Thus row corresponding to A=6 is printed.

Comments

No, that's another point. Go through this first:

https://www.techonthenet.com/sql/exists.php

What I mean to say is that all aggregate functions returns at least one record/row/tuple and more if group by is used. Output containing only NULL is still a row. For example,

count

NULL

This means the number of rows it has is 1, not 0. A table with 0 rows is like this(when no condition is met):

A

Here "A" is an attribute.

---

Sir please clear my one more doubt :- 

I executed above code and i got only 3 tuples My doubt is :- 

For one record of outer query the inner query is executed completely right. So when Say T1.A = 4 then only for T2.A = 6 one record will be printed " 4 3 4 ". Now when T1.A = 2 , For T2.A = 4 and T2.A = 6 it should execute i.e. should print " 2 7 2" two times right (because no distinct command is applied) and when T1.A = 6 last tuple is printed.

But sir why the order and number of output differs ?

---

Screenshot isn't clear enough to see the query. Try to crop it.

Answer 2

count(*) always provides a number(count) even though it may be 0 or some thing else (1,2,...etc)

 

therefore exists always true for each record..... that implies ans=no.of rows in your instance