Run the following script: DROP TABLE PATIENT; DROP TABLE BILLING; DROP TABLE DOC
ID: 3555212 • Letter: R
Question
Run the following script:
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-03','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;
Then you get the following output:
table PATIENT created.
table BILLING created.
table DOCTOR created.
PT_ID PT_LNAME PT_FNAME PTDOB DOC_ID NEXTAPPTD LASTAPPTD
---------- -------------------- -------------------- --------- ---------- --------- ---------
168 James Paul 14-MAR-97 432 01-JUL-03 01-JUN-03
331 Anderson Brian 31-MAR-48 235 01-JUL-03 01-JUN-03
313 James Scott 26-MAR-33 235 20-JUL-03 20-JUN-03
816 Smith Jason 12-DEC-99 509 15-NOV-03 15-MAY-03
314 Porter Susan 14-NOV-67 235 01-OCT-03 01-MAR-03
315 Saillez Debbie 09-SEP-55 235 01-JUL-03 01-JUN-03
719 Rogers Anthony 01-JAN-42 504 01-NOV-03 01-JAN-03
264 Walters Stephanie 26-JAN-45 504 12-DEC-03 12-DEC-02
267 Westra Lynn 12-JUL-57 235 02-FEB-04 02-FEB-03
103 Poole Jennifer 13-MAY-02 389 01-DEC-03 01-JUN-03
108 Baily Ryan 25-DEC-77 235 06-JUN-05 06-JUN-03
943 Crow Lewis 10-NOV-49 235 01-JUL-05 01-MAR-02
847 Cochran John 28-MAR-48 356 02-DEC-05 01-JAN-02
163 Roach Becky 08-SEP-75 235 01-DEC-05 01-JAN-02
504 Jackson John 08-NOV-43 235 21-JUL-03 10-NOV-02
809 Kowalczyk Paul 12-NOV-51 558 29-JUL-03 19-JUN-03
703 Davis Linda 17-JUL-02 509 21-JUL-03 22-MAY-03
307 Jones J.C. 17-JUL-02 509 21-JUL-03 22-MAY-03
439 Wright Chasity 23-APR-73 235
696 Vanderchuck Keith 08-AUG-68 504 15-JUN-03
966 Mcginnis Allen 03-MAY-59 504 15-JUN-03
669 Sakic Joe 16-SEP-76 504 15-JUN-03
22 rows selected
PT_ID BALANCE DUEDATE PHONE ADDR CITY ST ZIP PT_INS
-------------------- ---------- --------- ---------- ------------------------------ -------------------- -- ----- --------------------
168 15650 21-AUG-03 833-9569 128 W. APPLE #4 Jonesboro IL 62952 SIH
331 300 09-SEP-03 833-5587 3434 Mulberry St. Anna IL 62906 BCBS
313 0 01-JAN-04 893-9987 334 Tailgate Ln COBDEN IL 62920 Military
816 0 01-JAN-04 833-6654 8814 W. Apple JONESBORO IL 62952 SIH
314 100 31-MAR-03 457-6658 445 Oak St. Carbondale IL 62901 BCBS
264 35000 11-JAN-03 942-8065 8898 Bighill Driver HERRIN IL 62948 MediSupplA
103 4500 01-JUL-03 833-5547 298 Murphy School Rd Anna IL 62906 HealthCare
108 0 01-JAN-05 833-5542 334 Pansie Hill Rd. JONESBORO IL 62952 HealthCare
943 0 01-JAN-07 529-9963 456 E. Grand #14 Carbondale IL 62901 Military
847 98000 31-JAN-02 549-8854 6543 W. Parkview Ln. Carbondale IL 62901 BCBS
504 0 01-JAN-03 549-6139 6657 N. Allen Carbondale IL 62901 QualityCare
809 450 19-JUL-03 687-8852 3345 Hwy 127 N. Murphysboro IL 62966 QualityCare
703 225 31-AUG-03 529-8332 909 N. Brown St. Carbondale IL 62901 HealthCare
696 79850 15-JUL-03 549-7231 5546 W. James Carbondale IL 62901 BCBS
966 98700 15-JUL-03 833-5375 9009 Taylor Ave. Anna IL 62906 BCBS
267 0 01-JAN-05 942-3321 6755 US Route 148 HERRIN IL 62948 QualityCare
307 450 31-AUG-03 457-6967 234 N. Allen Carbondale IL 62901 HealthCare
719 0 01-JAN-04 549-7848 867 Henderson St. Carbondale IL 62901 HealthCare
439 500 31-AUG-03 833-5541 4456 N. Springer Anna IL 62906 QualityCare
315 1500 14-SEP-03 833-6272 404 Williford Rd. JONESBORO IL 62952 HealthCare
163 0 01-JAN-04 833-2133 129 Fountain St. Anna IL 62906 HealthCare
669 128450 15-JUL-03 833-6654 353 Tin Bender Rd. Jonesboro IL 62952 BCBS
22 rows selected
DOC_ID DOC_NAME DATEHIRED SALPERMON AREA SUPERVISOR_ID CHGPERAPPT ANNUAL_BONUS
---------- -------------------- --------- ---------- -------------------- ------------- ---------- ------------
432 Harrison 05-DEC-94 12000 Pediatrics 100 75 4500
509 Vester 09-JAN-02 8100 Pediatrics 432 40
389 Lewis 21-JAN-96 10000 Pediatrics 432 40 2250
504 Cotner 16-JUN-98 11500 Neurology 289 85 7500
235 Smith 22-JUN-98 4550 Family Practice 100 25 2250
356 James 01-AUG-98 7950 Neurology 289 80 6500
558 James 02-MAY-95 9800 Orthopedics 876 85 7700
876 Robertson 02-MAR-95 10500 Orthopedics 100 90 8900
889 Thompson 18-MAR-97 6500 Rehab 100 65 3200
239 Pronger 18-DEC-99 3500 Rehab 889 40
289 Borque 30-JUN-89 16500 Neurology 100 95 6500
100 Stevenson 30-JUN-79 23500 Director
12 rows selected
Write the statement and output for each of the following questions:
Explanation / Answer
1) select p.pt_lname, p.pt_fname, d.doc_name from patient p inner join doctor d on p.doc_id = d.doc_id;
2) select p.pt_id,d.doctor_id from patient p inner join doctor d on p.doc_id = d.doc_id where d.area = 'Pediatrics';
3) select d.doc_name as "Doctor Name", s.super_name as "Supervisor Name" from doctor d inner join supervisor s order on d.supervisor_id = s.supervisor_id; by s.super_name;
4) select d.doc_name as "Doctor Name", (d.salpermon * 12) + d.annual_bonus as "Doctor Salary", s.super_name as "Supervisor Name", (s.salpermon * 12) + s.annual_bonus as "Supervisor Salary" from doctor d inner join supervisor s on d.supervisor_id = s.supervisor_id where d.area in ('Pediatrics', 'Orthopedics');
5) select p.pt_fname, b.phone, d.doc_name from patient p inner join doctor d on p.doc_id = d.doc_id inner join billing b on b.pt_id = p.pt_id where p.pt_lname = 'Davis';
6) select count(distinct doc_id) as "Num Of Doc With Patients" from patient;
7) select sum(annual_bonus) as "Total Annual Bonus" from doctor where extract(year from DATEHIRED) = 1988;
8) select MAX(CHGPERAPPT) as "Max Charge per Appt", MIN(CHGPERAPPT) as "Min Charge Per Appt" from doctor;
9) select doc_id, count(pt_id) from patient group by doc_id;
10) select d.area, b.city, count(p.pt_id) as "Number of Patients" from patient p inner join doctor d on p.doc_id = d.doc_id inner join billing b on b.pt_id = p.pt_id; group by d.area, b.city;
11) select area, round(avg(CHGPERAPPT)) as "Average charge" from doctor group by area;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.