Chapter 8 Advanced Database

The EMPLOYEES and DEPARTMENTS tables have two identically named columns, department_id and manager_id. Which clause(s) join these tables based on both column values? (Choose all that apply.) FROM employees NATURAL JOIN departments; FROM employees JOIN departmentsUSING (department_id, manager_id); FROM employees e JOIN departments dON e.department_id = d.department_id AND e.manager_id = d.manager_id; FROM employees NATURAL JOIN departmentsON (department_id, manager_id);

FROM employees NATURAL JOIN departments; FROM employees JOIN departmentsUSING (department_id, manager_id); FROM employees e JOIN departments dON e.department_id = d.department_id AND e.manager_id = d.manager_id;

Which keyword is optional when doing NATURAL JOIN or JOIN... ON?

INNER

The general syntax for the NATURAL JOIN clause is as follows: SELECT table1.column, table2.column FROM table1 NATURAL JOIN table2; What condition must be true in order for a natural join to execute without any errors? table1 and table 2 must have a foreign key relationship between them table1 and table 2 must have the same number of columns table1 and table 2 must have at least one column with: (1) the same name and (2) these two columns must have compatible datatypes table1 and table 2 must have the same number of columns, and the corresponding columns must have compatible datatypes

table1 and table 2 must have at least one column with: (1) the same name and (2) these two columns must have compatible datatypes

What must be true in order for a join to happen between two tables? they must be related they must have a primary key - foreign key relationship they must have the same name they must have a column with the same name

they must be related

How many rows will be returned by this SQL query on the Regions table? SELECT * FROM Regions R1 JOIN Regions R2 ON (R1.Region_ID = LENGTH(R2.Region_name)/2); Region_ID Region_Name------------ ---------------1 Europe2 Americas3 Asia4 Middle East Question options: 1 2 3 4

3

The syntax diagram for the JOIN USING clause is: SELECT table1.column, table2.column, ... Question options: FROM JOIN table1 ON table2USING (join_column); FROM table1 JOIN table2USING (join_column1 = join_column2); FROM table1, table2USING (join_column); FROM table1 JOIN table2USING (join_column);

FROM table1 JOIN table2USING (join_column);

Oracle imposes a rule stating that the join columns in two distinct tables must have a primary key - foreign key relationship. True False

False

Qualifying a column reference using dot notation, such as employee.last_name or department.dept_name, have no performance benefit in a query. Question options: True False

False

The ZIPCODE table has primary key ZIP; the student table has foreign key ZIP, which references the ZIPCODE table. In both tables, ZIP is defined as NOT NULL. Considering that information, which of the following statement(s) are true about the following join (choose all that apply): SELECT s.last_name, z.zip FROM student s JOIN zipcode zON s.zip = z.zip; For each zip in ZIPCODE, there may be zero, one, or multiple students whose zip is a match For each student, there must be a matching row in the ZIPCODE table A student's ZIP may be NULL, which means they will not appear in the result set A zip that does not have a matching student will not appear in the result set.

For each zip in ZIPCODE, there may be zero, one, or multiple students whose zip is a match For each student, there must be a matching row in the ZIPCODE table A zip that does not have a matching student will not appear in the result set.

Which of the following statements accurately describe the SQL statement below? (Choose two.) SELECT A.EMPLOYEE_ID, B.POSITION FROM PAY_HISTORY A JOIN POSITIONS B ON A.SALARY <= B.MAX_SALARY; Question options: It contains a syntax error on line 1. It is an inner join. It is a non-equijoin. It contains a syntax error on line 2.

It is an inner join. It is a non-equijoin.

Review the following SQL statment (line numbers added): 01 SELECT vendor_id, invoice_date, total_price 02 FROM vendors JOIN invoices 03 USING (vendor_id); Which of the following is true for the statement? Question options: It will execute successfully, if vendor_id is a column in both tables. It will fail with a syntax error because there is no ON clause. It will fail with a syntax error on line 1 because VENDOR_ID is ambiguous. It will fail with a syntax error on line 3 because of the parenthesis around VENDOR_ID.

It will execute successfully, if vendor_id is a column in both tables.

Which is the most flexible way of performing a natural join regardless of the column names? NATURAL JOIN JOIN...USING JOIN...ON The choices are equally flexible.

JOIN...ON

Select which clauses below are categorized as "Natural Joins" (Choose all that apply): Question options: NATURAL JOIN OUTER JOIN JOIN...USING JOIN...ON

JOIN...USING JOIN...ON NATURAL JOIN

A table alias: (Choose three) Renames a table so that future joins can refer to this new name Makes for simpler and cleaner code Exists only for the SQL statement that declared it. Is a short-hand way of referring to a table

Makes for simpler and cleaner code Exists only for the SQL statement that declared it. Is a short-hand way of referring to a table

WHich of the following are NATURAL JOIN formats? Select all that apply. NATURAL JOIN JOIN...USING JOIN...ON NATURAL...ON

NATURAL JOIN JOIN...USING JOIN...ON

Which of the following statements are syntactically correct? Assume two tables emp and dept with common column deptno. (Choose all that apply.) Question options: SELECT * FROM emp e JOIN dept dUSING (deptno); SELECT * FROM emp e JOIN dept dUSING (d.deptno); SELECT d.deptno FROM emp e JOIN dept dUSING (deptno); SELECT * FROM emp INNER JOIN deptUSING (deptno);

SELECT * FROM emp e JOIN dept dUSING (deptno); SELECT * FROM emp INNER JOIN deptUSING (deptno);

What can be said about the following statement (line numbers added):1 SELECT P.PORT_NAME, S.SHIP_NAME, SC.ROOM_NUMBER2 FROM PORTS P JOIN SHIPS S3 ON P.PORT_ID = S.PORT_ID4 FROM SHIP_CABINS SC5 ON S.SHIP_ID = SC.SHIP_ID; It will successfully execute as expected. There will be a runtime error. There is a syntax error on line 4. There is a syntax error on line 3.

There is a syntax error on line 4.

Joins can connect two, three or more tables. True False

True

One of the categories of JOINS is OUTER join. True False

True

Table aliases are necessary to eliminate ambiguity in referring to columns in different tables in a join operation; and will help the query run faster. True False

True