Copy and Edit

CHAPTER 8: ADVANCED SQL

Description

CIS 3365 Quiz on CHAPTER 8: ADVANCED SQL, created by Miguel Lucero on 05/04/2017.
Miguel Lucero
Quiz by Miguel Lucero, updated more than 1 year ago
Miguel Lucero
Created by Miguel Lucero almost 8 years ago
472
2
0

Resource summary

Question 1

Question
1. A relational join operation merges rows from two tables.
Answer
  • True
  • False

Question 2

Question
2. An inequality condition is also known as a natural join and an equality condition is also called a theta join.
Answer
  • True
  • False

Question 3

Question
3. Subqueries cannot be used in combinations with joins.
Answer
  • True
  • False

Question 4

Question
4. The SELECT statement uses the attribute list to indicate what columns to project in the resulting set.
Answer
  • True
  • False

Question 5

Question
5. Numeric functions take one numeric parameter and return one value.
Answer
  • True
  • False

Question 6

Question
6. String manipulation functions are rarely used in programming.
Answer
  • True
  • False

Question 7

Question
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.
Answer
  • True
  • False

Question 8

Question
8. A view is a virtual table based on a SELECT query.
Answer
  • True
  • False

Question 9

Question
9. A sequence is not associated with a table and can be dropped from a database with a DROP SEQUENCE command.
Answer
  • True
  • False

Question 10

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

Question 11

Question
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.
Answer
  • True
  • False

Question 12

Question
12. A persistent stored module is stored and executed on the database client machine.
Answer
  • True
  • False

Question 13

Question
13. Every PL/SQL block must be given a name.
Answer
  • True
  • False

Question 14

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

Question 15

Question
15. PL/SQL blocks have a section used to declare variables.
Answer
  • True
  • False

Question 16

Question
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.
Answer
  • True
  • False

Question 17

Question
17. Automating business procedures and automatically maintaining data integrity and consistency are trivial in a modern business environment.
Answer
  • True
  • False

Question 18

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

Question 19

Question
19. Triggers can only be used to update table values.
Answer
  • True
  • False

Question 20

Question
20. A statement-level trigger is assumed if a designer omits the FOR EACH ROW keywords.
Answer
  • True
  • False

Question 21

Question
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.
Answer
  • True
  • False

Question 22

Question
22. MySQL allows multiple triggering conditions per trigger.
Answer
  • True
  • False

Question 23

Question
23. BEFORE means before the changes are made in memory but after the changes are permanently saved to disk.
Answer
  • True
  • False

Question 24

Question
24. Just like database triggers, stored procedures are stored in the database.
Answer
  • True
  • False

Question 25

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

Question 26

Question
26. Stored procedures must have at least one argument.
Answer
  • True
  • False

Question 27

Question
27. Variables can be declared inside a stored procedure.
Answer
  • True
  • False

Question 28

Question
28. Cursors are held in a reserved memory area in the client computer.
Answer
  • True
  • False

Question 29

Question
29. An implicit cursor is automatically created in procedural SQL when the SQL statement returns only one
Answer
  • True
  • False

Question 30

Question
30. An explicit cursor must return two or more rows.
Answer
  • True
  • False

Question 31

Question
31. A stored function is another name for a stored procedure.
Answer
  • True
  • False

Question 32

Question
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.
Answer
  • True
  • False

Question 33

Question
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;
Answer
  • a. set operator
  • b. natural join
  • c. “old­style” join
  • d. procedural statement

Question 34

Question
34. When using a(n) join, only rows that meet the given criteria are returned.
Answer
  • a. full
  • b. inner
  • c. outer
  • d. set

Question 35

Question
35. The statement SELECT * FROM T1, T2 produces a(n) join.
Answer
  • a. cross
  • b. natural
  • c. equi-
  • d. full

Question 36

Question
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?
Answer
  • a. 8
  • b. 18
  • c. 26
  • d. 144

Question 37

Question
37. A(n) join will select only the rows with matching values in the common attribute(s).
Answer
  • a. natural
  • b. cross
  • c. full
  • d. outer

Question 38

Question
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.
Answer
  • a. OF
  • b. USING
  • c. HAS
  • d. JOIN ON

Question 39

Question
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.
Answer
  • a. outer
  • b. inner
  • c. equi-
  • d. cross

Question 40

Question
40. The syntax for a left outer join is .
Answer
  • 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

Question 41

Question
41. In subquery terminology, the first query in the SQL statement is known as the query.
Answer
  • a. outer
  • b. left
  • c. inner
  • d. base

Question 42

Question
42. In the context of SELECT subquery types, a is returned when an UPDATE subquery is used.
Answer
  • a. NULL
  • b. single value
  • c. list of values
  • d. virtual table

Question 43

Question
43. Which of the following is a feature of a correlated subquery?
Answer
  • 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

Question 44

Question
44. The function returns the current system date in MS Access.
Answer
  • a. TO_DATE()
  • b. SYSDATE()
  • c. DATE()
  • d. TODAY()

Question 45

Question
45. When using the Oracle TO_DATE function, the code represents a three-letter month name.
Answer
  • a. MON
  • b. MM3
  • c. MONTH
  • d. MM

Question 46

Question
46. In Oracle, the function converts a date to a character string.
Answer
  • a. CONVERT()
  • b. TO_DATE
  • c. TO_CHAR()
  • d. TO_STRING()

Question 47

Question
is a string function that returns the number of characters in a string value.
Answer
  • a. LENGTH
  • b. SUBSTRING
  • c. CONCAT
  • d. UCASE

Question 48

Question
48. When using the Oracle TO_NUMBER function to convert a character string into a number, represents a digit.
Answer
  • a. 0
  • b. 9
  • c. $
  • d. #

Question 49

Question
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.
Answer
  • a. NVL
  • b. TO_CHAR
  • c. DECODE
  • d. CONVERT

Question 50

Question
50. is a relational set operator.
Answer
  • a. MINUS
  • b. PLUS
  • c. ALL
  • d. EXISTS

Question 51

Question
51. “Union­compatible” means that the .
Answer
  • 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

Question 52

Question
52. The data type is considered compatible with VARCHAR(35).
Answer
  • a. DATE
  • b. INT
  • c. TINYINT
  • d. CHAR(15)

Question 53

Question
53. The statement combines rows from two queries and excludes duplicates.
Answer
  • a. UNION
  • b. UNION ALL
  • c. INTERSECT
  • d. MINUS

Question 54

Question
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?
Answer
  • a. 7
  • b. 10
  • c. 15
  • d. 17

Question 55

Question
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?
Answer
  • a. 7
  • b. 10
  • c. 15
  • d. 17

Question 56

Question
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?
Answer
  • a. 0
  • b. 2
  • c. 7
  • d. 10

Question 57

Question
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.
Answer
  • a. UNION
  • b. UNION ALL
  • c. INTERSECT
  • d. MINUS

Question 58

Question
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?
Answer
  • a. 0
  • b. 2
  • c. 8
  • d. 10

Question 59

Question
59. The operator could be used in place of INTERSECT if the DBMS does not support it.
Answer
  • a. IN
  • b. OF
  • c. AND
  • d. UNION

Question 60

Question
60. The operator could be used in place of MINUS if the DBMS does not support it.
Answer
  • a. IN
  • b. NOT IN
  • c. AND
  • d. UNION

Question 61

Question
61. The Oracle equivalent to an MS Access AutoNumber is a(n) .
Answer
  • a. auto-number
  • b. sequence
  • c. TO_NUMBER function
  • d. trigger

Question 62

Question
62. Which of the following is a feature of oracle sequences?
Answer
  • 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.

Question 63

Question
63. The pseudo-column is used to select the next value from a sequence.
Answer
  • a. CURRVAL
  • b. NEXTVAL
  • c. NEXT
  • d. GET_NEXT

Question 64

Question
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.
Answer
  • a. cursor-style processing
  • b. stored procedures
  • c. embedded SQL
  • d. Procedural Language SQL

Question 65

Question
65. The Oracle string concatenation function is .
Answer
  • a. CONCAT
  • b. +
  • c. ||
  • d. &&

Question 66

Question
66. The PL/SQL block starts with the section.
Answer
  • a. IS
  • b. OPEN
  • c. DECLARE
  • d. BEGIN

Question 67

Question
67. Oracle recommends for creating audit logs.
Answer
  • a. triggers
  • b. stored procedures
  • c. stored functions
  • d. tables

Question 68

Question
68. is a cursor attribute that returns TRUE if the last FETCH returned a row, and FALSE if not.
Answer
  • a. %ROWCOUNT
  • b. %NOTFOUND
  • c. %FOUND
  • d. %ISOPEN
Show full summary Hide full summary

Want to create your own Quizzes for free with GoConqr? Learn more.

Similar

Causes of the Cold War Quiz
Fro Ninja
P1 - The Earth in the Universe
franimal
AS-Level Chemistry: Unit 1:The Atom
Daena Targaryen
Geography Coastal Zones Flashcards
Zakiya Tabassum
Of Mice and Men
amyk4321
An Inspector Calls - Quotes
jaynejuby
Chemistry GCSE Review - States of Matter, Particles, Atoms, Elements, Compounds and Mixtures
Morgan Overton
Othello Quotes
georgia2201
Maths: Formulas for Areas & Perimeters of Shapes
Annan S
A Christmas Carol (Key Quotes)
Samira Choudhury
NSI / PSCOD/ ASSD
Yuvraj Sunar
Browse Library