DBA 9-10

Structure of the ORDERITEMS table ​ 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?

1

In a Cartesian join, linking a table that contains 10 rows to a table that contains 9 rows will result in ____ rows being displayed in the output

90

Which of the following keywords is used to create an equality join?

All of the above (Using the WHERE clause) (Using the JOIN method with the NATURAL JOIN, JOIN...ON, OR JOIN....USING keywords

Set operators

Combines results of multiple SELECT statements. Includes the keywords UNION, UNION ALL, INTERSECT, and MINUS

Equality join (also known as an equijoin, an inner join, or a simple join)

Creates a join by using a commonly named and defined column. Can be created by two methods:

A cross join between two tables, containing four rows each, will display eight rows in its output

FALSE

A full outer join can be created by including an outer join operator on both sides of the linking condition stated in the WHERE clause

FALSE

A table alias is assigned to a table in the WHERE clause.

FALSE

A(n) non-equality join is when a table is joined to itself. _________________________

FALSE

An inequality join refers to a join that is used to link a table to a copy of itself.

FALSE

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.​

FALSE

An outer join operator consists of a minus sign enclosed in parentheses, (-).​

FALSE

If a table alias is assigned in the SELECT clause, it must be used any time the table is referenced in that SQL statement. _________________________

FALSE

The ON clause can be used only if the tables being joined have a common column with the same name

FALSE

The UNION set operator will not suppress rows that are returned by both queries.​

FALSE

​Data stored in multiple tables can be combined through the use of an ORDER BY clause.

FALSE

A column qualifier indicates the column containing the data being referenced. _________________________

FALSE. It indicates the table

If a Cartesian join is used to link table A which contains five rows to table B which contains eight rows, there will be 13 rows in the results.

FALSE. Rows * Rows

The JOIN keyword is included in which of the following clauses?

FROM

The JOIN keyword must be used in the WHERE clause of a SELECT statement. _________________________

False

​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?

INTERSECT

Outer join

Includes records of a table in output when there's no matching record in the other table. Can be created by two methods:

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?

JOIN...USING

Self-join

Joins a table to itself. Can be created by two methods:

Non-equality join

Joins tables when there are no equivalent rows in the tables to be joined- for example, to match values in one column of a table with a range of values in another table. Can be created by two methods:

Cartesian join (also known as Cartesian product or cross join)

Replicates each row from the first table with every row from the second table. Creates a join between tables by displaying every possible record combination. Can be created by two methods:

Structure of the PUBLISHER table Which of the following SQL statements will display the name of each publisher that publishes a book classified in the COMPUTER category?

SELECT UNIQUE name FROM books NATURAL JOIN publisher WHERE category = 'COMPUTER';

Which of the following SQL statements will display the gift or gifts that should be sent with order# 1003?

SELECT gift FROM promotion, orderitems oi, books b WHERE retail BETWEEN minretail AND maxretail AND oi.isbn = b.isbn AND order# = 1003;

Structure of the BOOKS table Which of the following SQL statements will display the gift that should be sent to any customer who orders the book titled SHORTEST POEMS?

SELECT gift FROM promotion, books WHERE retail BETWEEN minretail AND maxretail AND title = 'SHORTEST POEMS';

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?

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

Structure of the PUBLISHER table 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?

SELECT name, title, retail FROM books NATURAL JOIN publisher WHERE cost > 35.95;

Structure of the PUBLISHER table Which of the following will display the title, publication date, and publisher name of each book in the BUSINESS category?

SELECT title, pubdate, name FROM publisher JOIN books USING (pubid) WHERE category = 'BUSINESS';

A column qualifier is used to indicate the table containing the column being referenced.​

TRUE

A(n) Cartesian Join replicates each row from the first table with every row from the second table.

TRUE

Column qualifiers must be included in the WHERE clause if the columns used to join the tables have the same column names.

TRUE

If you are joining four tables in a SELECT statement, three joining conditions will be required. _________________________

TRUE

The INTERSECT set operator only displays the rows returned by both queries.​

TRUE

When a self-join is created, each copy of the table must be assigned a table alias.​

TRUE

Structure of the CUSTOMERS table ​ 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?

both a and b

Structure of the CUSTOMERS table ​ 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?

both a and c

Which of the following types of joins is created by matching equivalent values in each table?​

equality join

Structure of the BOOKS table Which of the following SQL statements will display the gift that should be sent to any customer who orders the book titled THE WOK WAY TO COOK?

none of the above

​In which of the following examples is the ORDERS table used as a column qualifier?

orders.order#

​A column qualifier is separated from the column using which symbol?

period (.)

Cartesian join keywords:

· Not including a joining condition in a WHERE clause · Using the JOIN method with the CROSS JOIN keywords

Non-equality Join keywords:

· Using the WHERE clause Using the JOIN method with the JOIN...ON keywords

Equality Join Keywords:

· Using the WHERE clause Using the JOIN method with the NATURAL JOIN, JOIN...ON, or JOIN.. USING keywords

Self-join keywords:

· Using the WHERE clause Using the JOIN method with the OUTER JOIN keywords and the assigned type of LEFT, RIGHT, or FULL

Outer join keywords:

· Using the WHERE clause with a (+) operator Using the JOIN method with the OUTER JOIN keywords and the assigned type of LEFT, RIGHT, or FULL

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

​FROM orders o, customers c

Structure of the BOOKS table Which of the following SQL statements will display the title of all books that have had multiple copies requested in a single order?

​SELECT title FROM books NATURAL JOIN orderitems WHERE qty > 1;