What data will the following query return:
SELECT 
    DISTINCT COUNT(T2.F_VAL) AS F,
    COUNT(DISTINCT T1.P_VAL) AS P
FROM
    T1, T2
    WHERE T1.P_KEY=T2.F_KEY
given following T1 and T2 tables:

T1
(
    P_KEY INTEGER,
    P_VAL VARCHAR(20)
)

P_KEY | P_VAL
--------------
1     | UK
2     | Russia
NULL  | Ukraine

T2
(
    P_KEY INTEGER,
    F_KEY INTEGER,
    F_VAL VARCHAR(20)
)

P_KEY | F_KEY | F_VAL
---------------------
1     | 1     | London
2     | 2     | Moscow
NULL  | 2     | St. Petersburg
NULL  | NULL  | Kiev
Explanation

SELECT 
    T2.F_VAL,
    T1.P_VAL
FROM
    T1, T2
    WHERE T1.P_KEY=T2.F_KEY
query basically performs inner join of T1 and T2 tables on P_KEY and F_KEY columns respectively and will return the following data:

F_VAL          | P_VAL
------------------------
London         | UK
Moscow         | Russia
St. Petersburg | Russia
“Kiev, Ukraine” record will not be returned by it as NULL to NULL comparison is not equal to TRUE (but it is not equal to FALSE either).
COUNT(DISTINCT T1.P_VAL) AS P
will return 2 as DISTINCT keyword inside aggregate function guarantees that distinct values will be counted only (repeatable values will be excluded).
COUNT(T2.F_VAL) AS F
will return 3.
Ultimately the SELECT DISTINCT clause will return 3, 2.
Strictly speaking, the DISTINCT keyword is redundant here as query returns only 1 record.
SQL
SQL Quiz
Start Quiz

or Read more about SQL Quiz

Follow CodeGalaxy

Mobile Beta

Get it on Google Play
Send Feedback