1 / 41

Hospital Database

Hospital Database. PROF. Dr. Sherif Kassem Adnan Al- kuwaiti 208008705 Redha al- mulla 209011421. Referential Integrity Constraints. doctors. Department. PATIENTSANDSUPERVISORS. PHARMACY. PHARMACISTS. PATIENTS. Relational Database. Relational Database.

kirsten-horn
Télécharger la présentation

Hospital Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Hospital Database PROF. Dr. SherifKassem Adnan Al-kuwaiti 208008705 Redha al-mulla 209011421

  2. Referential Integrity Constraints doctors Department PATIENTSANDSUPERVISORS PHARMACY PHARMACISTS PATIENTS

  3. Relational Database

  4. Relational Database

  5. Relational Database

  6. Relational Database

  7. 2/ For male patient , list the name of the patient ,the name of doctor is supervisor him, No. department supervised him, and salary. PATIENTS_ MALEP_NUMBER(σ sex=‘m’ (PATIENTS)) P_ M _PS (PATIENTS_ MALEP_NUMBER=PA_NUMBERPATIENTSANDSUPERVISORS) Result  P_NAME,DEP_NO,F_NAME,SALARY (P_ M _PS * DOCTORS)

  8. 2/ For male patient , list the name of the patient ,the name of doctor is supervisor him, No. department supervised him and, salary. SELECT P_NAME, DEP_NO, F_NAME , SALARY FROM PATIENTS, PATIENTSANDSUPERVISORS, DOCTORS WHERE PA_NUMBER=P_NUMBER AND SUPER_NUMBER=SSN AND PATIENTS.SEX='M';

  9. 2A/ For male patient , list the name of the patient ,the name of doctor is supervisor him, No. department supervised him and, salary. SELECT P_NAME, DEP_NO, F_NAME , SALARY  FROM ((PATIENTS JOIN PATIENTSANDSUPERVISORS ON PA_NUMBER=P_NUMBER) JOIN DOCTORS ON SUPER_NUMBER=SSN)  WHERE PATIENTS.SEX='M';

  10. Female_DOCssn(σ sex=‘f’ (DOCTORS)) • F_E_Dep (Female-DOCSSN=MGRSSN Department) • F_P  PA_NUMBER(F-E-Dep * PATIENTSANDSUPERVISORS) • Tclinc-Dep  NUM_DEPARTMENT(σ N_DEPARTMENT='TeethClinic‘ (DEPARTMENT)) • T_D_PSuper PA_NUMBER (Tclinc-Dep * PATIENTSANDSUPERVISORS) • Result ( F_P  T_D_Psuper ) 4/ Retrieve numbers all patients who are either supervised by the department managed by Female ,or supervised by the department is ‘Teeth Clinic’.

  11. 4/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, DEPARTMENT, DOCTORS WHERE DEP_NO = NUM_DEPARTMENT AND MGRSSN=SSN AND SEX='f') Union  ( SELECT DISTINCT PA_NUMBER FROM PATIENTSANDSUPERVISORS, DEPARTMENT WHERE DEP_NO = NUM_DEPARTMENT AND N_DEPARTMENT='TeethClinic');

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

  13. Q9/ Select all PATIENT NUMBER SELECT P_NUMBER FROM PATIENTS;

  14. 10/ Find all combinations of PATIENT P_NUMBER and DEPARTMENT N_DEPARTMENT. SELECT P_NUMBER,N_DEPARTMENT FROM PATIENTS, DEPARTMENT ;

  15. q10A/ RETRIEVE ALL EMPLOYEE WHO WORK IN ALL DEPARTMENT SELECT * FROM Doctors,DEPARTMENT ;

  16. Q13 /SHOW THE RESULTING SALARY IF EVERY doctor WORKING IN DEPARTMENT 1 GIVEN 5 PERCENT SELECT F_NAME,L_NAME ,1.05*SALARY AS INCREASED_SAL FROM doctors , DEPARTMENT WHERE DNO =NUM_DEPARTMENT AND N_DEPARTMENT ='SURGERYCLINIC';

  17. 14/Retrieve all male patients whose room number is between 300 and 400 . SELECT * FROM PATIENTS WHERE (ROOM_NUMBER BETWEEN 300 AND 400)AND SEX='M';

  18. 6/ Retrieve the names of doctors who don’t supervise any patient. SSN_D  SSN(DOCTORS) SUP_NUM (SSN) SUPER_NUMBER (PATIENTSANDSUPERVISORS) WITHOUT_SUPER _SSN (SSN_D - SUP_NUM) RESULTF_NAME,L_NAME (WITHOUT_SUPER _SSN *EMPLOYEE)

  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)

  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 ;

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

  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);

  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;

  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);

  26. q1 / Retrieve the name and salary of all doctorswho work for the teeth clinic. SELECT F_NAME, L_NAME, SALARY FROM DOCTORS,DEPARTMENT WHERE N_DEPARTMENT='TEETHCLINIC' AND NUM_DEPARTMENT=DNO;

  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;

  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_DEPARTMENT;

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

  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;

  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;

  32. Q 11 / Retrieve all the address of every doctor SELECT All address FROM doctors ;

  33. Q 11A / Retrieve all distinct the kind of job of every employee SELECT DISTINCT address FROM doctors;

  34. Q12/ Retrieve all pharmacists whose address is in hofof. SELECT no_pharm FROM PHARMACISTS WHERE ADDRESS LIKE '%hofof%';

  35. Q12A/ Find the name of all PATIENTS who Enter in the hospital In 2008 . SELECTP_NAME FROMPATIENTS WHERE ENTER_DATE LIKE ‘_ _0 8 _ _ _ _ _ _’ ;

  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;

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

  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;

  39. 8b/ Find all patients and the pharmacist who sold him. SELECT distinct pa_number,No_pharmacist FROM ( PATIENTSANDSUPERVISORS left JOIN PHARMACY on no_treat= no_treatment);

  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;

  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);

More Related