פתרון לתרגילים: (מקווה שזה יצא מסודר זה עם ג'יבריש)
-- Created by AVIAD COHEN, ID:032554875 --
--Q1 - 1. ���� ������� ���� ������� ������ 01/01/2010.
select First_Name,Last_Name
from personal,shifts
where personal.ID=shifts.Employee_id and shifts.Date='01/01/2010'
--Q2 - 2. ���� ������� ������� (�"�, �� ���� , �� �����) ���� ������� ������ 01/01/2010 ����� ����� ���� 13:00.
select ID,First_Name,Last_Name
from personal,shifts
where personal.ID=shifts.Employee_id and shifts.Date='01/01/2010' and shifts.Start_time='13:00'
--Q3 - 3. ���� �������� ����� ������� ������ ��� ������ 01/01/2010.
select First_Name,Last_Name,lab_name
from Patients,Labs,Patinets_Labs
where Patients.Patient_id=Patinets_Labs.Patient_ID and Labs.Lab_ID=Patinets_Labs.Lab_ID and Lab_date='01/01/2010'
--Q4 - 4. ���� ����� ��������� �� ����� "���", "�����".
select Specialty
from Personal_specialization,Personal
where Personal_Specialization.ID=Personal.ID and Personal.First_Name='Dana' and Personal.Last_Name='Biton'
--Q5 - 5. ���� ���� ����� (�"�, �� ����, �� �����) ��� �� ��� ���� ��������.
select Personal.ID,First_Name,Last_Name
from Personal,Personal_Specialization
where Personal.ID=Personal_Specialization.ID
group By Personal.ID,Personal.First_Name,Personal.Last_Name
having COUNT(*) >= ALL ( select COUNT(*)
from Personal_Specialization
group by ID )
--Q6 - 6. ���� �������� (�"�, �� ����, �� �����) ����� ��� ������ ��� �� �� ����� �������.
select Patients.Patient_ID,Patients.First_Name,Patients.Last_Name
from Patients,Patinets_Labs
where patients.Patient_ID=Patinets_Labs.Patient_id
except ( select Patients.Patient_ID,Patients.First_Name,Patients.Last_Name
from Patients,Patinets_Treatments
where patients.Patient_ID=Patinets_Treatments.Patient_ID )
--Q7 - 7. ���� ���� ����� (�"�, �� ����, �� �����) ����� ���� ���� ������� (�����)
select Personal.ID, Personal.First_Name, Personal.Last_Name
from Personal, Patinets_Treatments
where Personal.ID=Patinets_Treatments.Personal_id
group by Personal.ID, Personal.First_Name, Personal.Last_Name
having COUNT(distinct Patient_ID)>=all(select COUNT(distinct Patient_ID)
from Patinets_Treatments
group by Personal_id)
--Q8 - 8. ���� ������ (�"�, �� ����, �� �����) ����� ��� ���� ������� (�����)
select Patients.Patient_ID,First_Name,Last_Name
from Patients,Patinets_Treatments
where patients.Patient_ID=Patinets_Treatments.Patient_ID
group by Patients.Patient_ID,First_Name,Last_Name
having COUNT(distinct Treatment_code) >= ALL ( select COUNT(distinct Treatment_code)
from Patinets_Treatments
group by Patient_ID )
--Q9 - 9. ���� ���� �������� (�"�, �� ����, �� �����) ������ ���� ������ ������� �����.
select Patients.Patient_ID,First_Name,Last_Name
from Patients,dbo.Patinets_Treatments
where Patients.Patient_ID=Patinets_Treatments.Patient_ID
group By patients.Patient_ID,First_Name,Last_Name
having COUNT(*) >= 3
--Q10 - 10. ���� ���� �������� (�"�, �� ����, �� �����) ��� ����� �� �����.
select Patients.Patient_ID, Patients.First_Name,Patients.Last_Name
from Patients
except (
select Patients.Patient_ID,Patients.First_Name,Patients.Last_Name
from Patinets_Treatments,Patients
where patients.Patient_ID=Patinets_Treatments.Patient_ID
)
--Q11 - 11. ���� ���� �������� (�"�, �� ����, �� �����) ������ �� ���� ����� ����� ����� ��"� ��� ��� 999999999.
SELECT Patients.Patient_ID, First_Name, Last_Name
FROM Patients
WHERE Patient_ID <> '999999999' and NOT EXISTS (
( SELECT DISTINCT Treatment_code
FROM Patinets_Treatments
WHERE Patient_ID = '999999999' )
EXCEPT
( SELECT DISTINCT Treatment_code
FROM Patinets_Treatments
WHERE Patients.Patient_ID = Patinets_Treatments.Patient_ID ))
------------------------------ DONT CHECK BELOW ----------------------------------------------------
select distinct Patients.Patient_ID, Patients.First_Name,Patients.Last_Name
from Patients,Patinets_Treatments
where patients.Patient_ID=Patinets_Treatments.Patient_ID and patients.Patient_ID<>'999999999' and Treatment_code in (select Treatment_code
from Patinets_Treatments
--Q12 - 12. ���� �������� ��� ��� ��� ������� ������ ����. (�� ������ ��� ���� �� ������� �����)
select Patients.Patient_ID, Patients.First_Name,Patients.Last_Name
from Patients,Patinets_Treatments
where patients.Patient_ID=Patinets_Treatments.Patient_ID
except ( select Patients.Patient_ID, Patients.First_Name,Patients.Last_Name
from Patients,Patinets_Progress
where patients.Patient_ID=Patinets_Progress.Patient_ID)
--Q13 - 13. ������ ������ ��� �������� ���� �����. (����� ��� ���� ����� ����� �� �������� �����)
select Patients.Patient_ID, Patients.First_Name,Patients.Last_Name
from Patients,Patinets_Progress
where Patients.Patient_ID=Patinets_Progress.Patient_ID
group By Patients.Patient_ID, Patients.First_Name,Patients.Last_Name
having COUNT(*) >= ALL ( select COUNT(*)
from Patinets_Progress
group by Patient_ID )
--Q14 - ����� ����� ���� ����� ������� ��������. (����� �������)
insert into Patinets_Treatments
values ('777777777','333','444444444','15/8/2011','17/8/2011')
--Q15 - ����� ����� ���� ����� ������� �������� (����� �������)
insert into Patinets_Progress
values ('888888888','555','333333333','kaka','awesome','15/8/11')
--Q16 - ����� �� ������ �������� ����� ������ ��.�. ��� ��� 777777777.
delete from Patinets_Treatments
where Patient_ID='777777777'
--Q17 - ����� ����� �� ���� ������ ����� ��� '222222222' �-'Phd' .
update Personal
set Degree='Phd'
where ID='222222222'