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