46SqHXjp

1create database school;
2use school;
3CREATE TABLE students
4(
5ID serial,
6Name varchar(20),
7Lastname varchar(20),
8score tinyint unsigned
9);
10
11insert into students values
12(null,"John", "Cena", 32),
13(null,"Obi", "Kenobi", 99),
14(null,"Post", "Malone", 61),
15(null,"Tupac", "Shakur", 42),
16(null,"Vardas", "Pavarde", 25);
17
18
19
20DELIMITER //
21CREATE PROCEDURE GetTable()
22BEGIN
23select name, lastname, score from students;
24END //
25DELIMITER ;
26
27
28delimiter //
29create procedure CreateTable()
30BEGIN
31CREATE TABLE IF NOT EXISTS students
32(
33ID serial,
34Name varchar(20),
35Lastname varchar(20),
36score tinyint unsigned
37);
38end //
39delimiter ;
40
41DELIMITER //
42CREATE PROCEDURE MoreThan (IN num tinyint unsigned)
43BEGIN
44  SELECT Name, score FROM students WHERE score > num;
45END //
46DELIMITER ;
47CALL MoreThan(30);
48
49DELIMITER //
50CREATE PROCEDURE MaxScore(OUT maxsc tinyint unsigned)
51BEGIN
52SELECT MAX(score) INTO maxsc FROM students;
53END //
54DELIMITER ;
55
56CALL MaxScore(@maxsc);
57SELECT @maxsc;
58
59SET @kint = 30;
60CALL MoreThan(@kint);
61
62
63
64CREATE PROCEDURE MaxScore(INOUT nr tinyint unsigned)
65
66(select)Isveda vardus kuriu score didesnis uz paduota kintamaji
67Priskiria kintamajo reiksme, kuri yra maziausias score lenteleje
68
69
70DELIMITER //
71CReate PROCEDURE MoreMin(INOUT nr tinyint unsigned)
72BEGIN
73select Name from students where score > nr;
74select MIN(Score) into nr from students;
75END //
76DELIMITER ;
77SET @kint = 30;
78CALL MoreMin(@kint);
79select @kint;
80
81
82DELIMITER //
83DROP PROCEDURE IF EXISTS TextWhile//
84CREATE PROCEDURE TextWhile()
85BEGIN
86
87SET @x = 1;
88SET @str = '';
89
90WHILE @x<=3 DO
91SET @str= CONCAT(@str,(SELECT name FROM STUDENTS WHERE ID = @x),',');
92SET @x= @x + 1;
93END WHILE;
94
95SELECT @str;
96END//
97DELIMITER ;
98
99
100
101SELECT IF (1>3,'true','false');
102
103
104Parasyti procedura kuri sukuria lentele Results ir i ja 
105patalpina studentu vardus ir kuriu score yra didesnis uz 
106paduota skaiciu, stulpelyje passed paraso Yes, jei ne - No
107
108
109DELIMITER //
110DROP PROCEDURE IF EXISTS PassedProc//
111CREATE PROCEDURE PassedProc (IN LimitNr tinyint unsigned)
112BEGIN 
113
114DROP TABLE IF EXISTS Results;
115CREATE TABLE RESULTS
116(
117	Name varchar(20),
118	passed varchar(3)
119);
120SET @x = 1;
121WHILE @x <= (select count(name)from students) DO
122INSERT INTO Results VALUES(null, (select name from students where id = @x),(select if(((select score from students where id = @x)>LimitNr),'Yes', 'No')));
123SET @x = @x + 1;
124END WHILE;
125END//
126DELIMITER ;