Normal 0 false false false EN-US JA AR-SA /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:””; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-pagination:widow-orphan; font-size:12.0pt; font-family:”Cambria”,serif; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin;}
1. Use INSTR to find the position of character ‘a’ in the doctor’s name.
2. 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
3. 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
4. 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
–
–
5. 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
