CHAPTER 8: ADVANCED SQL

1

1. A relational join operation merges rows from two tables.

1

2. An inequality condition is also known as a natural join and an equality condition is also called a theta join.

1

3. Subqueries cannot be used in combinations with joins.

1

4. The SELECT statement uses the attribute list to indicate what columns to project in the resulting set.

1

5. Numeric functions take one numeric parameter and return one value.

1

6. String manipulation functions are rarely used in programming.

1

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

1

8. A view is a virtual table based on a SELECT query.

1

9. A sequence is not associated with a table and can be dropped from a database with a DROP SEQUENCE command.

1

10. SQL supports the conditional execution of procedures (IF-THEN-ELSE statements) that are typically supported by a programming language.

1

11. 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.

1

12. A persistent stored module is stored and executed on the database client machine.

1

13. Every PL/SQL block must be given a name.

1

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

1

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

1

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

1

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

1

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

1

19. Triggers can only be used to update table values.

1

20. A statement-level trigger is assumed if a designer omits the FOR EACH ROW keywords.

1

21. A row-level trigger is assumed if we omit the FOR EACH ROW keywords and a statement-level trigger required the use of the FOR EACH ROW keyword.

1

22. MySQL allows multiple triggering conditions per trigger.

1

23. BEFORE means before the changes are made in memory but after the changes are permanently saved to disk.

1

24. Just like database triggers, stored procedures are stored in the database.

1

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

1

26. Stored procedures must have at least one argument.

1

27. Variables can be declared inside a stored procedure.

1

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

1

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

1

30. An explicit cursor must return two or more rows.

1

31. A stored function is another name for a stored procedure.

1

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.

1

33. The following SQL statement uses a(n) .
SELECT P_CODE, P_DESCRIPT, P_PRICE, V_NAME FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

1

34. When using a(n) join, only rows that meet the given criteria are returned.

1

35. The statement SELECT * FROM T1, T2 produces a(n) join.

1

36. How many rows would be returned from a cross join of tables A and B, if A contains 8 rows and B contains 18?

1

37. A(n) join will select only the rows with matching values in the common attribute(s).

1

38. If a designer wishes to create an inner join, but the two tables do not have a commonly named attribute, he can use a(n) _____ clause.

1

39. 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.

1

40. The syntax for a left outer join is .

1

41. In subquery terminology, the first query in the SQL statement is known as the query.

1

42. In the context of SELECT subquery types, a is returned when an UPDATE subquery is used.

1

43. Which of the following is a feature of a correlated subquery?

1

44. The function returns the current system date in MS Access.

1

45. When using the Oracle TO_DATE function, the code represents a three-letter month name.

1

46. In Oracle, the function converts a date to a character string.

1

is a string function that returns the number of characters in a string value.

1

48. When using the Oracle TO_NUMBER function to convert a character string into a number, represents a digit.

1

49. The Oracle 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.

1

50. is a relational set operator.

1

51. “Unioncompatible” means that the .

1

52. The data type is considered compatible with VARCHAR(35).

1

53. The statement combines rows from two queries and excludes duplicates.

1

54. Assume a designer is using the UNION operator to combine the results from two tables with identical structure, CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains 7 rows. Customers Jenna and Howard are included in the CUSTOMER table as well as in the CUSTOMER_2 table. How many records are returned when using the UNION operator?

1

55. Assume you are using the UNION ALL operator to combine the results from two tables with identical structure, CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains 7 rows. Customers Dunne and Olowski are included in the CUSTOMER table as well as in the CUSTOMER_2 table. How many records are returned when using the UNION ALL operator?

1

56. Assume you are using the INTERSECT operator to combine the results from two tables with identical structure, CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains 7 rows. Customers Dunne and Olowski are included in the CUSTOMER table as well as in the CUSTOMER_2 table. How many records are returned when using the INTERSECT operator?

1

57. 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.

1

58. Assume you are using the MINUS operator to combine the results from two tables with identical structure, CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains 7 rows. Customers Dunne and Olowski are included in the CUSTOMER table as well as in the CUSTOMER_2 table. How many records are returned when using the MINUS operator?

1

59. The operator could be used in place of INTERSECT if the DBMS does not support it.

1

60. The operator could be used in place of MINUS if the DBMS does not support it.

1

61. The Oracle equivalent to an MS Access AutoNumber is a(n) .

1

62. Which of the following is a feature of oracle sequences?

1

63. The pseudo-column is used to select the next value from a sequence.

1

64. In Oracle, make(s) it possible to merge SQL and traditional programming constructs, such as variables, conditional processing (IF-THEN-ELSE), basic loops (FOR and WHILE loops,) and error trapping.

1

65. The Oracle string concatenation function is .

1

66. The PL/SQL block starts with the section.

1

67. Oracle recommends for creating audit logs.

1

68. is a cursor attribute that returns TRUE if the last FETCH returned a row, and FALSE if not.

CIS 3365 Test sobre CHAPTER 8: ADVANCED SQL, creado por Miguel Lucero el 05/04/2017.

CHAPTER 8: ADVANCED SQL

CHAPTER 8: ADVANCED SQL

1. A relational join operation merges rows from two tables.

2. An inequality condition is also known as a natural join and an equality condition is also called a theta join.

3. Subqueries cannot be used in combinations with joins.

4. The SELECT statement uses the attribute list to indicate what columns to project in the resulting set.

5. Numeric functions take one numeric parameter and return one value.

6. String manipulation functions are rarely used in programming.

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

8. A view is a virtual table based on a SELECT query.

9. A sequence is not associated with a table and can be dropped from a database with a DROP SEQUENCE command.

10. SQL supports the conditional execution of procedures (IF-THEN-ELSE statements) that are typically supported by a programming language.

11. 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.

12. A persistent stored module is stored and executed on the database client machine.

13. Every PL/SQL block must be given a name.

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

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

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

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

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

19. Triggers can only be used to update table values.

33. The following SQL statement uses a(n) .
SELECT P_CODE, P_DESCRIPT, P_PRICE, V_NAME FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE;