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

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