create database school;
use school;
CREATE TABLE students
(
ID serial,
Name varchar(20),
Lastname varchar(20),
score tinyint unsigned
);

insert into students values
(null,"John", "Cena", 32),
(null,"Obi", "Kenobi", 99),
(null,"Post", "Malone", 61),
(null,"Tupac", "Shakur", 42),
(null,"Vardas", "Pavarde", 25);



DELIMITER //
CREATE PROCEDURE GetTable()
BEGIN
select name, lastname, score from students;
END //
DELIMITER ;


delimiter //
create procedure CreateTable()
BEGIN
CREATE TABLE IF NOT EXISTS students
(
ID serial,
Name varchar(20),
Lastname varchar(20),
score tinyint unsigned
);
end //
delimiter ;

DELIMITER //
CREATE PROCEDURE MoreThan (IN num tinyint unsigned)
BEGIN
  SELECT Name, score FROM students WHERE score > num;
END //
DELIMITER ;
CALL MoreThan(30);

DELIMITER //
CREATE PROCEDURE MaxScore(OUT maxsc tinyint unsigned)
BEGIN
SELECT MAX(score) INTO maxsc FROM students;
END //
DELIMITER ;

CALL MaxScore(@maxsc);
SELECT @maxsc;

SET @kint = 30;
CALL MoreThan(@kint);



CREATE PROCEDURE MaxScore(INOUT nr tinyint unsigned)

(select)Isveda vardus kuriu score didesnis uz paduota kintamaji
Priskiria kintamajo reiksme, kuri yra maziausias score lenteleje


DELIMITER //
CReate PROCEDURE MoreMin(INOUT nr tinyint unsigned)
BEGIN
select Name from students where score > nr;
select MIN(Score) into nr from students;
END //
DELIMITER ;
SET @kint = 30;
CALL MoreMin(@kint);
select @kint;


DELIMITER //
DROP PROCEDURE IF EXISTS TextWhile//
CREATE PROCEDURE TextWhile()
BEGIN

SET @x = 1;
SET @str = '';

WHILE @x<=3 DO
SET @str= CONCAT(@str,(SELECT name FROM STUDENTS WHERE ID = @x),',');
SET @x= @x + 1;
END WHILE;

SELECT @str;
END//
DELIMITER ;



SELECT IF (1>3,'true','false');


Parasyti procedura kuri sukuria lentele Results ir i ja
patalpina studentu vardus ir kuriu score yra didesnis uz
paduota skaiciu, stulpelyje passed paraso Yes, jei ne - No


DELIMITER //
DROP PROCEDURE IF EXISTS PassedProc//
CREATE PROCEDURE PassedProc (IN LimitNr tinyint unsigned)
BEGIN

DROP TABLE IF EXISTS Results;
CREATE TABLE RESULTS
(
    Name varchar(20),
    passed varchar(3)
);
SET @x = 1;
WHILE @x <= (select count(name)from students) DO
INSERT INTO Results VALUES(null, (select name from students where id = @x),(select if(((select score from students where id = @x)>LimitNr),'Yes', 'No')));
SET @x = @x + 1;
END WHILE;
END//
DELIMITER ;















































CREATE DATABASE priestesta;
USE priestesta;
CREATE TABLE employee
(
    ID serial,
    Name varchar(50) NOT NULL,
    LastName varchar(50) NOT NULL,
    Email varchar(150) UNIQUE NOT NULL,
    Salary float(7,2) unsigned
);
INSERT INTO employee VALUES (null, 'Jonas', 'Kazlauskas','Jonukas@one.lt',4682.2),(null, 'Justas', 'Kaziukas','Justelio@gmail.com',7853.2), (null, 'Aloyzas', 'Kazlekas','Aliux@yahoo.com',3243.23);
UPDATE employee set salary=200 where email='Aliux@yahoo.com';








START TRANSACTION;
insert into employee values(null,'Justas','Jonaitis','lelel@gmail.com', 8888);

CREATE USER 'user1'@'localhost' identified by 'user1';

GRANT SELECT, INSERT, UPDATE, DELETE ON priestesta.* to 'user1'@'localhost';








create table stats
(
 ID serial,
 action enum('insert','delete','update'),
 curr_user varchar(50),
 curr_id bigint unsigned,
 mod_date timestamp
);


CREATE TRIGGER <trigger name> <actionas> <query type> on <table> for each row
BEGIN
END

<actionas> => before||after
<query type> => insert||delete||update


DELIMITER //
CREATE TRIGGER InsterTrigger AFTER INSERT ON employee for each row
BEGIN
    INSERT INTO stats VALUES(null,1, user(), new.ID, current_timestamp);
END //
DELIMITER ;


DELIMITER //
CREATE TRIGGER DeleteTrigger AFTER DELETE ON employee for each row
BEGIN
    INSERT INTO stats VALUES(null,2, user(), old.ID, current_timestamp);
END //
DELIMITER ;



CREATE TABLE stats2
(
    ID SERIAL,
    user_id bigint unsigned,
    oldSalary float(7,2) unsigned,
    newSalary float(7,2) unsigned,
    mod_date timestamp
);

DELIMITER $$
CREATE TRIGGER UserSalaryTrigger BEFORE UPDATE ON employee for each row
BEGIN
    INSERT INTO stats2 VALUES(null, old.ID, old.salary, new.salary, current_timestamp);
END$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER UserSalaryTrigger AFTER UPDATE ON employee for each row
BEGIN
    INSERT INTO stats2 VALUES(null, old.ID, old.salary, new.salary, current_timestamp);
END$$
DELIMITER ;