База Поликлиника SQL

USE master;
GO
DROP DATABASE Поликлиника;
GO
 
CREATE DATABASE Поликлиника
 
Go
-- Удаление таблицы
DROP TABLE patcient
GO
-- Удаление таблицы
DROP TABLE disstributor
GO
-- Удаление таблицы
DROP TABLE treatrment
GO
create table patcient  -- Создание таблицы patient, где храниться информация пациентах
(
patcient_id int not null primary key,      -- ID пациента 
patcient_name varchar(50) not null,        -- имя пациента 
adress varchar(50) not null,              -- адрес пациента 
marital_status varchar(50) not null,      -- социальный статус пациента 
sem_pol varchar(50) not null,             -- семейное положение
year_of_birth  int not null,              -- год рождения пациента 
);
GO
create table disstributor   -- Создание таблицы distributor , где храниться информация о лечении, назначении пациента
(
doctor_id int not null primary key,       -- ID доктора
doctor_name varchar(50) not null,         -- имя доктора 
adress varchar(50) not null,              -- адрес доктора 
qualification varchar(50) not null,       -- квалификация доктора
specialization varchar(50) not null,      -- специализация доктора 
);
GO
 
create table treatrment   -- Создание таблицы treatrment, где храниться информация о лечении, назначении пациента  
(
patcient_id int not null primary key,                                   -- ID пациента 
doctor_id int not null references disstributor(doctor_id),             -- ID доктора 
treatrment_name varchar(50) not null,                                  -- название лечения
start_date varchar(50) not null,                                       -- дата начала лечения 
period int not null,                                                   -- длительность лечения            
);
GO
 
insert into patcient values (1,'Иванов Иван Иванович','Минск','Студент','холост','2005');
insert into patcient values (2,'Петров Петр Петрович','Гомель','Инжинер','холост','2000');
insert into patcient values (3,'Сидоров Петр Иванович','Осиповичи','Врач','женат','1995');
insert into patcient values (4,'Ионов Николай Иванович','Пинск','Рабочий','женат','2001');
insert into patcient values (5,'Николаев Иван Петрович','Гродно','Механик','женат','2005');
insert into patcient values (6,'Петров Семен Петрович','Гомель','Инжинер','холост','2008');
insert into patcient values (7,'Петров Семен Петрович','Гомель','Инжинер','холост','1988');
insert into patcient values (8,'Шкляр Семен Петрович','Столин','Инжинер','холост','1988');
GO
 
 
insert into disstributor values (1,'Иванов Иван Иванович','Минск','Средняя','Невролог');
insert into disstributor values (2,'Крупник Петр Петрович','Брест','Высшая','Терапевт');
insert into disstributor values (3,'Сидоров Петр Иванович','Гродно','Средняя','Травмотолог');
insert into disstributor values (4,'Иванов Николай Иванович','Минск','Высшая','Хирург');
insert into disstributor values (5,'Попов Иван Петрович','Гомель','Высшая','Кардиолог');
insert into disstributor values (6,'Крумкач Кирилл Петрович','Клецк','Средняя','Кардиолог');
insert into disstributor values (7,'Бобко Дминтрий Петрович','Пинск','Средняя','Хирург');
insert into disstributor values (8,'Секач Кирилл Иванович','Клецк','Средняя','Кардиолог');
GO
 
insert into treatrment values (1,2,'Операция','28-05-1995','7');
insert into treatrment values (2,1,'Восполение легких','20-05-2005','15');
insert into treatrment values (3,5,'Герпес','20-03-2005','27');
insert into treatrment values (4,4,'Невроз','20-02-2005','9');
insert into treatrment values (5,3,'Крапивница','20-04-2005','16');
insert into treatrment values (6,3,'Крапивница','20-04-2005','16');
insert into treatrment values (7,3,'Крапивница','20-04-2005','16');
insert into treatrment values (8,3,'Крапивница','20-04-2005','18');
GO
 
--1.Выдать всю имеющуюся информацию о докторах.
 
SELECT * FROM disstributor
GO
 
-- проверка 
SELECT * FROM patcient
GO
 
SELECT * FROM treatrment
GO
--2.Выбрать имена и адрес всех пациентов.
 
SELECT patcient_name [Ф.И.О.], adress [Адрес пациента]  FROM patcient
GO
 
--3.Выбрать имена всех пациентов, проживающих в Минске.
 
SELECT patcient_name [Ф.И.О.], adress [Адрес пациента] FROM patcient WHERE adress='Минск';
GO
 
--4.Выбрать имена всех докторов, работающих в Минске или Гомеле.
 
SELECT doctor_name [Ф.И.О.], adress [Адрес доктора] FROM disstributor WHERE adress='Минск' or adress='Гомель';
GO
 
--5.Выбрать имена всех пациентов, которые женаты и родились в 2005 году.
 
SELECT patcient_name [Ф.И.О.], sem_pol [Семейное положение], year_of_birth [Год рождения]  FROM patcient WHERE sem_pol='женат' and year_of_birth='2005'
GO
 
--6.Выбрать название лечения, у которого период не равен 15 дней.
SELECT treatrment_name [Название лечения], period [Срок лечения] FROM treatrment WHERE period<>'15'
GO
 
--проверка
SELECT * FROM treatrment
GO
 
--7.Выбрать имена всех докторов, которые находятся в Гомеле и имеют специализацию "кардиолог"
 
SELECT doctor_name [Ф.И.О.], adress [Адрес доктора], specialization [Специализация] FROM disstributor WHERE adress='Гомель' and specialization='Кардиолог'
GO
 
--8.Выбрать название лечения, для которых дата начала равна 28-05-1995 и период лечения принадлежит множеству 3. 5, 7. 9.
 
SELECT treatrment_name [Название лечения]  FROM treatrment WHERE start_date='28-05-1995' and period in (3,5,7,9) 
GO
 
--9.Выбрать имена всех докторов, у которых специализация ТРАВМАТОЛОГ, и город проживания принадлежит множеству Гомель , Минск, Гродно.
--1
 
SELECT doctor_name [Ф.И.О.], specialization [Специализация], adress [Адрес доктора] FROM disstributor WHERE specialization='Травмотолог' and adress in ('Гомель', 'Минск', 'Гродно')
GO
 
--2
 
SELECT doctor_name [Ф.И.О.], specialization [Специализация], adress [Адрес доктора] FROM disstributor WHERE specialization='Травмотолог' or adress ='Гомель' and adress='Минск' and adress='Гродно'
GO
 
--10.Вычистить значение выражения 36*55-30 с помощью SQL.
 
SELECT 36*55-30
GO
 
--11.Выбрать имена всех докторов, у город проживания начинается с буквы "К".
 
SELECT doctor_name [Ф.И.О.], adress [Адрес доктора] FROM disstributor WHERE adress LIKE 'К%';
GO
 
--12. Выбрать имена всех пациентов, у которых адрес проживания начинается с буквы “М” и год рождении не заканчивается на 6.
 
SELECT patcient_name [Ф.И.О.], adress [Адрес пациента], year_of_birth [Год рождения] FROM patcient WHERE adress LIKE 'М%' and year_of_birth not like '%6' 
GO
 
 
--13. Вывести все виды лечений, у которых в названии есть слово “операция”.
SELECT treatrment_name [Вид лечения] FROM treatrment WHERE treatrment_name LIKE '%операция%'
GO
 
--14. Вывести специализации врачей без повторений.
SELECT DISTINCT  specialization [Специализация] FROM disstributor
GO
 
--15. Посчитать количество видов лечений, для которых период лечения равен 15 дней.
 
SELECT count (treatrment_name) [Количество видов лечения] FROM treatrment WHERE period=15 
GO
 
--16. Вычислить максимальный период лечения для видов, которые были начаты 20-05-2005.
 
SELECT max (period) [максимальный период лечения] FROM treatrment WHERE start_date = '20-05-2005'
GO
 
--17. Вычислить минимальный год рождения пациентов, проживающих Гомеле.
 
SELECT min (year_of_birth)[Год рождения] FROM patcient  WHERE adress ='Гомель' 
GO
 
--18. Вычислить среднее количество дней, которое уходит на лечение пациентов, год рождения которых 1988.
 
SELECT AVG(period)[Среднее количество дней], year_of_birth [Год рождения]  FROM treatrment e
JOIN patcient s  ON e.patcient_id=s.patcient_id  AND year_of_birth='1988' group by year_of_birth 
GO
 
--19. Вывести наименование лечения, у которого период лечения меньше 10 дней в алфавитном порядке.
 
 SELECT treatrment_name, period FROM treatrment e
 JOIN patcient s  ON e.patcient_id=s.patcient_id WHERE period<10 order by  treatrment_name ASC 
 GO
 
--20. Вычислить среднее количество дней в лечении для каждого из пациентов.
 
SELECT patcient_name [Имя пациента], AVG(period)[Среднее количество дней лечения] FROM treatrment e 
JOIN patcient s  ON e.patcient_id=s.patcient_id    group by patcient_name
GO
 
--21. Вывести все название лечений, у которых средняя продолжительность лечения больше 17 дней.
 
SELECT   treatrment_name, AVG(period) FROM treatrment e 
join patcient s on e.patcient_id=s.patcient_id 
WHERE period>17 group by e.treatrment_name 
Go
 
--22. Вывести все названия лечений, у которых дата начала не ранее 20-03-2005, их в обратном порядке по периоду.
-- не точно
 
SELECT  treatrment_name, start_date FROM treatrment WHERE  start_date CAST(start_date as date) >='20-03-2005'
order by  treatrment_name DESC
GO
 
--23. Вывести список пациентов в виде ‘Пациента’ ['имя пациента'] ‘лечит доктор’ [‘имя доктора’].
--работает  но не точно 
 
SELECT  patcient_name [имя_пациента],  d.doctor_name [имя_доктора] FROM  disstributor  d, patcient s 
JOIN treatrment e on s.patcient_id=e.patcient_id
JOIN disstributor on  e.doctor_id=e.doctor_id
GO
--работает  но не точно 
SELECT  patcient_name [имя_пациента],  doctor_name [имя_доктора] FROM  disstributor  d, patcient s, patcient e
 WHERE  s.patcient_id=e.patcient_id and e.doctor_id=e.doctor_id GROUP BY patcient_name
GO
 
 
--24. Вывести все названия лечений, у которых период равен среднему периоду лечения для пациентов, начавших лечение 20-03-2005.
--25. Вывести названия всех лечений, которые проводят врачи, проживающие в Минске.
--26. Вывести имена всех врачей, у которых количество пациентов более 5.
 
-- НЕ ПРАВИЛЬНО!!!!
 
SELECT doctor_name [имя_доктора], patcient_id, COUNT(*)
    FROM disstributor d
        JOIN patcient e
            ON d.doctor_id = e.patcient_id
                AND patcient_id = 5
    GROUP BY patcient_id
    GO
--27. Вывести имена всех пациентов, которых лечит врач ИВАНОВ.
 
-- Не работает
SELECT   patcient_name FROM patcient s
JOIN treatrment e  on  s.patcient_id=e.patcient_id
JOIN disstributor d  on d.doctor_id=e.doctor_id
WHERE doctor_name ='%ИВАНОВ%' 
Go
 
--28. Вывести имена всех пациентов, которых лечат врачи высшей квалификации.
--29. Вывести имена всех пациентов, назначенное им лечение, а также имена врачей, которых их лечат.
--30. Вывести квалификацию всех врачей, у которых количество пациентов более 4.
--31. Вывести имена пациентов, которые живут в том же городе, что и их лечащий врач.
--32. Вывести имена всех врачей, которые начали лечение 20-04-2005 и период лечения 10 дней.
--33. Вывести название всех городов, в которых живут пациенты, а также названия городов, в которых проживают врачи.
--34. Вывести год рождения пациентов, которых лечат врачи из Минска и Гомеля.
 
SELECT patcient_name, year_of_birth,  FROM patcient s
 
JOIN treatrment e  on  s.patcient_id=e.patcient_id
JOIN disstributor d  on d.doctor_id=e.doctor_id
WHERE d.adress IN ('Минск','Гомель');
 
 
--35. Изменить квалификацию докторов с СРЕДНЯЯ на ВЫСШАЯ.
--36. Увеличить период лечения на 5 дней для пациентов, у которых период лечения равен 7 дням.
--37. Уменьшить дату начала лечения на 7 дней для пациентов, проживающих в Гомеле.
--38. Удалить из таблицы пациентов, которые проживают в городе Осиповичи.
--39. Удалить из таблицы все виды лечений, которые назначали врачи, имеющие специализацию, ХИРУРГ.
--40. Удалить из таблицы всех пациентов, у которых период лечения менее 25 дней, и пациенты проживают в Минске.
--41. Написать SQL запрос, который добавляет врача в таблицу doctor.
 
INSERT INTO disstributor (doctor_id, doctor_name, adress, qualification, specialization)  
VALUES (9, 'Семак Алексей Павлович', 'Солигорск', 'Высшая', 'Стоматолог');
Go
 
--42. Написать SQL запрос, который создает таблицу как результат выполнения команды SELECT.
--43. Написать SQL запросы, которые создавали бы таблицы patient, treatment, doctor.