-
Notifications
You must be signed in to change notification settings - Fork 0
Procedures
This procedure is used to add a new student to the Student
table.
The stored procedure takes five arguments:
-
@first_name
— AVARCHAR(50)
type input which represents the first name of the student. -
@last_name
— AVARCHAR(50)
type input which represents the last name of the student. -
@address
— AVARCHAR(200)
type input which represents the address of the student. -
@email
— AVARCHAR(50)
type input which represents the email of the student. -
@phone
— AVARCHAR(20)
type input which represents the phone number of the student.
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;
EXEC add_student 'Jan', 'Kowalski', N'ul Piękna 3, Warszawa', '[email protected]', '123456789'
This procedure is used to add a new teacher to the Teacher
table.
The procedure takes five arguments:
-
@first_name
— AVARCHAR(50)
type input which represents the first name of the teacher. -
@last_name
— AVARCHAR(50)
type input which represents the last name of the teacher. -
@address
— AVARCHAR(200)
type input which represents the address of the teacher. -
@email
— AVARCHAR(50)
type input which represents the email of the teacher. -
@phone
— AVARCHAR(20)
type input which represents the phone number of the teacher.
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;
EXEC add_teacher 'Jan', 'Kowalski', N'ul Piękna 3, Warszawa', '[email protected]', '123456789'
This procedure is used to add a new translator to the Translator
table.
The procedure takes six arguments:
-
@first_name
— AVARCHAR(50)
type input which represents the first name of the translator. -
@last_name
— AVARCHAR(50)
type input which represents the last name of the translator. -
@address
— AVARCHAR(200)
type input which represents the address of the translator. -
@email
— AVARCHAR(50)
type input which represents the email of the translator. -
@phone
— AVARCHAR(20)
type input which represents the phone number of the translator.
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;
EXEC add_translator 'Jan', 'Kowalski', 'ul Piękna 3, Warszawa', '[email protected]', '123456789'
This procedure is used to update a student's data in the Student
table.
The procedure takes six arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@first_name
— AVARCHAR(50)
type input which represents the first name of the student. This is an optional argument. -
@last_name
— AVARCHAR(50)
type input which represents the last name of the student. This is an optional argument. -
@address
— AVARCHAR(200)
type input which represents the address of the student. This is an optional argument. -
@email
— AVARCHAR(50)
type input which represents the email of the student. This is an optional argument. -
@phone
— AVARCHAR(20)
type input which represents the phone number of the student. This is an optional argument.
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;
EXEC update_student 1, @address = 'ul Brzydka 3, Warszawa'
This procedure is used to update a teacher's data in the Teacher
table.
The procedure takes six arguments:
-
@teacher_id
— AnINT
type input which represents the ID of the teacher. -
@first_name
— AVARCHAR(50)
type input which represents the first name of the teacher. This is an optional argument. -
@last_name
— AVARCHAR(50)
type input which represents the last name of the teacher. This is an optional argument. -
@address
— AVARCHAR(200)
type input which represents the address of the teacher. This is an optional argument. -
@email
— AVARCHAR(50)
type input which represents the email of the teacher. This is an optional argument. -
@phone
— AVARCHAR(20)
type input which represents the phone number of the teacher. This is an optional argument.
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;
EXEC update_teacher 1, @address = N'ul Piękna 7, Warszawa'
This procedure is used to update a translator's data in the Translator
table.
The procedure takes seven arguments:
-
@translator_id
— AnINT
type input which represents the ID of the translator. -
@first_name
— AVARCHAR(50)
type input which represents the first name of the translator. This is an optional argument. -
@last_name
— AVARCHAR(50)
type input which represents the last name of the translator. This is an optional argument. -
@address
— AVARCHAR(200)
type input which represents the address of the translator. This is an optional argument. -
@email
— AVARCHAR(50)
type input which represents the email of the translator. This is an optional argument. -
@phone
— AVARCHAR(20)
type input which represents the phone number of the translator. This is an optional argument.
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;
EXEC update_translator 1, @address = 'ul Słoneczna 7, Warszawa'
This procedure is used to add a new webinar to the Webinar
table.
The procedure takes seven arguments:
-
@title
— AVARCHAR(50)
type input which represents the title of the webinar. -
@datetime
— ADATETIME
type input which represents the date of the webinar. -
@teacher_id
— AnINT
type input which represents the ID of the teacher. -
@url
— AVARCHAR(200)
type input which represents the URL of the webinar. -
@price
— AnINT
type input which represents the price of the webinar. This is an optional argument. If not provided, the price is set to 0. -
@language
— AVARCHAR(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
— AnINT
type input which represents the ID of the translator. This is an optional argument.
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;
EXEC add_webinar 'Webinar 1', '2019-01-01 12:00:00', 1, 'https://www.youtube.com/watch?v=1', 10000
This procedure is used to update a webinar's data in the Webinar
table.
The procedure takes seven arguments:
-
@webinar_id
— AnINT
type input which represents the ID of the webinar. -
@title
— AVARCHAR(50)
type input which represents the title of the webinar. This is an optional argument. -
@datetime
— ADATETIME
type input which represents the date of the webinar. This is an optional argument. -
@teacher_id
— AnINT
type input which represents the ID of the teacher. This is an optional argument. -
@url
— AVARCHAR(200)
type input which represents the URL of the webinar. This is an optional argument. -
@price
— AnINT
type input which represents the price of the webinar. This is an optional argument. -
@language
— AVARCHAR(50)
type input which represents the language of the webinar. This is an optional argument. -
@translator_id
— AnINT
type input which represents the ID of the translator. This is an optional argument.
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;
EXEC update_webinar 1, @url = 'https://www.youtube.com/watch?v=2', @teacher_id = 2
This procedure is used to add a new course to the Course
table.
The procedure takes six arguments:
-
@title
— AVARCHAR(50)
type input which represents the title of the course. -
@student_limit
— AnINT
type input which represents the student limit of the course. -
@price
— AnINT
type input which represents the price of the course. -
@advance_price
— AnINT
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
— AVARCHAR(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
— AVARCHAR(100)
type input which represents the subject of the course. This is an optional argument.
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;
EXEC add_course 'C#', 10, 50000, 500, 'English', 'Programming'
This procedure is used to update a course's data in the Course
table.
The procedure takes seven arguments:
-
@course_id
— AnINT
type input which represents the ID of the course. -
@title
— AVARCHAR(50)
type input which represents the title of the course. This is an optional argument. -
@student_limit
— AnINT
type input which represents the student limit of the course. This is an optional argument. -
@price
— AnINT
type input which represents the price of the course. This is an optional argument. -
@advance_price
— AnINT
type input which represents the advance price of the course. This is an optional argument. -
@language
— AVARCHAR(50)
type input which represents the language of the course. This is an optional argument. -
@subject
— AVARCHAR(100)
type input which represents the subject of the course. This is an optional argument.
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;
EXEC update_course 1, @title = 'C++', @price = 5000
This procedure is used to add a new module to the Module
table.
The procedure takes four arguments:
-
@course_id
— AnINT
type input which represents the ID of the course. -
@teacher_id
— AnINT
type input which represents the ID of the teacher. -
@type
— AVARCHAR(15)
type input which represents the type of the module. The possible values are 'online_sync', 'online_async', 'in_person', and 'hybrid'. -
@start_date
— ADATETIME
type input which represents the start date of the module. -
@room_id
— AnINT
type input which represents the ID of the room. This is an optional argument.
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;
EXEC add_module 12, 23, 'in_person','2019-01-01 12:00:00', 17
This procedure is used to update a module's data in the Module
table.
The procedure takes five arguments:
-
@module_id
— AnINT
type input which represents the ID of the module. -
@course_id
— AnINT
type input which represents the ID of the course. This is an optional argument. -
@teacher_id
— AnINT
type input which represents the ID of the teacher. This is an optional argument. -
@type
— AVARCHAR(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
— ADATETIME
type input which represents the start date of the module. This is an optional argument. -
@room_id
— AnINT
type input which represents the ID of the room. This is an optional argument.
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;
EXEC update_module 1, @type = 'online_sync'
This procedure is used to add a new studies to the Studies
table.
The procedure takes six arguments:
-
@title
— AVARCHAR(50)
type input which represents the title of the studies. -
@student_limit
— AnINT
type input which represents the student limit of the studies. -
@registration_price
— AnINT
type input which represents the registration price of the studies. This is an optional argument. -
@syllabus
— AVARCHAR(5000)
type input which represents the syllabus of the studies. This is an optional argument. -
@language
— AVARCHAR(50)
type input which represents the language of the studies. This is an optional argument. If not provided, the language is set to 'Polish'.
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;
EXEC add_studies 'Math', 150, 400000, 'we will be learning algebra'
This procedure is used to update a studies' data in the Studies
table.
The procedure takes seven arguments:
-
@studies_id
— AnINT
type input which represents the ID of the studies. -
@title
— AVARCHAR(50)
type input which represents the title of the studies. This is an optional argument. -
@student_limit
— AnINT
type input which represents the student limit of the studies. This is an optional argument. -
@registration_price
— AnINT
type input which represents the registration price of the studies. This is an optional argument. -
@syllabus
— AVARCHAR(5000)
type input which represents the syllabus of the studies. This is an optional argument. -
@language
— AVARCHAR(50)
type input which represents the language of the studies. This is an optional argument.
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;
EXEC update_studies 1, @syllabus = 'We will be learning geometry'
This procedure is used to add a new semester to the Semester
table.
The procedure takes five arguments:
-
@studies_id
— AnINT
type input which represents the ID of the studies. -
@start_date
— ADATE
type input which represents the start date of the semester. -
@end_date
— ADATE
type input which represents the end date of the semester. -
@price
— AnINT
type input which represents the price of the semester. -
@schedule_url
— AVARCHAR(200)
type input which represents the schedule URL of the semester. This is an optional argument. -
@number
— AnINT
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.
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;
EXEC add_semester 1, '2019-01-01', '2019-06-30',100000, 'https://web.usos.agh.edu.pl/kontroler.php?_action=home/plan'
This procedure is used to update a semester's data in the Semester
table.
The procedure takes six arguments:
-
@semester_id
— AnINT
type input which represents the ID of the semester. -
@studies_id
— AnINT
type input which represents the ID of the studies. This is an optional argument. -
@start_date
— ADATE
type input which represents the start date of the semester. This is an optional argument. -
@end_date
— ADATE
type input which represents the end date of the semester. This is an optional argument. -
@price
— AnINT
type input which represents the price of the semester. This is an optional argument. -
@schedule_url
— AVARCHAR(200)
type input which represents the schedule URL of the semester. This is an optional argument. -
@number
— AnINT
type input which represents the number of the semester. This is an optional argument.
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;
EXEC update_semester 17, @end_date = '2021-01-01', @schedule_url = 'https://web.usos.agh.edu.pl/kontroler.php?_action=home/plan'
This procedure is used to add a new subject to the Subject
table.
The procedure takes four arguments:
-
@name
— AVARCHAR(200)
type input which represents the name of the subject. -
@semester_id
— AnINT
type input which represents the ID of the semester. -
@teacher_id
— AnINT
type input which represents the ID of the teacher. -
@room_id
— AnINT
type input which represents the ID of the room.
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;
EXEC add_subject 'Bazy danych', 17, 1
This procedure is used to update a subject's data in the Subject
table.
The procedure takes five arguments:
-
@subject_id
— AnINT
type input which represents the ID of the subject. -
@name
— AVARCHAR(200)
type input which represents the name of the subject. This is an optional argument. -
@semester_id
— AnINT
type input which represents the ID of the semester. This is an optional argument. -
@teacher_id
— AnINT
type input which represents the ID of the teacher. This is an optional argument. -
@room_id
— AnINT
type input which represents the ID of the room. This is an optional argument.
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;
EXEC update_subject 17, @name = 'Programowanie obiektowe', @teacher_id = 23
This procedure is used to add a new meeting to the Meeting
table.
The procedure takes ten arguments:
-
@module_id
— AnINT
type input which represents the ID of the module. This is an optional argument. -
@subject_id
— AnINT
type input which represents the ID of the subject. This is an optional argument. -
@datetime
— ADATETIME
type input which represents the date of the meeting. -
@student_limit
— AnINT
type input which represents the student limit of the meeting. -
@type
— AVARCHAR(10)
type input which represents the type of the meeting. -
@url
— AVARCHAR(200)
type input which represents the URL of the meeting. This is an optional argument. -
@substituting_room_id
— AnINT
type input which represents the ID of the substituting room. This is an optional argument. -
@substituting_teacher_id
— AnINT
type input which represents the ID of the substituting teacher. This is an optional argument. -
@translator_id
— AnINT
type input which represents the ID of the translator. This is an optional argument. -
@standalone_price
— AnINT
type input which represents the standalone price of the meeting. This is an optional argument.
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;
EXEC add_meeting @module_id = 1, @date = '2021-01-01 12:00:00', @student_limit = 20, @type = 'in_person'
This procedure is used to update a meeting's data in the Meeting
table.
The procedure takes eleven arguments:
-
@meeting_id
— AnINT
type input which represents the ID of the meeting. -
@module_id
— AnINT
type input which represents the ID of the module. This is an optional argument. -
@subject_id
— AnINT
type input which represents the ID of the subject. This is an optional argument. -
@datetime
— ADATETIME
type input which represents the date of the meeting. This is an optional argument. -
@student_limit
— AnINT
type input which represents the student limit of the meeting. This is an optional argument. -
@type
— AVARCHAR(10)
type input which represents the type of the meeting. This is an optional argument. -
@url
— AVARCHAR(200)
type input which represents the URL of the meeting. This is an optional argument. -
@substituting_room_id
— AnINT
type input which represents the ID of the substituting room. This is an optional argument. -
@substituting_teacher_id
— AnINT
type input which represents the ID of the substituting teacher. This is an optional argument. -
@translator_id
— AnINT
type input which represents the ID of the translator. This is an optional argument. -
@standalone_price
— AnINT
type input which represents the standalone price of the meeting. This is an optional argument.
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;
EXEC update_meeting 1, @substituting_teacher_id = 27, @substituting_room_id = 17
This procedure is used to add a new student to a meeting in the StudentMeeting
table.
The procedure takes three arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@meeting_id
— AnINT
type input which represents the ID of the meeting. -
@payment_date
— ADATETIME
type input which represents the payment date. This is an optional argument.
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;
EXEC enroll_student_for_meeting 17, 23, '2021-01-01 12:00:00'
This procedure is used to remove a student from a meeting in the StudentMeeting
table.
The procedure takes two arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@meeting_id
— AnINT
type input which represents the ID of the meeting.
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;
EXEC disenroll_student_from_meeting 17, 23
This procedure is used to update the payment date for a student's meeting in the StudentMeeting
table.
The procedure takes three arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@meeting_id
— AnINT
type input which represents the ID of the meeting. -
@payment_date
— ADATETIME
type input which represents the payment date. This is an optional argument and defaults to the current timestamp.
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;
EXEC register_meeting_payment 17, 23, '2021-01-01 12:00:00'
This procedure is used to add a student to a course in the StudentCourse
table.
The procedure takes four arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@course_id
— AnINT
type input which represents the ID of the course. -
@advance_payment_date
— ADATETIME
type input which represents the date of the advance payment. This is an optional argument. -
@full_payment_date
— ADATETIME
type input which represents the date of the full payment. This is an optional argument.
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;
EXEC enroll_student_for_course 17, 23, @advance_payment_date = '2021-01-01 12:00:00'
This procedure is used to remove a student from a course in the StudentCourse
table.
The procedure takes two arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@course_id
— AnINT
type input which represents the ID of the course.
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;
EXEC disenroll_student_from_course 17, 23
This procedure is used to update the payment dates for a course in the StudentCourse
table.
The procedure takes four arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@course_id
— AnINT
type input which represents the ID of the course. -
@advance_payment_date
— ADATETIME
type input which represents the date of the advance payment. This is an optional argument and defaults to the current timestamp. -
@full_payment_date
— ADATETIME
type input which represents the date of the full payment. This is an optional argument and defaults to the current timestamp.
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;
EXEC register_course_payment 17, 23
This procedure is used to add a new student to a studies in the StudentStudies
table.
The procedure takes three arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@studies_id
— AnINT
type input which represents the ID of the studies. -
@registration_payment_date
— ADATETIME
type input which represents the registration payment date. This is an optional argument.
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;
EXEC enroll_student_for_studies 17, 23, @registration_payment_date = '2021-01-01 12:00:00'
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.
The procedure takes two arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@studies_id
— AnINT
type input which represents the ID of the studies.
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;
EXEC disenroll_student_from_studies 17, 23
This procedure is used to update the registration payment date for a student's studies in the StudentStudies
table.
The procedure takes three arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@studies_id
— AnINT
type input which represents the ID of the studies. -
@registration_payment_date
— ADATETIME
type input which represents the registration payment date. If not provided, the current timestamp is used.
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;
EXEC register_studies_payment 17, 23
This procedure is used to update the certificate post date for a student in the StudentStudies
table.
The procedure takes three arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@studies_id
— AnINT
type input which represents the ID of the studies. -
@certificate_post_date
— ADATETIME
type input which represents the date of the certificate post. This is an optional argument and defaults to the current timestamp.
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;
EXEC send_graduation_certificate 17, 23
This procedure is used to add a new student to a webinar in the StudentWebinar
table.
The procedure takes three arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@webinar_id
— AnINT
type input which represents the ID of the webinar. -
@payment_date
— ADATETIME
type input which represents the payment date. This is an optional argument.
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;
EXEC enroll_student_for_webinar 17, 23, @payment_date = '2021-01-01 12:00:00'
This procedure is used to remove a student from a webinar in the StudentWebinar
table.
The procedure takes two arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@webinar_id
— AnINT
type input which represents the ID of the webinar.
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;
EXEC disenroll_student_from_webinar 17, 23
This procedure is used to update the payment date for a student's webinar in the StudentWebinar
table.
The procedure takes three arguments:
-
@student_id
: This is anINT
type input which represents the ID of the student. -
@webinar_id
: This is anINT
type input which represents the ID of the webinar. -
@payment_date
: This is aDATETIME
type input which represents the payment date. If not provided, the current timestamp is used.
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;
EXEC register_webinar_payment 17, 23
This procedure is used to add a student to a semester in the StudentSemester
table.
The procedure takes three arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@semester_id
— AnINT
type input which represents the ID of the semester. -
@payment_date
— ADATETIME
type input which represents the date of the payment. This is an optional argument.
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;
EXEC enroll_student_for_semester 17, 23, @payment_date = '2021-01-01 12:00:00'
This procedure is used to enroll a student for an internship in the StudentInternship
table.
The procedure takes two arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@internship_id
— AnINT
type input which represents the ID of the internship.
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;
This procedure is used to remove a student from a semester in the StudentSemester
table.
The procedure takes two arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@semester_id
— AnINT
type input which represents the ID of the semester.
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;
EXEC disenroll_student_from_semester 17, 23
This procedure is used to update the payment date for a student in a semester in the StudentSemester
table.
The procedure takes three arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@semester_id
— AnINT
type input which represents the ID of the semester. -
@payment_date
— ADATETIME
type input which represents the date of the payment. This is an optional argument and defaults to the current timestamp.
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;
EXEC register_semester_payment 17, 23, @payment_date = '2021-01-01 12:00:00'
This procedure is used to remove a student from an internship in the StudentInternship
table.
The procedure takes two arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@internship_id
— AnINT
type input which represents the ID of the internship.
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;
EXEC disenroll_student_from_internship 17, 23
This procedure is used to log the attendance of a student in an internship in the StudentInternship
table.
The procedure takes two arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@internship_id
— AnINT
type input which represents the ID of the internship.
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;
EXEC register_internship_attendance 17, 23
This procedure is used to set the exam result for a student in an internship in the StudentInternship
table.
The procedure takes three arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@internship_id
— AnINT
type input which represents the ID of the internship. -
@exam_result
— AnINT
type input which represents the result of the exam.
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;
EXEC register_internship_exam_result 17, 23, 5
This procedure is used to log the attendance of a student in a meeting in the StudentMeetingAttendance
table.
The procedure takes two arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@meeting_id
— AnINT
type input which represents the ID of the meeting.
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;
EXEC register_meeting_attendance 17, 23
This procedure is used to add a new parameter to the Parameter
table.
The procedure takes three arguments:
-
@name
— AnNVARCHAR(50)
type input which represents the name of the parameter. -
@value
— AnNVARCHAR(50)
type input which represents the value of the parameter. -
@date
— ADATE
type input which represents the date of the parameter. This is an optional argument and defaults to the current timestamp.
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;
EXEC add_parameter @name = 'test', @value = 'oldValue', @date = '2021-01-01'
EXEC add_parameter @name = 'test', @value = 'newValue'
This procedure is used to create a new basket in the Basket
table.
The procedure takes one argument:
-
@student_id
— AnINT
type input which represents the ID of the student.
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;
EXEC create_basket 17
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.
The procedure takes five arguments:
-
@student_id
— AnINT
type input which represents the ID of the student. -
@course_id
— AnINT
type input which represents the ID of the course. This is an optional argument. -
@meeting_id
— AnINT
type input which represents the ID of the meeting. This is an optional argument. -
@studies_id
— AnINT
type input which represents the ID of the studies. This is an optional argument. -
@webinar_id
— AnINT
type input which represents the ID of the webinar. This is an optional argument.
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;
EXEC add_item_to_basket 17, @course_id = 3
This procedure is used to remove an item from a basket in the BasketItem
table.
The procedure takes five arguments:
-
@basket_id
— AnINT
type input which represents the ID of the basket. -
@course_id
— AnINT
type input which represents the ID of the course. This is an optional argument. -
@meeting_id
— AnINT
type input which represents the ID of the meeting. This is an optional argument. -
@studies_id
— AnINT
type input which represents the ID of the studies. This is an optional argument. -
@webinar_id
— AnINT
type input which represents the ID of the webinar. This is an optional argument.
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;
EXEC remove_item_from_basket 17, @course_id = 3
This procedure is used to update the state of a basket in the Basket
table to 'failed_payment'.
The procedure takes one argument:
-
@basket_id
— AnINT
type input which represents the ID of the basket.
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;
EXEC register_failed_payment 35
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.
The procedure takes one argument:
-
@basket_id
— AnINT
type input which represents the ID of the basket.
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;
EXEC register_successful_payment 35