Deck 8: Advanced Sql

Question 1

MySQL allows multiple triggering conditions per trigger.

Answer 1

False

Question 2

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

Answer 2

False

Question 3

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

Answer 3

True

Question 4

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

Question 5

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.

Question 6

Triggers can only be used to update table values.

Question 7

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

Question 8

The DECLARE section in the trigger is used to declare any variables used inside the trigger code.

Question 9

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

Question 10

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

Question 11

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.

Question 12

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

Question 13

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

Question 14

An explicit cursor must return two or more rows.

Question 15

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

Question 16

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

Question 17

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

Question 18

Stored procedures must have at least one argument.

Question 19

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.

Question 20

Every PL/SQL block must be given a name.

Question 21

You cannot have an invalid entry in the foreign key column; at the same time, you cannot delete a vendor row as long as a product row references that vendor. This isknown as _____.

A) batch updating
B) referential integrity
C) authentication
D) cross joining  

Question 22

The _____ constraint is used to validate data when an attribute value is entered.

A) UNIQUE
B) CASCADE
C) CHECK
D) SET NULL

Question 23

The _____ command is used to restore the database to its previous condition.

A) ROWCOUNT
B) BACKUP
C) COMMIT
D) ROLLBACK

Question 24

When a user issues the DELETE FROM tablename command without specifying a WHERE condition, _____.

A) no rows will be deleted
B) the first row will be deleted
C) the last row will be deleted
D) all rows will be deleted

Question 25

Which statement describes a feature of Oracle sequences?

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) Dropping a sequence does not delete values assigned to table attributes; it deletes only the sequence object from the database.

Question 26

The CREATE TABLE command lets you define constraints when you use the CONSTRAINT keyword, known as a(n) _____ constraint.

A) column
B) table
C) index
D) cell

Question 27

Which SQL format would be best used for a small, numeric data type?

A) INTEGER
B) SMALLINT
C) NUMERIC(L,D)
D) CHAR(L)

Question 28

All changes in a table structure are made using the _____ TABLE command, followed by a keyword that produces the specific changes a user wants to make.

A) ALTER
B) COMMIT
C) UPDATE
D) ROLLBACK

Question 29

A table can be deleted from the database by using the _____ TABLE command.

A) DROP
B) DELETE
C) MODIFY
D) ERASE

Question 30

When you create a new database, the RDBMS automatically creates the data_____ tables in which to store the metadata and creates a default database administrator.

A) index
B) chapter
C) dictionary
D) appendix

Question 31

The _____ specification creates an individualindex ona respective attribute; use it to avoid having duplicated values in a column.

A) UNIQUE
B) NOT NULL
C) UPDATE
D) VARCHAR

Question 32

SQL requires the use of the _____ command to enter data into a table.

A) INSERT
B) SELECT
C) COMMIT
D) NOT NULL

Question 33

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

A) auto-number
B) sequence
C) TO_NUMBER function
D) trigger

Question 34

The _____ command permanently saves all changes-such as rows added, attributes modified, and rows deleted-made to any table in the database.

A) COMMIT
B) SELECT
C) ROLLBACK
D) UPDATE

Question 35

Using the _____ command, SQL indexes can be created on the basis of any selected attribute.

A) CREATE INDEX
B) UPDATE CASCADE
C) SELECT
D) VARCHAR

Question 36

Which command would be used to delete the table row where the P_CODE is 'BRT-345'?

A) DELETE FROM      PRODUCT
WHERE                   P_CODE = 'BRT-345';
B) REMOVE FROM PRODUCT
WHERE P_CODE = 'BRT-345';
C) ERASE FROM        PRODUCT
WHERE                   P_CODE = 'BRT-345';
D) ROLLBACK FROM   PRODUCT
WHERE                      P_CODE = 'BRT-345';

Question 37

The tables on whicha view, or a virtual tablederived froma SELECT query, are based are called _____tables.

A) indexed
B) core
C) relation
D) base

Question 38

When writing SQL table-creating command sequences, the entire table definition is enclosed in _____.

A) asterisks
B) commas
C) quotations
D) parentheses

Question 39

Words used by a system that cannot be used for any other purpose are called _____ words. For example, in Oracle SQL, the word INITIAL cannot be used to name tables or columns.

A) reserved
B) unique
C) null
D) character

Question 40

The_____ constraint assigns a value to an attribute when a new row is added to a table.

A) NOT NULL
B) CASCADE
C) UNIQUE
D) DEFAULT

Question 41

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

A) CURRVAL
B) NEXTVAL
C) NEXT
D) GET_NEXT

Question 42

To make the SQL code more _____, most SQL programmers use one line per column (attribute) definition.

Question 43

A(n) _____ is a logical group of database objects, such as tables and indexes, that are related to each other.

Question 44

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

A) %ROWCOUNT
B) %NOTFOUND
C) %FOUND
D) %ISOPEN

Question 45

If the NOT NULL and UNIQUE specifications are not supported when using a command sequence, use _____ without the specifications.

Question 46

In a 1:M relationship, a user must always create the table for the "_____" side first.

Question 47

_____is the process the DBMS uses to verify that only registered users access the database.

Question 48

U.S. state abbreviations are always two characters, so _____(2) is a logical choice for the data type representing a state column.

Question 49

A(n) _____ cursor is automatically created in procedural SQL when the SQL statement returns only one value.

A) implicit
B) dynamic
C) explicit
D) static

Question 50

_____ words are words used by SQL to perform specific functions.

Question 51

No matter what language you use, if it contains embedded SQL statements, it is called the _____ language.

A) base
B) static
C) host
D) view

Question 52

The SQL data type DATE stores date in the _____ date format.

Question 53

Oracle recommends _____ for creating audit logs.

A) triggers
B) stored procedures
C) stored functions
D) tables

Question 54

In Oracle, _____ retrieves the current value of a sequence.

A) NEXTVAL
B) CURRVAL
C) VARCHAR
D) VARCHAR2

Question 55

The Oracle string concatenation function is _____.

A) CONCAT
B) +
C) ||
D) &&

Question 56

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.

A) dynamic SQL
B) stored procedures
C) embedded SQL
D) Procedural Language SQL

Question 57

The PL/SQL block starts with the _____ section.

A) IS
B) OPEN
C) DECLARE
D) BEGIN

Question 58

A common practice is to create a(n) _____ on any field that is used as a search key, in comparison operations in a conditional expression, or when a user wants to list rows in a specific order.

Question 59

A(n) _____-length character data type, like VARCHAR, is typically specified with a maximum length.

Question 60

A _____ is a block of code containing standard SQL statements and procedural extensions that is stored and executed at the DBMS server.

A) cursor-style process
B) statement-level trigger
C) base table
D) persistent storage module (PSM)

Question 61

A table can be deleted from the database by using the _____ command.

Question 62

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

Question 63

In an INSERT command, a user can indicate just the attributes that have required values by listing the _____ inside parentheses after the table name.

Question 64

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

Question 65

If a user adds a new column to a table that already has rows, the existing rows will default to a value of _____ for the new column.

Question 66

Summarize the hierarchy of steps involved in creating and running an executable program with embedded SQL statements.

Question 67

How are triggers critical to proper database operation and management?

Question 68

A row-level trigger requires use of the _____ keywords and is executed once for each row affected by the triggering statement.

Question 69

_____ is the term used to describe an environment in which the SQL statement is not known in advance and is generated at run time.

Question 70

To delete an index, one must use the _____ command.

Question 71

How can a table be deleted from the database? Provide an example.

Question 72

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

Question 73

Using Oracle _____, a designer can write a PL/SQL code block by enclosing the commands inside BEGIN and END clauses.

Question 74

In MS Access, a designer can use the _____ data type to define a column in his table that will be automatically populated with unique numeric values.

Question 75

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