Database programming section 7

1. If table A has 10 rows and table B has 5 rows, how many rows will be returned if you perform a cartesian join on those two tables?
50 (*)

2. Evaluate this SQL statement:
SELECT e.employee_id, e.last_name, e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND employees.department_id > 5000
ORDER BY 4;
Which clause contains a syntax error?
AND employees.department_id > 5000 (*)

3. You need to create a report that lists all employees in department 10 (Sales) whose salary is not equal to $25,000 per year. Which query should you issue to accomplish this task?
SELECT last_name, first_name, salary
FROM employees
WHERE salary != 25000 AND dept_id = 10; (*)

4. 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 (*)

5. What happens when you create a Cartesian product?
All rows from one table are joined to all rows of another table (*)

6. What is produced when a join condition is not specified in a multiple-table query using Oracle proprietary Join syntax?
A Cartesian product (*)

7. What is the minimum number of join conditions required to join 5 tables together?
4 (*)

8. 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. (*)

9. Which symbol is used to perform an outer join?
(+) (*)

10. Evaluate this SELECT statement:
SELECT p.player_id, m.last_name, m.first_name, t.team_name
FROM player p
LEFT OUTER JOIN player m ON (p.manager_id = m.player_id)
LEFT OUTER JOIN team t ON (p.team_id = t.team_id);
Which join is evaluated first?

The self-join of the player table (*)