ZuoTi.Pro
Question

SQL: CREATE STATEMENT For patients whose last name matches the name entered by the user, display...

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;

engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

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'
0 0
Add a comment
Know the answer?
Add Answer to:
SQL: CREATE STATEMENT For patients whose last name matches the name entered by the user, display...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • SQL - create statement for query For each patient, display his or her last name, first...

    SQL - create statement for query For each patient, display his or her last name, first name, and the name of his or her doctor. For each pediatrics patient, display his or her ID and his or her doctor's ID and name. For each doctor, display the name and the name of the doctor's supervisor in alphabetic order of supervisor's name. Include column aliases for clarity. Note: Check for accuracy. For each doctor in one of the two areas entered...

  • Oracle 12c SQL Chapter 12 Determine which orders were shipped to the same state as order...

    Oracle 12c SQL Chapter 12 Determine which orders were shipped to the same state as order 1014. CREATE TABLE Orders (Order# NUMBER(4), Customer# NUMBER(4), OrderDate DATE NOT NULL, ShipDate DATE, ShipStreet VARCHAR2(18), ShipCity VARCHAR2(15), ShipState VARCHAR2(2), ShipZip VARCHAR2(5), ShipCost NUMBER(4,2), CONSTRAINT orders_order#_pk PRIMARY KEY(order#), CONSTRAINT orders_customer#_fk FOREIGN KEY (customer#) REFERENCES customers(customer#)); INSERT INTO ORDERS VALUES (1000,1005,TO_DATE('31-MAR-09','DD-MON-YY'),TO_DATE('02-APR-09','DD-MON-YY'),'1201 ORANGE AVE', 'SEATTLE', 'WA', '98114' , 2.00); INSERT INTO ORDERS VALUES (1001,1010,TO_DATE('31-MAR-09','DD-MON-YY'),TO_DATE('01-APR-09','DD-MON-YY'), '114 EAST SAVANNAH', 'ATLANTA', 'GA', '30314', 3.00); INSERT INTO ORDERS VALUES (1002,1011,TO_DATE('31-MAR-09','DD-MON-YY'),TO_DATE('01-APR-09','DD-MON-YY'),'58...

  • Query 1: Retrieve names of all the projects as well as First and Last name of...

    Query 1: Retrieve names of all the projects as well as First and Last name of managers if they are working on any of these projects. Database: //STEP #1: CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), Super_ssn char(9), dno char(4), primary key (ssn)); CREATE TABLE department ( dname varchar(25) not null, dnumber char(4), Mgr_ssn char(9) not null, Mgr_start_date date, primary key (dnumber)); CREATE...

  • Q2. Retrieve the names of all employees from the employee table to produce output on CSV...

    Q2. Retrieve the names of all employees from the employee table to produce output on CSV format or delimited format with a common delimeter, rather than separete columns. Hint:Put the whole row into a string with a semicolon as the seperator(delimeter) between thecolumns: FORMAT:(fname;minit;lname) Example: EMPLOYEES -------------- James;E;Borg Frank;T;Wong Q3. Write a query to show the employees name from the employee table in this format: first letter of the first name, followed by a dot, a blank, and the full...

  • I am using oracle sql developer to create some queries to generated reports and it is...

    I am using oracle sql developer to create some queries to generated reports and it is not working. I am not sure how to use count, group by, and order by. Help me fix my query and explain to me how you did, so can do it next time. Also, I have to convert this query to a stored procedure, so can you help me do it too? Here is my query: SELECT COUNT(GUEST.FIRSTNAME), GUEST.FIRSTNAME, GUEST.LASTNAME, GUEST.GUESTTYPE, RESERVATION.RESERVATIONDATE, GUEST.EMAIL, FROM...

  • Query #2:       List the name of the project and total number of hours worked on by...

    Query #2:       List the name of the project and total number of hours worked on by all the employees on this project, also report the number of employees working on each project. Database: //STEP #1: CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), Super_ssn char(9), dno char(4), primary key (ssn)); CREATE TABLE department ( dname varchar(25) not null, dnumber char(4), Mgr_ssn char(9) not null,...

  • Oracle only database: DROP TABLE ORDERITEMS; DROP TABLE Orders; DROP TABLE BOOKAUTHOR; DRO...

    oracle only database: DROP TABLE ORDERITEMS; DROP TABLE Orders; DROP TABLE BOOKAUTHOR; DROP TABLE BOOKS; DROP TABLE PROMOTION; DROP TABLE AUTHOR; DROP TABLE CUSTOMERS; DROP TABLE PUBLISHER; CREATE TABLE Customers ( Customer# NUMBER(4), LastName VARCHAR2(10) NOT NULL, FirstName VARCHAR2(10) NOT NULL, Email VARCHAR(40), Address VARCHAR2(20), City VARCHAR2(12), State VARCHAR2(2), Zip VARCHAR2(5), Referred NUMBER(4), Region CHAR(2), CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#) ); INSERT INTO CUSTOMERS VALUES (1001, 'MORALES', 'BONITA', '[email protected]', 'P.O. BOX 651', 'EASTPOINT', 'FL', '32328', NULL, 'SE'); INSERT INTO CUSTOMERS VALUES...

  • * myCompany.SQL Introduction to SQL Script file for ORACLE DBMS This script file creates the following...

    * myCompany.SQL Introduction to SQL Script file for ORACLE DBMS This script file creates the following tables: VENDOR, PRODUCT, CUSTOMER, INVOICE, LINE EMPLOYEE and loads the default data rows */ set echo on; set serveroutput on; select systimestamp from dual; show user; ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; DROP TABLE LINE CASCADE CONSTRAINTS; DROP TABLE INVOICE CASCADE CONSTRAINTS; DROP TABLE CUSTOMER CASCADE CONSTRAINTS; DROP TABLE PRODUCT CASCADE CONSTRAINTS; DROP TABLE VENDOR CASCADE CONSTRAINTS; DROP TABLE EMPLOYEE CASCADE CONSTRAINTS; CREATE TABLE...

  • drop table department cascade constraints; create table department ( Dname   varchar2(15)   not null, Dnumber int   not...

    drop table department cascade constraints; create table department ( Dname   varchar2(15)   not null, Dnumber int   not null, Mgr_ssn   char(9)   not null, mgr_start_date   Date, primary key (Dnumber), Unique    (Dname)); insert into DEPARTMENT values ('Research', '5', '333445555', '22-May-1988'); insert into DEPARTMENT values ('Administration', '4', '987654321', '01-Jan-1995'); insert into DEPARTMENT values ('Headquarters', '1', '888665555', '19-Jun-1981'); drop table employee cascade constraints; create table employee ( Fname   varchar2(15) not null, Minit   char(1), Lname   varchar2(15) not null, Ssn   char(9), Bdate   date, Address   varchar2(30), Sex   char(1),...

  • I need help for SQL homework. the question: the code for part 1,2: drop table Customer;...

    I need help for SQL homework. the question: the code for part 1,2: drop table Customer; drop table Company; drop table Cruise; drop table TravelAgent; drop table Reservation; drop sequence customerID_seq; drop sequence cruiseID_seq; drop sequence travelAgentID_seq; drop sequence reservationID_seq; create table Customer( customerID number, firstName varchar2(15), lastName varchar2(15), address varchar2(30), phone number(10) not null, age number(3), Constraint Customer_PK Primary Key (customerID), constraint Customer_unique unique (firstName,lastName,phone), constraint Customer_check check(phone is not null) ); create sequence customerID_seq start with 1 increment...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT