Homework Help is Here – Start Your Trial Now!
Engineering
Computer Engineering
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

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

Computer Networking: A Top-Down Approach (7th Edition)
Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:James Kurose, Keith Ross
Chapter1: Computer Networks And The Internet
Section: Chapter Questions
Problem R1RQ: What is the difference between a host and an end system? List several different types of end...
See similar textbooks
icon
Related questions
Question

INFO 2103

Assignment #2

 Subject: Database Programming

 

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.

 

  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

 

 

 

 

 

 

  1. 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

 

  1. 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

-

-

 

  1. 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

 

.PATNN..... NEWDOB 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 patients' name alphabetically. PATNAME Allen Meginnis Andhony Rogers Becky Roach Beiun Anderson NEWDOB ... 3. May 1959 7, December 2041 K September 1975 6. March 2048 21. April 1973 9. September 1955 17, July 2002 12. December 1999 13, May 2002 16, September 1976 3. March 2048 14. Ocuber 2043 Chaity Wright Debbie Saillee J.C. Jones Jace Smith Jennifer Poole Joe Sakic John Cochran John Jackson Keith Vanderchack KAugust 1968 Lewis Crow 16 October 20e Linda Davis 17, July 2002 12. July 1957 12. November 19s1 14. March 1997 Lymn Westr Paul Kowakyk Pal Jues 25. December 197 1. March 2013 1. lanuary 2045 Ryan Baily Scot James Stephanie Walturs 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 ........ Bergue Neurology Cotaer Neuokogy Harrisen_Pediatrics Nourokogy Junnes Orthepedic Lewis Podiatrics r Rehab James Pronger Rebertson Orthepedie Semith Family Pra Stevenson Diretor Thompsan Vester Podiatrics Rehab 4. Display the patients' new appointment date 3 months from the next appointment date. NEXTAPPTDA NEW APP PAT NAME....!.............. James ol-JULO 01-OCT-0 1 Andenor 01-OCT0 Jame 20JUL3 20-OCT-01 Smith 15-NOV-00 15-FEB-04 01-OCTO 01JUL-0 Porer 01JAN 04 Saller Regers Walten Westra Poele 01-OCT-0) 01-NOV0 01-FEB-04 12-DECO 12-MAR-04 02-FEB-0 02-MAY-04 01-DECO S 01-MAR.O 06-SEP-05 Baily Crow 06-JUN-AS 01-JUL-05 01-OCT05 Cochean 02-DEC-0S 02-MAR-O 01-MAR-O 21XTO 29-OCT01 Roach 01-DEC-05 Jackson 21-JUL-AS 29-JUL-03 21-JUL-0) 21-JUL-0 Kowaleryk Davis 21-OCT01 Jens 21XT-01 Wright Vanderchuck Meginnis Sakie
Transcribed Image Text:.PATNN..... NEWDOB 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 patients' name alphabetically. PATNAME Allen Meginnis Andhony Rogers Becky Roach Beiun Anderson NEWDOB ... 3. May 1959 7, December 2041 K September 1975 6. March 2048 21. April 1973 9. September 1955 17, July 2002 12. December 1999 13, May 2002 16, September 1976 3. March 2048 14. Ocuber 2043 Chaity Wright Debbie Saillee J.C. Jones Jace Smith Jennifer Poole Joe Sakic John Cochran John Jackson Keith Vanderchack KAugust 1968 Lewis Crow 16 October 20e Linda Davis 17, July 2002 12. July 1957 12. November 19s1 14. March 1997 Lymn Westr Paul Kowakyk Pal Jues 25. December 197 1. March 2013 1. lanuary 2045 Ryan Baily Scot James Stephanie Walturs 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 ........ Bergue Neurology Cotaer Neuokogy Harrisen_Pediatrics Nourokogy Junnes Orthepedic Lewis Podiatrics r Rehab James Pronger Rebertson Orthepedie Semith Family Pra Stevenson Diretor Thompsan Vester Podiatrics Rehab 4. Display the patients' new appointment date 3 months from the next appointment date. NEXTAPPTDA NEW APP PAT NAME....!.............. James ol-JULO 01-OCT-0 1 Andenor 01-OCT0 Jame 20JUL3 20-OCT-01 Smith 15-NOV-00 15-FEB-04 01-OCTO 01JUL-0 Porer 01JAN 04 Saller Regers Walten Westra Poele 01-OCT-0) 01-NOV0 01-FEB-04 12-DECO 12-MAR-04 02-FEB-0 02-MAY-04 01-DECO S 01-MAR.O 06-SEP-05 Baily Crow 06-JUN-AS 01-JUL-05 01-OCT05 Cochean 02-DEC-0S 02-MAR-O 01-MAR-O 21XTO 29-OCT01 Roach 01-DEC-05 Jackson 21-JUL-AS 29-JUL-03 21-JUL-0) 21-JUL-0 Kowaleryk Davis 21-OCT01 Jens 21XT-01 Wright Vanderchuck Meginnis Sakie
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 DATEHIR SALPERM NOOF NEW BON ED ON YR US .---- -- 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
Transcribed Image Text: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 DATEHIR SALPERM NOOF NEW BON ED ON YR US .---- -- 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
Expert Solution
steps

Step by step

Solved in 3 steps with 3 images

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Recommended textbooks for you
Computer Networking: A Top-Down Approach (7th Edi…
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Concepts of Database Management
Concepts of Database Management
Computer Engineering
ISBN:
9781337093422
Author:
Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:
Cengage Learning
Prelude to Programming
Prelude to Programming
Computer Engineering
ISBN:
9780133750423
Author:
VENIT, Stewart
Publisher:
Pearson Education
Sc Business Data Communications and Networking, T…
Sc Business Data Communications and Networking, T…
Computer Engineering
ISBN:
9781119368830
Author:
FITZGERALD
Publisher:
WILEY
SEE MORE TEXTBOOKS