Week5 - Display Data from Multiple Tables / Explore

The questions can come in any order, so make sure you are selecting right option for all questions.

1. The INNER JOIN clause…

(b) returns all rows that have matching value in the field on which the 2 tables are joined.

2. A NATURAL JOIN is based on:

(c) Columns with the same name and datatype

3. Select the correct query/queries for cross join:

(a) Select * FROM Table1 T1 CROSS JOIN Table1 T2
(b) Select * FROM Table1 T1,Table1 T2

4. List all orders, showing order number, customer name and credit limit of a customer.

Orders Table:
Order_Number, Customer_Number, Product_Amount

Customers Table:
Customer_Number, Customer_Name, Credit_Limit

Which of the below query satisfy the above question:

A. Select Order_Number, Customer_Name, Credit_Limit
from Customers left join Orders
where Customer_Number = Customer_Number

B. Select Order_Number, Customer_Name, Credit_Limit
from Customers INNER JOIN Orders
ON Customers.Customer_Number = Orders.Customer_Number;

(b) B

5. Orders table got below attributes
Order_no, Custnbr, Product, Qty, Amt, Discount

Customers table got below attributes
Custnbr, Company, Custrep, Creditlim

Print the Order_no, Amt, Company and creditlim of the customers who placed orders.

(c) Select Order_no, Amt, Company, Creditlim from Customers inner join Orders on customers.custnbr = orders.custnbr

6. Examine the structure of the EMPLOYEES and DEPARTMENTS tables:

EMPLOYEES

EMPLOYEE_ID NUMBER

DEPARTMENT_ID NUMBER

MANAGER_ID NUMBER

LAST_NAME VARCHAR2(25)


DEPARTMENTS

DEPARTMENT_ID NUMBER

MANAGER_ID NUMBER

DEPARTMENT_NAME VARCHAR2(35)

LOCATION_ID NUMBER

You want to create a report displaying employee last names, department names, and locations.
Which query should you use to create an equi-join?

(d) SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;

7. If there are reasonably large tables then which of the following join will use maximum system resources unnecessarily:

(b) Cross join

8. A cartesian product is formed when:

(a) A join condition is omitted
(c) All rows in the first table are joined to all rows in the second table

9. We refer to a join as a self-join when?

(c) we are joining table to itself

10. In which case would you use a FULL OUTER JOIN?

(c) You want all unmatched data from both tables.

11. Examine the structures of the table EMPLOYEES and DEPARTMENTS as given below: (Consider the same table structure for all the quiz questions related to this two tables.)

[EMPLOYEES table]

[DEPARTMENT table]

What will be the outcome of the following query?
SELECT e.first_name, e.last_name, e.employee_id
FROM employees e
JOIN department d
ON (e.salary BETWEEN 1000 AND 10000);

(c) It will execute successfully and give the first name, last name and employee ID of employees with the condition mentioned.

12. Consider the table structure of EMPLOYEES and DEPARTMENTS.

[EMPLOYEES table]

[DEPARTMENT table]

You need to find a report which lists the first names and last names of the employees whose salary is greater than 20000 and who are located in any of the departments in the location Geneva.

Which of the following queries will give the required results?

A. SELECT e.first_name, e.last_name FROM employees e
JOIN departments d ON
(e.department_id = d.department_id and e.salary >20000)
AND d.loc = upper ('Geneva');

B. SELECT e.first_name, e.last_name FROM employees e
JOIN departments d ON
(e.department_id = d.department_id and e.salary >=20000);

C. SELECT e.first_name, e.last_name FROM employees e
JOIN departments d ON
(e.department_id = d.department_id and e.salary >20000)
AND d.loc = 'Geneva';

D. SELECT e.first_name, e.last_name FROM employees e
JOIN departments d ON
(e.department_id = d.department_id and e.salary >20000)
WHERE d.loc = upper('Genev');

(c) A and C

13. What will be the result of a NATURAL JOIN between two tables EMPLOYEES and DEPARTMENT as given in the query below? SELECT * FROM employees NATURAL JOIN department;

(c) The result set will have only one column for each pair of identically named columns from both tables

14. Consider the following query:
SELECT *
FROM employees e NATURAL JOIN department d;
You need to find the results obtained by the above query only for the departments 100 and 101. Which of the following clauses should be added / modified to the above query?

(c) WHERE e.department_id in (100,101) should be added

15. Consider the following query:
SELECT *
FROM employees e NATURAL JOIN department d;
You need to find the results obtained by the above query for all those employees who have salaries greater than 20000. Which of the following clauses should be added / modified to the above query?

(d) WHERE salary >20000;

16. Which join would be used to retrieve all rows in both the EMPLOYEES and DEPARTMENTS tables, even when there is no match?

(c) Outer Join

17. Consider the following query:
SELECT e.salary, d.department_id
FROM employees e JOIN department d
On (e.department_id = d.department_id and e.last_name = 'Brandon');
What is true with respect to the query given above?

(d) The JOIN..ON clause can be written in the form given above for putting more conditions.

18. Consider the following table structure of Department and Location:

[Table}

You need to find out the departments that have not been allocated any location.
Which query would give the required result?

(b) SELECT d.department_id , d.department_name FROM departments d RIGHT OUTER JOIN locations l ON (d.location_id = l.location_id);

19. Evaluate this SELECT statement:

SELECT *
FROM employee e, employee m
WHERE e.mgr_id = m.emp_id;

Which type of join is created by this SELECT statement?

(b) self join

20. How many tables may be included with a join?

(a) All of the given options