System variables, Stored procedures & functions, triggers & transactions

Question 1

There are two kinds of variables in MySQL, name them

Answer 1

global variables - affect the server overall

session variables - affect individual client connections

Question 2

How can you see all system variables?

Answer 2

SHOW GLOBAL (or) SESSION VARIABLES;

Question 3

When do changes to system variables apply?

Answer 3

On sessions started after the changes.

Question 4

How would you set the global variable ‘port’?

Answer 4

SET GLOBAL port = 2222;
or
SET @@GLOBAL.port = 2222;

Question 5

What is a user-defined variable

Answer 5

a session are session specific.

written as @var_name and are defined with a SET statement

Question 6

How can you select from tables into variables?

Answer 6

SELECT Dname, Dnumber

FROM Company.Department LIMIT 1 INTO @x, @y;

Question 7

What is a local variable?

Answer 7

its a variable scoped inside BEGIN - END as:

DECLARE var_name type(int) [DEFAULT value]

Question 8

what is a stored procedure/function

Answer 8

named blocks encapsulating SQL statements and procedures

Question 9

how are stored procedure and functions stored

Answer 9

stored in a memory cache for later calls i.e. it does not need to be reparsed, recompiled and re-optimized every time is is run

Question 10

what is a stored function

Answer 10

computes and returns ONE value.

– you pass it something and it returns a result
– should be small and fast; does it on the fly.

Question 11

what is a stored procedure

Answer 11

it is a group of possibly complex set of statements

Question 12

What are the main differences between a stored procedure and function?

Answer 12

• Procedures can have input/output parameters; functions have only input parameters
• Procedures may return one or more values; functions always one mandatory value
• Procedures allow all DML statement; Functions allow only SELECT statement
• Exception can be handled in procedures but cannot be used in functions

Question 13

How do you create a stored procedure with an output variable?

Answer 13

DELIMITER //
CREATE PROCEDURE calc_value (OUT ending_value INT) BEGIN
DECLARE total_value INT;
SET total_value = 50;
label1: WHILE total_value <= 2100 DO
SET total_value = total_value * 2; END WHILE label1;
SET ending_value = total_value; END //
DELIMITER ;

Question 14

how can you call stored procedures with output variables and see the variable??

Answer 14

CALL calc_value(@value); 
SELECT @value;

Question 15

What is a trigger?

Answer 15

an action that takes place when certain events occur.

Question 16

When does a trigger trigger?

Answer 16

you can set it to activate before or after the trigger event

Question 17

What happens when you create multiple triggers on the same event?

Answer 17

by default, triggers with same event and action activate in the order they were created

the order may also be explicitly defined at trigger creation

Question 18

what is the syntax for creating triggers?

Answer 18

CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON tbl_name FOR EACH ROW [{ FOLLOWS | PRECEDES } other_trigger_name]
trigger_body

Question 19

Can triggers call stored functions and procedures?

Answer 19

yes

Question 20

When would you use triggers?

Answer 20

– generating some derived column values automatically
– enforcing referential integrity
– event logging and storing information on table access
– synchronous replication of tables
– imposing security authorizations
– preventing invalid transactions

Question 21

what is DELIMITER //

Answer 21

Something you use to create and run blocks of codes in triggers and stored procedures

Question 22

what is the difference between a stored procedure and function?

Answer 22

procedures have inputs and outputs

function only has inputs

procedures can return multiple values and functions only one mandatory value

procedures allow DML and functions only SELECT

procedures allow transactions

Question 23

how do you define a stored procedure?

Answer 23

DELIMITER //
CREATE PROCEDURE decrementWith (IN start_value INT, IN decrement_amount INT ,OUT output INT)
BEGIN
SET output = start_value - decrement_amount;
END //
DELIMITER ;

Question 24

What is a transaction?

Answer 24

A transaction is a logical unit of work that contains one or more SQL statements. A transaction is an atomic unit. The effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database)