lab4.sql - master - arseyfullin/lab4

lab4.sql
106 строк4 KB
arseyfullin
upload files
06 дек 2025, 13:07
06 дек 2025, 13:0779ae98b
CREATE DATABASE SRDB;
GO

USE SRDB;
GO

-- 1. �������� ������� Groups ������ (��� ������� ������)
CREATE TABLE Groups (
    GroupID INT PRIMARY KEY IDENTITY(1,1),
    GroupName VARCHAR(50) NOT NULL,
    Faculty VARCHAR(100)
);
GO

-- 2. �������� ������� Students
CREATE TABLE Students (
    StudentID INT PRIMARY KEY IDENTITY(1,1),
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    GroupID INT,
    Email VARCHAR(100),
    CONSTRAINT FK_Students_Groups FOREIGN KEY (GroupID) REFERENCES Groups(GroupID)
);
GO

-- 3. ���������� ������� Groups
INSERT INTO Groups (GroupName, Faculty) VALUES
('����-01-23', '����������� � �������������� �������'),
('����-02-23', '�������������� ������������'),
('����-03-23', '����������� ���������');
GO

-- 4. ���������� ������� Students
INSERT INTO Students (FirstName, LastName, GroupID, Email) VALUES
('����', '������', 1, 'ivan.petrov@edu.ru'),
('�����', '��������', 2, 'maria.sidorova@edu.ru'),
('�������', '������', 1, 'alexey.ivanov@edu.ru'),
('�����', '�������', 3, 'elena.kozlova@edu.ru');
GO

-- 5. �������� ������� Conferences
CREATE TABLE Conferences (
    ConferenceID INT PRIMARY KEY IDENTITY(1,1),
    ConferenceName VARCHAR(200) NOT NULL,
    ConferenceDate DATE,
    Location VARCHAR(100),
    Organizer VARCHAR(100)
);
GO

-- 6. �������� ������� Articles
CREATE TABLE Articles (
    ArticleID INT PRIMARY KEY IDENTITY(1,1),
    StudentID INT NOT NULL,
    ArticleTitle VARCHAR(300) NOT NULL,
    ArticleTopic VARCHAR(100),
    PublicationDate DATE,
    ConferenceID INT,
    CONSTRAINT FK_Articles_Students FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    CONSTRAINT FK_Articles_Conferences FOREIGN KEY (ConferenceID) REFERENCES Conferences(ConferenceID)
);
GO

-- 7. ���������� ������� Conferences
INSERT INTO Conferences (ConferenceName, ConferenceDate, Location, Organizer) VALUES 
('������������� ������� ����������� �� �����������', '2024-04-15', '������', '���'),
('����������� ������� ������', '2024-05-20', '�����-���������', '�����'),
('������������� ����������� �� AI', '2023-11-10', '������', '���');
GO

-- 8. ���������� ������� Articles
INSERT INTO Articles (StudentID, ArticleTitle, ArticleTopic, PublicationDate, ConferenceID) VALUES 
(1, '������������ ���������� ��������� ��������', '������������� ���������', '2024-03-10', 1),
(2, '������ ������ � �������� ��������', '�����������������', '2024-04-01', 1),
(1, '������ ����������� ��� ������', '���������� �������', '2024-02-15', 2),
(3, '���������� ��������� ����������', '����������������', '2023-10-20', 3),
(1, '��������� ���� � ������������ ������', '������������� ���������', '2024-01-25', 1);
GO

-- 9.1. �������� � ����������� ������
SELECT s.StudentID, s.FirstName, s.LastName, COUNT(a.ArticleID) as ArticlesCount
FROM Students s 
JOIN Articles a ON s.StudentID = a.StudentID
GROUP BY s.StudentID, s.FirstName, s.LastName
ORDER BY ArticlesCount DESC;

-- 9.2. ������ � ������������� � ��������
SELECT s.FirstName, s.LastName, 
       a.ArticleTitle, 
       a.ArticleTopic,
       c.ConferenceName, 
       c.ConferenceDate,
       c.Location
FROM Articles a
JOIN Students s ON a.StudentID = s.StudentID
JOIN Conferences c ON a.ConferenceID = c.ConferenceID
ORDER BY c.ConferenceDate DESC, s.LastName;

-- 9.3. ��������, ��������������� � 2024 ����
SELECT DISTINCT s.StudentID, s.FirstName, s.LastName, g.GroupName
FROM Students s
JOIN Articles a ON s.StudentID = a.StudentID  
JOIN Conferences c ON a.ConferenceID = c.ConferenceID
JOIN Groups g ON s.GroupID = g.GroupID
WHERE YEAR(c.ConferenceDate) = 2024
ORDER BY s.LastName;