hospital.sql/hospital.rtf. 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 alphabetically. patients' name NEWDOR ... May 1959 1, Deoember 2041 PATNAME ................. Allm Meginni Antheny Rogers Becky Roh KSepmber 1975 6. Manh 2048 21, Apvil 193 Brian Anderson Chaity Wright Debbie Saillee 9. Sepember 1985 17, uly 2002 12. Deenber 19 LC. Jones Jun Smith 13, May 2002 16, September 196 Jenifer Pole Joe Sakic 3, Mesh 2048 14, Ocober 20) leln Cochran John lackson Keih Vandent KAugut 19 16 Otber 20ee Lewis Crow Linda tavi 17. July 2002 Lynn Wedr Pal Kowaknk 12. July 1957 12. Novemher 91 Paul Aumes 14. Maach 1997 25. Deember I977 1. March 2013 Stephanie Walurs 1. lmy 2045 14, November 1967 Ryan Baily Scon ames Susan Porter 3. Write a query to display the doc_name and area as follows. Sort the result according to the doctors' name. SADDING BorgueNeunohogy Coter Neroky Hanisun Pediateies Jumes Neurokogy Jue Orthepelie Podiatrie Lewis Pronger Rchal Roburtn Orhepedie Smih Family Pra Stevenon Direter Thempsan Rehab Vester r Podiais 4. Display the patients' new appointment date 3 months from the next appointment date. NEXTAPPTDA NEW APP PAT NAME Jme TE ..... .. Anderson James Smith 20JUL 1S-NOV0 01-OCTO 20-CT0) 15-FEB 04 01JAN 04 Porer Saller Regers Waters 01 NOV 01-FEB 04 12-DEC0 12-MAR-04 West s 02-FEB- 02-MAY O4 01-DECA 0-JUN-S Poele 01MAR.04 4 Baily SEP 0 Crow 01-JUL-05 Cochean 02-DECOS 02.MAR-00 Reach 01-MAR-06 21 JULAS 29JUL Jackon 21XT0 Kowaleryk Dnis Jees S 29-OCT-01 21-JUL-0) 21-OCT-0) 21-JUL-O 21XT0) Wright Vanderchuck Miginnis Sak
INFO 2103
Assignment #2
Subject:
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.
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 |
- 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 |
Step by step
Solved in 3 steps with 3 images