CREATE TABLE Student (
  login    CHAR(6) PRIMARY KEY,
  fname    VARCHAR(30) NOT NULL,
  lname    VARCHAR(50) NOT NULL,
  email    VARCHAR(50) NOT NULL
  );

CREATE PROCEDURE AddStudent(
@p_login CHAR(6),
@p_fname VARCHAR(30),
@p_lname VARCHAR(50),
@p_email VARCHAR(50))
AS
BEGIN
    INSERT INTO Student(login,fname,lname,email)
    VALUES(@p_login,@p_fname,@p_lname,@p_email)
END

EXEC AddStudent 'KRA','Jakub','Kraus','email'



CREATE PROCEDURE PAddStudent(
@p_login CHAR(6),
@p_fname VARCHAR(30),
@p_lname VARCHAR(50),
@p_email VARCHAR(50),
@out_ VARCHAR(10) out
)

AS
BEGIN TRANSACTION
BEGIN TRY
    INSERT INTO Student(login,fname,lname,email)
    VALUES(@p_login,@p_fname,@p_lname,@p_email)
    SET @out = 'OK'
    COMMIT
END TRY
BEGIN CATCH
    SET @out = 'ERROR'
    ROLLBACK
END CATCH

BEGIN

DECLARE @out_ VARCHAR(10)
EXEC PAddStudent 'CCCC','Jakub','Somik','email11', @out_ out

END





CREATE TABLE Teacher (
  login CHAR(6) NOT NULL PRIMARY KEY,
  fname VARCHAR(30) NOT NULL,
  lname VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL,
  department INT NOT NULL,
  specialization VARCHAR(30) NULL);


ALTER PROCEDURE StudentBecomeTeacher(
@p_login CHAR(6),
@p_department INT
)AS

BEGIN TRANSACTION
BEGIN TRY
DECLARE @v_login CHAR(6)
DECLARE @v_fname VARCHAR(30)
DECLARE @v_lname VARCHAR(50)
DECLARE @v_email VARCHAR(50)

select @v_login = login FROM Student WHERE Student.login = @p_login
select @v_fname = fname FROM Student WHERE Student.login = @p_login
select @v_lname = lname FROM Student WHERE Student.login = @p_login
select @v_email = email FROM Student WHERE Student.login = @p_login

INSERT INTO TEACHER(login, fname,lname,email,department)
VALUES(@v_login,@v_fname,@v_lname,@v_email,@p_department)
COMMIT
END TRY
BEGIN CATCH
    print 'Error'
    ROLLBACK
END CATCH

EXEC StudentBecomeTeacher 'SOM', 99


CREATE TABLE Student (
  login    CHAR(6) PRIMARY KEY,
  fname    VARCHAR(30) NOT NULL,
  lname    VARCHAR(50) NOT NULL,
  email    VARCHAR(50) NOT NULL,
  tallness INT NOT NULL);

CREATE PROCEDURE AddStudent2(
@p_fname VARCHAR(30),
@p_lname VARCHAR(50),
@p_tallness INT
)
AS
BEGIN
DECLARE @v_login CHAR(6)
DECLARE @v_email VARCHAR(50)
SET @v_login = SUBSTRING(@p_lname,1,3) + '000'
SET @v_email = @v_login + '@vsb.cz'

INSERT INTO Student(login, fname, lname, email, tallness)
VALUES(@v_login, @p_fname, @p_lname, @v_email, @p_tallness)
END

EXEC AddStudent2 'Jakub', 'Pepa', 180


ALTER TABLE Student
ADD isTall VARCHAR(1) NULL


ALTER PROCEDURE IsStudentTall(
@p_login CHAR(6)
)
AS
BEGIN
DECLARE @v_tallness INT
DECLARE @v_AVG INT
SELECT @v_tallness = tallness FROM Student WHERE @p_login = login
SELECT @v_AVG = AVG(tallness) FROM Student
if(@v_tallness >= @v_AVG)
    UPDATE STUDENT SET isTall = 1 WHERE login = @p_login
else if(@v_tallness < @v_AVG)
    UPDATE STUDENT SET isTall = 0 WHERE login = @p_login

END

EXEC IsStudentTall 'Pep000'


ALTER FUNCTION LoginExist(@p_login CHAR(6))
RETURNS VARCHAR(1)
AS
BEGIN
    DECLARE @result VARCHAR(1)
    DECLARE @v_login CHAR(6)
    SELECT @v_login = login FROM STUDENT WHERE login = @p_login
    IF(@v_login is null)
        SET @result = '0'
    ELSE
        SET @result = '1'

    RETURN @result
END


ALTER PROCEDURE AddStudent22(
@p_fname VARCHAR(30),
@p_lname VARCHAR(50),
@p_tallness INT
)
AS
BEGIN
DECLARE @v_login CHAR(6)
DECLARE @v_email VARCHAR(50)
DECLARE @v_count INT = 0

SET @v_login = ''

WHILE DBO.LoginExist(@v_login) != '0'
BEGIN
SET @v_login = SUBSTRING(@p_lname,1,3) + '00' + CAST(@v_count AS VARCHAR(5))
SET @v_count = @v_count + 1
END
PRINT @v_login
SET @v_email = @v_login + '@vsb.cz'
INSERT INTO Student(login, fname, lname, email, tallness)
VALUES(@v_login, @p_fname, @p_lname, @v_email, @p_tallness)
END

EXEC AddStudent22 'Filip', 'Kral', 180