sql queries on the medical database_answers

www.oraclecoach.com Offering Live Oracle SQL Training

Live Oracle Training ….www.oraclecoach.com 1

Answers to SQL Queries on the Medical Database

1. Display the physician ID, first name, last name and address for all physicians.

SQL> SELECT PHYSICIAN_ID, PHYSICIAN_FIRSTNAME, PHYSICIAN_LASTNAME FROM PHYSICIAN; 2. Display the patient number and ward, for all patients.

SQL> SELECT PATIENT_NO, WARD_NO FROM PATIENT; 3. Display the name of the nurse and the shift he/she is working in.

SQL> SELECT NURSENAME, SHIFT FROM NURSE; 4. Display the names of all wards that have more than 22 beds.

SQL> SELECT WARDNAME FROM WARD WHERE NO_OF_BEDS > 22; 5. Display the names of patients and their physicians. Display this output for patients assigned to

Ward No. 2.

SQL> SELECT PATIENT_NAME, PHYSICIAN FROM PATIENT WHERE WARD_NO=2; 6. Display the names of patients who do a day shift.

SQL> SELECT NURSENAME FROM NURSE WHERE SHIFT='DAY'; 7. Display the details of all physicians who earn more than $50000.

SQL> SELECT PHYSICIAN_ID AS ID, PHYSICIAN_FNAME AS FNAME, PHYSICIAN_LNAME AS LNAME,

SALARY, DEPARTMENT FROM PHYSICIAN WHERE SALARY > 50000; 8. Display the result as shown:

http://www.oraclecoach.com/�



HOW MUCH Doyle earns a salary of : 60000 Frank earns a salary of: 45000 Raj earns a salary of : 77000 Marena earns a salary of: 50000 Dunn earns a salary of : 62000 Clark earns a salary of: 80000 SQL> SELECT PHYSICIAN_LNAME || 'earns a salary of :' || SALARY

"HOW MUCH" FROM PHYSICIAN;

9. Display a unique listing of the fields in which the physicians specialize.

SQL>SELECT DISTINCT FIELD_OF_SPECIALIZATION FROM PHYSICIAN_SPECIALITY; 10. Display the output of question 9) in a descending order.

SQL>SELECT DISTINCT FIELD_OF_SPECIALIZATION FROM PHYSICIAN_SPECIALITY

ORDER BY 1 DESC;

11. Display the first name and department of all physicians who work in departments beginning with the letter P.

SQL> SELECT PHYSICIAN_FNAME, DEPARTMENT FROM PHYSICIAN WHERE DEPARTMENT LIKE 'P%'; 12. Display the details of patients whose names have the letter 'n' in them and are being treated by

physician PHY001.

SQL> SELECT * FROM PATIENT WHERE PATIENT_NAME LIKE '%n%' AND PHYSICIAN='PHY001'; 13. Display the following output : (Display an asterisk, corresponding to the value of ward number,

i.e. 1 => *, 2 => ** and so on)

PATIENT_NAME WARD Ryan Rogers * Fred Fuller **** Wendy Silk **




… for all the patients.

SQL> SELECT PATIENT_NAME, DECODE(WARD_NO,1,'*',2,'**',3,'***',4,'****') WARD_NO

FROM PATIENT;

14. Display the following result: (for all the patients)

PATIENT NU PATIENT NAME DATE_IN CS0012 Ryan Rogers 20th of Oct, 2002 CS0014 Fred Fuller 16th of Dec, 2002 …. SQL> SELECT PATIENT_NO "PATIENT NUMBER", PATIENT_NAME "PATIENT NAME", TO_CHAR(DATE_IN, 'fmddth "of" Mon "," YYYY') DATE_IN FROM PATIENT;

15. Display the physician name and their monthly salary in the format displayed below. (Note: the salary in the PHYSICIAN table represents an annual salary). Round the monthly salary.

Name of Physician Monthly Salary John, Doyle 5000 Edna, Frank 3750 Joseph, Raj 6417 … SQL> SELECT PHYSICIAN_FNAME || ',' || PHYSICIAN_LNAME

"Name Of Physician", ROUND(SALARY/12) "Monthly Salary" FROM PHYSICIAN;

16. Display the output of 15) in the descending order of monthly salary.

SQL> SELECT PHYSICIAN_FNAME || ',' || PHYSICIAN_LNAME "Name Of Physician", ROUND(SALARY/12) "Monthly Salary" FROM PHYSICIAN

ORDER BY "Monthly Salary" Desc;

17. Display the names of physicians whose first name consists of 5 letters only.

SQL> SELECT PHYSICIAN_FNAME FROM PHYSICIAN




WHERE LENGTH(PHYSICIAN_FNAME)=5; 18. Display the names of all patients that are admitted between October 31st, 2002 and December

31st, 2002.

SQL> SELECT PATIENT_NAME FROM PATIENT WHERE DATE_IN BETWEEN '31-OCT-2002' AND '31-DEC-2002'; 19. Display the names of the departments that physicians belong to that do not start with the letter

P.

SQL> SELECT DEPARTMENT FROM PHYSICIAN WHERE DEPARTMENT NOT LIKE 'P%'; 20. Display the names of patients and their date of follow-up. The date of follow-up is a ‘the first

Tuesday, 6 weeks after the patient was admitted’.

SQL> SELECT PATIENT_NAME, NEXT_DAY(ADD_MONTHS(DATE_IN,6), 'Tuesday') "FOLLOW UP DATE"

FROM PATIENT;

21. Display the names of all patients who have been admitted more than 2 months ago.

SQL> SELECT PATIENT_NAME FROM PATIENT WHERE MONTHS_BETWEEN(SYSDATE,DATE_IN) > 2; 22. Display the physicians first name and salary in the format shown below: (for all physicians)

PHYSICIAN_FNAME SALARY John $60,000.00 Edna $45,000.00 Joseph $77,000.00 … SQL> SELECT PHYSICIAN_FNAME, TO_CHAR(SALARY,'$99,999.00') SALARY FROM PHYSICIAN;

23. Display the current system date in the format : January 20, 2010 (Note: the date will vary depending on the day you issue this query)

SQL> SELECT TO_CHAR(SYSDATE,'fmDD Month, YYYY') "DATE" FROM DUAL;




24. Display the average salary of all physicians. Round the result to 2 decimal places.

SQL> SELECT ROUND(AVG(SALARY),2) FROM PHYSICIAN;

25. Display the maximum salary earned by a physician, taken department-wise. The result is displayed below:

DEPARTMENT MAX(SALARY) General Practice 45000 Gynecology 50000 Pediatrics 80000 Psychiatry 77000 SQL> SELECT DEPARTMENT, MAX(SALARY) FROM PHYSICIAN GROUP BY DEPARTMENT;

26. Display the result of 25) only for those departments which have more than one physician working in them.

SQL> SELECT DEPARTMENT, MAX(SALARY) FROM PHYSICIAN GROUP BY DEPARTMENT

HAVING COUNT(*) > 1;

27. Display the name of the physician earning the least annual salary.

SQL> SELECT PHYSICIAN_FNAME FROM PHYSICIAN WHERE SALARY = (SELECT MIN(SALARY) FROM PHYSICIAN);

28. Display the count of the number of distinct fields of specialization. Label the result column as Count.

SQL> SELECT COUNT(DISTINCT FIELD_OF_SPECIALIZATION) "Count" FROM PHYSICIAN_SPECIALITY;

29. Display the following output:

SHIFT Number of Nurses DAY 5 NIGHT 3 SQL> SELECT SHIFT, COUNT(*) "Number of Nurses"




FROM NURSE GROUP BY SHIFT;

30. Display the names of patients who have received IN-PATIENT treatment, as shown below:

PATIENT_NAME SERVICE_TYPE Sue Shepherd IN-PATIENT Wendy Silk IN-PATIENT SQL> SELECT A.PATIENT_NAME, SERVICE_TYPE

FROM INVOICE I , PATIENT A WHERE I.PATIENT_NO = A.PATIENT_NO AND SERVICE_TYPE='IN-PATIENT';

SQL 1999 Join syntax: SQL> SELECT PATIENT_NAME, SERVICE_TYPE FROM INVOICE JOIN PATIENT USING (PATIENT_NO) WHERE SERVICE_TYPE='IN-PATIENT';


sql queries on the medical database_answers

Documents