Quiz 6 (week 401)

FULL JOIN:

- Return all rows when there is a match in ONE of the tables -combines the result of both LEFT and RIGHT joins.

________ use the result of the outer query to determine the processing of the inner query. A. Correlated subqueries B. Outer subqueries C. Inner subqueries D. Subqueries

A. correlated subqueries

A join operation A. brings together data from two different fields. B. causes two tables with a common domain to be combined into a single table or view. C. causes two disparate tables to be combined into a single table or view. D. is used to combine indexing operations.

B. causes two tables with a common domain to be combined into a single table or view.

A ________ is a temporary table used in the FROM clause of an SQL query. A. correlated subquery B. derived table C.view table D.None of these

B. derived table

A type of join where a table is joined to itself is called a(n): A. unary join. B. self-join. C. unnatural join. D.pinned join.

B.Self-join

A type of query that is placed within a WHERE or HAVING clause of another query is called a: Selected Answer: A. master query. B. subquery. C. superquery. D. multi-query.

B.subquery

A join in which rows that do not have matching values in common columns are still included in the result table is called a(n): A. natural join. B. equi-join. C. outer join. D. union join.

C. Outer join

The ________ clause is used to combine the output from multiple queries into a single result table. Answers: A. INTERSECT B. DIVIDE C. COLLATE D. UNION

D. UNION

A join in which the joining condition is based on equality between values in the common columns is called a(n): A. equi-join. B. unilateral join. C. natural join. D. both equi-join and natural join.

D. both equi-join and natural join.

non-correlated subqueries

Do not depend on data from the outer query Execute once for the entire outer query -processes completely before the outer query begins.

Subqueries can only be used in the WHERE clause. True or false

FALSE

correlated subquery

Make use of data from the outer query Execute once for each row of the outer query Can use the EXISTS operator

RIGHT JOIN:

Return all rows from the right table, and the matched rows from the left table

NATURAL JOIN EXAMPLE

SELECT * FROM table1 NATURAL JOIN table2;

example subquery

SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostaiCode FROM Customer_T WHERE Customer_T.CustomeriD = /////////////////////////////////////////////////////////// (SELECT Order_ T.CustomeriD FROM Order_T WHERE OrderiD = 1 008);

Correlated subquery example

SELECT ProductDescription, ProductFinish, ProductStandardPrice FROM Product_r@----------WHERE PA.ProductStandardPrice >ALL (SELECT ProductStandardPr~ FROM Product_T WHERE PB.ProductiD ! =~~)ProductiD);

EQUI JOIN Example

SELECT column_list FROM table1, table2.... WHERE table1.column_name = table2.column_name;

Left join example

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name; LEFT OUTER JOIN can also be used

RIGHT JOIN example

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name

A correlated subquery is executed once for each iteration through the outer loop. TRUE OR FALSE

TRUE

A join in which the joining condition is based on equality between values in the common column is called a(n) equi-join. True False

TRUE

A natural join is the same as an equi-join, except that it is performed over matching columns that have been defined with the same name, and one of the duplicate columns is eliminated. TRUE FALSE

TRUE

natural join.

associated tables= columns with same name - columns same data type. - Don't use ON clause in a natural join.

An equi-join is a join in which one of the duplicate columns is eliminated in the result table true false

false

equi-join.

like inner join match rows from different tables uses =

subqueries are always placed in

parenthesis

Subqueries

putting an inner query (SELECT ... FROM ... WHERE) in a WHERE or HAVING clause of another (outer) query. The inner query provides a set of one or more values for the search condition of the outer query.

outer join

return all the values in one of the tables included in the join, regardless of whether or not a match exists in the other table

LEFT JOIN

returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

INNER JOIN

selects all rows from both tables as long as there is a match between the columns in both tables.

An SQL query that implements an outer join will return rows that do not have matching values in common columns. True fasle

true

JOINS

used to combine rows from two or more tables