PROGRAMMING: Section 15

CARTESIAN PRODUCT AND THE JOIN OPERATIONS
When must column names be prefixed by table names in join syntax?
When the same column name appears in more than one table of the query
          
Will the following statement work?
SELECT department_name, last_name
FROM employees, departments
WHERE department_id = department_id;
No, Oracle will return a column ambiguously defined error.
          
If table A have 10 rows and table B have 5 rows, how many rows will be returned if you perform a cartesian join on those two tables?
50
          
Oracle proprietary JOINS can use the WHERE clause for conditions other than the join-condition. True or False?
True
          
What is the result of a query that selects from two tables but includes no join condition?
A Cartesian product
          
If table A have 10 rows and table B have 5 rows, how many rows will be returned if you perform a equi-join on those two tables?
It depends on the data found in the two tables.
          

NONEQUIJOINS
The following statement is an example of a nonequi-join?
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
True or False?
True
          
Which of the following operators is/are typically used in a nonequijoin?
>=, <=, BETWEEN ...AND
          
Which statement about joining tables with a non-equijoin is false?
A WHERE clause must specify a column in one table that is compared to a column in the second table
          


OUTER JOINS
To perform a valid outer join between DEPARMENTS and EMPLOYEES to list departments without employees select the correct WHERE clause for the following select statement:
SELECT d.department_name, e.last_name
FROM employees e, departments d
WHERE
e.department_id(+) = d.department_id
          
The following is a valid outer join statement:
SELECT c.country_name, d.department_name
FROM countries c, departments d
WHERE c.country_id (+) = d.country_id (+)
True or False?
False
          
Which symbol is used to perform an outer join?
(+)
          
The ID column in the CLIENT table that corresponds to the CLIENT_ID column of the ORDER table contains null values for rows that need to be displayed. Which type of join should you use to display the data?         
Outer join