database programming ( Use the DOCTOR and PATIENT table )

database programming ( Use the DOCTOR and PATIENT table )

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

Question Attachments

1 attachments —

Assignment-database-programming.docx

Download

Pending

Other / Other

29 Mar 2021

Lost Password