פתרון לתרגילים: (מקווה שזה יצא מסודר זה עם ג'יבריש)
-- 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'