Database Final Module 7

10. How many times is a Type II nested query executed as part of an outer query?

A Type II nested query executes one time for each row in the outer query.

11. How is a Type II nested query like a nested loop in a computer program?

A Type II nested query is executed repeatedly like a loop.

2. Explain a situation when a full outer join is useful?

A full outer join is useful when you want the output of the query with the matching rows plus the nonmatching rows from both tables.For example, if you want to find all university people (students and faculty) who live in a certain city, a full outer join can combine the Student and Faculty tables.

1. Explain a situation when a one-sided outer join is useful?

A one-sided outer join is useful when you want the output of the query with the matching rows plus the nonmatching rows from one of the tables. For example in the University Database, it can be useful to see all offerings listed in the output even if an offering does not have an assigned faculty.

13. What makes a query ambiguous?

A query is ambiguous if a non-preserved table (table with only matching rows in the result) in a one-sided outer join is involved in another join or outer join operation. The result of an ambiguous query may depend on the order of joins and one-sided join operations in the FROM clause.

How many times is a Type I nested query executed as part of an outer query?

A type I nested query does not execute as part of an outer query because the nested (inner) query does not reference the outer query. Instead, it produces output before the outer query is executed.

Null values ignored Effects can be subtle COUNT(*) may differ from Count(Column) SUM(Column1) + SUM(Column2) may differ from SUM(Column1 + Column2) ?

Aggregate functions

Involve self-referencing relationships Child row related to at most one parent row Organization chart Part explosion diagram Chart of accounts Finding details or summarizing features of employees managed directly or indirectly Hierarchical queries not common but important in some business situations?

Background

involve one or more simple conditions connected by the Boolean operators AND, OR, and NOT. Like simple conditions, compound conditions evaluate to true, false, or null. A row is selected if the entire _________ in the WHERE clause evaluates to true.

Compound Condition

Why arent ambiguous queries common

Despite the possibility for ambiguous queries, they are not common because the non-preserved table is typically not involved in other operations. The parent table is typically the non-preserved table. The child table is usually preserved and involved in other operations. An ambiguous query may indicate an error in query formulation rather than a valid formulation to address a legitimate business request.

4. What is the interpretation of the FULL JOIN keywords in the FROM clause?

FULL JOIN is short for FULL OUTER JOIN. The FULL JOIN keywords indicate a full outer join operation in which the non matching rows from both input tables are preserved in the result.

Example of a query using right or left join?

FacFirstName, FacLastName FROM Faculty RIGHT JOIN Offering ON Offering.FacNo = Faculty.FacNo WHERE CourseNo LIKE 'IS%'

Rows with null values are grouped together Grouping column contains null values Null group can be placed at beginning or end of the non-null groups?

Grouping Effects

queries involve self-referencing relationships in which a child entity is related to at most one parent entity. Classical organization charts, part explosion diagrams, chart of accounts, and XML documents are the most prominent examples amendable to these?

Hierarchiel queries

8. What is the distinguishing feature about the appearance of Type II nested queries?

In type II nested queries, the inner query references a table used in the outer query

what is the divide operator

Match on a subset of values Suppliers who supply all parts Faculty who teach every IS course Specialized operator Typically applied to associative tables representing M-N relationships Can also be applied to child (M) tables in a 1-M relationship

Ambiguity Rule?

Non preserved table (only matching rows) No other operations (joins or outer joins) for non preserved tables

Identify two situations in which nested queries in the FROM clause are necessary.

One usage of nested queries in the FROM clause is to compute an aggregate function within an aggregate function (nested aggregates). SQL does not permit an aggregate function inside another aggregate function. A nested query in the FROM clause overcomes the prohibition against nested aggregates. Another usage of a nested query in the FROM clause is to compute aggregates from multiple groupings. Without a nested query in the FROM clause, a query can contain aggregates from one grouping.

Full outer join example?

SELECT FacNo, FacFirstName, FacLastName, FacSalary, StdNo, StdFirstName, StdLastName, StdGPA FROM Faculty, Student WHERE Student.StdNo = Faculty.FacNo (+) UNION SELECT FacNo, FacFirstName, FacLastName, FacSalary, StdNo, StdFirstName, StdLastName, StdGPA FROM Faculty, Student WHERE Student.StdNo (+) = Faculty.FacNo

nested query example?

SELECT FacNo, FacLastName, FacDept FROM Faculty WHERE FacDept = 'FIN' AND FacNo IN ( SELECT FacNo FROM Offering WHERE CourseNo LIKE 'IS%' AND CourseNo IN ( SELECT CourseNo FROM Course WHERE CrsUnits = 4 ) ) Delete nested query DELETE FROM Offering WHERE Offering.FacNo IN ( SELECT FacNo FROM Faculty WHERE FacFirstName = 'Leonard' AND FacLastName = 'Vince' )

Example of type 2 nested query?

SELECT FacNo, FacLastName, FacDept FROM Faculty WHERE FacDept = 'MS' AND NOT EXISTS ( SELECT * FROM Offering WHERE OffTerm = 'WINTER' AND OffYear = 2013 AND Faculty.FacNo = Offering.FacNo )

Ambiguity query example?

SELECT OfferNo, Offering.CourseNo, OffTerm, CrsDesc, Faculty.FacNo, FacLastName FROM ( Faculty LEFT JOIN Offering ON Offering.FacNo = Faculty.FacNo ) INNER JOIN Course ON Course.CourseNo = Offering.CourseNo Ambiguity: Non preserved table (Offering) joined with Course after the outer join

Importance of SQL extensions?

Self-joins not sufficient due to variable number of self-joins Write stored procedure with looping for variable number of joins Higher productivity with SQL extension SQL compiler optimization with SQL extensions

What is a type II nested query?

Similar to nested loops Executes one time for each row of outer query Reference to outer query Also known as correlated or variably nested query Use for difference problems not joins

conditions that involve a comparison operator, a column or column expression and a constant, column, or column expression. A _______ results in a null value if either column (or column expression) in a comparison is null. A row qualifies in the result if the simple condition evaluates to true for the row. Rows evaluating to false and null are discarded.

Simple condition

- Use of every or all connecting different parts of a sentence - Use any or some: join problem - Specialized matching but important when necessary

Subset Matching

12. What is the meaning of the IN comparison operator?

The IN comparison operator means "element of". When it is used to express a join, a row is selected in the outer query if the join column is in an element of the nested query result

3. How do you interpret the meaning of the LEFT and RIGHT JOIN keywords in the FROM clause?

The LEFT JOIN keyword means generate a table containing the matching rows and the nonmatching rows of the "left" table. The RIGHT JOIN keyword means generate a table containing the matching rows and the nonmatching rows of the "right" table.

7. What is the distinguishing feature about the appearance of Type I nested queries?

Type I nested query is independent of outer query - Non-correlated or independent nested query No reference to outer queries

5. How do you perform a full outer join in SQL implementations (such as Microsoft Access and Oracle 8i) that do not support the FULL JOIN keywords?

You need to perform a union operation on the results of two one-sided join operation

9. How is a Type I nested query like a procedure in a computer program?

executed one time like a program

what is a nested query?

query inside a query (SELECT statement inside a SELECT statement) - Use in conditions in the WHERE and HAVING clauses Similar to a nested procedure Executes one time No reference to outer query Also known as non-correlated or independent nested query Replaced by a list of values

What is a full outer join?

retrieve all rows of two similar but not union compatible tables Can use full outer join on tables that are not union compatible - Full outer join makes comparison on join column(s) not all columns - Preserve non matching rows of both tables

Simple condition is null if either left-hand or right-hand side is null. Discard rows evaluating to false or null Retain rows evaluating to true Rows evaluating to null will not appear in the result of the simple condition or its negation

simple conditions

Null values effect?

values affect simple conditions involving comparison operators, compound conditions involving Boolean operators, aggregate calculations, and grouping