Chapter 9

  1. Equality, non-equality, and self-joins are broadly categorized as outer joins.
    True/False
    False
  2. The NATURAL JOIN keywords can be used to link two tables that have a commonly named and defined column.
    True/False
    True
  3. The MINUS set operator is used to display rows that were uniquely returned by the first query in the set.
    True/False
    True
  4. The most common type of join is an equijoin, which joins two or more tables together based upon the tables having equivalent data values in a common column.
    True/False
    True
  5. An outer join operator can be included in a FROM clause to list all rows from one table that do not have a corresponding row in the other table.
    True/False
    False
  6. When combining the results of two SELECT statements with the MINUS keyword, duplicate rows are suppressed in the results.
    True/False
    False
  7. An outer join operator consists of a minus sign enclosed in parentheses, (-).
    True/False
    False
  8. An outer join only lists rows that contain a match in both tables.
    True/False
    False
  9. The outer join operator is placed on the side of the joining condition that references
    the table containing the deficient rows.
    True/False
    True
  10. A(n) inner join will only include rows that have matching rows in the other table.
    True/False
    True
  11. The INTERSECT set operator only displays the rows returned by both queries.
    True/False
    True
  12. When combining the results of two SELECT statements with the UNION keyword, duplicate rows are suppressed in the results.
    True/False
    True
  13. The ON clause can be used only if the tables being joined have a common column with the same name.
    True/False
    False
  14. The USING clause must be used with the JOIN keyword when linking tables that do not contain a commonly named column.
    True/False
    False
  15. The UNION set operator will not suppress rows that are returned by both queries.
    True/False
    False
  16. If a table alias is assigned in the SELECT clause, it must be used any time the table is referenced in that SQL statement.
    True/False
    False
  17. A Cartesian join can be created by not including a joining condition in the WHERE clause of a SELECT statement.
    True/False
    True
  18. If you are joining five tables in a SELECT statement, five joining conditions will be required.
    True/False
    False
  19. A table alias is assigned to a table in the WHERE clause.
    True/False
    False
  20. The number of joining conditions required to join tables is always one less than the number of tables being joined.
    True/False
    True
  21. Set operators are used to combine the results of multiple queries.
    True/False
    True
  22. Column qualifiers must be included in the WHERE clause if the columns used to join the tables have the same column names.
    True/False
    True
  23. A Cartesian join usually results from the user omitting the joining condition that links two or more tables together.
    True/False
    True
  24. A(n) non-equality join is when a table is joined to itself.
    True/False
    False
  25. The JOIN keyword must be used in the WHERE clause of a SELECT statement.
    True/False
    False
  26. In Oracle12c, tables can be linked through which clause(s)?



    D. both b and c
  27. Which of the following SQL statements will list the name of each customer stored in the customers table, and, if the customer has placed an order that is contained in the ORDERS table, the order# of any order each customer has placed?
    A. SELECT lastname, firstname, order#
        FROM orders
        RIGHT OUTER JOIN customers
        USING (customer#);
    B. SELECT lastname, firstname, order#
        FROM orders
        LEFT OUTER JOIN customers
        USING (customer#);
    C. SELECT lastname, firstname, order#
        FROM orders
        FULL OUTER JOIN customers
        USING (customer#);
    D. both a and c
    D. both a and c
  28. Which of the following SQL statements will display books that have not been ordered recently?
    A. SELECT title
        FROM books, orderitems
        WHERE books.isbn <> orderitems.isbn;
    B. SELECT title
        FROM books NATURAL JOIN orderitems
        WHERE JOIN IS NULL;
    C. SELECT title
        FROM books NATURAL JOIN orderitems
        MINUS SELECT title FROM books;
    D. SELECT title FROM books
        MINUS
        SELECT title FROM books
        NATURAL JOIN orderitems;
    • D. SELECT title FROM books
    •     MINUS
    •     SELECT title FROM books
    •     NATURAL JOIN orderitems;
  29. How many joining conditions will be required in an SQL statement that is used to determine the gift that corresponds to each book in the BOOKS table?



    D. 1
  30. Which of the following keywords can be used to join two tables that do not contain a commonly named and defined column?



    B. JOIN...ON
  31. Which of the following SQL statements will display the publisher name, book title, and retail price of all books that cost more than $35.95?
    A. SELECT name, title, retail
        FROM books, publisher
        WHERE cost > 35.95;
    B. SELECT name, title, retail
        FROM books
        NATURAL JOIN publisher
        WHERE cost > 35.95;
    C. SELECT p.name, b.title, b.retail
        FROM books b
        NATURAL JOIN publisher p
        WHERE b.cost > 35.95;
    D. none of the above
    • B. SELECT name, title, retail
    •     FROM books
    •     NATURAL JOIN publisher
    •     WHERE cost > 35.95;
  32. To display the name of each customer and the title of each book purchased by the customers would require how many join conditions?



    D. 3
  33. To display the name of each customer and the ISBN of each book purchased by the customers would require how many joins in the FROM clause of the SQL statement?



    D. 2
  34. Which of the following is used to create an outer join in a WHERE clause?



    C. outer join operator (+)
  35. Which of the following queries will display the first and last name of the individual who referred another customer, along with the customer# of the referred customer?
    A. SELECT r.firstname, r.lastname, c.customer#
        FROM customers r, customers c
        WHERE r.customer# = c.referred;
    B. SELECT r.firstname, r.lastname, c.customer#
        FROM customers r JOIN customers c
        ON r.customer# = c.referred;
    C. SELECT r.firstname, r.lastname, c.customer#
        FROM customers r
        NATURAL JOINcustomers c;
    D. both a and b
    D. both a and b
  36. Which of the following SQL statements will display the name of each publisher that publishes a book classified in the COMPUTER category?
    A. SELECT UNIQUE name
        FROM books, publisher;
    B. SELECT UNIQUE name
        FROM books NATURAL JOIN publisher
        WHERE category = 'COMPUTER';
    C. SELECT DISTINCT name
        FROM books JOIN publisher
       WHERE category = 'COMPUTER';
    D. both b and c
    • B. SELECT UNIQUE name
    •     FROM books NATURAL JOIN publisher
    •     WHERE category = 'COMPUTER';
  37. Which of the following terms refers to a
    column with equivalent data that exists in two or more tables?



    C. common column
  38. Which of the following set operators will display the results of the combined SQL statements without suppressing duplicate rows?



    B. UNION ALL
  39. The outer join operator in the WHERE clause cannot be used with which of the following operators?



    D. both a and b
  40. In which of the following examples is the ORDERS table used as a column qualifier?



    C. orders.order#
  41. Which of the following SQL statements will display the title of all books that have had multiple copies requested in a single order?
    A. SELECT title
        FROM books NATURAL JOIN orderitems
        WHERE qty > 1;
    B. SELECT title
        FROM books JOIN orderitems
        WHERE qty > 1;
    C. SELECT title
        FROM books JOIN orderitems
        ON (isbn) JOIN orders ON (order#)
        WHERE qty>1;
    D. SELECT title
        FROM books
        JOIN orderitems USING(isbn);
    • A. SELECT title
    •     FROM books NATURAL JOIN orderitems
    •     WHERE qty > 1;
  42. Which of the following SQL statements will display the title and cost of each book stored in the BOOKS table, as well as the name of the contact person and the phone number to call to reorder the book?
    A. SELECT title, cost, contact, phone
        FROM publisher, books;
    B. SELECT title, cost, contact, phone
        FROM publisher JOIN books
        USING (pubid);
    C. SELECT title, cost, contact, phone
        FROM publisher JOIN books
        USING (isbn);
    D. SELECT title, cost, contact, phone
        FROM publisher JOIN books
        USING (contact);
    • B. SELECT title, cost, contact, phone
    •     FROM publisher JOIN books
    •     USING (pubid);
  43. The JOIN keyword is included in which of the following clauses?



    C. FROM
  44. Which of the following set operators can be used to make certain that only the rows returned by both queries are displayed in the results?



    B. INTERSECT
  45. Which of the following SQL statements will list the name of each customer stored in the CUSTOMERS table, and, if the customer has placed an order that is contained in the ORDERS table, the order# of any order each customer has placed?
    A. SELECT lastname, firstname, order#
        FROM customers NATURAL JOIN orders
       WHERE orders.customer# IS NOT NULL;
    B. SELECT lastname, firstname, order#
        FROM customers, orders
        WHERE orders.customer# (+) = customers.customer#;
    C. SELECT lastname, firstname, order#
        FROM customers, orders
       WHERE orders.customer# = customers.customer# (+);
    D. SELECT lastname, firstname, order#
        FROM customers NATURAL JOIN orders
        WHERE orders.customer# IS NULL;
    • B. SELECT lastname, firstname, order#
    •     FROM customers, orders
    •     WHERE orders.customer# (+) = customers.customer#;
  46. Which of the following SQL statements will display the title of each book in the BOOKS table and the name of its publisher?
    A. SELECT title, name
        FROM publisher, books;
    B. SELECT title, name
        FROM publisher NATURAL JOIN books;
    C. SELECT title, name
        FROM publisher, books
        WHERE p.pubid = b.pubid;
    D. both b and c
    • B. SELECT title, name
    •     FROM publisher NATURAL JOIN books;
  47. If you are attempting to join two tables that have multiple common columns, which of the following JOIN keywords should be used to specify how the tables should be linked?



    C. JOIN...USING
  48. A join based upon a column from each table containing equivalent data is known as a(n)



    C. equality join
  49. Which of the following types of joins is created by matching equivalent values in each table?



    D. equality join
  50. Which of the following is an example of assigning "o" as a table alias for the ORDERS table in the FROM clause?



    C. FROM orders o, customers c
Author
Lencha
ID
325786
Card Set
Chapter 9
Description
Chapter 9
Updated