For this lab you will be using SQL SELECT statements to query your database tabl
ID: 3912195 • Letter: F
Question
For this lab you will be using SQL SELECT statements to query your database tables. You will be turning in the results of the following queries:
1. List all Patients and what Bed they are assigned to
2. List all patients who had Treatments and what Treatment they received
3. List all patients who had tests and what Test they had
4. List the employees (doctors, nurses, etc.) who assisted each patient.
5. List all patients in alphabetical order
6. List all patients who live in Atlanta and had a test completed
7. List all patients who live in either Woodstock or Roswell who had a treatment completed.
If you don't have data in your created tables that will allow you to create these queries - add appropriate data to your tables before doing the queries.
Capture your results from each SQL query (SELECT statement) - that also shows the actual written query - and turn them in.
if you have any question , please ask!
QL> desc patient Name Null? Type SSN NAME AGE GENDER NOT NULL NUMBER(4) VARCHAR2 (20) NUMBER (3) VARCHAR2(6) SQL> desc department Name Null? Type NOT NULL NUMBER(4) DID DNAME ADDRESS VARCHAR2 (20) VARCHAR2(30) SQL> desc treatment Name Null? Type TID DETAIL NOT NULL NUMBER(4) VARCHAR2 (20) SQL> desc bed Name Null? Type BID SIZ NOT NULL NUMBER(4) VARCHAR2(10) SQL> desc persorn Name Null? Type PNAME SALARY VARCHAR2 (20) NUMBER(7)Explanation / Answer
1. List all Patients and what Bed they are assigned to There is no data given for where Bed information corresponding to Patient has been stored. BedId BID and Patient SSN have not been related in any table 2. List all patients who had Treatments and what Treatment they received. select Patient.NAME, Patient.SSN, Treatment.DETAIL from Patient, Patient_Treatment, Treatment where Patient.SSN = Patient_Treatment.PID and Patient_Treatment.TID = Treatment.TID 3. List all patients who had tests and what Test they had Again, there is no patient_test table, through which i can get this info. 4. List the employees (doctors, nurses, etc.) who assisted each patient. Again, There is no mapping on which patient is getting treated by which employee. 5. List all patients in alphabetical order select Name from Patient order by Name desc; 6. List all patients who live in Atlanta and had a test completed Again, There is no mapping of Patient address with Patient table. 7. List all patients who live in either Woodstock or Roswell who had a treatment completed. Again, There is no mapping of Patient address with Patient table. ================= please give me answers to my queries above and i will again come and help you through comments.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.