Miguel Lucero
Quiz by , created more than 1 year ago

CIS 3365 Quiz on CHAPTER 8: ADVANCED SQL, created by Miguel Lucero on 05/04/2017.

452
2
0
Miguel Lucero
Created by Miguel Lucero over 7 years ago
Close

CHAPTER 8: ADVANCED SQL

CHAPTER 8: ADVANCED SQL

Question 1 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 2 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 3 of 68

1

3. Subqueries cannot be used in combinations with joins.

Select one of the following:

  • True
  • False

Explanation

Question 4 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 5 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 6 of 68

1

6. String manipulation functions are rarely used in programming.

Select one of the following:

  • True
  • False

Explanation

Question 7 of 68

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.

Select one of the following:

  • True
  • False

Explanation

Question 8 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 9 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 10 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 11 of 68

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.

Select one of the following:

  • True
  • False

Explanation

Question 12 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 13 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 14 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 15 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 16 of 68

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.

Select one of the following:

  • True
  • False

Explanation

Question 17 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 18 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 19 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 20 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 21 of 68

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.

Select one of the following:

  • True
  • False

Explanation

Question 22 of 68

1

22. MySQL allows multiple triggering conditions per trigger.

Select one of the following:

  • True
  • False

Explanation

Question 23 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 24 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 25 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 26 of 68

1

26. Stored procedures must have at least one argument.

Select one of the following:

  • True
  • False

Explanation

Question 27 of 68

1

27. Variables can be declared inside a stored procedure.

Select one of the following:

  • True
  • False

Explanation

Question 28 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 29 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 30 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 31 of 68

1

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

Select one of the following:

  • True
  • False

Explanation

Question 32 of 68

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.

Select one of the following:

  • True
  • False

Explanation

Question 33 of 68

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;

Select one of the following:

  • a. set operator

  • b. natural join

  • c. “old­style” join

  • d. procedural statement

Explanation

Question 34 of 68

1

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

Select one of the following:

  • a. full

  • b. inner

  • c. outer

  • d. set

Explanation

Question 35 of 68

1

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

Select one of the following:

  • a. cross

  • b. natural

  • c. equi-

  • d. full

Explanation

Question 36 of 68

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?

Select one of the following:

  • a. 8

  • b. 18

  • c. 26

  • d. 144

Explanation

Question 37 of 68

1

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

Select one of the following:

  • a. natural

  • b. cross

  • c. full

  • d. outer

Explanation

Question 38 of 68

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.

Select one of the following:

  • a. OF

  • b. USING

  • c. HAS

  • d. JOIN ON

Explanation

Question 39 of 68

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.

Select one of the following:

  • a. outer

  • b. inner

  • c. equi-

  • d. cross

Explanation

Question 40 of 68

1

40. The syntax for a left outer join is .

Select one of the following:

  • 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

  • SELECT column-list
    WHERE LEFT table1 = table 2

  • . SELECT column-list
    FROM table1 LEFT table2 [JOIN]
    WHERE join-condition

Explanation

Question 41 of 68

1

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

Select one of the following:

  • a. outer

  • b. left

  • c. inner

  • d. base

Explanation

Question 42 of 68

1

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

Select one of the following:

  • a. NULL

  • b. single value

  • c. list of values

  • d. virtual table

Explanation

Question 43 of 68

1

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

Select one of the following:

  • a. The inner subquery executes first.

  • b. The outer subquery initiates the process of execution in a subquery.

  • c. The inner subquery initiates the process of execution in a subquery.

  • d. The outer subquery executes independent of the inner subquery

Explanation

Question 44 of 68

1

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

Select one of the following:

  • a. TO_DATE()

  • b. SYSDATE()

  • c. DATE()

  • d. TODAY()

Explanation

Question 45 of 68

1

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

Select one of the following:

  • a. MON

  • b. MM3

  • c. MONTH

  • d. MM

Explanation

Question 46 of 68

1

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

Select one of the following:

  • a. CONVERT()

  • b. TO_DATE

  • c. TO_CHAR()

  • d. TO_STRING()

Explanation

Question 47 of 68

1

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

Select one of the following:

  • a. LENGTH

  • b. SUBSTRING

  • c. CONCAT

  • d. UCASE

Explanation

Question 48 of 68

1

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

Select one of the following:

  • a. 0

  • b. 9

  • c. $

  • d. #

Explanation

Question 49 of 68

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.

Select one of the following:

  • a. NVL

  • b. TO_CHAR

  • c. DECODE

  • d. CONVERT

Explanation

Question 50 of 68

1

50. is a relational set operator.

Select one of the following:

  • a. MINUS

  • b. PLUS

  • c. ALL

  • d. EXISTS

Explanation

Question 51 of 68

1

51. “Union­compatible” means that the .

Select one of the following:

  • a. names of the relation attributes can be different, but the data types must be alike

  • b. names of the relation attributes must be the same, but the data types can be different

  • c. names of the relation attributes must be the same and their data types must be alike

  • d. number of attributes must be the same, but the names and data types can be different

Explanation

Question 52 of 68

1

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

Select one of the following:

  • a. DATE

  • b. INT

  • c. TINYINT

  • d. CHAR(15)

Explanation

Question 53 of 68

1

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

Select one of the following:

  • a. UNION

  • b. UNION ALL

  • c. INTERSECT

  • d. MINUS

Explanation

Question 54 of 68

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?

Select one of the following:

  • a. 7

  • b. 10

  • c. 15

  • d. 17

Explanation

Question 55 of 68

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?

Select one of the following:

  • a. 7

  • b. 10

  • c. 15

  • d. 17

Explanation

Question 56 of 68

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?

Select one of the following:

  • a. 0

  • b. 2

  • c. 7

  • d. 10

Explanation

Question 57 of 68

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.

Select one of the following:

  • a. UNION

  • b. UNION ALL

  • c. INTERSECT

  • d. MINUS

Explanation

Question 58 of 68

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?

Select one of the following:

  • a. 0

  • b. 2

  • c. 8

  • d. 10

Explanation

Question 59 of 68

1

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

Select one of the following:

  • a. IN

  • b. OF

  • c. AND

  • d. UNION

Explanation

Question 60 of 68

1

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

Select one of the following:

  • a. IN

  • b. NOT IN

  • c. AND

  • d. UNION

Explanation

Question 61 of 68

1

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

Select one of the following:

  • a. auto-number

  • b. sequence

  • c. TO_NUMBER function

  • d. trigger

Explanation

Question 62 of 68

1

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

Select one of the following:

  • a. Oracle sequences are tied to columns and tables.

  • b. Oracle sequences generate a character string
    that can be assigned to tables.

  • c. An oracle sequence uses the identity column property to automatically number rows.

  • d. An oracle sequence can be created and deleted anytime.

Explanation

Question 63 of 68

1

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

Select one of the following:

  • a. CURRVAL

  • b. NEXTVAL

  • c. NEXT

  • d. GET_NEXT

Explanation

Question 64 of 68

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.

Select one of the following:

  • a. cursor-style processing

  • b. stored procedures

  • c. embedded SQL

  • d. Procedural Language SQL

Explanation

Question 65 of 68

1

65. The Oracle string concatenation function is .

Select one of the following:

  • a. CONCAT

  • b. +

  • c. ||

  • d. &&

Explanation

Question 66 of 68

1

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

Select one of the following:

  • a. IS

  • b. OPEN

  • c. DECLARE

  • d. BEGIN

Explanation

Question 67 of 68

1

67. Oracle recommends for creating audit logs.

Select one of the following:

  • a. triggers

  • b. stored procedures

  • c. stored functions

  • d. tables

Explanation

Question 68 of 68

1

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

Select one of the following:

  • a. %ROWCOUNT

  • b. %NOTFOUND

  • c. %FOUND

  • d. %ISOPEN

Explanation

Previous
Next