Unit_8

Question 1

Stored Procedure

structures allow a single command, or a group of statements, to be executed repeatedly.

Answer 1

Looping / Loops

Question 2

Stored Procedure

WHILE loops are commonly used with ________ to process a set of data one row at a time.

Answer 2

cursors

Question 3

Using Loops

Three loop statements in MySQL
(W,R,L)

Answer 3

While
Repeat
Loop

Question 4

loop checks the expression at the beginning of each iteration.

Answer 4

WHILE

Question 5

loop is called pretest loop because it checks the expression before the statements execute.

Answer 5

WHILE

Question 6

First, MySQL executes the statements, and then it evaluates the expression.

What loop is this?

Answer 6

REPEAT loop

Question 7

If the expressionevaluates to FALSE, MySQL executes the statements repeatedly until the expression evaluates to TRUE.

What loop is this?

Answer 7

REPEAT Loop

Question 8

Because this checks the statements expression after the execution of statement thefore this loop statement is also known as post-test loop

Answer 8

REPEAT Loop

Question 9

This command specifies that the control should pass to the command immediately following the current loop.

Answer 9

BREAK command

Question 10

In MySQL, ________ is used to exit the flow control construct that has the given label. If the label is for the outermost stored program block, ________ exits the program.

Answer 10

LEAVE

Question 11

If you wish to terminate a single iteration of a loop you can use the ________ command.

This command immediately stops the current iteration and rechecks the loop’s condition.

Answer 11

CONTINUE

Question 12

In MySQL, The ________ statement allows you to skip the entire code under it and start a new iteration.

Also means “start the loop again”

Answer 12

ITERATE

Question 13

A ________ is a database object that permits the data set generated by a query to be processed one row at a time

Answer 13

cursor

Question 14

This provides a pointer to a single row and allows the information in that row to be extracted and processed.

Answer 14

cursor

Question 15

Commonly a ________ is combined with a ________ loop to enable row-by-row processing of an entire data set.

Answer 15

cursor, WHILE

Question 16

True or False

cursors use resources whilst they are open and read data from the database each time a new row is fetched.

This can increase network traffic, lower the available resources of the SQL Server instance and lead to poor performance.

Answer 16

True

Question 17

A cursor cannot be used until it has been opened using the ____ statement.

Answer 17

OPEN

Question 18

Once a cursor is opened, how can you determine the number of rows it has been using?

Answer 18

@@CURSOR_ROWS

Question 19

there is only one available command that can be used to obtain a row from the cursor. This command is _____ _____

Answer 19

FETCH NEXT

Question 20

Once you have finished working with a cursor it must be closed to free the resources associated with it and to clear any locks that the cursor has created.

How will you close the cursor?

what syntax??

Answer 20

CLOSE table_name

Question 21

To release these structures and destroy the cursor after closing you should ________ it.

what syntax??

Answer 21

DEALLOCATE table_name

Question 22

if one is present, and sets a flag that specifies whether the cursor is exhausted or if further rows are present. The flag is accessed using the____________ function.

what syntax??

Answer 22

@@FETCH_STATUS

Question 23

An ____________ ________ is not affected by changes to the underlying data.

Answer 23

insensitive cursor

Question 24

These are a special type of table that, as the name suggests, are used to hold data temporarily.

Answer 24

Temporary tables

Question 25

A ____ ____ ____ is visible only to the session that it is created from and is dropped automatically when the connection is closed.

Answer 25

Local Temporary Table

Question 26

______ ______ ____ are available to all users and are automatically dropped when the last user accessing the table disconnects from the database.

Answer 26

Global Temporary Tables

Question 27

A ______ ______ provides functionality similar to a standard variable and a local temporary table combined.

Answer 27

Table Variables

Question 28

For SQL Server

What syntax do we use if we want to skip or move on to the next condition?

Answer 28

CONTINUE (keyword)

Question 29

For SQL Server

What syntax do we use if we want to exit out of the loop we use??

Answer 29

BREAK (keyword)

Question 30

For MySQL

What syntax do we use if we want to skip or move on to the next condition?

Answer 30

ITERATE (keyword) label_ofLoop

Question 31

For MySQL

What syntax do we use if we want to exit out of the loop we use??

Answer 31

LEAVE label_ofLoop