Ch.4 - How to retrieve data from two or more tables

Code example 4-1 SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS V JOIN Invoices AS I ON V.VendorID = I.VendorID; (Refer to code example 4-1.) The column name for the second column in the result set will be _________________.

Date

When you code a union that combines two result sets, which of the following is not true?

The result sets must be derived from different tables.

When you use the implicit syntax for coding inner joins, the join conditions are coded in the ____________ clause.

WHERE

When you need to code multiple conditions in a join, its best to

code only join conditions in the ON clause

Code example 4-1 SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS V JOIN Invoices AS I ON V.VendorID = I.VendorID; (Refer to code example 4-1.) The name "V" is known as a/an ________________.

correlation name

In most cases, the join condition of an inner join uses the _______________ operator to compare two keys.

equal

Code example 4-1 SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS V JOIN Invoices AS I ON V.VendorID = I.VendorID; (Refer to code example 4-1.) This join is coded using the _______________ syntax.

explicit

In most cases, the join condition of an inner join compares the primary key of one table to the ____________________ key of another table.

foreign

If you want to join all of the rows in two tables whether or not they are matched, you use a/an _______________ join.

full outer

When you code a union with the INTERSECT keyword to combine two result sets, the union

includes only rows that occur in both result sets

Code example 4-1 SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS V JOIN Invoices AS I ON V.VendorID = I.VendorID; (Refer to code example 4-1.) This type of join is called a/an __________________ join.

inner

If you want to join all of the rows in the first table of a SELECT statement with just the matched rows in a second table, you use a/an _______________ join.

left outer

Code example 4-2 SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; (Refer to code example 4-2.) If the LEFT keyword is replaced with the FULL keyword, the total number of rows that are returned must equal

none of the above

Code example 4-2 SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; (Refer to code example 4-2.) If the LEFT keyword is replaced with the RIGHT keyword, the total number of rows that are returned must equal

none of the above

A union combines the rows from two or more ___________________.

result tables

A full outer join includes rows that satisfy the join condition, plus

rows in both tables that don't satisfy the join condition

In a cross join, all of the rows in the first table are joined with all of the

rows in the second table

Code example 4-2 SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; (Refer to code example 4-2.) The total number of rows returned by this query must equal

the number of rows in the Invoices table

In a join, column names need to be qualified only

when the same names are used in both tables

If you assign a correlation name to one table in a join,

you have to use that name for the table