RSS Feed

Multiple Choice Questions - SQL Joins - Set 7

Multiple Choice Questions - SQL Joins - Set 7


1. Which of the below point(s) are true with respect to joins:

A) Null values never join.
B) Columns used in the joins mush have compatible data types.
C) Join column is usually a primary key or foreign key.
D) Cross join without a WHERE clause returns a Cartesian product.
E) A & C
F) All of above

2. 
Orders table
Order_no, Cust, Prodt, Qty, Amt, Discount

Customers table
Custnbr, Company, Custrep, Creditlim
Print all the orders showing order number, amount, company name and credit limit of customers.

A) 
Select Order_no, Amt, Company, Creditlim 
from Customers outer join Orders 
on customers.custnbr = orders.cust;
B) 
Select Order_no, Amt, Company, Creditlim 
from Customers left outer join Orders 
on customers.custnbr = orders.cust;
C) 
Select Order_no, Amt, Company, Creditlim 
from Customers inner join Orders 
on customers.custnbr = orders.cust;
D) 
Select Order_no, Amt, Company, Creditlim 
from Customers right outer join Orders 
on customers.custnbr = orders.cust;

3. . . . . . matches the rows using a non-equal condition, that is, using a comparison operator other than equality like <,>,<=,>=,<>.

A) Multiple condition join
B) Theta join
C) Semi join
D) Self join

4. 
Orders table
order_number, customer, prod, qty, cost, disc

Customers table
customer_number, company, cust_rep

Sales_Persons table
repnbr, name, rep_office, quota, sales

Display all the orders over $95000 along with the name of the salesperson who took the order and the name of the customer who placed it.

A) 
Select order_number, cost, company, name, 
From orders, customers, Sales_Persons
Where customer = customer_number and 
cust_rep = repnbr and cost <= 95000;
B) 
Select order_number, cost, company, name, 
From orders, customers, Sales_Persons
Where customer = customer_number or 
cust_rep = repnbr and cost >= 95000;
C) 
Select order_number, cost, company, name, 
From orders, customers, Sales_Persons
Where customer = customer_number and 
cust_rep = repnbr or cost >= 95000;
D) 
Select order_number, cost, company, name, 
From orders, customers, Sales_Persons
Where customer = customer_number and 
cust_rep = repnbr and cost >= 95000;
5.
Select * from Employees1
Empno Ename Title Mgr
1 n1 t1 3
2 n2 t2 3
3 n3 t3 6
4 n4 t4 3
5 n5 t5 6
6 n6 t6 3


We want the result as shown below:
employee manager
n1 n3
n2 n3
n3 n6
n4 n3
n5 n6
n6 n3


What query or queries can be used to get the above result.

A) 
Select emp.ename as employee, manager.ename as manager
from Employees1 as emp 
join 
Employees1 as manager 
on emp.Mgr = manager.Empno
B) 
Select emp.ename as employee, manager.ename as manager
from Employees1 as emp 
inner join 
Employees2 as manager 
on emp.Mgr = manager.Empno
C) 
Select emp.ename as employee, manager.ename as manager
from Employees1 as emp 
inner join 
Employees1 as manager 
on emp.Mgr = emp.Empno
D) 
Select emp.ename as employee, manager.ename as manager
from Employees1 as emp 
inner join 
Employees1 as manager 
on emp.Mgr = manager.Empno


6. In case of inner joins the order of the data sources is very important.

A) True
B) False

7. In case of outer joins, if the condition is in the . . . . . . . , SQL Server includes all rows from the outer table and then uses the condition to include rows from the second table. If the condition is in the . . . . . . . . , the join is performed and then the where clause is applied to the joined rows.

A) FROM clause, WHERE clause
B) JOIN clause, WHERE clause
C) SELECT clause, FROM clause
D) JOIN clause, SELECT clause

8. 
Customers table
Custnbr, Company, Custrep, Creditlim

Orders table
Order_no, Cust, Prodt, Qty, Amt, Discount
Find all the customers with orders more than 500 or credit limits greater than or equal to 500.

A) 
Select distinct Custnbr
from Customers Right JOIN Orders 
on Custnbr = Cust
where (Creditlim >= 500 OR Amt > 500)
B) 
Select distinct Custnbr
from Customers LEFT JOIN Orders 
on Custnbr = Cust
where (Creditlim > 500 OR Amt >= 500)
C) 
Select Custnbr
from Customers LEFT JOIN Orders 
on Custnbr = Cust
where (Creditlim > 500 OR Amt > 500)
D) 
Select distinct Custnbr
from Customers LEFT JOIN Orders 
on Custnbr = Cust
where (Creditlim >= 500 OR Amt > 500)

9. Every outer join operation can be simulated using the . . . . . . operator plus the NOT EXISTS function.

A) IN ( )
B) LIKE
C) UNION
D) EXISTS

10. It is not necessary that only primary and foreign keys should be used for joins. The join can match a row in one table with a row in another table using any column as long as the columns share compatible data types and the data match.

A) Ture
B) False

11. SQL Server join type "nested loop" usually works well in case of . . . . . . .

A) small tables
B) medium sized tables
C) large tables
D) all of above

Answers