RSS Feed

Multiple Choice Questions - SQL Joins - Set 4

Multiple Choice Questions - SQL Joins - Set 4

1.
Table A
A
1
2
3
4

Table B
B
3
4
5
6

The result of both the below queries will be same.

SELECT * FROM A JOIN B ON A.A = B.B
SELECT * FROM A INNER JOIN B ON A.A = B.B

A) True
B) False

2.
SQL Server has the ability to detect that some joins are not needed to return results to the user and can skip them.

A) True
B) False

3.
Table A
A
1
2
3
4

Table B
B
3
4
5
6

SELECT * FROM A JOIN B ON A.A = A.A

A) The above query will return an error.
B) The above query will run successfully.

4.
If you have a join based on multiple attributes, and you specify at least one expression but forget the others, neither syntax will trap the error.

A) True
F) False

5.
The . . . . . operator returns each row from the first (top) input when there is a matching row in the second (bottom) input. If no join predicate exists in the Argument column, each row is a matching row.

A) Right Anti Semi Join
B) Right Semi Join
C) Left Semi Join
D) Left Anti Semi Join

6. 
Select * from orders inner remote join customer
on orders.customer_id = customer.customer_id
where quantity > 50
In the above query:
A) The REMOTE join hint tells that the join operation is performed on the client hosting the right table.
B) The REMOTE join hint tells that the join operation is performed on the client hosting the left table.
C) The REMOTE join hint tells that the join operation is performed on the server hosting the left table.
D) The REMOTE join hint tells that the join operation is performed on the server hosting the right table.

7.
Table A
Col1 Col2 Col3
a b c
e null f
h i j
l null m
o null p

Table B
Col1 Col2 Col4
a b d
e null g
h i k
l null n
o null q

Result:
Col1 Col2 Col3 Col4
a b c d
e null f g
h i j k
l null m n
o null p q

Which of the following query or queries will give the above result when Table A and Table B are joined:
A) 
SELECT A.Col1, A.Col2, A.Col3, B.Col4
FROM A INNER JOIN B
ON (A.Col1 = B.Col1 or A.Col1 is NULL and B.Col1 is NULL)
AND (A.Col2 = B.Col2 or A.Col2 is NULL and B.Col2 is NULL)
B) 
SELECT A.Col1, A.Col2, A.Col3, B.Col4
FROM A INNER JOIN B
ON (coalesce(A.Col1, '') = coalesce(B.Col1, ''))
AND (coalesce(A.Col2, '') = coalesce(B.Col2, ''))
C) 
SELECT A.Col1, A.Col2, A.Col3, B.Col4
FROM A 
INNER JOIN B
   ON A.Col1 = B.Col1 
   AND ISNULL(A.Col2, '****') = ISNULL(B.Col2,'****')
D) All of above

8.
A self-join is a table that is joined to itself. Insert or update operations that are based on a self-join may or may not follow the order in the FROM clause.

A) True
B) False

9.
Table A
A
1
2
3
4

Table B
B
3
4
5
6

Table C
C
4
5
6
7

Table D
D
5
6
7
8
4

Below two queries are run (using above four tables):
select * from A inner Join B 
on A.A = B.B
inner Join C 
on B.B = C.C
Left outer Join D 
on C.C = D.D

select * from A inner Join B 
on A.A = B.B
inner Join C 
on B.B = C.C
Inner Join D 
on C.C = D.D
A) the result of both the queries will be different.
B) the result of both the queries will be same.
C) second query will result in an error.
D) first query will result in an error.

10.
Unlike the nested loops join, which supports any join predate, the merge join requires at least one . . . . predicate.

A) LIKE
B) BETWEEN
C) EQUIJOIN
D) EXISTS

Answers