Stored procedures

Question 1

Cite a main problem with mixing sql code with say c#, java, or python production code?

Answer 1

If any of the tables or rows are changed that code will also have to be refactored. Use stored procedures instead

Question 2

What is considered the ‘body’ of this stored procedure?

CREATE PROCEDURE get_clients()
BEGIN
SELECT * FROM clients
END

Answer 2

SELECT * FROM clients

Question 3

What is the syntax for creating a stored procedure?

Answer 3

CREATE PROCEDURE named_procedure()

Question 4

True or False
a single statement in a store procedure requires termination

Answer 4

True
DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
select * from clients;
END$$

Question 5

What is the purpose of the DELIMITER clause in sql?
DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
select * from clients;
END$$

Answer 5

treats the entire stored procedure as a single unit

Question 6

True or False
You can use anything as a DEMILITER (&&, **, $$, //)

Answer 6

True
but $$, // seems conventional

Question 7

True or False
When executing a stored procedure, a resulting table is generated?

Answer 7

False
not in mysql

Question 8

In myql what must be done to see any changes after creating a stored procedure?

Answer 8

must refresh the left workbench panel

Question 9

What is the syntax for calling a stored procedure?

Answer 9

CALL named_procedure()

Question 10

– Create a stored procedure called
– get_invoices_with_balance
– to return all the invoices with a balance > 0

Answer 10

DELIMITER $$
CREATE PROCEDURE get_invoices_with_balance()
BEGIN
select *
from invoices_with_balance
where balance > 0;
END$$

DELIMITER ;

Question 11

True or False
When creating a view, stored procedure, trigger … it’s best practice to make sure the underlining query works as anticipated.

Answer 11

True, always do this!

Question 12

True or False
Whenever you change the DELIMITER, you must change it back to it’s default value of?

Answer 12

True
DELIMITER ;

Question 13

To create a stored procedure in mysql workbench all you have to do is?

Answer 13

Right-click stored procedure, create stored procedure.

Question 14

When creating a store procedure in mysql through the workbench, you always have to adjust the delimiter.

Answer 14

No this is performed automatically in mysql workbench

Question 15

Why does mysql use back ticks when naming a stored procedure? ie get_payments

Answer 15

To prevent naming clashes with sql keywords

Question 16

What is the syntax to drop a stored procedure?

Answer 16

DROP PROCEDURE ‘get_payments’

Question 17

What is the syntax for dropping a store procedure if it exists?

Answer 17

DROP PROCEDURE IF EXISTS ‘get_payment’

Question 18

True or False
An error will be thrown if you attempt to drop a view or stored procedure if it no longer exists or was previously dropped.

Answer 18

True
Which is why you should use IF EXITS condition when dropping views or stored procedures

Question 19

Write a stored procedure that returns the state when called.

CALL get_clients_by_state(‘CA’)

Answer 19

DELIMITER $$
CREAT STORED PROCEDURE get_cleints_by_state
(
state CHAR(2) – ‘FL’
)
BEGIN
SELECT * FROM clients c
WHERE c.state = state;
END $$

Question 20

True or False
All parameters are required in mysql?

Answer 20

True

Question 21

– Write a stored procedure to return invoices
– for a given client
– call get_invoices_by_client

Answer 21

create procedure ‘invoices_by_client’
(
client_id INT
)
begin
select *
from invoices i
where i.client_id = client_id;
end

Question 22

True or False
When creating a stored procedure via workbench console you have to ctrl+enter just like executing any sql query.

Answer 22

False
You have to click the apply button on the bottom right corner (apply | revert)

Question 23

What syntax is required when setting a stored procedure default value with an IF clause?

Answer 23

END IF;

Question 24

Break down this stored procedure. By name? Was this create in the workbench? What is the delimiter $$ and end $$ mean? What is happening with the IF clause statement?

Answer 24

name: get_clients_by_state,
not created in workbench can see delimiter being set manually, the delimiter allows the SP to be seen as a whole, the IF clause is a condition all null values will default to “CA”

Question 25

What is the query to get this default (NULL) result from a stored procedure ‘get_clients_by_state’

Answer 25

CALL get_clients_by_state(NULL)

Question 26

True or False
The NULL parameter is required in the following query?

CALL get_clients_by_state(NULL)

Answer 26

True

Question 27

Refactor this into a single SELECT query

Answer 27

SELECT * FROM clients c
WHERE c.state = IFNULL(state, c.state)
– ifnull c.state = state is true, state will be returned
– ifnull false then c.state = c.state which is always true

Question 28

– Write a stored procedure called get_payments
– with two parameters
–
– client_id => INT
– payment_method_id => TINYINT

Answer 28

CREATE PROCEDURE ‘get_payments’(
cleint_id INT,
payment_method_id TINYINT
)
BEGIN
SELECT *
FROM payments p
WHERE
p.client_id = IFNULL(cleint_id, p.client_id) AND
p.payment_method = (payment_method_id, p.payment_method);
END