SQL Project 1, computer science homework help

User Generated

TKVNBLHNA

Computer Science

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

CIS 645 Oracle SQL Developer Setup The oracle database server has been set up. To access the oracle database server, you need to do the following: 1. Create your Oracle account at https://profile.oracle.com/myprofile/account/createaccount.jspx, if you don’t have one yet. 2. Go to http://www.oracle.com/technetwork/developer-tools/sqldeveloper/downloads/index.html; log in with your Username and Password created in Step 1. 3. While there, check the radio button of “Accept License Agreement.” 4. Download the appropriate Oracle SQL Developer. If you don’t know whether your PC is running the 32-bit or 64-bit version of Windows, take a look at the following link: https://support.microsoft.com/en-us/help/15056/windows-7-32-64-bit-faq (copy and paste the link to your browser if clicking on it doesn’t work). 5. Unzip the downloaded software to your desktop. A folder (e.g., sqldeveloper) should be created on your desktop. 6. Find and run the executable file sqldeveloper.exe under the folder sqldeveloper >> sqldeveloper >> bin. 7. You might see the following input box asking for the full pathname for java.exe: Click on Browse... button and provide the full pathname for java.exe, similar to the following (note that you may need to download and install the JDK if necessary): 8. Double click on the same sqldeveloper.exe to launch the Oracle SQL Developer. You can create a shortcut by right-clicking on it then select Send To >> Desktop (create shortcut). 9. After sqldeveloper launches, click the green plus sign on the Connection panel to create a new connection, as shown in the screen shot below: 10. Fill in the following information for the new connection: Connection Name: Oracle Username: cis645 Password: weak_password Check Save Password Skip to hostname below Hostname: unacsis.una.edu Port: 2246 SID: csis Click the Test button and you should see “Status: Success” on the left bottom just above the Help button, as shown in the screen shot below. Click on Connect to establish the connection, you will see the following screen shot: You will now be able to type SQL statements in the worksheet. To test whether you’ve successfully set up your Oracle SQL Developer, copy the following and paste it into the Worksheet panel: CREATE TABLE Clinic_x( clinicNo CHAR(5) NOT NULL, street VARCHAR2(40) NOT NULL, city VARCHAR2(15) NOT NULL, state CHAR(2) NOT NULL, zipCode VARCHAR2(9) NOT NULL UNIQUE, telNo VARCHAR2(20) NOT NULL UNIQUE, faxNo VARCHAR2(20) NOT NULL UNIQUE, mgrStaffNo CHAR(4), PRIMARY KEY (clinicNo) ); Click the Run Script button, your screen should look like the following: Clear both the Worksheet pane and the Script Output panel by clicking the Clear button on both panels. In the Worksheet panel, type “drop table clinic_x;” and then click the Run Script button, your screen should looks like the following: Congratulations! You’ve successfully set up your Oracle SQL Developer. Please let me know if you have any questions. Jason Watson SQL Project Submission Example Jason Watson (Replace my name with yours) CIS 645-I01: Database Management Systems Summer 2017 (Double check the semester and year) SQL Project 1 (Double check the project number) 1. select * from doctor; select * from patient; select * from billing; DOC_ID AREA DOC_NAME DATEHIRED SUPERVISOR_ID CHGPERAPPT SALPERMON ANNUAL_BONUS ---------- -------------------- ------------------------- ----------------------------------------- ---------------------- ------------------------------------------432 Harrison 05-DEC-94 12000 Pediatrics 100 75 4500 509 Vester Pediatrics 432 09-JAN-02 389 Lewis Pediatrics 432 504 Neurology 289 Cotner 21-JAN-96 40 16-JUN-98 235 Smith Family Practice 100 356 Neurology 289 James 558 James Orthopedics 8100 40 22-JUN-98 25 876 Robertson Orthopedics 100 02-MAR-95 889 Rehab Thompson 18-MAR-97 239 Rehab Pronger 289 Neurology Borque 100 Director Stevenson 100 4550 2250 7950 85 6500 9800 7700 10500 90 65 18-DEC-99 889 100 7500 80 02-MAY-95 2250 11500 85 01-AUG-98 876 10000 8900 6500 3200 3500 40 30-JUN-89 30-JUN-79 95 16500 6500 23500 12 rows selected PT_ID DOC_ID ------------------168 PT_LNAME PT_FNAME PTDOB NEXTAPPTD LASTAPPTD -------------------- -------------------- ------------------------------------------------- ------------------------James Paul 14-MAR-97 432 331 235 313 235 816 509 314 235 315 235 719 504 264 504 267 235 103 389 108 235 943 235 847 356 163 235 504 235 809 558 703 509 307 509 439 235 696 504 966 504 669 504 01-JUL-03 Anderson 01-JUL-03 James 20-JUL-03 Smith 15-NOV-03 Porter 01-OCT-03 Saillez 01-JUL-03 Rogers 01-NOV-03 Walters 12-DEC-03 Westra 02-FEB-04 Poole 01-DEC-03 Baily 06-JUN-05 Crow 01-JUL-05 Cochran 02-DEC-05 Roach 01-DEC-05 Jackson 21-JUL-03 Kowalczyk 29-JUL-03 Davis 21-JUL-03 Jones 21-JUL-03 Wright 01-JUN-03 Brian 01-JUN-03 Scott 20-JUN-03 Jason 15-MAY-03 Susan 01-MAR-03 Debbie 01-JUN-03 Anthony 01-JAN-03 Stephanie 12-DEC-02 Lynn 02-FEB-03 Jennifer 01-JUN-03 Ryan 06-JUN-03 Lewis 01-MAR-02 John 01-JAN-02 Becky 01-JAN-02 John 10-NOV-02 Paul 19-JUN-03 Linda 22-MAY-03 J.C. 22-MAY-03 Chasity Vanderchuck Keith Allen Sakic 26-MAR-33 12-DEC-99 14-NOV-67 09-SEP-55 01-JAN-42 26-JAN-45 12-JUL-57 13-MAY-02 25-DEC-77 10-NOV-49 28-MAR-48 08-SEP-75 08-NOV-43 12-NOV-51 17-JUL-02 17-JUL-02 23-APR-73 08-AUG-68 15-JUN-03 Mcginnis 31-MAR-48 Joe 15-JUN-03 03-MAY-59 16-SEP-76 15-JUN-03 22 rows selected PT_ID ADDR BALANCE CITY DUEDATE ST ZIP PT_INS PHONE -------------------- ---------------------- ------------------------- --------------------------------------- -------------------- -- -----------------------168 15650 21-AUG-03 833-9569 128 W. APPLE #4 Jonesboro IL 62952 SIH 331 3434 Mulberry St. 300 313 334 Tailgate Ln 0 816 8814 W. Apple 0 Anna 09-SEP-03 IL 62906 BCBS 833-5587 COBDEN 01-JAN-04 893-9987 IL 62920 Military JONESBORO 01-JAN-04 IL 62952 SIH 833-6654 314 445 Oak St. 100 Carbondale 31-MAR-03 IL 62901 BCBS 264 8898 Bighill Driver 35000 457-6658 HERRIN 11-JAN-03 942-8065 IL 62948 MediSupplA 103 4500 298 Murphy School Rd Anna 01-JUL-03 833-5547 IL 62906 HealthCare 108 334 Pansie Hill Rd. 0 JONESBORO 01-JAN-05 833-5542 IL 62952 HealthCare 943 456 E. Grand #14 0 Carbondale 01-JAN-07 529-9963 IL 62901 Military 847 98000 6543 W. Parkview Ln. Carbondale 31-JAN-02 IL 62901 BCBS 504 6657 N. Allen 0 Carbondale 01-JAN-03 549-6139 IL 62901 QualityCare 809 3345 Hwy 127 N. 450 Murphysboro 19-JUL-03 687-8852 IL 62966 QualityCare 703 909 N. Brown St. 225 Carbondale 31-AUG-03 529-8332 IL 62901 HealthCare 696 5546 W. James 79850 15-JUL-03 IL 62901 BCBS 549-7231 Carbondale 966 9009 Taylor Ave. 98700 Anna 15-JUL-03 IL 62906 BCBS 833-5375 267 6755 US Route 148 0 HERRIN 01-JAN-05 942-3321 IL 62948 QualityCare 307 234 N. Allen 450 Carbondale 31-AUG-03 457-6967 IL 62901 HealthCare 719 867 Henderson St. 0 Carbondale 01-JAN-04 549-7848 IL 62901 HealthCare 439 4456 N. Springer 500 Anna 31-AUG-03 833-5541 IL 62906 QualityCare 315 404 Williford Rd. 1500 JONESBORO 14-SEP-03 833-6272 IL 62952 HealthCare 163 129 Fountain St. 0 Anna 01-JAN-04 833-2133 IL 62906 HealthCare 669 353 Tin Bender Rd. 128450 Jonesboro 15-JUL-03 IL 62952 BCBS 549-8854 833-6654 22 rows selected 2. select pt_fname "First Name", pt_lname "Last Name", ptdob "Birth Date" from patient; First Name Last Name Birth Date -------------------Paul Brian Scott Jason Susan Debbie Anthony Stephanie Lynn Jennifer Ryan Lewis John Becky John Paul Linda J.C. Chasity Keith Allen Joe 22 rows selected -------------------James Anderson James Smith Porter Saillez Rogers Walters Westra Poole Baily Crow Cochran Roach Jackson Kowalczyk Davis Jones Wright Vanderchuck Mcginnis Sakic ------------------------14-MAR-97 31-MAR-48 26-MAR-33 12-DEC-99 14-NOV-67 09-SEP-55 01-JAN-42 26-JAN-45 12-JUL-57 13-MAY-02 25-DEC-77 10-NOV-49 28-MAR-48 08-SEP-75 08-NOV-43 12-NOV-51 17-JUL-02 17-JUL-02 23-APR-73 08-AUG-68 03-MAY-59 16-SEP-76
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

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...


Anonymous
Great content here. Definitely a returning customer.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags