Chapter 9 - Joining Data from Multiple Tables

What is the maximum number of characters allowed in a table alias? 10 30 255 256

30

How many join conditions are needed for a query that joins five tables?

4

How many rows are returned in a Cartesian join between one table having 5 records and a second table having 10 records?

50 rows

If the CUSTOMERS table contains seven records and the ORDERS table has eight records, how many records does the following query produce? SELECT* FROM customers CROSS JOIN orders; 0 8 7 15 56

56

What's the purpose of a column qualifier? When are you required to use one?

A column qualifier indicates the table containing the column being referenced. It must be used when more than one table contains the referenced column to avoid the ambiguity error.

Explain the difference between an inner join and an outer join.

An inner join are equality, non-equality, and self-joins because a row is returned ONLY if a corresponding record in each table is queried. An outer join is a join that links data in tables that don't have equivalent rows, in other words, records existing in one table that don't have a matching record in the other table are included in the results.

Describe problems you might encounter when using the NATURAL JOIN keywords to perform join operations.

As tables are modified, commonly named columns could be added but don't actually represent the same data and there and are then used incorrectly in join operation. (ex: imagine if a column named description is added to both books and orderitems tables, but they have nothing in common.)

What's the difference between an equality and a non-equality join?

Equality joins are joins that link table data in two or more tables having equivalent data stored in a common column. Non-equality joins are used when the related columns can't be joined with an equal sign (they have no equivalent rows that can be joined).

In an OUTER JOIN query, the outer join operator (+) is placed after which table?

It's placed after the deficient table (one that doesn't have a row that matches a row from the other table that needs to be displayed).

Which of the following SQL statements is not valid? SELECT b.isbn, p.name FROM books b NATURAL JOIN publisher p; SELECT isbn, name FROM books b, publisher p WHERE b.pubid = p.pubid; SELECT isbn, name FROM books b JOIN publisher p ON b.pubid = p.pubid; SELECT isbn, name FROM books JOIN publisher USING (pubid); None—all the above are valid SQL statements.

None—all the above are valid SQL statements.

Which of the following operators is not allowed in an outer join? AND = OR >

OR

Which of the following queries is valid? SELECT b.title, b.retail, o.quantity FROM books b NATURAL JOIN orders od NATURAL JOIN orderitems o WHERE od.order# = 1005; SELECT b.title, b.retail, o.quantity FROM books b, orders od, orderitems o WHERE orders.order# = orderitems.order# AND orderitems.isbn = books.isbn AND od.order# = 1005; SELECT b.title, b.retail, o.quantity FROM books b, orderitems o WHERE o.isbn = b.isbn AND o.order# = 1005; none of the above

SELECT b.title, b.retail, o.quantity FROM books b, orderitems o WHERE o.isbn = b.isbn AND o.order# = 1005;

Which of the following SQL statements is valid? SELECT books.title, orderitems.quantity FROM books b, orderitems o WHERE b.isbn = o.ibsn; SELECT title, quantity FROM books b JOIN orderitems o; SELECT books.title, orderitems.quantity FROM books JOIN orderitems ON books.isbn = orderitems.isbn; none of the above

SELECT books.title, orderitems.quantity FROM books JOIN orderitems ON books.isbn = orderitems.isbn;

Which of the following SQL statements is not valid? SELECT isbn FROM books MINUS SELECT isbn FROM orderitems; SELECT isbn, name FROM books, publisher WHERE books.pubid (+) = publisher.pubid (+); SELECT title, name FROM books NATURAL JOIN publisher All the above SQL statements are valid.

SELECT isbn, name FROM books, publisher WHERE books.pubid (+) = publisher.pubid (+);

Given the following query: SELECT lastname, firstname, order# FROM customers LEFT OUTER JOIN orders USING (customer#) ORDER BY customer#; Which of the following queries returns the same results? SELECT lastname, firstname, order# FROM customers c OUTER JOIN orders o ON c.customer# = o.customer# ORDER BY c.customer#; SELECT lastname, firstname, order# FROM orders o RIGHT OUTER JOIN customers c ON c.customer# = o.customer# ORDER BY c.customer# SELECT lastname, firstname, order# FROM customers c, orders o WHERE c.customer# = o.customer# (+) ORDER BY c.customer#; none of the above

SELECT lastname, firstname, order# FROM customers c, orders o WHERE c.customer# = o.customer# (+) ORDER BY c.customer#;

Which of the following lists all books published by the publisher named Printing Is Us? SELECT title FROM books NATURAL JOIN publisher WHERE name = 'PRINTING IS US'; SELECT title FROM books, publisher WHERE pubname = 1; SELECT * FROM books b, publisher p JOIN tables ON b.pubid = p.pubid WHERE name = 'PRINTING IS US'; none of the above

SELECT title FROM books NATURAL JOIN publisher WHERE name = 'PRINTING IS US';

Which of the following queries contains an equality join? SELECT title, authorid FROM books, bookauthor WHERE books.isbn = bookauthor.isbn AND retail > 20; SELECT title, name FROM books CROSS JOIN publisher; SELECT title, gift FROM books, promotion WHERE retail >= minretail AND retail <= maxretail; none of the above

SELECT title, authorid FROM books, bookauthor WHERE books.isbn = bookauthor.isbn AND retail > 20;

Given the following query: SELECT title, gift FROM books CROSS JOIN promotion; Which of the following queries is equivalent? SELECT title, gift FROM books NATURAL JOIN promotion; SELECT title FROM books INTERSECT SELECT gift FROM promotion; SELECT title FROM books UNION ALL SELECT gift FROM promotion; all of the above

SELECT title, gift FROM books NATURAL JOIN promotion;

Which of the following queries contains a non-equality join? SELECT title, authorid FROM books, bookauthor WHERE books.isbn = bookauthor.isbn AND retail > 20; SELECT title, name FROM books JOIN publisher USING (pubid); SELECT title, gift FROM books, promotion WHERE retail >= minretail AND retail <= maxretail; none of the above

SELECT title, gift FROM books, promotion WHERE retail >= minretail AND retail <= maxretail;

Given the following query: SELECT zip, order# FROM customers NATURAL JOIN orders; Which of the following queries is equivalent? SELECT zip, order# FROM customers JOIN orders WHERE customers.customer# = orders.customer#; SELECT zip, order# FROM customers, orders WHERE customers.customer# = orders.customer#; SELECT zip, order# FROM customers, orders WHERE customers.customer# = orders.customer# (+); none of the above

SELECT zip, order# FROM customers, orders WHERE customers.customer# = orders.customer#;

What are the differences between the JOIN ... USING and JOIN ... ON approaches for joining tables?

The USING clause technique requires a commonly named column to perform the join. However, perhaps you have created tables with common columns but without a common name. When there are no commonly named columns to use in a join, you need to use the JOIN keyword in the FROM clause and add an ON clause immediately after the FROM clause to specify which fields are related.

Which of the following statements about an outer join between two tables is true? If the relationship between the tables is established with a WHERE clause, both tables can include the outer join operator. To include unmatched records in the results, the record is paired with a NULL record in the deficient table. The RIGHT, LEFT, and FULL keywords are equivalent. all of the above none of the above

To include unmatched records in the results, the record is paired with a NULL record in the deficient table.

Why are the NATURAL JOIN keywords not an option for producing a self-join? (Hint: Think about what happens if you use a table alias with the NATURAL JOIN keywords.)

To produce a self-join, a copy of table is created but assigned a table alias to ''trick'' Oracle 11g into "thinking" it's working with two tables. Table aliases can't be assigned when using the natural join keywords.

What's the difference between the UNION and UNION ALL set operators?

UNION returns the results of both queries and removes duplicates. UNION ALL returns the results of both queries but includes duplicates

To answer the following questions, refer to the tables in the JustLee Books database. Which of the following queries creates a Cartesian join? SELECT title, authorid FROM books, bookauthor; SELECT title, name FROM books CROSS JOIN publisher; SELECT title, gift FROM books NATURAL JOIN promotion; all of the above

all of the above

Which line in the following SQL statement raises an error? 1. SELECT name, title 2. FROM books JOIN publisher 3. WHERE books.pubid = publisher.pubid 4. AND 5. cost < 45.95 line 1 line 2 line 3 line 4 line 5

line 2

Which line in the following SQL statement raises an error? 1. SELECT name, title 2. FROM books NATURAL JOIN publisher 3. WHERE category = 'FITNESS' 4. OR 5. books.pubid = 4; line 1 line 2 line 3 line 4 line 5

line 5

Which line in the following SQL statement raises an error? 1. SELECT name, title 2. FROM books b, publisher p 3. WHERE books.pubid = publisher.pubid 4. AND 5. (retail > 25 OR retail-cost > 18.95); line 1 line 3 line 4 line 5

line 5

The following SQL statement contains which type of join? SELECT title, order#, quantity FROM books FULL JOIN orderitems ON books.isbn = orderitems.isbn; equality self-join non-equality outer join

outer join