---

-- Вставка данных в таблицу Specialties
INSERT INTO Specialties (Name, Vakansii) VALUES ('АСОИУ', 90);
INSERT INTO Specialties (Name, Vakansii) VALUES ('БУАА', 50);
INSERT INTO Specialties (Name, Vakansii) VALUES ('ЮР', 25);

-- Вставка данных в таблицу Applicants
INSERT INTO Applicants (LastName, FirstName, MiddleName, Status, City) VALUES ('Иванов', 'Петр', 'Сергеевич', 'не зачислен', 'Ханты-Мансийск');
INSERT INTO Applicants (LastName, FirstName, MiddleName, Status, City) VALUES ('Попова', 'Людмила', 'Валентиновна', 'не зачислен', 'Сургут');
INSERT INTO Applicants (LastName, FirstName, MiddleName, Status, City) VALUES ('Сыроежкин', 'Сергей', 'Павлович', 'зачислен', 'Ханты-Мансийск');
INSERT INTO Applicants (LastName, FirstName, MiddleName, Status, City) VALUES ('Шариков', 'Егор', 'Андреевич', 'зачислен', 'Пыть-Ях');
INSERT INTO Applicants (LastName, FirstName, MiddleName, Status, City) VALUES ('Каренина', 'Анна', 'Васильевна', 'забрал документы', 'Ханты-Мансийск');
INSERT INTO Applicants (LastName, FirstName, MiddleName, Status, City) VALUES ('Трубецкой', 'Петр', 'Фомич', 'не зачислен', 'Сургут');
INSERT INTO Applicants (LastName, FirstName, MiddleName, Status, City) VALUES ('Романова', 'Анастасия', 'Юрьевна', 'не зачислен', 'Ханты-Мансийск');

-- Вставка данных в таблицу Applications
INSERT INTO Applications (ApplicantID, SpecialtyID, Status)
SELECT ApplicantID, IDspecialties, Status
FROM Applicants, Specialties
WHERE Name = 'АСОИУ';

INSERT INTO Applications (ApplicantID, SpecialtyID, Status)
SELECT TOP 5 ApplicantID, IDspecialties, Status
FROM Applicants, Specialties
WHERE Name = 'БУАА';

INSERT INTO Applications (ApplicantID, SpecialtyID, Status)
SELECT TOP 6 ApplicantID, IDspecialties, Status
FROM Applicants, Specialties
WHERE Name = 'ЮР';

-- Обновление статусов в таблице Applications
UPDATE z
SET z.Status = 'не зачислен'
FROM Applications z
JOIN Applicants a ON a.ApplicantID = z.ApplicantID
JOIN Specialties s ON z.SpecialtyID = s.IDspecialties
WHERE a.LastName = 'Сыроежкин' 
  AND s.Name IN ('БУАА', 'ЮР');

UPDATE z
SET z.Status = 'не зачислен'
FROM Applications z
JOIN Applicants a ON a.ApplicantID = z.ApplicantID
JOIN Specialties s ON z.SpecialtyID = s.IDspecialties
WHERE a.LastName = 'Шариков' 
  AND s.Name IN ('АСОИУ', 'ЮР');

-- Вставка данных в таблицу Exams
INSERT INTO Exams (SpecialtyID, DisciplineID, Date, PassingScore)
SELECT IDspecialties, discipline_id, '2012-07-17', 20
FROM Disciplines, Specialties
WHERE Disciplines.name = 'математика';

INSERT INTO Exams (SpecialtyID, DisciplineID, Date, PassingScore)
SELECT IDspecialties, discipline_id, '2012-07-21', 25
FROM Disciplines, Specialties
WHERE Disciplines.name = 'русский язык';

INSERT INTO Exams (SpecialtyID, DisciplineID, Date, PassingScore)
SELECT IDspecialties, discipline_id, '2012-07-25', 25
FROM Disciplines, Specialties
WHERE Disciplines.name = 'история россии';

-- Вставка данных в таблицу Grades
INSERT INTO Grades (ApplicantID, ExamID, Grade)
SELECT ApplicantID, ExamID, 
       CAST(100 * RAND(ApplicantID * ExamID * DATEPART(ms, GETDATE())) AS INT)
FROM Applicants, Exams;