/
Alterego94
/
DB
Обзор
Документация
Войти
/
Alterego94
/
DB
Код
Запросы
0
Задачи
Вики
Пакеты
0
Релизы
0
CI/CD
Аналитика
Безопасность
master
lab_6/Scripts.sql
32 строки
1 KB
Alterego94
create lab_6/Scripts.sql
30 ноя 2025, 17:26
30 ноя 2025, 17:26
d080611
Код
Авторство
О чём код?
CREATE VIEW "StudentResearch" AS SELECT s.FirstName || ' ' || s.LastName as StudentName, g.GroupName, a.ArticleTitle, a.ArticleTopic, c.ConferenceName, c.ConferenceDate, CASE WHEN a.PublicationDate IS NOT NULL THEN 'Опубликована' ELSE 'В работе' END as Status FROM "Students" s JOIN "Groups" g ON s.GroupID = g.GroupID LEFT JOIN "Articles" a ON s.StudentID = a.StudentID LEFT JOIN "Conferences" c ON a.ConferenceID = c.ConferenceID; CREATE VIEW "DepartmentResearchReport" AS SELECT g.GroupName, COUNT(DISTINCT s.StudentID) as TotalStudents, COUNT(a.ArticleID) as TotalArticles, COUNT(DISTINCT c.ConferenceID) as ConferencesCount FROM "Groups" g LEFT JOIN "Students" s ON g.GroupID = s.GroupID LEFT JOIN "Articles" a ON s.StudentID = a.StudentID LEFT JOIN "Conferences" c ON a.ConferenceID = c.ConferenceID GROUP BY g.GroupName; SELECT * FROM "StudentResearch" WHERE ArticleTitle IS NOT NULL; SELECT * FROM "DepartmentResearchReport" ORDER BY TotalArticles DESC;