Chapter 6 SQL Databases

What is a join in relational databases?

A relational operation where two or more tables with a common domain combine into a single table/view.

What is an equi-join?

A join where the condition is based on equality between values in the common columns, which appear redundantly in the result table.

Provide an example of an equi-join SQL query.

SELECT Customer_T.CustomerID, Order_T.CustomerID, CustomerName, OrderID FROM Customer_T, Order_T WHERE Customer_T.CustomerID = Order_T.CustomerID ORDER BY OrderID.

What is an inner join?

Matches primary and foreign keys and only returns rows from each table that have matching rows in the other.

What is a natural inner-join?

An equi-join where one of the duplicate columns is eliminated in the result table.

What is an outer join?

A join where rows that don’t have common columns are included in the result table regardless.

What is a union join?

Includes all data from each table that was joined.

What is a left outer join?

Causes rows from the first-mentioned table to appear even if there is no corresponding data in the second table.

What is a self join?

Involves tables that implement 1-many unary relationships.

What is a subquery?

Placing an inner query (SELECT statement) inside an outer query, usually placed in the WHERE or HAVING clause of the outer query.

What is a noncorrelated subquery?

A subquery that does not depend on data from the outer query and is executed once for the entire outer query.

What is a correlated subquery?

A subquery that makes use of data from the outer query and is executed once for each row returned by the outer query.

List one way to improve query efficiency related to subqueries.

Limit the number of subqueries; try to make everything done in a single query if possible.

What is one strategy for reducing network traffic in a SQL query?

Instead of SELECT *, identify the specific attributes in the SELECT clause.

What can be done if data needs to be used multiple times in a database?

Make a separate query and store it as a view.