Question

SQL: CREATE STATEMENT

1. For patients whose last name matches the name entered by the user, display their first name, phone number, and doctor's name. Note: Try Davis.

(TEXT FOR DATABASE BELOW)

DROP TABLE PATIENT;
DROP TABLE BILLING;
DROP TABLE DOCTOR;

CREATE TABLE DOCTOR(
DOC_ID VARCHAR2(10) NOT NULL,
DOC_NAME VARCHAR2(20),
DATEHIRED DATE,
SALPERMON NUMBER(8),
AREA VARCHAR2(20),
SUPERVISOR_ID NUMBER(8),
CHGPERAPPT NUMBER(8),
ANNUAL_BONUS NUMBER(8),
PRIMARY KEY (DOC_ID)
);

INSERT INTO DOCTOR VALUES('432', 'Harrison', to_date('05-DEC-1994','dd-mon-yyyy'), 12000, 'Pediatrics', 100, 75, 4500);
INSERT INTO DOCTOR VALUES('509', 'Vester', to_date('09-JAN-2002','dd-mon-yyyy'), 8100, 'Pediatrics', 432, 40, null);
INSERT INTO DOCTOR VALUES('389', 'Lewis', to_date('21-JAN-1996','dd-mon-yyyy'), 10000, 'Pediatrics', 432, 40, 2250);
INSERT INTO DOCTOR VALUES('504', 'Cotner', to_date('16-JUN-1998','dd-mon-yyyy'), 11500, 'Neurology', 289, 85, 7500);
INSERT INTO DOCTOR VALUES('235', 'Smith', to_date('22-JUN-1998','dd-mon-yyyy'), 4550, 'Family Practice', 100, 25, 2250);
INSERT INTO DOCTOR VALUES('356', 'James', to_date('01-AUG-1998','dd-mon-yyyy'), 7950, 'Neurology', 289, 80, 6500);
INSERT INTO DOCTOR VALUES('558', 'James', to_date('02-MAY-1995','dd-mon-yyyy'), 9800, 'Orthopedics', 876, 85, 7700);
INSERT INTO DOCTOR VALUES('876', 'Robertson', to_date('02-MAR-1995','dd-mon-yyyy'), 10500, 'Orthopedics', 100, 90, 8900);
INSERT INTO DOCTOR VALUES('889', 'Thompson', to_date('18-MAR-1997','dd-mon-yyyy'), 6500, 'Rehab', 100, 65, 3200);
INSERT INTO DOCTOR VALUES('239', 'Pronger', to_date('18-DEC-1999','dd-mon-yyyy'), 3500, 'Rehab', 889, 40, null);
INSERT INTO DOCTOR VALUES('289', 'Borque', to_date('30-JUN-1989','dd-mon-yyyy'), 16500, 'Neurology', 100, 95, 6500);
INSERT INTO DOCTOR VALUES('100', 'Stevenson', to_date('30-JUN-1979','dd-mon-yyyy'), 23500, 'Director', null, null, null);

CREATE TABLE PATIENT(
PT_ID VARCHAR2(10) NOT NULL,
PT_LNAME VARCHAR2(20),
PT_FNAME VARCHAR2(20),
PTDOB DATE,
DOC_ID VARCHAR2(10),
NEXTAPPTD DATE,
LASTAPPTD DATE,
PRIMARY KEY (PT_ID),
CONSTRAINT DOCTORID FOREIGN KEY (DOC_ID) REFERENCES DOCTOR(DOC_ID)
);

INSERT INTO PATIENT VALUES
('168', 'James','Paul', to_date('14-MAR-1997','dd-mon-yyyy'), '432', to_date('01-JUL-2003','dd-mon-yyyy'), to_date('01-JUN-2003','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('331', 'Anderson', 'Brian', to_date('31-MAR-1948','dd-mon-yyyy'), '235', to_date('01-JUL-2003','dd-mon-yyyy'), to_date('01-JUN-2003','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('313', 'James', 'Scott', to_date('26-MAR-1933','dd-mon-yyyy'), '235', to_date('20-JUL-2003','dd-mon-yyyy'), to_date('20-JUN-2003','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('816', 'Smith', 'Jason', to_date('12-DEC-1999','dd-mon-yyyy'), '509', to_date('15-NOV-2003','dd-mon-yyyy'), to_date('15-MAY-2003','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('314', 'Porter', 'Susan', to_date('14-NOV-1967','dd-mon-yyyy'), '235', to_date('01-OCT-2003','dd-mon-yyyy'), to_date('01-MAR-2003','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('315', 'Saillez', 'Debbie', to_date('09-SEP-1955','dd-mon-yyyy'), '235', to_date('01-JUL-2003','dd-mon-yyyy'), to_date('01-JUN-2003','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('719', 'Rogers', 'Anthony', to_date('01-JAN-1942','dd-mon-yyyy'), '504', to_date('01-NOV-2003','dd-mon-yyyy'), to_date('01-JAN-2003','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('264', 'Walters', 'Stephanie', to_date('26-JAN-1945','dd-mon-yyyy'), '504', to_date('12-DEC-2003','dd-mon-yyyy'), to_date('12-DEC-2002','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('267', 'Westra', 'Lynn', to_date('12-JUL-1957','dd-mon-yyyy'), '235', to_date('02-FEB-2004','dd-mon-yyyy'), to_date('02-FEB-2003','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('103', 'Poole', 'Jennifer', to_date('13-MAY-2002','dd-mon-yyyy'), '389', to_date('01-DEC-2003','dd-mon-yyyy'), to_date('01-JUN-2003','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('108', 'Baily', 'Ryan', to_date('25-DEC-1977','dd-mon-yyyy'), '235', to_date('06-JUN-2005','dd-mon-yyyy'), to_date('06-JUN-2003','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('943', 'Crow', 'Lewis', to_date('10-NOV-1949','dd-mon-yyyy'), '235', to_date('01-JUL-2005','dd-mon-yyyy'), to_date('01-MAR-2002','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('847', 'Cochran', 'John', to_date('28-MAR-1948','dd-mon-yyyy'), '356', to_date('02-DEC-2005','dd-mon-yyyy'), to_date('01-JAN-2002','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('163', 'Roach', 'Becky', to_date('08-SEP-1975','dd-mon-yyyy'), '235', to_date('01-DEC-2005','dd-mon-yyyy'), to_date('01-JAN-2002','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('504', 'Jackson', 'John', to_date('08-NOV-1943','dd-mon-yyyy'), '235', to_date('21-JUL-2003','dd-mon-yyyy'), to_date('10-NOV-2002','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('809', 'Kowalczyk', 'Paul', to_date('12-NOV-1951','dd-mon-yyyy'), '558', to_date('29-JUL-2003','dd-mon-yyyy'), to_date('19-JUN-2003','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('703', 'Davis', 'Linda', to_date('17-JUL-2002','dd-mon-yyyy'), '509', to_date('21-JUL-2003','dd-mon-yyyy'), to_date('22-May-2003','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('307', 'Jones', 'J.C.', to_date('17-JUL-2002','dd-mon-yyyy'), '509', to_date('21-JUL-2003','dd-mon-yyyy'), to_date('22-May-2003','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('439', 'Wright', 'Chasity', to_date('23-APR-1973','dd-mon-yyyy'), '235', null, null);
INSERT INTO PATIENT VALUES
('696', 'Vanderchuck', 'Keith', to_date('08-AUG-1968','dd-mon-yyyy'), '504', null, to_date('15-JUN-2003','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('966', 'Mcginnis', 'Allen', to_date('03-MAY-1959','dd-mon-yyyy'), '504', null, to_date('15-JUN-2003','dd-mon-yyyy'));
INSERT INTO PATIENT VALUES
('669', 'Sakic', 'Joe', to_date('16-SEP-1976','dd-mon-yyyy'), '504', null, to_date('15-JUN-2003','dd-mon-yyyy'));

CREATE TABLE BILLING(
PT_ID VARCHAR2(20),
BALANCE NUMBER(8),
DUEDATE DATE,
PHONE VARCHAR2(10),
ADDR VARCHAR2(30),
CITY VARCHAR2(20),
ST VARCHAR2(2),
ZIP VARCHAR2(5),
PT_INS VARCHAR2(20),
PRIMARY KEY (PT_ID)
);

INSERT INTO BILLING VALUES('168', 15650, to_date('21-AUG-2003','dd-mon-yyyy'), '833-9569', '128 W. APPLE #4', 'Jonesboro', 'IL', '62952', 'SIH');

INSERT INTO BILLING VALUES('331', 300, to_date('09-SEP-2003','dd-mon-yyyy'), '833-5587', '3434 Mulberry St.', 'Anna', 'IL', '62906', 'BCBS');

INSERT INTO BILLING VALUES('313', 0, to_date('01-JAN-2004','dd-mon-yyyy'), '893-9987', '334 Tailgate Ln', 'COBDEN', 'IL', '62920', 'Military');

INSERT INTO BILLING VALUES('816', 0, to_date('01-JAN-2004','dd-mon-yyyy'), '833-6654', '8814 W. Apple', 'JONESBORO', 'IL', '62952', 'SIH');

INSERT INTO BILLING VALUES('314', 100, to_date('31-MAR-2003','dd-mon-yyyy'), '457-6658', '445 Oak St.', 'Carbondale', 'IL', '62901', 'BCBS');

INSERT INTO BILLING VALUES('264', 35000, to_date('11-JAN-2003','dd-mon-yyyy'), '942-8065', '8898 Bighill Driver', 'HERRIN', 'IL', '62948', 'MediSupplA');

INSERT INTO BILLING VALUES('103', 4500, to_date('01-JUL-2003','dd-mon-yyyy'), '833-5547', '298 Murphy School Rd', 'Anna', 'IL', '62906', 'HealthCare');

INSERT INTO BILLING VALUES('108', 0, to_date('01-JAN-2005','dd-mon-yyyy'), '833-5542', '334 Pansie Hill Rd.', 'JONESBORO', 'IL', '62952', 'HealthCare');

INSERT INTO BILLING VALUES('943', 0, to_date('01-JAN-2007','dd-mon-yyyy'), '529-9963', '456 E. Grand #14', 'Carbondale', 'IL', '62901', 'Military');

INSERT INTO BILLING VALUES('847', 98000, to_date('31-JAN-2002','dd-mon-yyyy'), '549-8854', '6543 W. Parkview Ln.', 'Carbondale', 'IL', '62901', 'BCBS');

INSERT INTO BILLING VALUES('504', 0, to_date('01-JAN-2003','dd-mon-yyyy'), '549-6139', '6657 N. Allen', 'Carbondale', 'IL', '62901', 'QualityCare');

INSERT INTO BILLING VALUES('809', 450, to_date('19-JUL-2003','dd-mon-yyyy'), '687-8852', '3345 Hwy 127 N.', 'Murphysboro', 'IL', '62966', 'QualityCare');

INSERT INTO BILLING VALUES('703', 225, to_date('31-AUG-2003','dd-mon-yyyy'), '529-8332', '909 N. Brown St.', 'Carbondale', 'IL', '62901', 'HealthCare');

INSERT INTO BILLING VALUES('696', 79850, to_date('15-JUL-2003','dd-mon-yyyy'), '549-7231', '5546 W. James', 'Carbondale', 'IL', '62901', 'BCBS');

INSERT INTO BILLING VALUES('966', 98700, to_date('15-JUL-2003','dd-mon-yyyy'), '833-5375', '9009 Taylor Ave.', 'Anna', 'IL', '62906', 'BCBS');

INSERT INTO BILLING VALUES('267', 0, to_date('01-JAN-2005','dd-mon-yyyy'), '942-3321', '6755 US Route 148', 'HERRIN', 'IL', '62948', 'QualityCare');

INSERT INTO BILLING VALUES('307', 450, to_date('31-AUG-2003','dd-mon-yyyy'), '457-6967', '234 N. Allen', 'Carbondale', 'IL', '62901', 'HealthCare');

INSERT INTO BILLING VALUES('719', 0, to_date('01-JAN-2004','dd-mon-yyyy'), '549-7848', '867 Henderson St.', 'Carbondale', 'IL', '62901', 'HealthCare');

INSERT INTO BILLING VALUES('439', 500, to_date('31-AUG-2003','dd-mon-yyyy'), '833-5541', '4456 N. Springer', 'Anna', 'IL', '62906', 'QualityCare');

INSERT INTO BILLING VALUES('315', 1500, to_date('14-SEP-2003','dd-mon-yyyy'), '833-6272', '404 Williford Rd.', 'JONESBORO', 'IL', '62952', 'HealthCare');

INSERT INTO BILLING VALUES('163', 0, to_date('01-JAN-2004','dd-mon-yyyy'), '833-2133', '129 Fountain St.', 'Anna', 'IL', '62906', 'HealthCare');

INSERT INTO BILLING VALUES('669', 128450, to_date('15-JUL-2003','dd-mon-yyyy'), '833-6654', '353 Tin Bender Rd.', 'Jonesboro', 'IL', '62952', 'BCBS');

SELECT * FROM PATIENT;
SELECT * FROM BILLING;
SELECT * FROM DOCTOR;

Answer 1

select
        PATIENT.PT_FNAME, BILLING.PHONE, DOCTOR.DOC_NAME
from
        PATIENT, BILLING, DOCTOR
where
        DOCTOR.DOC_ID = PATIENT.DOC_ID
        and PATIENT.PT_ID = BILLING.PT_ID
        and PATIENT.PT_LNAME = 'Davis'