Multiple Choice Questions - SQL JOIN Set - 2
1. A cross join without WHERE clause returns a Cartesian product.
A) True
B) False
2. List all orders, showing order number, customer name and credit limit of a customer.
Orders Table:
Order_Number, Customer, Product_Amount
Customers Table:
Customer_Number, Customer_Name, Credit_Limit
Which of the below query satisfy the above question:
A)
Select Order_Numer, Customer_Name, Credit_Limit
from Customers, Orders
where Customer = Customer_Number
B)
Select Order_Numer, Customer_Name, Credit_Limit
from Customers INNER JOIN Orders
ON Customers.Customer_Number = Orders.Customer;
C)
Both
D)
None
3. Joins are limited to only primary and foreign keys.
A) True
B) False
4. Which one of the below is an ANSI Syntax
A)
SELECT tablel.column, table2. column2 FROM table1, table2
WHERE tablel. columnl = table2. column2;
B)
SELECT tablel.column, table2. column2
FROM table1 INNER JOIN table2
ON tablel. columnl = table2. column2;
C)
Both
D)
None
5. Table 1
Table 2
Consider the below query which uses Table 1 & Table 2:
SELECT tab1.*,tab2.*
FROM Table1 tab1
INNER JOIN Table2 tab2 ON tab1.ID = tab2.ID
Result of the above query is:
A)
B)
C)
D)
6. With an outer join the columns from the table where data is "missing" are returned as NULL values.
A) True
B) False
7. Consider the Table 1 & Table 2 from Question 5.
SELECT tab1.*,tab2.*
FROM Table1 tab1
FULL OUTER JOIN Table2 tab2 ON tab1.ID = tab2.ID
WHERE tab1.ID IS NULL OR tab2.ID IS NULL
Result of above query will be:
A)
B)
C)
D)
8. For an Inner Join it is very important, which table is first one and which second one.
A) True
B) False
9. Consider the Table 1 & Table 2 from Question 5.
UPDATE
table1
SET
Value = 'unmatch'
FROM
table2
INNER JOIN
table1
ON
table1.ID = table2.ID
Result of the above query will be:
A)
Table 1
Table 2
B)
Table 1
Table 2
C)
Table 1
Table 2
D)
None of Above
10. You can have a maximum of 256 tables in a single Select statement in SQL SERVER 2005 and Tables per SELECT statement in SQL SERVER 2012 are
A) 4096
B) 256
C) 2094
D) Limited only by available resources
Answers
1. A cross join without WHERE clause returns a Cartesian product.
A) True
B) False
2. List all orders, showing order number, customer name and credit limit of a customer.
Orders Table:
Order_Number, Customer, Product_Amount
Customers Table:
Customer_Number, Customer_Name, Credit_Limit
Which of the below query satisfy the above question:
A)
Select Order_Numer, Customer_Name, Credit_Limit
from Customers, Orders
where Customer = Customer_Number
B)
Select Order_Numer, Customer_Name, Credit_Limit
from Customers INNER JOIN Orders
ON Customers.Customer_Number = Orders.Customer;
C)
Both
D)
None
3. Joins are limited to only primary and foreign keys.
A) True
B) False
4. Which one of the below is an ANSI Syntax
A)
SELECT tablel.column, table2. column2 FROM table1, table2
WHERE tablel. columnl = table2. column2;
B)
SELECT tablel.column, table2. column2
FROM table1 INNER JOIN table2
ON tablel. columnl = table2. column2;
C)
Both
D)
None
5. Table 1
| ID | Value |
|---|---|
| 1 | One |
| 2 | Two |
| 3 | Three |
| 4 | Four |
| 5 | Five |
Table 2
| ID | Value |
|---|---|
| 1 | One |
| 2 | Two |
| 3 | Three |
| 6 | Six |
| 7 | Seven |
Consider the below query which uses Table 1 & Table 2:
SELECT tab1.*,tab2.*
FROM Table1 tab1
INNER JOIN Table2 tab2 ON tab1.ID = tab2.ID
Result of the above query is:
A)
| ID | Value | ID | Value |
|---|---|---|---|
| 1 | One | 1 | One |
| 2 | Two | 6 | Six |
| 3 | Three | 3 | Three |
| ID | Value | ID | Value |
|---|---|---|---|
| 1 | One | 1 | One |
| 2 | Two | 2 | Two |
| 3 | Three | 3 | Three |
| ID | Value | ID | Value |
|---|---|---|---|
| 1 | One | 1 | One |
| 2 | Two | 2 | Two |
| 3 | Three | 7 | Seven |
| ID | Value | ID | Value |
|---|---|---|---|
| 1 | One | 1 | One |
| 2 | Two | 2 | Two |
6. With an outer join the columns from the table where data is "missing" are returned as NULL values.
A) True
B) False
7. Consider the Table 1 & Table 2 from Question 5.
SELECT tab1.*,tab2.*
FROM Table1 tab1
FULL OUTER JOIN Table2 tab2 ON tab1.ID = tab2.ID
WHERE tab1.ID IS NULL OR tab2.ID IS NULL
Result of above query will be:
A)
| ID | Value | ID | Value |
|---|---|---|---|
| NULL | NULL | 6 | Six |
| NULL | NULL | 7 | Seven |
B)
| ID | Value | ID | Value |
|---|---|---|---|
| 4 | Four | NULL | NULL |
| 5 | Five | NULL | NULL |
C)
| ID | Value | ID | Value |
|---|---|---|---|
| 4 | Four | NULL | NULL |
| NULL | NULL | 7 | Seven |
D)
| ID | Value | ID | Value |
|---|---|---|---|
| 4 | Four | NULL | NULL |
| 5 | Five | NULL | NULL |
| NULL | NULL | 6 | Six |
| NULL | NULL | 7 | Seven |
8. For an Inner Join it is very important, which table is first one and which second one.
A) True
B) False
9. Consider the Table 1 & Table 2 from Question 5.
UPDATE
table1
SET
Value = 'unmatch'
FROM
table2
INNER JOIN
table1
ON
table1.ID = table2.ID
Result of the above query will be:
A)
Table 1
| ID | Value |
|---|---|
| 1 | unmatch |
| 2 | unmatch |
| 3 | unmatch |
| 4 | Four |
| 5 | Five |
Table 2
| ID | Value |
|---|---|
| 1 | One |
| 2 | Two |
| 3 | Three |
| 6 | Six |
| 7 | Seven |
B)
Table 1
| ID | Value |
|---|---|
| 1 | One |
| 2 | Two |
| 3 | Three |
| 4 | Four |
| 5 | Five |
Table 2
| ID | Value |
|---|---|
| 1 | unmatch |
| 2 | unmatch |
| 3 | unmatch |
| 6 | Six |
| 7 | Seven |
C)
Table 1
| ID | Value |
|---|---|
| 1 | One |
| 2 | Two |
| 3 | Three |
| 4 | unmatch |
| 5 | unmatch |
Table 2
| ID | Value |
|---|---|
| 1 | One |
| 2 | Two |
| 3 | Three |
| 6 | unmatch |
| 7 | unmatch |
D)
None of Above
10. You can have a maximum of 256 tables in a single Select statement in SQL SERVER 2005 and Tables per SELECT statement in SQL SERVER 2012 are
A) 4096
B) 256
C) 2094
D) Limited only by available resources
Answers