PATIENT table:
PATIENT_ID NUMBER NOT NULL,Primary Key
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(25) NOT NULL
DOB DATE
INS_CODE NUMBER
PHYSICIAN table:
PHYSICIAN_ID NUMBER NOT NULL,Primary Key
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(25) NOT NULL
LICENSE_NO NUMBER(7) NOT NULL
HIRE_DATE DATE
ADMISSION table:
PATIENT_ID NUMBER NOT NULL,Primary Key,References PATIENT_ID column of the PATIENT table
PHYSICIAN_ID NUMBER NOT NULL,Primary Key,References PHYSICIAN_ID column of the PHYSICIAN table
Which SQL statement will produce a list of all patients who have more than one physician?
(A) SELECT p.patient_id FROM patient p WHERE p.patient_id IN (SELECT patient_id FROM admission GROUP BY patient_id HAVING COUNT(*) > 1);
(B) SELECT DISTINCT a.patient_id FROM admission a, admission a2 WHERE a.patient_id = a2.patient_id AND a.physician_id <> a2.physician_id;
(C) SELECT patient_id FROM admission WHERE COUNT(physician_id) > 1;
(D) SELECT patient_id FROM patient FULL OUTER JOIN physician;
这题的A错在哪?想了很久
------解决方案--------------------
A没有错啊...从语法角度看...
------解决方案--------------------
a没错吧