מבוא לSQL ותרגילי חזרה.

פתרון לתרגילים: (מקווה שזה יצא מסודר זה עם ג'יבריש)

-- 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'