Q&A Hero

Q: A ____ join returns rows with matching values and includes all rows from both tables (T1 and T2) with unmatched values.a. natural b. cross c. full outerd. left outer

Q: The syntax for a left outer join is ____. a. SELECT column-list FROM table1 OUTER JOIN table2 LEFT WHERE join-condition b. SELECT column-list FROM table1 LEFT [OUTER] JOIN table2 ON join-condition c. SELECT column-list WHERE LEFT table1 = table 2 d. SELECT column-list FROM table1 LEFT table2 [JOIN] WHERE join-condition

Q: A(n) ____ join returns not only the rows matching the join condition (that is, rows with matching values in the common columns) but also the rows with unmatched values.a. outer b. inner c. equi-d. cross

Q: The ANSI standard defines ____ type(s) of outer join(s)a. one b. two c. threed. four

Q: If you wish to create an inner join, but the two tables do not have a commonly named attribute, you can use a(n) ____ clause.a. OF b. USING c. HASd. JOIN ON

Q: A(n) ____ join will select only the rows with matching values in the common attribute(s).a. natural b. cross c. fulld. outer

Q: How many rows would be returned from a cross join of tables A and B, if A contains 8 rows and B contains 18?a. 8 b. 18 c. 26d. 144

Q: The statement SELECT * FROM T1, T2 produces a(n) ____ join.a. cross b. natural c. equi-d. full

Q: When using a(n) ____ join, only rows that meet the given criteria are returned.a. full b. inner c. outerd. set

Q: The following SQL statement uses a(n) ____.SELECT P_CODE, P_DESCRIPT, P_PRICE, V_NAMEFROM PRODUCT, VENDORWHERE PRODUCT.V_CODE = VENDOR.V_CODE;a. set operatorb. natural join c. "old-style" joind. procedural statement

Q: "Linked SQL" is a term used to refer to SQL statements that are contained within an application programming language such as COBOL, C++, ASP, Java, or ColdFusion.

Q: A stored function is another name for a stored procedure.

Q: Cursor-style processing involves retrieving data from the cursor, one row at a time.

Q: The NEXT command for a cursor is used to retrieve a row from the cursor and place it in the respective PL/SQL variables.

Q: An explicit cursor must return two or more rows.

Q: An implicit cursor is automatically created in procedural SQL when the SQL statement returns only one value.

Q: Cursors are held in a reserved memory area in the client computer.

Q: Stored procedures are executed using the EXEC command.

Q: You can declare variables inside a stored procedure.

Q: Stored procedures must have at least one argument.

Q: Stored procedures help reduce code duplication by means of code isolation and code sharing.

Q: One of the disadvantages of stored procedures is that they increase network traffic.

Q: One of the major advantages of stored procedures is that they can be used to encapsulate and represent business transactions.

Q: Just like database triggers, stored procedures are stored in the database.

Q: To test a trigger, you should use the EXECUTE TRIGGER command.

Q: Each statement inside the PL/SQL code must end with a period (".").

Q: A statement-level trigger is assumed if you omit the FOR EACH ROW keywords.

Q: Triggers can only be used to update table values.

Q: A trigger is executed as part of the transaction that triggered it.

Q: A trigger is procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a given data manipulation event.

Q: Automating business procedures and automatically maintaining data integrity and consistency are trivial in a modern business environment.

Q: The most useful feature of PL/SQL blocks is that they let you create code that can be named, stored, and executed by the DBMS.

Q: PL/SQL blocks have a section used to declare variables.

Q: In Oracle, you can use the SQL*Plus command SHOW ERRORS to help you diagnose errors found in PL/SQL blocks.

Q: Every PL/SQL block must be given a name.

Q: PL/SQL functions are executed in the same way as functions such as MIN and AVG.

Q: Procedural code is executed on the database client machine.

Q: To remedy the lack of procedural functionality in SQL, and to provide some standardization within the many vendor offerings, the SQL-99 standard defined the use of persistent stored modules.

Q: SQL supports the conditionalexecution of procedures (if...then...else statements) that are typically supported by a programming language.

Q: UNION, INTERSECT, and MINUS work properly only if relations are union-compatible, which means that the names of the relation attributes and their data types must be different.

Q: Triggers are critical to proper database operation and management. What are some of the ways that triggers are used?

Q: Describe the characteristics of an Oracle sequence.

Q: What are SQL functions and when are they used? Provide a couple of examples of situations in which they are necessary.

Q: What are the four different result types that can be returned from a subquery? For each type, explain what it is and when it is used.

Q: Explain the different basic types of join operations. What are they and how do they work?

Q: A row-level triggerrequires use of the ____________________ keywords. This type of trigger is executed once for each row affected by the triggering statement.

Q: Using Oracle ____________________, you can write a PL/SQL code block by enclosing the commands inside BEGIN and END clauses.

Q: A(n) ____________________ view is a view that can be used to update attributes in the base table(s) that are used in the view.

Q: A(n) ____________________ routine pools multiple transactions into a single batch to update a master table field in a single operation.

Q: In an Oracle sequence, the ____________________ pseudo-column retrieves the current value of a sequence.

Q: In MS Access, you can use the ____________________ data type to define a column in your table that will be automatically populated with unique numeric values.

Q: If the DBMS does not support the INTERSECT statement, you can use a(n) ____________________ subquery to achieve the same result.

Q: The ____________________ statement in SQL combines rows from two queries and returns only the rows that appear in the first set but not in the second.

Q: The ____________________ statement can be used to combine rows from two queries, returning only the rows that appear in both sets.

Q: A(n) ____________________ query can be used to produce a relation that retains the duplicate rows.

Q: The ____________________ statement combines rows from two or more queries without including duplicate rows.

Q: The ____________________ function compares an attribute or expression with a series of values and returns an associated value or a default value if no match is found.

Q: ____________________ functions allow you to take a value of a given data type and convert it to the equivalent value in another data type.

Q: Oracle uses the ____________________ function to extract the various parts of a date.

Q: A(n) ____________________ subquery is a subquery that executes once for each row in the outer query.

Q: The use of the ____________________ operator allows you to compare a single value with a list of values returned by the first subquery (sqA) using a comparison operator other than EQUALS.

Q: The IN subquery uses a(n) ____________________ operator.

Q: The ____________________ clause is used to restrict the output of a GROUP BY query by applying a conditional criteria to the grouped rows.

Q: When using a subquery, the output of a(n) ____________________ query is used as the input for the outer query.

Q: The ____________________ outer join returns not only the rows matching the join condition (that is, rows with matching values in the common column), but also the rows in the left-side table with unmatched values in the right-side table.

Q: An alternate syntax for a join is SELECT column-list FROM table1 JOIN table2 ____________________ (common-column).

Q: A(n) ____________________ join returns all rows with matching values in the matching columns and eliminates duplicate columns.

Q: A(n) ____________________ join performs a relational product (also known as the Cartesian product) of two tables.

Q: A(n) ____________________ joinreturns not only the matching rows but also the rows with unmatched attribute values for one or both tables to be joined.

Q: How do you delete a table from the database? Provide an example.

Q: What are the wildcard characters that are used with the LIKE command? Provide one or more examples of each.

Q: What is a subquery? When is it used? Does the RDBMS deal with subqueries any differently from normal queries?

Q: What command is used to save changes to the database? What is the syntax for this command?

Q: What is a schema? How many schemas can be used in one database?

Q: An alias is especially useful when a table must be joined to itself in a(n) ____________________ query.

Q: A(n) ____________________ is performed when data is retrieved from more than one table at a time.

Q: The ____________________ clause of the GROUP BY statement operates very much like the WHERE clause in the SELECT statement.

Q: Rows can be grouped into smaller collections quickly and easily using the SQL ____________________ clause.

Q: A(n) ____________________ order sequence is a multilevel ordered sequence that can be created easily by listing several attributes, separated by commas, after the ORDER BY clause.

Q: A table can be deleted from the database by using the ____________________ command.