Chapter 4
A union combines the rows from two or more what?
All of the above: SELECT statements Result tables Queries
Consider the follwoing code example: SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoice AS i ON v.VendorID = i.VendorID; The name "v" is known as?
Both b and C Correlation name Table alias
Consider the follwoing code example: SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; The column name for the second column in the result set will be?
Date
In most cases, the join condition of an inner join compares the primary key of one table to the ______key of another table.
Foreign
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
Consider the following code example: SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS JOIN Invoices AS i ON v.VendorID = i.VendorID; This type of join is called/an
Inner join
If you want to join all of the rows in the first table of a SELECT statement with just the matched rows in the second table, you use what kind of join
Left outer
Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for inner joins? The explicit syntax
Let's you combine the join and seearch conditions
A full outer join includes rows that satisfy the join conditon, plus
Rows in both tables that dont satisfy the join condition
Consider the following code example: SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; The total number of rows returned by this query must equal?
The number of rows in the Invoices table
When you use the implicit syntax for coding inner joins, the join conditions are coded in which clause?
Where
If you assign a correlation name to one table in a join,
You have to use the name for the table in the query
A join that joins a table with itself is called
a self-join
In a cross join, all the rows in the first table are joined with all of the
rows in the second table
Correlation names are temporary table names assigned in which clause?
From
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
Consider the following code example: SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; This join is coded using the syntax?
Both B and C Explicit SQL-92
When you need to code multiple conditions in a join, it's best to
Code only join conditions in the ON clause
_______ names can be used when you want to assign a temporary name to a table.
Correlation
Consider the following code example: SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; If the LEFT keyword is replaced with the RIGHT keyword, the total number of rows that are returned must equal
None of the above: THe number of rows in the Invoices table The number of rows in the Vendors table The number of rows in the Invocies table plus the number of rows in the Vendors table
Consider the following code example: SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; If the LEFT keyword is replaced with the FULL keyword, the total number fo rows that are returned must equal
None of the above: The number of rows in the Invoices table The number of rows in the Vendors table The number of rows in the Invoices table plus the number of rows in the Vendors table
A fuller outer join includes rows that satisfy the join condition, plus
Rows in both tables that don't satisfy the join condition
Your code will be easier to read if you code the join condition in the ON expression, and the search conditions in the which clause?
WHERE
In a join, column names only need to be qualified where?
When the same names are used in both tables