---

Го, вся практика 2–5 части, без CONSTRAINT (где не критично), для базы `HospitalGavrilov`.

## Часть 2. Новые таблицы

**Задание 5** — Patients:
```sql
CREATE TABLE Patients (
    Id           INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name         NVARCHAR(100) NOT NULL,
    Surname      NVARCHAR(100) NOT NULL,
    Phone        CHAR(10) NULL,
    BirthDate    DATE NOT NULL,
    DepartmentId INT NULL FOREIGN KEY REFERENCES Departments(Id)
);
```

**Задание 6** — PatientDiseases:
```sql
CREATE TABLE PatientDiseases (
    Id            INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    PatientId     INT NOT NULL FOREIGN KEY REFERENCES Patients(Id),
    DiseaseId     INT NOT NULL FOREIGN KEY REFERENCES Diseases(Id),
    DiagnosisDate DATE NOT NULL
);
```

**Задание 7** — Rooms:
```sql
CREATE TABLE Rooms (
    Id           INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Number       INT NOT NULL UNIQUE,
    Building     INT NOT NULL CHECK (Building >= 1 AND Building <= 5),
    Capacity     INT NOT NULL DEFAULT 1 CHECK (Capacity >= 1),
    DepartmentId INT NOT NULL FOREIGN KEY REFERENCES Departments(Id)
);
```

**Задание 8** — PatientRooms:
```sql
CREATE TABLE PatientRooms (
    Id            INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    PatientId     INT NOT NULL FOREIGN KEY REFERENCES Patients(Id),
    RoomId        INT NOT NULL FOREIGN KEY REFERENCES Rooms(Id),
    AdmissionDate DATE NOT NULL,
    DischargeDate DATE NULL
);
```

## Часть 3. Наполнение данными

**Задание 9:**
```sql
INSERT INTO Departments (Building, Financing, Name) VALUES (3, 15000, N'Неврология');
INSERT INTO Departments (Building, Financing, Name) VALUES (5, 22000, N'Кардиология');
```

**Задание 10:**
```sql
INSERT INTO Diseases (Name, Severity) VALUES (N'Грипп', 1);
INSERT INTO Diseases (Name, Severity) VALUES (N'Пневмония', 3);
INSERT INTO Diseases (Name, Severity) VALUES (N'Мигрень', 2);
```

**Задание 11:**
```sql
INSERT INTO Doctors (Name, Surname, Phone, Salary, DepartmentId)
VALUES (N'Иван', N'Петров', '1234567890', 25000, (SELECT Id FROM Departments WHERE Name = N'Неврология'));

INSERT INTO Doctors (Name, Surname, Phone, Salary, DepartmentId)
VALUES (N'Анна', N'Сидорова', '0987654321', 30000, (SELECT Id FROM Departments WHERE Name = N'Кардиология'));
```

**Задание 12:**
```sql
INSERT INTO Patients (Name, Surname, Phone, BirthDate, DepartmentId)
VALUES (N'Сергей', N'Иванов', '1112223334', '1985-04-12', (SELECT Id FROM Departments WHERE Name = N'Неврология'));

INSERT INTO Patients (Name, Surname, Phone, BirthDate, DepartmentId)
VALUES (N'Ольга', N'Кузнецова', '2223334445', '1990-08-23', (SELECT Id FROM Departments WHERE Name = N'Кардиология'));

INSERT INTO Patients (Name, Surname, Phone, BirthDate, DepartmentId)
VALUES (N'Дмитрий', N'Смирнов', NULL, '1978-01-05', (SELECT Id FROM Departments WHERE Name = N'Неврология'));
```

**Задание 13:**
```sql
INSERT INTO Rooms (Number, Building, Capacity, DepartmentId)
VALUES (101, 3, 2, (SELECT Id FROM Departments WHERE Name = N'Неврология'));

INSERT INTO Rooms (Number, Building, Capacity, DepartmentId)
VALUES (102, 3, 1, (SELECT Id FROM Departments WHERE Name = N'Неврология'));

INSERT INTO Rooms (Number, Building, Capacity, DepartmentId)
VALUES (201, 5, 3, (SELECT Id FROM Departments WHERE Name = N'Кардиология'));
```

**Задание 14:**
```sql
INSERT INTO PatientDiseases (PatientId, DiseaseId, DiagnosisDate)
VALUES (
    (SELECT Id FROM Patients WHERE Name = N'Сергей' AND Surname = N'Иванов'),
    (SELECT Id FROM Diseases WHERE Name = N'Мигрень'),
    '2024-02-10'
);

INSERT INTO PatientDiseases (PatientId, DiseaseId, DiagnosisDate)
VALUES (
    (SELECT Id FROM Patients WHERE Name = N'Ольга' AND Surname = N'Кузнецова'),
    (SELECT Id FROM Diseases WHERE Name = N'Грипп'),
    '2024-03-05'
);

INSERT INTO PatientDiseases (PatientId, DiseaseId, DiagnosisDate)
VALUES (
    (SELECT Id FROM Patients WHERE Name = N'Дмитрий' AND Surname = N'Смирнов'),
    (SELECT Id FROM Diseases WHERE Name = N'Пневмония'),
    '2024-03-12'
);
```

**Задание 15:**
```sql
INSERT INTO PatientRooms (PatientId, RoomId, AdmissionDate, DischargeDate)
VALUES (
    (SELECT Id FROM Patients WHERE Name = N'Сергей' AND Surname = N'Иванов'),
    (SELECT Id FROM Rooms WHERE Number = 101),
    '2024-02-10', '2024-02-20'
);

INSERT INTO PatientRooms (PatientId, RoomId, AdmissionDate, DischargeDate)
VALUES (
    (SELECT Id FROM Patients WHERE Name = N'Ольга' AND Surname = N'Кузнецова'),
    (SELECT Id FROM Rooms WHERE Number = 201),
    '2024-03-05', NULL
);

INSERT INTO PatientRooms (PatientId, RoomId, AdmissionDate, DischargeDate)
VALUES (
    (SELECT Id FROM Patients WHERE Name = N'Дмитрий' AND Surname = N'Смирнов'),
    (SELECT Id FROM Rooms WHERE Number = 102),
    '2024-03-12', '2024-03-25'
);
```

## Часть 4. Простые SELECT-запросы

**16:**
```sql
SELECT * FROM Departments WHERE Financing > 20000;
```

**17:**
```sql
SELECT Name, Severity FROM Diseases WHERE Severity > 1;
```

**18:**
```sql
SELECT Name, Surname, Salary FROM Doctors WHERE Salary >= 20000 AND Salary <= 30000;
```

**19:**
```sql
SELECT Name FROM Examinations WHERE DayOfWeek = 1 OR DayOfWeek = 2;
```

**20:**
```sql
SELECT Name, Surname FROM Doctors WHERE Phone IS NULL;
```

**21:**
```sql
SELECT Name FROM Departments WHERE Building = 1 OR Building = 2 OR Building = 3;
```

**22:**
```sql
SELECT Name FROM Diseases WHERE Name LIKE N'П%';
```

**23:**
```sql
SELECT Name, Surname, BirthDate FROM Patients WHERE BirthDate > '1980-01-01';
```

**24:**
```sql
SELECT Number, Capacity FROM Rooms WHERE Capacity > 1 AND Building = 3;
```

**25:**
```sql
SELECT Name, StartTime, EndTime FROM Examinations WHERE StartTime > '12:00';
```

**26:**
```sql
SELECT Name, Surname, Salary FROM Doctors ORDER BY Salary DESC;
```

**27:**
```sql
SELECT Name, Financing FROM Departments ORDER BY Name ASC;
```

**28:**
```sql
SELECT Patients.Name, Patients.Surname, PatientDiseases.DiagnosisDate
FROM Patients, PatientDiseases
WHERE Patients.Id = PatientDiseases.PatientId
  AND PatientDiseases.DiagnosisDate >= '2024-03-01'
  AND PatientDiseases.DiagnosisDate <= '2024-03-31';
```

**29:**
```sql
SELECT Diseases.Name, PatientDiseases.DiagnosisDate
FROM Patients, PatientDiseases, Diseases
WHERE Patients.Id = PatientDiseases.PatientId
  AND PatientDiseases.DiseaseId = Diseases.Id
  AND Patients.Surname = N'Иванов';
```

**30:**
```sql
SELECT d.Name, d.Surname, dep.Name
FROM Doctors d, Departments dep
WHERE d.DepartmentId = dep.Id
  AND d.Salary > 20000;
```

## Часть 5. Повышенная сложность

**31:**
```sql
SELECT p.Name, p.Surname, r.Number
FROM Patients p, PatientRooms pr, Rooms r
WHERE p.Id = pr.PatientId
  AND pr.RoomId = r.Id
  AND pr.DischargeDate IS NULL;
```

**32:**
```sql
SELECT dep.Name, r.Number
FROM Departments dep, Rooms r
WHERE dep.Id = r.DepartmentId;
```

**33:**
```sql
SELECT doc.Name, doc.Surname, e.Name
FROM Doctors doc, Examinations e
WHERE doc.Id = e.DoctorId
  AND doc.Salary > 25000;
```

**34:**
```sql
SELECT p.Name, p.Surname, dis.Name, dis.Severity
FROM Patients p, PatientDiseases pd, Diseases dis
WHERE p.Id = pd.PatientId
  AND pd.DiseaseId = dis.Id
  AND dis.Severity > 1;
```

**35:**
```sql
SELECT dep.Name, doc.Name, doc.Surname, e.Name
FROM Departments dep, Doctors doc, Examinations e
WHERE dep.Id = doc.DepartmentId
  AND doc.Id = e.DoctorId
  AND dep.Name = N'Неврология';
```

Важно: задания 33 и 35 сработают только если у тебя есть строки в Examinations с заполненным `DoctorId` — в исходных данных задания 9–15 это не заполняется явно, так что для проверки 33/35 сначала сделай `UPDATE Examinations SET DoctorId = ... WHERE Id = ...` вручную (или это отдельное упущение задания).