12
4A/Retrieve numbers all patients who are either supervised by the department managed by Female, or supervised by the department Teeth Clinic. SELECT DISTINCT PA_NUMBER FROM PATIENTSANDSUPERVISORS WHERE PA_NUMBER IN (SELECT PA_NUMBER FROM PATIENTSANDSUPERVISORS, DEPARTMENT, DOCTORS WHERE DEP_NO = NUM_DEPARTMENT AND MGRSSN=SSN AND DOCTORS.SEX='F') OR PA_NUMBER IN (SELECT PA_NUMBER FROM PATIENTSANDSUPERVISORS, DEPARTMENT WHERE DEP_NO = NUM_DEPARTMENT AND N_DEPARTMENT='TEETHCLINIC'); PA_NUMBER 13 48 56 90

19
6/ Retrieve the names of doctors who don’t supervise any patient. SELECT F_NAME,L_NAME FROM doctors WHERE NOT EXISTS (SELECT * FROM PATIENTSANDSUPERVISORS WHERE SSN=SUPER_NUMBER) L_NAMEF_NAME SalemShref

20
20/ Find the sum of the salaries of all doctors who supervise a patient at least work in the department number 9, as well as the maximum salary, the minimum salary, and the average salary. SELECT SUM (SALARY) AS SUM, MAX (SALARY)AS MAX, MIN (SALARY) AS MIN, AVG (SALARY) AS AVG FROM (DOCTORS JOIN PATIENTSANDSUPERVISORS ON SSN=SUPER_NUMBER) WHERE DNO=9 ; AVGMINMAXSUM 13300130001360026600

21
22/ Retrieve The Number Of Patients Supervised By Doctors From Esoteric Clinic Department. SELECT COUNT(*) AS COUNT_DOCTORS FROM PATIENTSANDSUPERVISORS,DOCTORS,DEPARTMENT WHERE SUPER_NUMBER=SSN AND DNO=NUM_DEPARTMENT AND N_DEPARTMENT='EsotericClinic'; COUNT_DOCTORS 2

22
18/ Find all the names of DOCTORS who do not have address SELECT F_NAME, L_NAME FROM EMPLOYEE WHERE ADDRESS IS NULL;

23
16/ Retrieve SSN of Doctors who supervise the patients have the same sex. SELECT SSN FROM DOCTORS WHERE SSN IN ( SELECT SUPER_NUMBER FROM PATIENTSANDSUPERVISORS, PATIENTS AS P, DOCTORS AS D WHERE PA_NUMBER=P_NUMBER AND SUPER_NUMBER=SSN AND D.SEX=P.SEX); SSN 101 203 761

24
16A/ Retrieve SSN of Doctors who supervise the patients have the same sex. SELECT SSN FROM PATIENTSANDSUPERVISORS, PATIENTS AS P, DOCTORS AS D WHERE PA_NUMBER=P_NUMBER AND SUPER_NUMBER=SSN AND D.SEX=P.SEX; SSN 101 203 761

25
16B/ Retrieve SSN of Doctors who supervise the patients have the same sex. SELECT SSN FROM DOCTORS AS D WHERE EXISTS ( SELECT * FROM PATIENTSANDSUPERVISORS, PATIENTS AS P WHERE PA_NUMBER=P_NUMBER AND SUPER_NUMBER=SSN AND D.SEX=P.SEX); SSN 101 203 761

26
doctors q1 / Retrieve the name and salary of all doctors who work for the teeth clinic. SELECT F_NAME, L_NAME, SALARY FROM DOCTORS,DEPARTMENT WHERE N_DEPARTMENT='TEETHCLINIC' AND NUM_DEPARTMENT=DNO; SALARYL_NAMEF_NAME 11500AhmedSalman 17000ShrefFaten 7500MohamdKaled

27
Q1B /rewrite query 1 assuming that doctors relation the F_Name attribute is called Name and the DNO attribute is called NUM_DEPARTMENT and the N_DEPARTMENT attribute in Department relation is called Name. SELECT NAME, L_NAME, SALARY FROM DOCTORS,DEPARTMENT WHERE DOCTORS.NAME='TEETHCLINIC' AND DOCTORS.NUM_DEPARTMENT= DEPARTMENT.NUM_DEPARTMENT; SALARYL_NAMENAME 11500AhmedSalman 17000ShrefFaten 7500MohamdKaled

28
Q1B /rewrite query 1 assuming that doctors relation the F_Name attribute is called Name and the DNO attribute is called NUM_DEPARTMENT and the N_DEPARTMENT attribute in Department relation is called Name. SELECT E.NAME, L_NAME, SALARY FROM DOCTORS AS E,DEPARTMENT AS D WHERE D.NAME='TEETHCLINIC' AND E.NUM_DEPARTMENT=D.NUM_DEPARTM ENT; SALARYL_NAMENAME 11500AhmedSalman 17000ShrefFaten 7500MohamdKaled

29
Q1C/ Retrieve all ATTRIBUTE of the doctor(s) who work in department 5. SELECT * FROM DOCTORS WHERE DNO=5; SALARYDNOADDRESSSEXSSNL_NAMEF_NAME 115005HufufM367AhmedSalman 170005DammamF430ShrefFaten

30
Q1D/ Retrieve all attribute of the employee and all attribute of the department who work for the teeth clinic. SELECT * FROM DOCTORS,DEPARTMENT WHERE N_DEPARTMENT='TEETHCLINIC' AND NUM_DEPARTMENT=DNO; MGRSSNNUM_DEPARTMENTN_DEPARTMENTSALARYDNOADDRESSSEXSSNL_NAMEF_NAME 3675TeethClinic115005HufufM367AhmedSalman 3675TeethClinic170005DammamF430ShrefFaten

31
q0/ Retrieve the sex and patient number of patient whose name is ‘Ali’ and who is in room 102. SELECT SEX,P_NUMBER FROM PATIENTS WHERE P_NAME='ALI' AND ROOM_NUMBER=102; P_NUMBERSEX 13M

35
Q12A/ Find the name of all PATIENTS who Enter in the hospital In 2008. SELECT P_NAME FROM PATIENTS WHERE ENTER_DATE LIKE ‘_ _0 8 _ _ _ _ _ _’ ; P_NAME Ali Saad

36
Q24/ For each kind of job, retrieve the name kind of job, the number of employees For each kind of job, and their average salary. SELECT address, COUNT (*), AVG (SALARY) FROM doctors GROUP BY address; AVG (SALARY)(COUNT (*)address 148333Dammam 112502Hufuf 133002Riyadh

37
3/ Retrieve the name of the doctor who supervise all patients who have kind of sick 'REMOVE TOOTH' SELECT F_NAME,L_NAME FROM DOCTORS WHERE( (SELECT PA_NUMBER FROM PATIENTSANDSUPERVISORS WHERE SUPER_NUMBER=SSN) CONTAINS (SELECT P_NUMBER FROM PATIENTS WHERE KIND_OF_SICK='REMOVE TOOTH') ); L_NAMEF_NAME ShrefFaten

38
28/ For each Doctor supervise on more than one patient, Find the doctors number who are making more than 4000 and the number of their patients who supervise them. SELECT SSN, COUNT (*) FROM DOCTORS, PATIENTSANDSUPERVISORS WHERE SSN=SUPER_NUMBER AND SALARY>4000 AND SUPER_NUMBER IN (SELECT SUPER_NUMBER FROM PATIENTSANDSUPERVISORS GROUP BY SUPER_NUMBER HAVING COUNT (*) > 1) GROUP BY SSN; (COUNT (*)SSN 2430

40
Q 15/ Retrieve a list of department and doctor who works in it, ordered alphabetically by last name and first name. SELECT N_DEPARTMENT,L_NAME,F_NAME FROM DEPARTMENT,DOCTORS WHERE DNO =NUM_DEPARTMENT ORDER BY L_NAME, F_NAME; F_NAMEL_NAMEN_DEPARTMENT SmeraAbdulaSurgeryClinic AliAhmedEsotericClinic SalmanAhmedTeethClinic TamerHosneEsotericClinic NoorMoatySurgeryClinic ShrefSalemEsotericClinic FatenShrefTeethClinic

41
Q7 /LIST THE NAMES OF MANAGERS WHO HAVE AT LEAST ONE PATIENTS SELECT F_NAME,L_NAME FROM DOCTORS WHERE EXISTS (SELECT * FROM PATIENTSANDSUPERVISORS WHERE SUPER_NUMBER=SSN ) AND EXISTS (SELECT * FROM DEPARTMENT WHERE MGRSSN=SSN); L_NAMEF_NAME MoatyNoor AhmedSalman