Procedures - halotukozak/PBD GitHub Wiki

add_student

Purpose

This procedure is used to add a new student to the Student table.

Input Arguments

The stored procedure takes five arguments:

  • @first_nameβ€” A VARCHAR(50) type input which represents the first name of the student.
  • @last_nameβ€” A VARCHAR(50) type input which represents the last name of the student.
  • @addressβ€” A VARCHAR(200) type input which represents the address of the student.
  • @emailβ€” A VARCHAR(50) type input which represents the email of the student.
  • @phoneβ€” A VARCHAR(20) type input which represents the phone number of the student.

Functionality

The stored procedure inserts a new row into the Student table with the given input arguments.

CREATE PROCEDURE add_student
    @first_name VARCHAR(50),
    @last_name VARCHAR(50),
    @address VARCHAR(200),
    @email VARCHAR(50),
    @phone VARCHAR(20)
AS
BEGIN
    INSERT INTO Student (first_name, last_name, address, email, phone_number)
    VALUES (@first_name, @last_name, @address, @email, @phone);
END;

Example

EXEC add_student 'Jan', 'Kowalski', N'ul PiΔ™kna 3, Warszawa', '[email protected]', '123456789'

add_teacher

Purpose

This procedure is used to add a new teacher to the Teacher table.

Input Arguments

The procedure takes five arguments:

  • @first_nameβ€” A VARCHAR(50) type input which represents the first name of the teacher.
  • @last_nameβ€” A VARCHAR(50) type input which represents the last name of the teacher.
  • @addressβ€” A VARCHAR(200) type input which represents the address of the teacher.
  • @emailβ€” A VARCHAR(50) type input which represents the email of the teacher.
  • @phoneβ€” A VARCHAR(20) type input which represents the phone number of the teacher.

Functionality

The procedure inserts a new row into the Teacher table with the given input arguments.

CREATE PROCEDURE add_teacher
    @first_name VARCHAR(50),
    @last_name VARCHAR(50),
    @address VARCHAR(200),
    @email VARCHAR(50),
    @phone VARCHAR(20)
AS
BEGIN
    INSERT INTO Teacher (first_name, last_name, address, email, phone_number)
    VALUES (@first_name, @last_name, @address, @email, @phone);
END;

Example

EXEC add_teacher 'Jan', 'Kowalski', N'ul PiΔ™kna 3, Warszawa', '[email protected]', '123456789'

add_translator

Purpose

This procedure is used to add a new translator to the Translator table.

Input Arguments

The procedure takes six arguments:

  • @first_nameβ€” A VARCHAR(50) type input which represents the first name of the translator.
  • @last_nameβ€” A VARCHAR(50) type input which represents the last name of the translator.
  • @addressβ€” A VARCHAR(200) type input which represents the address of the translator.
  • @emailβ€” A VARCHAR(50) type input which represents the email of the translator.
  • @phoneβ€” A VARCHAR(20) type input which represents the phone number of the translator.

Functionality

The procedure inserts a new row into the Translator table with the given input arguments.

CREATE PROCEDURE add_translator
  @first_name VARCHAR(50),
    @last_name VARCHAR(50),
    @address VARCHAR(200),
    @email VARCHAR(50),
    @phone VARCHAR(20)
AS
BEGIN
INSERT INTO Translator (first_name, last_name, address, email, phone_number)
VALUES (@first_name, @last_name, @address, @email, @phone);
END;

Example

EXEC add_translator 'Jan', 'Kowalski', 'ul PiΔ™kna 3, Warszawa', '[email protected]', '123456789'

update_student

Purpose

This procedure is used to update a student's data in the Student table.

Input Arguments

The procedure takes six arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @first_nameβ€” A VARCHAR(50) type input which represents the first name of the student. This is an optional argument.
  • @last_nameβ€” A VARCHAR(50) type input which represents the last name of the student. This is an optional argument.
  • @addressβ€” A VARCHAR(200) type input which represents the address of the student. This is an optional argument.
  • @emailβ€” A VARCHAR(50) type input which represents the email of the student. This is an optional argument.
  • @phoneβ€” A VARCHAR(20) type input which represents the phone number of the student. This is an optional argument.

Functionality

The procedure updates the corresponding columns in the Student table for the given @student_id, if the corresponding arguments are not NULL.

CREATE PROCEDURE update_student
    @student_id INT,
    @first_name VARCHAR(50) = NULL,
    @last_name VARCHAR(50) = NULL,
    @address VARCHAR(200) = NULL,
    @email VARCHAR(50) = NULL,
    @phone VARCHAR(20) = NULL
AS
BEGIN
    IF @first_name IS NOT NULL
        BEGIN
            UPDATE Student
            SET first_name = @first_name
            WHERE id = @student_id;
        END
    IF @last_name IS NOT NULL
        BEGIN
            UPDATE Student
            SET last_name = @last_name
            WHERE id = @student_id;
        END
    IF @address IS NOT NULL
        BEGIN
            UPDATE Student
            SET address = @address
            WHERE id = @student_id;
        END
    IF @email IS NOT NULL
        BEGIN
            UPDATE Student
            SET email = @email
            WHERE id = @student_id;
        END
    IF @phone IS NOT NULL
        BEGIN
            UPDATE Student
            SET phone_number = @phone
            WHERE id = @student_id;
        END
END;

Example

EXEC update_student 1, @address = 'ul Brzydka 3, Warszawa'

update_teacher

Purpose

This procedure is used to update a teacher's data in the Teacher table.

Input Arguments

The procedure takes six arguments:

  • @teacher_idβ€” An INT type input which represents the ID of the teacher.
  • @first_nameβ€” A VARCHAR(50) type input which represents the first name of the teacher. This is an optional argument.
  • @last_nameβ€” A VARCHAR(50) type input which represents the last name of the teacher. This is an optional argument.
  • @addressβ€” A VARCHAR(200) type input which represents the address of the teacher. This is an optional argument.
  • @emailβ€” A VARCHAR(50) type input which represents the email of the teacher. This is an optional argument.
  • @phoneβ€” A VARCHAR(20) type input which represents the phone number of the teacher. This is an optional argument.

Functionality

The procedure updates the corresponding columns in the Teacher table for the given @teacher_id, if the corresponding arguments are not NULL.

CREATE PROCEDURE update_teacher
    @teacher_id INT,
    @first_name VARCHAR(50) = NULL,
    @last_name VARCHAR(50) = NULL,
    @address VARCHAR(200) = NULL,
    @email VARCHAR(50) = NULL,
    @phone VARCHAR(20) = NULL
AS
BEGIN
    IF @first_name IS NOT NULL
        BEGIN
            UPDATE Teacher
            SET first_name = @first_name
            WHERE id = @teacher_id;
        END
    IF @last_name IS NOT NULL
        BEGIN
            UPDATE Teacher
            SET last_name = @last_name
            WHERE id = @teacher_id;
        END
    IF @address IS NOT NULL
        BEGIN
            UPDATE Teacher
            SET address = @address
            WHERE id = @teacher_id;
        END
    IF @email IS NOT NULL
        BEGIN
            UPDATE Teacher
            SET email = @email
            WHERE id = @teacher_id;
        END
    IF @phone IS NOT NULL
        BEGIN
            UPDATE Teacher
            SET phone_number = @phone
            WHERE id = @teacher_id;
        END
END;

Example

EXEC update_teacher 1, @address = N'ul PiΔ™kna 7, Warszawa'

update_translator

Purpose

This procedure is used to update a translator's data in the Translator table.

Input Arguments

The procedure takes seven arguments:

  • @translator_idβ€” An INT type input which represents the ID of the translator.
  • @first_nameβ€” A VARCHAR(50) type input which represents the first name of the translator. This is an optional argument.
  • @last_nameβ€” A VARCHAR(50) type input which represents the last name of the translator. This is an optional argument.
  • @addressβ€” A VARCHAR(200) type input which represents the address of the translator. This is an optional argument.
  • @emailβ€” A VARCHAR(50) type input which represents the email of the translator. This is an optional argument.
  • @phoneβ€” A VARCHAR(20) type input which represents the phone number of the translator. This is an optional argument.

Functionality

The procedure updates the corresponding columns in the Translator table for the given @translator_id, if the corresponding arguments are not NULL.

CREATE PROCEDURE update_translator
    @translator_id INT,
    @first_name VARCHAR(50) = NULL,
    @last_name VARCHAR(50) = NULL,
    @address VARCHAR(200) = NULL,
    @email VARCHAR(50) = NULL,
    @phone VARCHAR(20) = NULL,
AS
BEGIN
    IF @first_name IS NOT NULL
        BEGIN
            UPDATE Translator
            SET first_name = @first_name
            WHERE id = @translator_id;
        END
    IF @last_name IS NOT NULL
        BEGIN
            UPDATE Translator
            SET last_name = @last_name
            WHERE id = @translator_id;
        END
    IF @address IS NOT NULL
        BEGIN
            UPDATE Translator
            SET address = @address
            WHERE id = @translator_id;
        END
    IF @email IS NOT NULL
        BEGIN
            UPDATE Translator
            SET email = @email
            WHERE id = @translator_id;
        END
    IF @phone IS NOT NULL
        BEGIN
            UPDATE Translator
            SET phone_number = @phone
            WHERE id = @translator_id;
        END
END;

Example

EXEC update_translator 1, @address =  'ul SΕ‚oneczna 7, Warszawa'

add_webinar

Purpose

This procedure is used to add a new webinar to the Webinar table.

Input Arguments

The procedure takes seven arguments:

  • @titleβ€” A VARCHAR(50) type input which represents the title of the webinar.
  • @datetimeβ€” A DATETIME type input which represents the date of the webinar.
  • @teacher_idβ€” An INT type input which represents the ID of the teacher.
  • @urlβ€” A VARCHAR(200) type input which represents the URL of the webinar.
  • @priceβ€” An INT type input which represents the price of the webinar. This is an optional argument. If not provided, the price is set to 0.
  • @languageβ€” A VARCHAR(50) type input which represents the language of the webinar. This is an optional argument. If not provided, the language is set to 'Polish'.
  • @translator_idβ€” An INT type input which represents the ID of the translator. This is an optional argument.

Functionality

The procedure inserts a new row into the Webinar table with the given input arguments. If @price or @language are not provided, they are set to default values.

CREATE PROCEDURE add_webinar
  @title VARCHAR(50),
    @datetime DATETIME,
    @teacher_id INT,
    @url VARCHAR(200),
    @price INT = NULL,
    @language VARCHAR(50) = NULL,
    @translator_id INT = NULL
AS
BEGIN
    IF @price IS NULL
        SET @price = 0;
    IF @language IS NULL
        SET @language = 'Polish';

INSERT INTO Webinar (title, datetime, teacher_id, url, price, language, translator_id)
VALUES (@title, @datetime, @teacher_id, @url, @price, @language, @translator_id);
END;

Example

EXEC add_webinar 'Webinar 1', '2019-01-01 12:00:00', 1, 'https://www.youtube.com/watch?v=1', 10000

update_webinar

Purpose

This procedure is used to update a webinar's data in the Webinar table.

Input Arguments

The procedure takes seven arguments:

  • @webinar_idβ€” An INT type input which represents the ID of the webinar.
  • @titleβ€” A VARCHAR(50) type input which represents the title of the webinar. This is an optional argument.
  • @datetimeβ€” A DATETIME type input which represents the date of the webinar. This is an optional argument.
  • @teacher_idβ€” An INT type input which represents the ID of the teacher. This is an optional argument.
  • @urlβ€” A VARCHAR(200) type input which represents the URL of the webinar. This is an optional argument.
  • @priceβ€” An INT type input which represents the price of the webinar. This is an optional argument.
  • @languageβ€” A VARCHAR(50) type input which represents the language of the webinar. This is an optional argument.
  • @translator_idβ€” An INT type input which represents the ID of the translator. This is an optional argument.

Functionality

The procedure updates the corresponding columns in the Webinar table for the given @webinar_id, if the corresponding arguments are not NULL.

CREATE PROCEDURE update_webinar
    @webinar_id INT,
    @title VARCHAR(50) = NULL,
    @datetime DATETIME = NULL,
    @teacher_id INT = NULL,
    @url VARCHAR(200) = NULL,
    @price INT = NULL,
    @language VARCHAR(50) = NULL,
    @translator_id INT = NULL
AS
BEGIN
    IF @title IS NOT NULL
        BEGIN
            UPDATE Webinar
            SET title = @title
            WHERE id = @webinar_id;
        END
    IF @datetime IS NOT NULL
        BEGIN
            UPDATE Webinar
            SET datetime = @datetime
            WHERE id = @webinar_id;
        END
    IF @teacher_id IS NOT NULL
        BEGIN
            UPDATE Webinar
            SET teacher_id = @teacher_id
            WHERE id = @webinar_id;
        END
    IF @url IS NOT NULL
        BEGIN
            UPDATE Webinar
            SET url = @url
            WHERE id = @webinar_id;
        END
    IF @price IS NOT NULL
        BEGIN
            UPDATE Webinar
            SET price = @price
            WHERE id = @webinar_id;
        END
    IF @language IS NOT NULL
        BEGIN
            UPDATE Webinar
            SET language = @language
            WHERE id = @webinar_id;
        END
    IF @translator_id IS NOT NULL
        BEGIN
            UPDATE Webinar
            SET translator_id = @translator_id
            WHERE id = @webinar_id;
        END
END;

Example

EXEC update_webinar 1, @url = 'https://www.youtube.com/watch?v=2', @teacher_id = 2

add_course

Purpose

This procedure is used to add a new course to the Course table.

Input Arguments

The procedure takes six arguments:

  • @titleβ€” A VARCHAR(50) type input which represents the title of the course.
  • @student_limitβ€” An INT type input which represents the student limit of the course.
  • @priceβ€” An INT type input which represents the price of the course.
  • @advance_priceβ€” An INT type input which represents the advance price of the course. This is an optional argument. If not provided, the advance price is set to 0.
  • @languageβ€” A VARCHAR(50) type input which represents the language of the course. This is an optional argument. If not provided, the language is set to 'Polish'.
  • @subjectβ€” A VARCHAR(100) type input which represents the subject of the course. This is an optional argument.

Functionality

The procedure inserts a new row into the Course table with the given input arguments. If @advance_price or @language are not provided, they are set to default values.

CREATE PROCEDURE add_course
    @title VARCHAR(50),
    @student_limit INT,
    @price INT,
    @advance_price INT = NULL,
    @language VARCHAR(50) = NULL,
    @subject VARCHAR(100) = NULL
AS
BEGIN
    IF @advance_price IS NULL
        SET @advance_price = 0;
    IF @language IS NULL
        SET @language = 'Polish';

    INSERT INTO Course (title, student_limit, price, advance_price, language, subject)
    VALUES (@title, @student_limit, @price, @advance_price, @language, @subject);
END;

Example

EXEC add_course 'C#', 10, 50000, 500, 'English', 'Programming'

update_course

Purpose

This procedure is used to update a course's data in the Course table.

Input Arguments

The procedure takes seven arguments:

  • @course_idβ€” An INT type input which represents the ID of the course.
  • @titleβ€” A VARCHAR(50) type input which represents the title of the course. This is an optional argument.
  • @student_limitβ€” An INT type input which represents the student limit of the course. This is an optional argument.
  • @priceβ€” An INT type input which represents the price of the course. This is an optional argument.
  • @advance_priceβ€” An INT type input which represents the advance price of the course. This is an optional argument.
  • @languageβ€” A VARCHAR(50) type input which represents the language of the course. This is an optional argument.
  • @subjectβ€” A VARCHAR(100) type input which represents the subject of the course. This is an optional argument.

Functionality

The procedure updates the corresponding columns in the Course table for the given @course_id, if the corresponding arguments are not NULL.

CREATE PROCEDURE update_course
    @course_id INT,
    @title VARCHAR(50) = NULL,
    @student_limit INT = NULL,
    @price INT = NULL,
    @advance_price INT = NULL,
    @language VARCHAR(50) = NULL,
    @subject VARCHAR(100) = NULL
AS
BEGIN
    IF @title IS NOT NULL
        BEGIN
            UPDATE Course
            SET title = @title
            WHERE id = @course_id;
        END
    IF @student_limit IS NOT NULL
        BEGIN
            UPDATE Course
            SET student_limit = @student_limit
            WHERE id = @course_id;
        END
    IF @price IS NOT NULL
        BEGIN
            UPDATE Course
            SET price = @price
            WHERE id = @course_id;
        END
    IF @advance_price IS NOT NULL
        BEGIN
            UPDATE Course
            SET advance_price = @advance_price
            WHERE id = @course_id;
        END
    IF @language IS NOT NULL
        BEGIN
            UPDATE Course
            SET language = @language
            WHERE id = @course_id;
        END
    IF @subject IS NOT NULL
        BEGIN
            UPDATE Course
            SET subject = @subject
            WHERE id = @course_id;
        END
END;

Example

EXEC update_course 1, @title = 'C++', @price = 5000

add_module

Purpose

This procedure is used to add a new module to the Module table.

Input Arguments

The procedure takes four arguments:

  • @course_idβ€” An INT type input which represents the ID of the course.
  • @teacher_idβ€” An INT type input which represents the ID of the teacher.
  • @typeβ€” A VARCHAR(15) type input which represents the type of the module. The possible values are 'online_sync', 'online_async', 'in_person', and 'hybrid'.
  • @start_dateβ€” A DATETIME type input which represents the start date of the module.
  • @room_idβ€” An INT type input which represents the ID of the room. This is an optional argument.

Functionality

The procedure inserts a new row into the Module table with the given input arguments.

CREATE PROCEDURE add_module
    @course_id INT,
    @teacher_id INT,
    @type VARCHAR(15),
    @start_date DATE,
    @room_id INT = NULL
AS
BEGIN
    INSERT INTO Module (course_id, teacher_id, type, start_date, room_id)
    VALUES (@course_id, @teacher_id, @type, @start_date, @room_id);
END;

Example

EXEC add_module 12, 23, 'in_person','2019-01-01 12:00:00', 17

update_module

Purpose

This procedure is used to update a module's data in the Module table.

Input Arguments

The procedure takes five arguments:

  • @module_idβ€” An INT type input which represents the ID of the module.
  • @course_idβ€” An INT type input which represents the ID of the course. This is an optional argument.
  • @teacher_idβ€” An INT type input which represents the ID of the teacher. This is an optional argument.
  • @typeβ€” A VARCHAR(15) type input which represents the type of the module. This is an optional argument. The possible values are 'online_sync', 'online_async', 'in_person', and 'hybrid'.
  • @start_dateβ€” A DATETIME type input which represents the start date of the module. This is an optional argument.
  • @room_idβ€” An INT type input which represents the ID of the room. This is an optional argument.

Functionality

The procedure updates the corresponding columns in the Module table for the given @module_id, if the corresponding arguments are not NULL. If @type is 'online_sync' or 'online_async', room_id is set to NULL.

CREATE PROCEDURE update_module
    @module_id INT,
    @course_id INT = NULL,
    @teacher_id INT = NULL,
    @type VARCHAR(15) = NULL,
    @start_date DATE = NULL,
    @room_id INT = NULL
AS
BEGIN
    IF @course_id IS NOT NULL
        BEGIN
            UPDATE Module
            SET course_id = @course_id
            WHERE id = @module_id;
        END
    IF @teacher_id IS NOT NULL
        BEGIN
            UPDATE Module
            SET teacher_id = @teacher_id
            WHERE id = @module_id;
        END
    IF @type IN ('online_sync', 'online_async')
        BEGIN
            UPDATE Module
            SET type = @type,
                room_id = NULL
            WHERE id = @module_id;
        END
    IF @type = 'in_person'
        BEGIN
            UPDATE Module
            SET type = @type,
                room_id = @room_id
            WHERE id = @module_id;
        END
    IF @type = 'hybrid'
        BEGIN
            UPDATE Module
            SET type = @type
            WHERE id = @module_id;
        END
    IF @start_date IS NOT NULL
        BEGIN
            UPDATE Module
            SET start_date = @start_date
            WHERE id = @module_id;
        END
    IF @room_id IS NOT NULL
        BEGIN
            UPDATE Module
            SET room_id = @room_id
            WHERE id = @module_id;
        END
END;

Example

EXEC update_module 1, @type = 'online_sync'

add_studies

Purpose

This procedure is used to add a new studies to the Studies table.

Input Arguments

The procedure takes six arguments:

  • @titleβ€” A VARCHAR(50) type input which represents the title of the studies.
  • @student_limitβ€” An INT type input which represents the student limit of the studies.
  • @registration_priceβ€” An INT type input which represents the registration price of the studies. This is an optional argument.
  • @syllabusβ€” A VARCHAR(5000) type input which represents the syllabus of the studies. This is an optional argument.
  • @languageβ€” A VARCHAR(50) type input which represents the language of the studies. This is an optional argument. If not provided, the language is set to 'Polish'.

Functionality

The procedure inserts a new row into the Studies table with the given input arguments. If @language is not provided, it is set to default value 'Polish'.

CREATE PROCEDURE add_studies
    @title VARCHAR(50),
    @student_limit INT,
    @registration_price INT = NULL,
    @syllabus VARCHAR(5000) = NULL,
    @language VARCHAR(50) = NULL
AS
BEGIN
    IF @language IS NULL
        SET @language = 'Polish';

    INSERT INTO Studies (title, student_limit, registration_price, syllabus, language)
    VALUES (@title, @student_limit, @registration_price, @syllabus, @language);
END;

Example

EXEC add_studies 'Math', 150, 400000, 'we will be learning algebra'

update_studies

Purpose

This procedure is used to update a studies' data in the Studies table.

Input Arguments

The procedure takes seven arguments:

  • @studies_idβ€” An INT type input which represents the ID of the studies.
  • @titleβ€” A VARCHAR(50) type input which represents the title of the studies. This is an optional argument.
  • @student_limitβ€” An INT type input which represents the student limit of the studies. This is an optional argument.
  • @registration_priceβ€” An INT type input which represents the registration price of the studies. This is an optional argument.
  • @syllabusβ€” A VARCHAR(5000) type input which represents the syllabus of the studies. This is an optional argument.
  • @languageβ€” A VARCHAR(50) type input which represents the language of the studies. This is an optional argument.

Functionality

The procedure updates the corresponding columns in the Studies table for the given @studies_id, if the corresponding arguments are not NULL.

CREATE PROCEDURE update_studies
    @studies_id INT,
    @title VARCHAR(50) = NULL,
    @student_limit INT = NULL,
    @registration_price INT = NULL,
    @syllabus VARCHAR(5000) = NULL,
    @language VARCHAR(50) = NULL
AS
BEGIN
    IF @title IS NOT NULL
        BEGIN
            UPDATE Studies
            SET title = @title
            WHERE id = @studies_id;
        END
    IF @student_limit IS NOT NULL
        BEGIN
            UPDATE Studies
            SET student_limit = @student_limit
            WHERE id = @studies_id;
        END
    IF @registration_price IS NOT NULL
        BEGIN
            UPDATE Studies
            SET registration_price = @registration_price
            WHERE id = @studies_id;
        END
    IF @syllabus IS NOT NULL
        BEGIN
            UPDATE Studies
            SET syllabus = @syllabus
            WHERE id = @studies_id;
        END
    IF @language IS NOT NULL
        BEGIN
            UPDATE Studies
            SET language = @language
            WHERE id = @studies_id;
        END
END;

Example

EXEC update_studies 1, @syllabus = 'We will be learning geometry'

add_semester

Purpose

This procedure is used to add a new semester to the Semester table.

Input Arguments

The procedure takes five arguments:

  • @studies_idβ€” An INT type input which represents the ID of the studies.
  • @start_dateβ€” A DATE type input which represents the start date of the semester.
  • @end_dateβ€” A DATE type input which represents the end date of the semester.
  • @priceβ€” An INT type input which represents the price of the semester.
  • @schedule_urlβ€” A VARCHAR(200) type input which represents the schedule URL of the semester. This is an optional argument.
  • @numberβ€” An INT type input which represents the number of the semester. This is an optional argument. If not provided, the number is set to the last semester number of the given studies incremented by 1.

Functionality

The procedure inserts a new row into the Semester table with the given input arguments. If @number is not provided, it is set to the last semester number of the given studies incremented by 1.

CREATE PROCEDURE add_semester
  @studies_id INT,
    @start_date DATE,
    @end_date DATE,
    @price INT,
    @schedule_url VARCHAR(200) = NULL,
    @number INT = NULL
AS
BEGIN
    IF @number IS NULL
        SET @number = dbo.get_last_semester(@studies_id) + 1;

INSERT INTO Semester (studies_id, number, start_date, end_date, price, schedule_url)
VALUES (@studies_id, @number, @start_date, @end_date, @price, @schedule_url);
END;

Example

EXEC add_semester 1, '2019-01-01', '2019-06-30',100000, 'https://web.usos.agh.edu.pl/kontroler.php?_action=home/plan'

update_semester

Purpose

This procedure is used to update a semester's data in the Semester table.

Input Arguments

The procedure takes six arguments:

  • @semester_idβ€” An INT type input which represents the ID of the semester.
  • @studies_idβ€” An INT type input which represents the ID of the studies. This is an optional argument.
  • @start_dateβ€” A DATE type input which represents the start date of the semester. This is an optional argument.
  • @end_dateβ€” A DATE type input which represents the end date of the semester. This is an optional argument.
  • @priceβ€” An INT type input which represents the price of the semester. This is an optional argument.
  • @schedule_urlβ€” A VARCHAR(200) type input which represents the schedule URL of the semester. This is an optional argument.
  • @numberβ€” An INT type input which represents the number of the semester. This is an optional argument.

Functionality

The procedure updates the corresponding columns in the Semester table for the given @semester_id, if the corresponding arguments are not NULL.

CREATE PROCEDURE update_semester
    @semester_id INT,
    @studies_id INT = NULL,
    @start_date DATE = NULL,
    @end_date DATE = NULL,
    @price INT = NULL,
    @schedule_url VARCHAR(200) = NULL,
    @number INT = NULL
AS
BEGIN
    IF @studies_id IS NOT NULL
        BEGIN
            UPDATE Semester
            SET studies_id = @studies_id
            WHERE id = @semester_id;
        END
    IF @start_date IS NOT NULL
        BEGIN
            UPDATE Semester
            SET start_date = @start_date
            WHERE id = @semester_id;
        END
    IF @end_date IS NOT NULL
        BEGIN
            UPDATE Semester
            SET end_date = @end_date
            WHERE id = @semester_id;
        END
    IF @price IS NOT NULL
        BEGIN
            UPDATE Semester
            SET price = @price
            WHERE id = @semester_id;
        END
    IF @schedule_url IS NOT NULL
        BEGIN
            UPDATE Semester
            SET schedule_url = @schedule_url
            WHERE id = @semester_id;
        END
    IF @number IS NOT NULL
        BEGIN
            UPDATE Semester
            SET number = @number
            WHERE id = @semester_id;
        END
END;

Example

EXEC update_semester 17, @end_date =  '2021-01-01', @schedule_url = 'https://web.usos.agh.edu.pl/kontroler.php?_action=home/plan'

add_subject

Purpose

This procedure is used to add a new subject to the Subject table.

Input Arguments

The procedure takes four arguments:

  • @nameβ€” A VARCHAR(200) type input which represents the name of the subject.
  • @semester_idβ€” An INT type input which represents the ID of the semester.
  • @teacher_idβ€” An INT type input which represents the ID of the teacher.
  • @room_idβ€” An INT type input which represents the ID of the room.

Functionality

The procedure inserts a new row into the Subject table with the given input arguments.

CREATE PROCEDURE add_subject
    @name VARCHAR(200),
    @semester_id INT,
    @teacher_id INT,
    @room_id INT
AS
BEGIN
    INSERT INTO Subject (name, semester_id, teacher_id, room_id)
    VALUES (@name, @semester_id, @teacher_id, @room_id);
END;

Example

EXEC add_subject 'Bazy danych', 17, 1

update_subject

Purpose

This procedure is used to update a subject's data in the Subject table.

Input Arguments

The procedure takes five arguments:

  • @subject_idβ€” An INT type input which represents the ID of the subject.
  • @nameβ€” A VARCHAR(200) type input which represents the name of the subject. This is an optional argument.
  • @semester_idβ€” An INT type input which represents the ID of the semester. This is an optional argument.
  • @teacher_idβ€” An INT type input which represents the ID of the teacher. This is an optional argument.
  • @room_idβ€” An INT type input which represents the ID of the room. This is an optional argument.

Functionality

The procedure updates the corresponding columns in the Subject table for the given @subject_id, if the corresponding arguments are not NULL.

CREATE PROCEDURE update_subject
    @subject_id INT,
    @name VARCHAR(200) = NULL,
    @semester_id INT = NULL,
    @teacher_id INT = NULL,
    @room_id INT = NULL
AS
BEGIN
    IF @name IS NOT NULL
        BEGIN
            UPDATE Subject
            SET name = @name
            WHERE id = @subject_id;
        END
    IF @semester_id IS NOT NULL
        BEGIN
            UPDATE Subject
            SET semester_id = @semester_id
            WHERE id = @subject_id;
        END
    IF @teacher_id IS NOT NULL
        BEGIN
            UPDATE Subject
            SET teacher_id = @teacher_id
            WHERE id = @subject_id;
        END
    IF @room_id IS NOT NULL
        BEGIN
            UPDATE Subject
            SET room_id = @room_id
            WHERE id = @subject_id;
        END
END;

Example

EXEC update_subject 17, @name = 'Programowanie obiektowe', @teacher_id = 23

add_meeting

Purpose

This procedure is used to add a new meeting to the Meeting table.

Input Arguments

The procedure takes ten arguments:

  • @module_idβ€” An INT type input which represents the ID of the module. This is an optional argument.
  • @subject_idβ€” An INT type input which represents the ID of the subject. This is an optional argument.
  • @datetimeβ€” A DATETIME type input which represents the date of the meeting.
  • @student_limitβ€” An INT type input which represents the student limit of the meeting.
  • @typeβ€” A VARCHAR(10) type input which represents the type of the meeting.
  • @urlβ€” A VARCHAR(200) type input which represents the URL of the meeting. This is an optional argument.
  • @substituting_room_idβ€” An INT type input which represents the ID of the substituting room. This is an optional argument.
  • @substituting_teacher_idβ€” An INT type input which represents the ID of the substituting teacher. This is an optional argument.
  • @translator_idβ€” An INT type input which represents the ID of the translator. This is an optional argument.
  • @standalone_priceβ€” An INT type input which represents the standalone price of the meeting. This is an optional argument.

Functionality

The procedure inserts a new row into the Meeting table with the given input arguments.

CREATE PROCEDURE add_meeting
    @module_id INT = NULL,
    @subject_id INT = NULL,
    @datetime DATETIME,
    @student_limit INT,
    @type VARCHAR(10),
    @url VARCHAR(200) = NULL,
    @substituting_room_id INT = NULL,
    @substituting_teacher_id INT = NULL,
    @translator_id INT = NULL,
    @standalone_price INT = NULL
AS
BEGIN
    INSERT INTO Meeting (module_id, subject_id, datetime, student_limit, type, url, substituting_room_id, substituting_teacher_id, translator_id, standalone_price)
    VALUES (@module_id, @subject_id, @datetime, @student_limit, @type, @url, @substituting_room_id, @substituting_teacher_id, @translator_id, @standalone_price);
END;

Example

EXEC add_meeting @module_id = 1, @date = '2021-01-01 12:00:00', @student_limit = 20, @type = 'in_person'

update_meeting

Purpose

This procedure is used to update a meeting's data in the Meeting table.

Input Arguments

The procedure takes eleven arguments:

  • @meeting_idβ€” An INT type input which represents the ID of the meeting.
  • @module_idβ€” An INT type input which represents the ID of the module. This is an optional argument.
  • @subject_idβ€” An INT type input which represents the ID of the subject. This is an optional argument.
  • @datetimeβ€” A DATETIME type input which represents the date of the meeting. This is an optional argument.
  • @student_limitβ€” An INT type input which represents the student limit of the meeting. This is an optional argument.
  • @typeβ€” A VARCHAR(10) type input which represents the type of the meeting. This is an optional argument.
  • @urlβ€” A VARCHAR(200) type input which represents the URL of the meeting. This is an optional argument.
  • @substituting_room_idβ€” An INT type input which represents the ID of the substituting room. This is an optional argument.
  • @substituting_teacher_idβ€” An INT type input which represents the ID of the substituting teacher. This is an optional argument.
  • @translator_idβ€” An INT type input which represents the ID of the translator. This is an optional argument.
  • @standalone_priceβ€” An INT type input which represents the standalone price of the meeting. This is an optional argument.

Functionality

The procedure updates the corresponding columns in the Meeting table for the given @meeting_id, if the corresponding arguments are not NULL.

CREATE PROCEDURE update_meeting
    @meeting_id INT,
    @module_id INT = NULL,
    @subject_id INT = NULL,
    @datetime DATETIME = NULL,
    @student_limit INT = NULL,
    @type VARCHAR(10) = NULL,
    @url VARCHAR(200) = NULL,
    @substituting_room_id INT = NULL,
    @substituting_teacher_id INT = NULL,
    @translator_id INT = NULL,
    @standalone_price INT = NULL
AS
BEGIN
    IF @module_id IS NOT NULL OR @subject_id IS NOT NULL
        BEGIN
            UPDATE Meeting
            SET module_id = @module_id,
                subject_id = @subject_id
            WHERE id = @meeting_id;
        END
    IF @datetime IS NOT NULL
        BEGIN
            UPDATE Meeting
            SET datetime = @datetime
            WHERE id = @meeting_id;
        END
    IF @student_limit IS NOT NULL
        BEGIN
            UPDATE Meeting
            SET student_limit = @student_limit
            WHERE id = @meeting_id;
        END
    IF @type IN ('online, video')
        BEGIN
            UPDATE Meeting
            SET type = @type,
                url = NULL
            WHERE id = @meeting_id;
        END
    IF @type LIKE 'in_person'
        BEGIN
            UPDATE Meeting
            SET type = @type
            WHERE id = @meeting_id;
        END
    IF @url IS NOT NULL
        BEGIN
            UPDATE Meeting
            SET url = @url
            WHERE id = @meeting_id;
        END
    IF @substituting_room_id IS NOT NULL
        BEGIN
            UPDATE Meeting
            SET substituting_room_id = @substituting_room_id
            WHERE id = @meeting_id;
        END
    IF @substituting_teacher_id IS NOT NULL
        BEGIN
            UPDATE Meeting
            SET substituting_teacher_id = @substituting_teacher_id
            WHERE id = @meeting_id;
        END
    IF @translator_id IS NOT NULL
        BEGIN
            UPDATE Meeting
            SET translator_id = @translator_id
            WHERE id = @meeting_id;
        END
    IF @standalone_price IS NOT NULL
        BEGIN
            UPDATE Meeting
            SET standalone_price = @standalone_price
            WHERE id = @meeting_id;
        END
END;

Example

EXEC update_meeting 1, @substituting_teacher_id = 27, @substituting_room_id = 17

enroll_student_for_meeting

Purpose

This procedure is used to add a new student to a meeting in the StudentMeeting table.

Input Arguments

The procedure takes three arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @meeting_idβ€” An INT type input which represents the ID of the meeting.
  • @payment_dateβ€” A DATETIME type input which represents the payment date. This is an optional argument.

Functionality

The procedure inserts a new row into the StudentMeeting table with the given input arguments.

CREATE PROCEDURE enroll_student_for_meeting
    @student_id INT,
    @meeting_id INT,
    @payment_date DATETIME = NULL
AS
BEGIN
    INSERT INTO StudentMeeting (student_id, meeting_id, payment_date)
    VALUES (@student_id, @meeting_id, @payment_date);
END;

Example

EXEC enroll_student_for_meeting 17, 23, '2021-01-01 12:00:00'

disenroll_student_from_meeting

Purpose

This procedure is used to remove a student from a meeting in the StudentMeeting table.

Input Arguments

The procedure takes two arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @meeting_idβ€” An INT type input which represents the ID of the meeting.

Functionality

The procedure deletes the row from the StudentMeeting table where the student_id and meeting_id match the given input arguments.

CREATE PROCEDURE disenroll_student_from_meeting
    @student_id INT,
    @meeting_id INT
AS
BEGIN
    DELETE
    FROM StudentMeeting
    WHERE student_id = @student_id
      AND meeting_id = @meeting_id;
END;

Example

EXEC disenroll_student_from_meeting 17, 23

register_meeting_payment

Purpose

This procedure is used to update the payment date for a student's meeting in the StudentMeeting table.

Input Arguments

The procedure takes three arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @meeting_idβ€” An INT type input which represents the ID of the meeting.
  • @payment_dateβ€” A DATETIME type input which represents the payment date. This is an optional argument and defaults to the current timestamp.

Functionality

The procedure updates the payment_date in the StudentMeeting table for the given @student_id and @meeting_id.

CREATE PROCEDURE register_meeting_payment
    @student_id INT,
    @meeting_id INT,
    @payment_date DATETIME = NULL
AS
BEGIN
    IF @payment_date IS NULL
        SET @payment_date = CURRENT_TIMESTAMP

    UPDATE StudentMeeting
    SET payment_date = @payment_date
    WHERE student_id = @student_id AND meeting_id = @meeting_id;
END;

Example

EXEC register_meeting_payment 17, 23, '2021-01-01 12:00:00'

enroll_student_for_course

Purpose

This procedure is used to add a student to a course in the StudentCourse table.

Input Arguments

The procedure takes four arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @course_idβ€” An INT type input which represents the ID of the course.
  • @advance_payment_dateβ€” A DATETIME type input which represents the date of the advance payment. This is an optional argument.
  • @full_payment_dateβ€” A DATETIME type input which represents the date of the full payment. This is an optional argument.

Functionality

The procedure inserts a new row into the StudentCourse table with the given input arguments.

CREATE PROCEDURE enroll_student_for_course
    @student_id INT,
    @course_id INT,
    @advance_payment_date DATETIME = NULL,
    @full_payment_date DATETIME = NULL
AS
BEGIN
    INSERT INTO StudentCourse (student_id, course_id, advance_payment_date, full_payment_date)
    VALUES (@student_id, @course_id, @advance_payment_date, @full_payment_date);
END;

Example

EXEC enroll_student_for_course 17, 23, @advance_payment_date = '2021-01-01 12:00:00'

disenroll_student_from_course

Purpose

This procedure is used to remove a student from a course in the StudentCourse table.

Input Arguments

The procedure takes two arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @course_idβ€” An INT type input which represents the ID of the course.

Functionality

The procedure deletes the row from the StudentCourse table for the given @student_id and @course_id.

CREATE PROCEDURE disenroll_student_from_course
    @student_id INT,
    @course_id INT
AS
BEGIN
    DELETE
    FROM StudentCourse
    WHERE student_id = @student_id
      AND course_id = @course_id;
END;

Example

EXEC disenroll_student_from_course 17, 23

register_course_payment

Purpose

This procedure is used to update the payment dates for a course in the StudentCourse table.

Input Arguments

The procedure takes four arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @course_idβ€” An INT type input which represents the ID of the course.
  • @advance_payment_dateβ€” A DATETIME type input which represents the date of the advance payment. This is an optional argument and defaults to the current timestamp.
  • @full_payment_dateβ€” A DATETIME type input which represents the date of the full payment. This is an optional argument and defaults to the current timestamp.

Functionality

The procedure updates the advance_payment_date and full_payment_date columns in the StudentCourse table for the given @student_id and @course_id.

CREATE PROCEDURE register_course_payment
    @student_id INT,
    @course_id INT,
    @advance_payment_date DATETIME = NULL,
    @full_payment_date DATETIME = NULL
AS
BEGIN
    IF @advance_payment_date IS NULL
        SET @advance_payment_date = CURRENT_TIMESTAMP
    IF @full_payment_date IS NULL
        SET @full_payment_date = CURRENT_TIMESTAMP

    UPDATE StudentCourse
    SET full_payment_date = @full_payment_date,
        advance_payment_date = @advance_payment_date
    WHERE student_id = @student_id AND course_id = @course_id;
END;

Example

EXEC register_course_payment 17, 23

enroll_student_for_studies

Purpose

This procedure is used to add a new student to a studies in the StudentStudies table.

Input Arguments

The procedure takes three arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @studies_idβ€” An INT type input which represents the ID of the studies.
  • @registration_payment_dateβ€” A DATETIME type input which represents the registration payment date. This is an optional argument.

Functionality

The procedure inserts a new row into the StudentStudies table with the given input arguments.

CREATE PROCEDURE enroll_student_for_studies
    @student_id INT,
    @studies_id INT,
    @registration_payment_date DATETIME = NULL
AS
BEGIN
    INSERT INTO StudentStudies (student_id, studies_id, registration_payment_date)
    VALUES (@student_id, @studies_id, @registration_payment_date);
END;

Example

EXEC enroll_student_for_studies 17, 23, @registration_payment_date = '2021-01-01 12:00:00'

disenroll_student_from_studies

Purpose

This procedure is used to remove a student from a studies in the StudentStudies table and from all semesters of that studies in the StudentSemester table.

Input Arguments

The procedure takes two arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @studies_idβ€” An INT type input which represents the ID of the studies.

Functionality

The procedure deletes the row from the StudentStudies table where the student_id and studies_id match the given input arguments. It also deletes all rows from the StudentSemester table where the student_id matches the given input and the semester_id is in the Semester table with the given studies_id.

CREATE PROCEDURE disenroll_student_from_studies
    @student_id INT,
    @studies_id INT
AS
BEGIN
    DELETE
    FROM StudentStudies
    WHERE student_id = @student_id
      AND studies_id = @studies_id;

    DELETE StudentSemester
    FROM StudentSemester
             INNER JOIN Semester ON StudentSemester.semester_id = Semester.id AND Semester.studies_id = @studies_id
    WHERE student_id = @student_id;
END;

Example

EXEC disenroll_student_from_studies 17, 23

register_studies_payment

Purpose

This procedure is used to update the registration payment date for a student's studies in the StudentStudies table.

Input Arguments

The procedure takes three arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @studies_idβ€” An INT type input which represents the ID of the studies.
  • @registration_payment_dateβ€” A DATETIME type input which represents the registration payment date. If not provided, the current timestamp is used.

Functionality

The procedure updates the registration_payment_date in the StudentStudies table for the given @student_id and @studies_id.

CREATE PROCEDURE register_studies_payment
    @student_id INT,
    @studies_id INT,
    @registration_payment_date DATETIME = NULL
AS
BEGIN
    IF @registration_payment_date IS NULL
        SET @registration_payment_date = CURRENT_TIMESTAMP

    UPDATE StudentStudies
    SET registration_payment_date = @registration_payment_date
    WHERE student_id = @student_id AND studies_id = @studies_id;
END;

Example

EXEC register_studies_payment 17, 23

send_graduation_certificate

Purpose

This procedure is used to update the certificate post date for a student in the StudentStudies table.

Input Arguments

The procedure takes three arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @studies_idβ€” An INT type input which represents the ID of the studies.
  • @certificate_post_dateβ€” A DATETIME type input which represents the date of the certificate post. This is an optional argument and defaults to the current timestamp.

Functionality

The procedure updates the certificate_post_date column in the StudentStudies table for the given @student_id and @studies_id.

CREATE PROCEDURE send_graduation_certificate
    @student_id INT,
    @studies_id INT,
    @certificate_post_date DATETIME = NULL
AS
BEGIN
    IF @certificate_post_date IS NULL
        SET @certificate_post_date = CURRENT_TIMESTAMP

    UPDATE StudentStudies
    SET certificate_post_date = @certificate_post_date
    WHERE student_id = @student_id AND studies_id = @studies_id;
END;

Example

EXEC send_graduation_certificate 17, 23

enroll_student_for_webinar

Purpose

This procedure is used to add a new student to a webinar in the StudentWebinar table.

Input Arguments

The procedure takes three arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @webinar_idβ€” An INT type input which represents the ID of the webinar.
  • @payment_dateβ€” A DATETIME type input which represents the payment date. This is an optional argument.

Functionality

The procedure inserts a new row into the StudentWebinar table with the given input arguments.

CREATE PROCEDURE enroll_student_for_webinar
    @student_id INT,
    @webinar_id INT,
    @payment_date DATETIME = NULL
AS
BEGIN
    INSERT INTO StudentWebinar (student_id, webinar_id, payment_date)
    VALUES (@student_id, @webinar_id, @payment_date);
END;

Example

EXEC enroll_student_for_webinar 17, 23, @payment_date = '2021-01-01 12:00:00'

disenroll_student_from_webinar

Purpose

This procedure is used to remove a student from a webinar in the StudentWebinar table.

Input Arguments

The procedure takes two arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @webinar_idβ€” An INT type input which represents the ID of the webinar.

Functionality

The procedure deletes the row from the StudentWebinar table where the student_id and webinar_id match the given input arguments.

CREATE PROCEDURE disenroll_student_from_webinar
    @student_id INT,
    @webinar_id INT
AS
BEGIN
    DELETE
    FROM StudentWebinar
    WHERE student_id = @student_id
      AND webinar_id = @webinar_id;
END;

Example

EXEC disenroll_student_from_webinar 17, 23

register_webinar_payment

Purpose

This procedure is used to update the payment date for a student's webinar in the StudentWebinar table.

Input Arguments

The procedure takes three arguments:

  • @student_id: This is an INT type input which represents the ID of the student.
  • @webinar_id: This is an INT type input which represents the ID of the webinar.
  • @payment_date: This is a DATETIME type input which represents the payment date. If not provided, the current timestamp is used.

Functionality

The procedure updates the payment_date in the StudentWebinar table for the given @student_id and @webinar_id.

CREATE PROCEDURE register_webinar_payment
  @student_id INT,
    @webinar_id INT,
    @payment_date DATETIME = NULL
AS
BEGIN
    IF @payment_date IS NULL
        SET @payment_date = CURRENT_TIMESTAMP

UPDATE StudentWebinar
SET payment_date = @payment_date
WHERE student_id = @student_id AND webinar_id = @webinar_id;
END;

Example

EXEC register_webinar_payment 17, 23

enroll_student_for_semester

Purpose

This procedure is used to add a student to a semester in the StudentSemester table.

Input Arguments

The procedure takes three arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @semester_idβ€” An INT type input which represents the ID of the semester.
  • @payment_dateβ€” A DATETIME type input which represents the date of the payment. This is an optional argument.

Functionality

The procedure inserts a new row into the StudentSemester table with the given input arguments.

CREATE PROCEDURE enroll_student_for_semester
    @student_id INT,
    @semester_id INT,
    @payment_date DATETIME = NULL
AS
BEGIN
    INSERT INTO StudentSemester (student_id, semester_id, payment_date)
    VALUES (@student_id, @semester_id, @payment_date);
END;

Example

EXEC enroll_student_for_semester 17, 23, @payment_date = '2021-01-01 12:00:00'

enroll_student_for_internship

Purpose

This procedure is used to enroll a student for an internship in the StudentInternship table.

Input Arguments

The procedure takes two arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @internship_idβ€” An INT type input which represents the ID of the internship.

Functionality

The procedure inserts a new row into the StudentInternship table with the given input arguments.

CREATE PROCEDURE enroll_student_for_internship
    @student_id INT,
    @internship_id INT
AS
BEGIN
    INSERT INTO StudentInternship (student_id, internship_id)
    VALUES (@student_id, @internship_id);
END;

disenroll_student_from_semester

Purpose

This procedure is used to remove a student from a semester in the StudentSemester table.

Input Arguments

The procedure takes two arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @semester_idβ€” An INT type input which represents the ID of the semester.

Functionality

The procedure deletes the row from the StudentSemester table for the given @student_id and @semester_id.

CREATE PROCEDURE disenroll_student_from_semester
    @student_id INT,
    @semester_id INT
AS
BEGIN
    DELETE
    FROM StudentSemester
    WHERE student_id = @student_id
      AND semester_id = @semester_id;
END;

Example

EXEC disenroll_student_from_semester 17, 23

register_semester_payment

Purpose

This procedure is used to update the payment date for a student in a semester in the StudentSemester table.

Input Arguments

The procedure takes three arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @semester_idβ€” An INT type input which represents the ID of the semester.
  • @payment_dateβ€” A DATETIME type input which represents the date of the payment. This is an optional argument and defaults to the current timestamp.

Functionality

The procedure updates the payment_date column in the StudentSemester table for the given @student_id and @semester_id.

CREATE PROCEDURE register_semester_payment
    @student_id INT,
    @semester_id INT,
    @payment_date DATETIME = NULL
AS
BEGIN
    IF @payment_date IS NULL
        SET @payment_date = CURRENT_TIMESTAMP

    UPDATE StudentSemester
    SET payment_date = @payment_date
    WHERE student_id = @student_id AND semester_id = @semester_id;
END;

Example

EXEC register_semester_payment 17, 23, @payment_date = '2021-01-01 12:00:00'

disenroll_student_from_internship

Purpose

This procedure is used to remove a student from an internship in the StudentInternship table.

Input Arguments

The procedure takes two arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @internship_idβ€” An INT type input which represents the ID of the internship.

Functionality

The procedure deletes the row from the StudentInternship table where the student_id and internship_id match the given input arguments.

CREATE PROCEDURE disenroll_student_from_internship
    @student_id INT,
    @internship_id INT
AS
BEGIN
    DELETE FROM StudentInternship
    WHERE student_id = @student_id AND internship_id = @internship_id;
END;

Example

EXEC disenroll_student_from_internship 17, 23

register_internship_attendance

Purpose

This procedure is used to log the attendance of a student in an internship in the StudentInternship table.

Input Arguments

The procedure takes two arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @internship_idβ€” An INT type input which represents the ID of the internship.

Functionality

The procedure increments the attended_days in the StudentInternship table for the given @student_id and @internship_id.

CREATE PROCEDURE register_internship_attendance
    @student_id INT,
    @internship_id INT
AS
BEGIN
    UPDATE StudentInternship
    SET attended_days = attended_days + 1
    WHERE student_id = @student_id
      AND internship_id = @internship_id;
END;

Example

EXEC register_internship_attendance 17, 23

register_internship_exam_result

Purpose

This procedure is used to set the exam result for a student in an internship in the StudentInternship table.

Input Arguments

The procedure takes three arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @internship_idβ€” An INT type input which represents the ID of the internship.
  • @exam_resultβ€” An INT type input which represents the result of the exam.

Functionality

The procedure updates the exam_result column in the StudentInternship table for the given @student_id and @internship_id.

CREATE PROCEDURE register_internship_exam_result
    @student_id INT,
    @internship_id INT,
    @exam_result INT
AS
BEGIN
    UPDATE StudentInternship
    SET exam_result = @exam_result
    WHERE student_id = @student_id
      AND internship_id = @internship_id;
END;

Example

EXEC register_internship_exam_result 17, 23, 5

register_meeting_attendance

Purpose

This procedure is used to log the attendance of a student in a meeting in the StudentMeetingAttendance table.

Input Arguments

The procedure takes two arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @meeting_idβ€” An INT type input which represents the ID of the meeting.

Functionality

The procedure inserts a new row into the StudentMeetingAttendance table with the given input arguments.

CREATE PROCEDURE register_meeting_attendance
    @student_id INT,
    @meeting_id INT
AS
BEGIN
    INSERT INTO StudentMeetingAttendance (student_id, meeting_id)
    VALUES (@student_id, @meeting_id);
END;

Example

EXEC register_meeting_attendance 17, 23

add_parameter

Purpose

This procedure is used to add a new parameter to the Parameter table.

Input Arguments

The procedure takes three arguments:

  • @nameβ€” An NVARCHAR(50) type input which represents the name of the parameter.
  • @valueβ€” An NVARCHAR(50) type input which represents the value of the parameter.
  • @dateβ€” A DATE type input which represents the date of the parameter. This is an optional argument and defaults to the current timestamp.

Functionality

The procedure inserts a new row into the Parameter table with the given input arguments.

CREATE PROCEDURE add_parameter
    @name NVARCHAR(50),
    @value NVARCHAR(50),
    @date DATE = NULL
AS
BEGIN
    IF @date IS NULL
        SET @date = CURRENT_TIMESTAMP;

    INSERT INTO Parameter (name, value, date)
    VALUES (@name, @value, @date);
END;

Example

EXEC add_parameter @name = 'test', @value = 'oldValue', @date = '2021-01-01'
EXEC add_parameter @name = 'test', @value = 'newValue'

create_basket

Purpose

This procedure is used to create a new basket in the Basket table.

Input Arguments

The procedure takes one argument:

  • @student_idβ€” An INT type input which represents the ID of the student.

Functionality

The procedure inserts a new row into the Basket table with the given @student_id, sets the state to 'open', and the create_date to the current timestamp.

CREATE PROCEDURE create_basket
    @student_id INT
AS
BEGIN
    INSERT INTO Basket (student_id, state, create_date)
    VALUES (@student_id, 'open', CURRENT_TIMESTAMP);
END;

Example

EXEC create_basket 17

add_item_to_basket

Purpose

This procedure is used to add an item (a course, meeting, studies, or webinar) to a student's basket in the BasketItem table. If the student doesn't have a basket yet, one will be created.

Input Arguments

The procedure takes five arguments:

  • @student_idβ€” An INT type input which represents the ID of the student.
  • @course_idβ€” An INT type input which represents the ID of the course. This is an optional argument.
  • @meeting_idβ€” An INT type input which represents the ID of the meeting. This is an optional argument.
  • @studies_idβ€” An INT type input which represents the ID of the studies. This is an optional argument.
  • @webinar_idβ€” An INT type input which represents the ID of the webinar. This is an optional argument.

Functionality

The procedure first checks if the student already has a basket by calling the dbo.get_student_basket function. If the student doesn't have a basket, the CreateBasket procedure is executed to create one. Then, depending on which arguments are not NULL, the procedure inserts a new row into the BasketItem table with the basket_id and the ID of the course, meeting, studies, or webinar.

CREATE PROCEDURE add_item_to_basket
    @student_id INT,
    @course_id INT = NULL,
    @meeting_id INT = NULL,
    @studies_id INT = NULL,
    @webinar_id INT = NULL
AS
BEGIN
    DECLARE @basket_id INT = dbo.get_student_basket(@student_id);

    IF @basket_id IS NULL
        BEGIN
            EXEC create_basket @student_id;
            SET @basket_id = dbo.get_student_basket(@student_id);
        END

    IF @course_id IS NOT NULL
        BEGIN
            INSERT INTO BasketItem (basket_id, course_id)
            VALUES (@basket_id, @course_id);
        END

    IF @meeting_id IS NOT NULL
        BEGIN
            INSERT INTO BasketItem (basket_id, meeting_id)
            VALUES (@basket_id, @meeting_id);
        END

    IF @studies_id IS NOT NULL
        BEGIN
            INSERT INTO BasketItem (basket_id, studies_id)
            VALUES (@basket_id, @studies_id);
        END

    IF @webinar_id IS NOT NULL
        BEGIN
            INSERT INTO BasketItem (basket_id, webinar_id)
            VALUES (@basket_id, @webinar_id);
        END
END;

Example

EXEC add_item_to_basket 17, @course_id = 3

remove_item_from_basket

Purpose

This procedure is used to remove an item from a basket in the BasketItem table.

Input Arguments

The procedure takes five arguments:

  • @basket_idβ€” An INT type input which represents the ID of the basket.
  • @course_idβ€” An INT type input which represents the ID of the course. This is an optional argument.
  • @meeting_idβ€” An INT type input which represents the ID of the meeting. This is an optional argument.
  • @studies_idβ€” An INT type input which represents the ID of the studies. This is an optional argument.
  • @webinar_idβ€” An INT type input which represents the ID of the webinar. This is an optional argument.

Functionality

The procedure deletes the row from the BasketItem table for the given @basket_id and the ID of the course, meeting, studies, or webinar, if the corresponding arguments are not NULL.

CREATE PROCEDURE remove_item_from_basket
    @basket_id INT,
    @course_id INT = NULL,
    @meeting_id INT = NULL,
    @studies_id INT = NULL,
    @webinar_id INT = NULL
AS
BEGIN
    IF @course_id IS NOT NULL
        BEGIN
            DELETE
            FROM BasketItem
            WHERE basket_id = @basket_id
              AND course_id = @course_id;
        END

    IF @meeting_id IS NOT NULL
        BEGIN
            DELETE
            FROM BasketItem
            WHERE basket_id = @basket_id
              AND meeting_id = @meeting_id;
        END

    IF @studies_id IS NOT NULL
        BEGIN
            DELETE
            FROM BasketItem
            WHERE basket_id = @basket_id
              AND studies_id = @studies_id;
        END

    IF @webinar_id IS NOT NULL
        BEGIN
            DELETE
            FROM BasketItem
            WHERE basket_id = @basket_id
              AND webinar_id = @webinar_id;
        END
END;

Example


EXEC remove_item_from_basket 17, @course_id = 3

'register_failed_payment'

Purpose

This procedure is used to update the state of a basket in the Basket table to 'failed_payment'.

Input Arguments

The procedure takes one argument:

  • @basket_idβ€” An INT type input which represents the ID of the basket.

Functionality

The procedure updates the state column in the Basket table to 'failed_payment' for the given @basket_id.

CREATE PROCEDURE register_failed_payment
    @basket_id INT
AS
BEGIN
    UPDATE Basket
    SET state = 'failed_payment'
    WHERE id = @basket_id;
END;

Example

EXEC register_failed_payment 35

register_successful_payment

Purpose

This procedure is used to approve the payment for a basket in the Basket table. It also adds the student to the course, meeting, studies, or webinar in the BasketItem table.

Input Arguments

The procedure takes one argument:

  • @basket_idβ€” An INT type input which represents the ID of the basket.

Functionality

The procedure first gets the student_id from the Basket table for the given @basket_id. Then, it opens a cursor for the BasketItem table where the basket_id matches the given input. For each row in the cursor, it checks if the course_id, meeting_id, studies_id, and webinar_id are not NULL, and if they are not, it executes the corresponding procedure to add the student to the course, meeting, studies, or webinar. Finally, it updates the state column in the Basket table to 'success_payment' for the given @basket_id.

CREATE PROCEDURE register_successful_payment
@basket_id INT
AS
BEGIN
    DECLARE @current_time DATETIME = CURRENT_TIMESTAMP;
    DECLARE @student_id INT = (SELECT student_id FROM Basket WHERE id = @basket_id)
    DECLARE @current_course INT
    DECLARE @current_meeting INT
    DECLARE @current_studies INT
    DECLARE @current_webinar INT

    DECLARE BasketItemCursor CURSOR FOR
        SELECT course_id, meeting_id, studies_id, webinar_id
        FROM BasketItem
        WHERE basket_id = @basket_id

    OPEN BasketItemCursor
    FETCH NEXT FROM BasketItemCursor INTO @current_course, @current_meeting, @current_studies, @current_webinar

    WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @current_course IS NOT NULL
                BEGIN
                    EXEC enroll_student_for_course @student_id, @current_course, @current_time, @current_time;
                END
            IF @current_meeting IS NOT NULL
                BEGIN
                    EXEC enroll_student_for_meeting @student_id, @current_meeting, @current_time;
                END
            IF @current_studies IS NOT NULL
                BEGIN
                    EXEC enroll_student_for_studies @student_id, @current_studies, @current_time;
                END
            IF @current_webinar IS NOT NULL
                BEGIN
                    EXEC enroll_student_for_webinar @student_id, @current_webinar, @current_time;
                END

            FETCH NEXT FROM BasketItemCursor INTO @current_course, @current_meeting, @current_studies, @current_webinar
        END;

    CLOSE BasketItemCursor
    DEALLOCATE BasketItemCursor

    UPDATE Basket
    SET state = 'success_payment'
    WHERE id = @basket_id;
END;

Example

EXEC register_successful_payment 35