For this assignment, please use the DOCTOR and PATIENT table. The script is available in both iTaleemC and GC. Script name: hospital.sql/hospital.rtf.
- Use INSTR to find the position of character ‘a’ in the doctor’s name.
- CONCAT the patients’ first and last name and display their DOB in the following format ‘DD, Month YYYY’. Sort the patients’ name alphabetically.
| PATNAME |
NEWDOB |
| Allen Mcginnis |
3, May 1959 |
| Anthony Rogers |
7, December 2041 |
| Becky Roach |
8, September 1975 |
| Brian Anderson |
6, March 2048 |
| Chasity Wright |
23, April 1973 |
| Debbie Saillez |
9, September 1955 |
| J.C. Jones |
17, July 2002 |
| Jason Smith |
12, December 1999 |
| Jennifer Poole |
13, May 2002 |
| Joe Sakic |
16, September 1976 |
| John Cochran |
3, March 2048 |
| John Jackson |
14, October 2043 |
| Keith Vanderchuck |
8, August 1968 |
| Lewis Crow |
16, October 2049 |
| Linda Davis |
17, July 2002 |
| Lynn Westra |
12, July 1957 |
| Paul Kowalczyk |
12, November 1951 |
| Paul James |
14, March 1997 |
| Ryan Baily |
25, December 1977 |
| Scott James |
1, March 2033 |
| Stephanie Walters |
1, January 2045 |
| Susan Porter |
14, November 1967 |
- Write a query to display the doc_name and area as follows. Sort the result according to the doctors’ name.
| PADDING |
| Borque_____Neurology |
| Cotner_____Neurology |
| Harrison__Pediatrics |
| James______Neurology |
| James_____Orthopedic |
| Lewis_____Pediatrics |
| Pronger________Rehab |
| Robertson_Orthopedic |
| Smith_____Family Pra |
| Stevenson___Director |
| Thompson_______Rehab |
| Vester____Pediatrics |
- Display the patients’ new appointment date 3 months from the next appointment date.
| PAT_NAME |
NEXTAPPTDATE |
NEW_APPT |
| James |
01-JUL-03 |
01-OCT-03 |
| Anderson |
01-JUL-03 |
01-OCT-03 |
| James |
20-JUL-03 |
20-OCT-03 |
| Smith |
15-NOV-03 |
15-FEB-04 |
| Porter |
01-OCT-03 |
01-JAN-04 |
| Saillez |
01-JUL-03 |
01-OCT-03 |
| Rogers |
01-NOV-03 |
01-FEB-04 |
| Walters |
12-DEC-03 |
12-MAR-04 |
| Westra |
02-FEB-04 |
02-MAY-04 |
| Poole |
01-DEC-03 |
01-MAR-04 |
| Baily |
06-JUN-05 |
06-SEP-05 |
| Crow |
01-JUL-05 |
01-OCT-05 |
| Cochran |
02-DEC-05 |
02-MAR-06 |
| Roach |
01-DEC-05 |
01-MAR-06 |
| Jackson |
21-JUL-03 |
21-OCT-03 |
| Kowalczyk |
29-JUL-03 |
29-OCT-03 |
| Davis |
21-JUL-03 |
21-OCT-03 |
| Jones |
21-JUL-03 |
21-OCT-03 |
| Wright |
– |
– |
| Vanderchuck |
– |
– |
| Mcginnis |
– |
– |
| Sakic |
– |
– |
- The hospital has decided for each year the doctor has worked, he/she will be given an extra RM1000. Write a query that will calculate the bonus for each doctor according to this requirement. Note: Use Nesting Functions
| DATEHIRED |
SALPERMON |
NOOFYR |
NEW_BONUS |
| 05-DEC-94 |
12000 |
26 |
38000 |
| 09-JAN-00 |
8100 |
21 |
29100 |
| 21-JAN-96 |
10000 |
25 |
35000 |
| 16-JUN-98 |
11500 |
23 |
34500 |
| 22-JUN-98 |
4550 |
23 |
27550 |
| 01-AUG-98 |
7950 |
23 |
30950 |
| 02-MAY-95 |
9800 |
26 |
35800 |
| 02-MAR-95 |
10500 |
26 |
36500 |
| 18-MAR-97 |
6500 |
24 |
30500 |
| 18-DEC-99 |
3500 |
21 |
24500 |
| 30-JUN-89 |
16500 |
32 |
48500 |
| 30-JUN-79 |
23500 |
42 |
65500 |
