use master;
IF DB_ID('db_Polyclinic') IS NOT NULL
Begin
alter database db_Polyclinic set single_user with rollback immediate;
drop database db_Polyclinic;
End;
go
IF DB_ID('db_Polyclinic') IS NULL
create database db_Polyclinic;
go
use db_Polyclinic;
create table Speciality(
Id int primary key not null IDENTITY,
Name NVARCHAR(45) not null
);
create table Doctor(
Id int primary key not null IDENTITY,
Name NVARCHAR(20) not null,
LastName NVARCHAR(20) not null,
SpecialityId int not null,
CONSTRAINT FK_Doctor_Speciality FOREIGN KEY (SpecialityId) REFERENCES Speciality (Id)
);
create table Patient(
Id int primary key not null IDENTITY,
Name NVARCHAR(20) not null,
LastName NVARCHAR(20) not null
);
create table Appointment(
Id int primary key not null IDENTITY,
DoctorId int not null,
PatientId int not null,
AppointmentDate Date Default GetDate() null,
CONSTRAINT FK_Appointment_Doctor FOREIGN KEY (DoctorId) REFERENCES Doctor (Id),
CONSTRAINT FK_Appointment_Patient FOREIGN KEY (PatientId) REFERENCES Patient (Id)
);
create table WishesSpeciality(
Id int not null IDENTITY,
PatientId int not null,
SpecialityId int not null,
Whish NVARCHAR(255) not null
CONSTRAINT FK_WishesSpeciality_Speciality FOREIGN KEY (SpecialityId) REFERENCES Speciality (Id),
CONSTRAINT FK_WishesSpeciality_Patient FOREIGN KEY (PatientId) REFERENCES Patient (Id)
)
insert Speciality Values(N'Аллерголог'), (N'Дерматолог'), (N'Диетолог'), (N'Вирусолог');
insert Doctor Values(N'Александр', N'Дзидзария', 1), (N'Олег', N'Прокофьев', 2), (N'Василий', N'Смышляев', 3);
insert Patient Values(N'Петя', N'Соловьев'), (N'Света', N'Фролова');
insert WishesSpeciality Values (1,1, N'Хотелось бы попасть к опытному Аллергологу...'), (2,3, N'Еще не придумала'), (1,4, N'Вирусологи лучшие!');
Select Top (1000) * From Speciality;
Select Top (1000) Doctor.Id, Doctor.Name, LastName, Speciality.Name as 'Speciality' From Doctor inner join Speciality on SpecialityId = Speciality.Id;
Select Top (1000) Appointment.Id, Doctor.Name + ' ' + Doctor.LastName as 'Doctor', Speciality.Name as 'Doctor Speciality' , Patient.Name + ' ' + Patient.LastName as 'Patient', AppointmentDate From Appointment
inner join Doctor on DoctorId = Doctor.Id inner join Patient on PatientId = Patient.Id
inner join Speciality on Doctor.SpecialityId = Speciality.Id;
Select Top (1000) * From Patient;
Select Top (1000) WishesSpeciality.Id, Patient.Name + ' ' + Patient.LastName as 'Patient', Speciality.Name as 'Speciality', WishesSpeciality.Whish from WishesSpeciality
inner Join Speciality on SpecialityId = Speciality.Id
inner Join Patient on PatientId = Patient.Id;