Description
SQL Project 1
- Display the contents of the DOCTOR, PATIENT, and BILLING tables.
- Display each patient’s first name, last name, and date of birth, using “First Name”, “Last Name”, and “Birth Date” instead of the default ones. Note: Use column aliases.
- For each doctor, display his or her name and annual income, using “Annual Income” as the column alias. Note: Annual income = 12*(Salary per month) + Annual bonus. What’s the problem with your result? Why is it? How to handle this problem? Hint: Use NVL.
- Display each patient’s name and date of birth in sentence form under heading of “Patient Information”. One example is “Paul James was born on 14-MAR-97.” Note: Use the concatenation operator (||) to join two character strings.
- Display the current date. Note: Use a dummy table named DUAL.
- Determine the number of days that have elapsed since each patient’s last visit, using “Days Since Last Visit” as the column alias.
- Display the IDs of all doctors assigned to patients. Modify the previous example to eliminate display of duplicate values by using DISCTINCT.
- Display all doctor’s names and hire dates in chronological order with the person on staff the longest listed first. Modify the previous example to begin the list with the person on staff for the shortest time.
- For each patient, display the patient ID and the number of days since their last appointment in order with the most recently seen patient listed first, using “Days Since Appt” as the column alias.
- Display the names of doctors who change at least $40 per appointment.
- Display the names of patients whose next appointment is less than 60 days since their last appointment.
- Display all data on patients whose last name is James. Note: Make sure to match the case of literal string in WHERE clause with the case used in the table.
- Display the IDs and balances of patients that owe at least $300 and no more than $500. Note: Use the BETWEEN operator.
- Display the names and areas of doctors specializing in pediatrics or family practice. Note: Using the IN operator.
- Display the IDs of doctors that do not receive annual bonuses. Note: Use the IS NULL operator.
- Display all billing data for patients living on N. Allen Street. Note: Use the LIKE operator. The LIKE operator is used in conjunction with wildcard characters (% or _) to match character string patterns.
- Display the IDs, balances, and due dates for patients that have balances over $10,000.
- Display all billing data on patients who either have balances of more than $90,000 or have insurance with SIH.
- Display all data on patients except those with a last name of James, Jones, Wright, or Walters. Note: Use NOT IN.
- Display all data on patients whose full name matches that entered by the user. Try “Brian Anderson”. Note: Use ACCEPT.
Unformatted Attachment Preview
Purchase answer to see full attachment
Explanation & Answer
Hey buddy, here's the answer. Took me quite some time to complete but all is well. See you later.💪
SCRIPTS AND STATEMENTS
1.
select * from doctor;
select * from patient;
select * from billing;
2.
select pt_fname "First Name", pt_lname "Last Name", ptdob "Birth Date"
from patient;
3.
select DOC_NAME "DOCTOR",
((SALPERMON *12) + NVL (ANNUAL_BONUS,0)) "ANNUAL INCOME"
from DOCTOR
4.
SELECT PT_FNAME||' '||PT_LNAME||' '||'was born on'||' '||PTDOB
"PATIENT INFORMATION"
FROM PATIENT
5.
SELECT TO_CHAR
(SYSDATE, 'MM-DD-YYYY') "NOW"
FROM DUAL;
6.
select 'It has been'||' '|| ROUND(SYSDATE - LASTAPPTD,0)||','||'days since'||' '||
PT_FNAME||' '||PT_LNAME||' '|| 'last visit.' "Days Since Appt"
FROM PATIENT
7.
select distinct doc_id "DOCs w/ PTs by: DOC ID"
from patient;
select doc_id "*Same* But Has Duplicates"
from patient;
8.
select doc_name, datehired
from doctor
order by datehired;
select doc_name, datehired
from doctor
order by datehired desc;
9.
select PT_ID ||'-'||PT_FNAME||' '||PT_LNAME||' '||
ROUND(SYSDATE - LASTAPPTD,0)||','||' days since last appt.' "Days Since Appt"
FROM PATIENT
order by LASTAPPTD desc;
10.
SELECT DOC_NAME"DOCTOR",CHGPERAPPT"CHARGE PER APPT LESS $40"
From DOCTOR
WHERE CHGPERAPPT >= 40
order by CHGPERAPPT;
11.
SELECT PT_FNAME||' '||PT_LNAME "Name",
(NEXTAPPTD-LASTAPPTD)"Days Until Next Appt"
from PATIENT
WHERE(NEXTAPPTD-LASTAPPTD) < 60
order by "Days Until Next Appt";
12.
SELECT * FROM PATIENT
WHERE PT_LNAME = 'James';
13.
SELECT PT_ID,BALANCE
From BILLING
WHERE BALANCE between 300 and 500;
14.
select DOC_NAME,DOC_ID,AREA
from doctor
where AREA IN ('Pediatrics','Family Practice');
15.
select DOC_ID,DOC_NAME,ANNUAL_BONUS
FROM DOCTOR
Where ANNUAL_BONUS IS NULL
16.
SELECT * FROM billing
WHERE ADDR LIKE '% N. Allen';
17.
select PT_ID,BALANCE,DUEDATE
from billing
where BALANCE > 10000;
18.
select * from BILLING
where PT_INS LIKE '%SIH' or balance > 90000;
19.
select * from PATIENT
where PT_LNAME NOT IN ('james','jones','wright','walter');
20.
accept last_name prompt 'What is the patient''s last name?'
accept first_name prompt 'And the patient''s first name?'
select * from patient
where pt_lname = '&last_name' and pt_fname = '&first_name';
OUTPUTS of SCRIPTS AND STATEMENTS
1.
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
1...