lab6.sql - master - arseyfullin/lab6

lab6.sql
37 строк3 KB
arseyfullin
upload files
13 дек 2025, 13:40
13 дек 2025, 13:405fc0b19
-- 1. �������� ���� ������ � ������
CREATE DATABASE UDB;
GO
USE UDB;
GO
CREATE TABLE Groups (GroupID INT PRIMARY KEY IDENTITY(1,1), GroupName VARCHAR(50) NOT NULL);
GO
CREATE TABLE Students (StudentID INT PRIMARY KEY IDENTITY(1,1), FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, GroupID INT FOREIGN KEY REFERENCES Groups(GroupID));
GO
CREATE TABLE Conferences (ConferenceID INT PRIMARY KEY IDENTITY(1,1), ConferenceName VARCHAR(100) NOT NULL, ConferenceDate DATE);
GO
CREATE TABLE Articles (ArticleID INT PRIMARY KEY IDENTITY(1,1), ArticleTitle VARCHAR(200) NOT NULL, ArticleTopic VARCHAR(100), StudentID INT FOREIGN KEY REFERENCES Students(StudentID), ConferenceID INT FOREIGN KEY REFERENCES Conferences(ConferenceID), PublicationDate DATE);
GO

-- 2. ���������� �������� ������
INSERT INTO Groups (GroupName) VALUES ('���-101'), ('���-102'), ('���-201'), ('���-202');
GO
INSERT INTO Students (FirstName, LastName, GroupID) VALUES ('����', '������', 1), ('����', '������', 1), ('�����', '��������', 2), ('�������', '��������', 3), ('����', '��������', 2);
GO
INSERT INTO Conferences (ConferenceName, ConferenceDate) VALUES ('������� ����� 2024', '2024-03-15'), ('IT-���������', '2024-04-20'), ('������� ������', '2024-05-10');
GO
INSERT INTO Articles (ArticleTitle, ArticleTopic, StudentID, ConferenceID, PublicationDate) VALUES ('��������� ���� � ������� ������', '������������� ���������', 1, 1, '2024-03-01'), ('��������� ����������', '��������� ������', 1, 2, '2024-04-15'), ('���������� ��������� ����������', '����������������', 3, 1, '2024-02-20'), ('������� ������ � ��������', 'Data Science', 4, 3, NULL), ('����������������� ������', '�������������� ������������', 2, NULL, '2024-01-10');
GO

-- 3. �������� �������������
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;
GO
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;
GO

-- 4. ������������ �������������
-- ����� ��������� � �������� ������������
SELECT * FROM StudentResearch WHERE ArticleTitle IS NOT NULL;
GO
-- ���������� �� ������� (���������� �� ���������� ������)
SELECT * FROM DepartmentResearchReport ORDER BY TotalArticles DESC;
GO