Database programming section 6

1. Which statement about a natural join is true?
Columns with the same names must have the same precision and datatype. (*)

2. You need to join two tables that have two columns with the same name, datatype, and precision. Which type of join would you create to join the tables on both of the columns?
Natural join (*)

3. You need to join all the rows in the EMPLOYEES table to all the rows in the EMP_REFERENCE table. Which type of join should you create?

A cross join (*)

4. If you select rows from two tables (employees and departments) using the outer join specified in the example, what will you get?
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
All employees including those that do not have a departement_id assigned to them (*)

5. You need to display all the rows (both matching and non-matching) from both the EMPLOYEE
and EMPLOYEE_HIST tables. Which type of join would you use?
A full outer join (*)

6. Which syntax would be used to retrieve all rows in both the EMPLOYEES and DEPARTMENTS tables, even when there is no match?
FULL OUTER JOIN (*)

7. EMPLOYEES Table:
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
DEPARTMENT_ID NUMBER (4)
DEPARTMENTS Table:
Name Null? Type
DEPARTMENT_ID NOT NULL NUMBER 4
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER (6)
A query is needed to display each department and its manager name from the above tables. However, not all departments have a manager but we want departments returned in all cases. Which of the following SQL: 1999 syntax scripts will accomplish the task?
SELECT d.department_id, e.first_name, e.last_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.employee_id = d.manager_id); (*)

8. Table aliases MUST be used with columns referenced in the JOIN USING clause. True or False?
False(*)

9. The keywords JOIN _____________ should be used to join tables with the same column names but different datatypes.
Using (*)

10. The primary advantage of using JOIN ON is:
It permits columns with different names to be joined. (*)

11. Hierarchical queries MUST use the LEVEL pseudo column. True or False?
False (*)

12. Hierarchical queries can walk both Top-Down and Bottom-Up. True or False?
True (*)

13. Which of the following database design concepts is implemented with a self join?
Recursive Relationship (*)

14. Which SELECT statement implements a self join?

SELECT p.part_id, t.product_id
FROM part p, part t
WHERE p.part_id = t.product_id; (*)

15. 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?
A self join (*)