S307 Final Exam Study Guide with Complete

S307 Final Exam Study Guide with Complete Solutions join - Correct Answer - relational operation that causes two or more tables with a common domain to be combined into a single table or view

• can use "JOIN" or "INNER JOIN"..."ON"
• common columns appear redundantly in the result table
• can use "=", ">","<" ,"<>"

equi-join - Correct Answer - join in which the joining condition is based on equality between values in the common columns natural join - Correct Answer - equi-join in which one of the duplicate columns is eliminated in the result table outer join - Correct Answer join in which rows that do not have matching values in common columns are nonetheless included in the result table union - Correct Answer combines the result of two or more SELECT statements inner join - Correct Answer - only return rows from each table that have matching rows in the other What do you have to do in joins to ensure referential integrity? - Correct Answer each pair of tables requires an equality-check condition in the WHERE clause, matching primary keys against foreign keys self-join - Correct Answer - add table twice using aliases

• SELECT E.EmployeeID, E.EmployeeName, M.EmployeeName AS Manager

FROM Employee_T E, Employee_T M WHERE E.EmployeeSupervisor = M.EmployeeID subquery - Correct Answer placing an inner query (SELECT statement) inside an outer query 1 / 2

non correlated subquery - Correct Answer - do not depend on data from outer query

• execute once for entire outer query

correlated subquery - Correct Answer - execute once for each row returned by the outer query

• make use of data from outer query
• can use EXISTS operator

IN operator - Correct Answer - allows you to specify multiple values in a WHERE clause

• SELECT * FROM Customers WHERE City IN ('Paris', 'London');

Can aggregates be in WHERE clauses? - Correct Answer NO What must be true for a UNION query? - Correct Answer quantity and data types of the attributes in the SELECT clauses of both queries must be identical transaction - Correct Answer - discrete unit of work that must be completely processed or not processed at all BEGIN TRANSACTION/END TRANSACTION - Correct Answer marks boundaries of a transaction COMMIT - Correct Answer makes all updates permanent ROLLBACK - Correct Answer cancels updates since last commit routines - Correct Answer program modules that execute on demand functions - Correct Answer routines that return values and take input parameters procedures - Correct Answer routines that do not return values and can take input or output parameters

• / 2