Creates TRIGGER

to_lower

that will run before updating any student and makes

first_name

lower case

DROP TRIGGER IF EXISTS to_lower;

CREATE TRIGGER to_lower
BEFORE UPDATE ON students
FOR EACH ROW
    SET NEW.first_name = LOWER(NEW.first_name);

Creates TRIGGER

to_upper

that will run before inserting student into students table and makes

first_name

UPPER CASE

DROP TRIGGER IF EXISTS to_upper;

CREATE TRIGGER to_upper
BEFORE INSERT ON students
FOR EACH ROW
    SET NEW.first_name = UPPER(NEW.first_name);

Lists all the possible commands that we can use with command line

--help

Grants limited privileges to

uniuser1

user on

university

database

GRANT 
    SELECT, 
    INSERT, 
    UPDATE, 
    DELETE 
ON  `university`.* 
TO 'uniuser1'@'localhost';

There are several differences between user defined functions and procedures: 1. Function must return a value, whereas it's not necessary for procedure to return any value. 2. Function returns only one value, whereas procedure can return multiple values. 3. Function can be called by SQL statement like

SELECT full_name(first_name, last_name) FROM students

, whereas procedure can not be called in SQL queries. 4. Function uses

RETURN

keyword to return value, whereas procedure does not need the keyword to return values. 5. Function parameter can only be

IN

type, whereas procedure parameters can have one of the three types:

IN

,

OUT

or

IN OUT

6. Function usually is used in expressions like built-in functions or variables whereas procedure is usually used to execute some business logic

If the two parameters are equal,

NULLIF

function is going to return

NULL

SELECT
    id,
    first_name,
    NULLIF(gender, 0) student_gender
FROM students

CASE

used in

SELECT

statement determines student's gender based

gender_id

column

SELECT
    id,
    first_name,
    CASE gender
    	WHEN 1 THEN 'Male'
    	WHEN 2 THEN 'Female'
        ELSE 'Undefined'
    END AS student_gender
FROM students

Decrypts encrypted text. So we can get back the original string. In this example string

12000

is encrypted and then decrypted back

SELECT 
    '12000' AS original_string,
    AES_ENCRYPT('12000', 'some secret key') AS encrypted_string,
    AES_DECRYPT(AES_ENCRYPT('12000', 'some secret key'), 'some secret key') decrypted_string

Encrypts text using

AES

algorithm

SELECT 
    '12000' AS original_string,
    AES_ENCRYPT('12000', 'some secret key') AS encrypted_string

Calculates HASH of the given string. Length of the returned HASH is always the same.

SELECT
    SHA2('passwd', 256) password_hash

The Result of the function with

256

will always be ``64 characters long alphanumeric string. In this example the result will be:

0d6be69b264717f2dd33652e212b173104b4a647b7c11ae72e9885f11cd312fb

Note: We can never get back to the original value. There is no way to undo the hashing calculation