SQL: Chapter 4

fully-qualified versus partially-qualified object names

- fully-qualified object names have four parts: server name, database name, schema name (dbo) and object (table) DBServer.ProductOrders.dbo.Customers - partially-qualified object name allows you to omit the name that falls between two parts of the name by coding two periods to indicate the name is omitted. ProductOrders..Customers

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... 1. the number of rows in the Invoices table 2. the number of rows in the Vendors table 3. the number of rows in the Invoices table plus the number of rows in the Vendors table 4. none of the above

1. the number of rows in the Invoices table

When you code a union with the INTERSECT keyword to combine two result sets, the union 1. excludes rows from the first result set if they also occur in the second result set 2. includes only rows that occur in both result sets 3. excludes rows from the second result set if they also occur in the first result set 4. includes all rows that occur in both result sets if the primary keys are the same

2. includes only rows that occur in both result sets

In a cross join, all of the rows in the first table are joined with all of the 1. unmatched rows in the second table 2. matched rows in the second table 3. rows in the second table 4. distinct rows in the second table

3. rows in the second table A cross join joins each row from the first table with each row from the second table. Because of the way the cross join works, you don't include a join condition (ON).

You don't ever need to code a right outer join because 1. left outer joins are easier to code 2. right outer joins are less efficient 3. left outer joins are just as efficient 4. right outer joins can be converted to left outer joins

4. right outer joins can be converted to left outer joins Common practice to avoid right outer join by reversing the order of the tables in the FROM clause and using a left outer join. IT is easier to read statements that join more than two tables.

outer join

A join that includes all of the rows from one table and only those rows from the other table that match the join field in the first table. If the second table does not have a match, a null value is used in the result table.

Unions

Combines data from two or more tables.

Interim Table

Contains interim results of joins of more than 2 tables. Each join is an interim.

what are correlation names (table aliases) and how do they work?

Correlation names are temporary table names assigned in the FROM clause. They are used when long table names make qualified column names long or confusing. If you use a correlation name, it must be used when referring to the table throughout the code. FROM table AS correlation name

Which keyword is often used with self-joins to eliminate duplicate rows?

DISTINCT

EXCEPT versus INTERCEPT

EXCEPT returns any distinct values from the left query that are not also found on the right query. INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand. The basic rules for combining the result sets of two queries that use EXCEPT or INTERSECT are the following: 1. The number and the order of the columns must be the same in all queries. 2. The data types must be compatible.

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

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

Foreign

What is the join condition?

Join conditions indicate how the two tables should be compared. Most cases, the join is on the relationship between the primary key and the foreign key. SELECT FROM JOIN ON < this is the join condition for explicit syntax (SQL-92 syntax). SELECT FROM table, table WHERE < this is the join condition for implicit syntax.

self-join

Joins a table to itself When doing so you must correlate the names of the table, and qualify each column name. SELECT I.VendorID, I2.InvoiceNo FROM Invoice AS I JOIN Invoice AS I2 ON I.InvoiceID=I2.InvoiceID;

The similarities and differences between joins and unions:

Joins and Unions can be used to combine data from one or more tables. The difference lies in how the data is combined. -In simple terms, joins combine data into new columns. If two tables are joined together, then the data from the first table is shown in one set of column alongside the second table's column in the same row. -Unions combine data into new rows. If two tables are "union-ed" together, then the data from the first table is in one set of rows, and the data from the second table in another set. The rows are in the same result. Additionally, each result set must return the same number of columns, and the corresponding columns in each result set must have compatible data types. Lastly, duplicate rows are eliminating (code ALL to prevent this)

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

Can you combine inner and outer joins using the implicit syntax?

No, combining inner and outer joins can only be done using explicit join syntax.

Is it best to join as many tables as possible since SQL Server lets you join data from up to 256 tables?

No, you should limit the number of joined tables whenever possible.

Implicit Syntax (Theta syntax)

Pre SQL92 join Syntax. Instead of coding a join condition in the FROM clause, you code it in the WHERE clause along with any search conditions. If no join condition is in the WHERE clause, a cross join is performed.

A union combines the rows from two or more _______________________.

SELECT statements With unions, the results of each SELECT statement must have the same number of columns, and data types of the corresponding columns in each table must be compatible.

How do you code a qualified column name?

Table.columnName

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

WHERE With implicit syntax for an inner join, the tables are joined by listing the tables in the FROM clause. Then, the join conditions are in the WHERE clause.

Can a join have more than one condition?

Yes, join conditions can include two or more conditions connected by AND or OR operators.

Can you union data from the same table?

Yes, you can union two select statements using the same table. Remember to use ORDER BY to sort the data.

Before you can specify a server name, you must add a ________ ______ to the current instance of the server.

linked server A virtual server that specifies all the information necessary to be able to connect to a local or remote server.

What is the syntax of a fully-qualified object name?

linkedServer.Database.Schema(dbo).Object(table)

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

second table. This is known as a Cartesian product.

In a join, column names need to be qualified only 1. when the same names are used in both tables 2. in outer joins 3. in inner joins 4. when the code is confusing

when the same column names exist in both tables