×
Night mode
Sign up
Login
Results: 1578
Notes
  • Newest first
  • Oldest first
  • Newest first(All)
  • Oldest first(All)
Create procedure that checks and prints income level 
DROP PROCEDURE IF EXISTS printIncomeLevel;

DELIMITER //

CREATE PROCEDURE printIncomeLevel (IN monthly_value INT)

BEGIN

   DECLARE income_level varchar(20);

   IF monthly_value <= 4000 THEN
        SET income_level = 'Low Income';
   ELSEIF monthly_value > 4000 AND monthly_value <= 7000 THEN
        SET income_level = 'Avg Income';
   ELSE
        SET income_level = 'High Income';
   END IF;

   INSERT INTO log (description)
   VALUES ('getIncomeLevel procedure was called');

   SELECT income_level;

END;
After creating the procedure, we can call it using 
CALL
keyword:
CALL printIncomeLevel(450)
by Valeri Tandilashvili
5 years ago
0
MySQL
User defined procedures
1
Create function salary level 
DROP FUNCTION IF EXISTS getIncomeLevel;

DELIMITER //

CREATE FUNCTION getIncomeLevel ( monthly_value INT )
RETURNS varchar(20)

BEGIN

   DECLARE income_level varchar(20);

   IF monthly_value <= 4000 THEN
        SET income_level = 'Low Income';
   ELSEIF monthly_value > 4000 AND monthly_value <= 7000 THEN
        SET income_level = 'Avg Income';
   ELSE
        SET income_level = 'High Income';
   END IF;

   INSERT INTO log (description)
   VALUES ('getIncomeLevel function was called');

   RETURN income_level;

END; //

DELIMITER ;
After creating the function we can call it:
SELECT getIncomeLevel(450)
by Valeri Tandilashvili
5 years ago
0
MySQL
User defined functions
1
Foreign key constraint Referential Actions
MySQL supports several referential actions: 
CASCADE
- deletes or updates appropriate rows in the child table 
SET NULL
- sets the foreign key column of the child table to 
NULL
RESTRICT
- rejects the operation for the parent table 
NO ACTION
- equivalent to 
RESTRICT
Note: 
RESTRICT
is the same as omitting the 
ON DELETE
or 
ON UPDATE
clause, which means that it's default action
by Valeri Tandilashvili
5 years ago
0
MySQL
FOREIGN KEY
1
Create TRIGGER after INSERT
Creates trigger that runs 
after
insert
and logs into 
log
table
DROP TRIGGER IF EXISTS after_student_insert;

DELIMITER $$

    CREATE TRIGGER after_student_insert BEFORE INSERT 
    ON students
    FOR EACH ROW 
    BEGIN

    INSERT INTO log (description)
    VALUES ('One student inserted');

    END$$

DELIMITER ;
by Valeri Tandilashvili
5 years ago
0
0
Create TRIGGER before INSERT
Creates trigger that runs 
before
insert
and checks & replaces gender values
DROP TRIGGER IF EXISTS before_student_insert;

DELIMITER $$

    CREATE TRIGGER before_student_insert BEFORE INSERT 
    ON students
    FOR EACH ROW 
    BEGIN

    INSERT INTO log (description)
    VALUES ('One student will be inserted');
    
    IF (NEW.gender = 'Female') THEN
        SET NEW.gender = 2;
    ELSE
        SET NEW.gender = 1;
    END IF;

    END$$

DELIMITER ;
by Valeri Tandilashvili
5 years ago
0
MySQL
TRIGGERS
1
Trigger types
Trigger can run 
before
or 
after
some database event. The event can be 
insert
, 
update
or 
delete
. It means there are six types of triggers: 
before / after
insert 
before / after
update 
before / after
delete
by Valeri Tandilashvili
5 years ago
0
MySQL
TRIGGERS
1
Add FOREIGN KEY constraint after creating a table
Creates 
FOREIGN KEY
constraint with 
student_id
column and references it to 
students.id
ALTER TABLE notes 
ADD CONSTRAINT fk_student_note
    FOREIGN KEY (student_id)  
    REFERENCES students(id) 
    ON UPDATE CASCADE 
    ON DELETE RESTRICT
We can omit 
CONSTRAINT fk_student_note
sub-clause. The difference is that if we omit the sub-clause, MySQL names the constraint automatically
ALTER TABLE notes 
ADD FOREIGN KEY (student_id)  
    REFERENCES students(id) 
    ON UPDATE CASCADE 
    ON DELETE RESTRICT
by Valeri Tandilashvili
5 years ago
0
MySQL
ALTER TABLE
1
Delete FOREIGN KEY by its name 
ALTER TABLE notes 
DROP FOREIGN KEY fk_student_note`;
by Valeri Tandilashvili
5 years ago
0
MySQL
ALTER TABLE
1
Differences between NO ACTION and RESTRICT 
NO ACTION
is equivalent to RESTRICT. The MySQL Server rejects the delete and update operations of the parent table if there is a related foreign key value in the referenced table
by Valeri Tandilashvili
5 years ago
0
MySQL
FOREIGN KEY
1
FOREIGN KEY 
FOREIGN KEY
constraint protects data integrity. Let's create 
students
and 
note
tables
USE university;
DROP TABLE IF EXISTS students;
CREATE TABLE students (
    id int AUTO_INCREMENT,
    first_name varchar(50),
    last_name varchar(50),
    PRIMARY KEY(id)
);
Let's add 
FOREIGN KEY
called 
fk_student_note
to the 
notes
table
DROP TABLE IF EXISTS notes;
CREATE TABLE notes (
    id int AUTO_INCREMENT,
    student_id int,
    title varchar(50),
    description varchar(50),
    PRIMARY KEY(id),
    INDEX(student_id),
    CONSTRAINT fk_student_note  
        FOREIGN KEY (student_id)  
        REFERENCES students(id) 
        ON UPDATE CASCADE 
        ON DELETE RESTRICT
);
by Valeri Tandilashvili
5 years ago
0
MySQL
1
Results: 1578