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

If the two parameters are equal,

NULLIF

function is going to return

NULL

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

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

Grants limited privileges to

uniuser1

user on

university

database

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

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

--help

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);

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);

Adds

JSON

type column for storing JSON objects / arrays

ALTER TABLE students
ADD  COLUMN document_details JSON

Returns database version

SELECT 
    VERSION()

Equivalent of the query above is to use system variable

@@VERSION

SELECT 
    @@VERSION

Adds new row with JSON column

document_details

INSERT INTO students (
    first_name, 
    last_name, 
    gender, 
    points, 
    document_details
) 
VALUES (
    'Beka', 
    'Gelashvili', 
    '1', 
    '82', 
    '{"issue_date":"2020-05-18", "expire_date":"2030-05-18", "number":"ET2312UO"}'
);