Views - halotukozak/PBD GitHub Wiki

webinar_financial_report

CREATE VIEW webinar_financial_report AS
SELECT Webinar.id, CAST(SUM(Webinar.price) / 100.0 AS DECIMAL(10, 2)) AS income
FROM Webinar
         INNER JOIN StudentWebinar on Webinar.id = StudentWebinar.webinar_id
GROUP BY Webinar.id;

This view provides a financial report for each webinar. It calculates the total income generated from each webinar. The income is calculated by summing up the prices of all the webinars that students have registered for, and it’s represented as a decimal value.

course_financial_report

CREATE VIEW course_financial_report AS
SELECT Course.id,
       CAST((dbo.course_full_income(Course.id) + dbo.course_advance_income(Course.id)) /
            100.0 AS DECIMAL(10, 2)) AS income
FROM Course;

This view provides a financial report for each course. It calculates the total income generated from each course. The income is calculated by summing up the full income and advance income of all the courses, and it’s represented as a decimal value.

studies_financial_report

CREATE VIEW studies_financial_report AS
SELECT Studies.id,
       CAST(((dbo.studies_registration_income(Studies.id) + SUM(dbo.semester_income(Semester.id))) /
             100.0) AS DECIMAL(10, 2)) AS income
FROM Studies
         INNER JOIN Semester ON Studies.id = Semester.studies_id
GROUP BY Studies.id;

This view provides a financial report for each study program. It calculates the total income generated from each study program. The income is calculated by adding the income from study program registrations and the income from all semesters of the study program. The result is represented as a decimal value.

students_who_purchased_meeting

CREATE VIEW students_who_purchased_meeting AS
SELECT Student.id, Student.first_name, Student.last_name, COUNT(*) AS purchased_meetings
FROM StudentMeeting
         INNER JOIN Student ON StudentMeeting.student_id = Student.id
GROUP BY Student.id, Student.first_name, Student.last_name;

This view lists all students who have purchased single meetings. For each student, it provides their ID, first name, last name, and the total number of meetings they have purchased.

debtor_list

CREATE VIEW debtor_list AS
SELECT DISTINCT TOP 100 PERCENT Student.id, Student.first_name, Student.last_name
FROM Student
         INNER JOIN StudentCourse ON Student.id = StudentCourse.student_id AND StudentCourse.full_payment_date IS NULL
ORDER BY Student.id;

This view generates a list of students who have outstanding payments for their courses. It provides the ID, first name, and last name of each student who has not yet made a full payment for a course. The students are listed in order of their ID.

future_studies_students

CREATE VIEW future_studies_students AS
SELECT TOP 100 PERCENT Studies.id AS studies, Students.id, Students.first_name, Students.last_name
FROM Studies
         CROSS APPLY dbo.students_enrolled_on_studies(Studies.id) AS students
WHERE dbo.studies_start_date(Studies.id) > CURRENT_TIMESTAMP
ORDER BY Studies.id, Students.id;

This view lists the students who are enrolled in study programs that will start in the future. For each study program, it provides the ID of the study program and the ID, first name, and last name of each student enrolled in it. The study programs and students are listed in order of their IDs.

future_courses_students

CREATE VIEW future_courses_students AS
SELECT TOP 100 PERCENT Course.id AS course, Students.id, Students.first_name, Students.last_name
FROM Course
         CROSS APPLY dbo.students_enrolled_on_course(Course.id) AS Students
WHERE dbo.course_start_date(Course.id) > CURRENT_TIMESTAMP
ORDER BY Course.id, Students.id;

This view lists the students who are enrolled in courses that will start in the future. For each course, it provides the ID of the course and the ID, first name, and last name of each student enrolled in it. The courses and students are listed in order of their IDs.

future_webinars_students

CREATE VIEW future_webinars_students AS
SELECT TOP 100 PERCENT Webinar.id AS webinar, Students.id, Students.first_name, Students.last_name
FROM Webinar
         CROSS APPLY dbo.students_enrolled_on_webinar(Webinar.id) AS Students
WHERE Webinar.datetime > CURRENT_TIMESTAMP
ORDER BY Webinar.id, Students.id;

This view lists the students who are enrolled in webinars that will occur in the future. For each webinar, it provides the ID of the webinar and the ID, first name, and last name of each student enrolled in it. The webinars and students are listed in order of their IDs.

future_meetings_students

CREATE VIEW future_meetings_students AS
SELECT TOP 100 PERCENT Meeting.id as meeting, Meeting.type, Students.id, Students.first_name, Students.last_name
FROM Meeting
         CROSS APPLY (SELECT Student.id, Student.first_name, Student.last_name
                      FROM Student
                               INNER JOIN StudentMeeting ON Student.id = StudentMeeting.student_id
                      WHERE StudentMeeting.meeting_id = Meeting.id) AS students
WHERE Meeting.datetime > CURRENT_TIMESTAMP
ORDER BY Meeting.id, Students.id;

This view lists the students who are enrolled in meetings that will occur in the future. For each meeting, it provides the ID and type of the meeting, and the ID, first name, and last name of each student enrolled in it. The meetings and students are listed in order of their IDs.

attendance_on_meetings

CREATE VIEW attendance_on_meetings AS
SELECT TOP 100 PERCENT Meeting.id,
                       (SELECT COUNT(*) FROM dbo.students_enrolled_on_meeting(Meeting.id)) AS enrolled_students,
                       (SELECT COUNT(*) FROM dbo.students_present_on_meeting(Meeting.id))  AS present_students
FROM Meeting
WHERE Meeting.datetime < CURRENT_TIMESTAMP
ORDER BY Meeting.id;

This view provides an attendance report for each past meeting. For each meeting, it provides the ID of the meeting, the number of students enrolled in the meeting, and the number of students who were present at the meeting. The meetings are listed in order of their IDs.

student_bilocation_list

CREATE VIEW student_bilocation_list AS
SELECT TOP 100 PERCENT id, first_name, last_name
FROM Student
WHERE dbo.student_overlapping_meetings(id) = 1
ORDER BY id;
GO

This view lists the students who have overlapping meetings. For each student, it provides the ID, first name, and last name. The students are listed in order of their IDs.

teacher_bilocation_list

CREATE VIEW teacher_bilocation_list AS
SELECT TOP 100 PERCENT id, first_name, last_name
FROM Teacher
WHERE dbo.teacher_overlapping_meetings(id) = 1
ORDER BY id;
GO

This view lists the teachers who have overlapping meetings. For each teacher, it provides the ID, first name, and last name. The teachers are listed in order of their IDs.

translator_bilocation_list

CREATE VIEW translator_bilocation_list AS
SELECT TOP 100 PERCENT id, first_name, last_name
FROM Translator
WHERE dbo.translator_overlapping_meetings(id) = 1
ORDER BY id;
GO

This view lists the translators who have overlapping meetings. For each translator, it provides the ID, first name, and last name. The translators are listed in order of their IDs.

master_list

CREATE VIEW master_list AS
SELECT Student.id, Student.first_name, Student.last_name, Studies.id, Studies.title
FROM Student
         INNER JOIN StudentStudies ON StudentStudies.student_id = Student.id AND
                                      StudentStudies.certificate_post_date IS NOT NULL
         INNER JOIN Studies ON Studies.id = StudentStudies.studies_id;
GO

This view lists the students who have completed their studies. For each student, it provides the ID, first name, and last name of the student, and the ID and title of the studies they have completed.

graduates_without_diploma

CREATE VIEW graduates_without_diploma AS
SELECT id, first_name, last_name
FROM Student
         INNER JOIN StudentStudies ON Student.id = StudentStudies.student_id AND
                                      StudentStudies.credit_date IS NOT NULL AND
                                      StudentStudies.certificate_post_date IS NULL

UNION

SELECT id, first_name, last_name
FROM Student
         INNER JOIN StudentCourse ON Student.id = StudentCourse.student_id AND
                                     StudentCourse.credit_date IS NOT NULL AND
                                     StudentCourse.certificate_post_date IS NULL;
GO

This view lists the students who have completed their studies or courses but have not yet received their certificates. For each student, it provides the ID, first name, and last name.

room_bilocation_list

CREATE VIEW room_bilocation_list AS
SELECT TOP 100 PERCENT id, building, number
FROM Room
WHERE dbo.room_overlapping_meetings(id) = 1;
GO

This view lists the rooms that have overlapping meetings. For each room, it provides the ID, building, and number.

pending_payments

CREATE VIEW pending_payments
AS
SELECT Basket.id,
       CAST(SUM(dbo.basket_item_price(BasketItem.course_id, BasketItem.meeting_id,
                                 BasketItem.studies_id,
                                 BasketItem.webinar_id)) / 100.0 AS DECIMAL(10, 2)) AS price
FROM Basket
         INNER JOIN BasketItem ON BasketItem.basket_id = Basket.id
WHERE state = 'pending'
GROUP BY Basket.id;
GO

This view lists the baskets that have pending payments. For each basket, it provides the ID and the total price of the items in the basket, calculated using the basket_item_price function.