Homework Help is Here – Start Your Trial Now!
Engineering
Computer Engineering
Write the following queries in SQL, using the relations in the  appendix.  i. Find the full names of patients who have been given  prescriptions, each with the drug name and the dosage. ii. Find the names of drugs that have been prescribed to the  patients; make sure there are no duplicates in the result. iii. Find the codes and names of all drugs that are prescribed by a  doctor whose ID is 32445; make sure there are no duplicates  in the result. iv. Find the highest price of a drug.

Write the following queries in SQL, using the relations in the  appendix.  i. Find the full names of patients who have been given  prescriptions, each with the drug name and the dosage. ii. Find the names of drugs that have been prescribed to the  patients; make sure there are no duplicates in the result. iii. Find the codes and names of all drugs that are prescribed by a  doctor whose ID is 32445; make sure there are no duplicates  in the result. iv. Find the highest price of a drug.

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

Write the following queries in SQL, using the relations in the 
appendix. 
i. Find the full names of patients who have been given 
prescriptions, each with the drug name and the dosage.
ii. Find the names of drugs that have been prescribed to the 
patients; make sure there are no duplicates in the result.
iii. Find the codes and names of all drugs that are prescribed by a 
doctor whose ID is 32445; make sure there are no duplicates 
in the result.
iv. Find the highest price of a drug. 

PAT NUM PAT TITLE PAT LNAME 100 Kolnycz 101 Lewis 102 Vandam 103 Jones 104 Lange 105 Williams 106 107 108 109 110 111 112 113 114 115 116 Mr. Ms. Mr. Ms. Mr. Mr. Mrs. Mr. Mr. Mr. Mrs. Mr. Mr. Ms. Ms. Mrs. Mr. Smith Diante Wesenbach Smith Genkazi Washington Johnson Smythe Brandon Saranda Smith FIGURE 3 PATIENT RELATION PAT FNAME PAT NITIAL George Rhonda Rhett Anne John Robert Jeanine Jorge Paul George Leighla Rupert Edward Melanie Marie Hermine George D G M P D K D R K W E E P G R A PAT DOB PAT AREACODE PAT PHONE 15-Jun-1942 615 19-Mar-2005 615 14-Nov-1958 901 16-Oct-1974 615 08-Nov-1971 901 14-Mar-1975 615 12-Feb-2003 615 21-Aug-1974 615 14-Feb-1966 615 18-Jun-1961 901 19-May-1970 901 03-Jan-1966 615 14-May-1961 615 15-Sep-1970 615 02-Nov-1932 901 25-Jul-1972 615 08-Nov-1965 615 324-5456 324-4472 675-8993 898-3456 504-4430 890-3220 324-7883 890-4567 897-4358 504-3339 569-0093 890-4925 898-4387 324-9006 882-0845 324-5505 890-2984
Transcribed Image Text:PAT NUM PAT TITLE PAT LNAME 100 Kolnycz 101 Lewis 102 Vandam 103 Jones 104 Lange 105 Williams 106 107 108 109 110 111 112 113 114 115 116 Mr. Ms. Mr. Ms. Mr. Mr. Mrs. Mr. Mr. Mr. Mrs. Mr. Mr. Ms. Ms. Mrs. Mr. Smith Diante Wesenbach Smith Genkazi Washington Johnson Smythe Brandon Saranda Smith FIGURE 3 PATIENT RELATION PAT FNAME PAT NITIAL George Rhonda Rhett Anne John Robert Jeanine Jorge Paul George Leighla Rupert Edward Melanie Marie Hermine George D G M P D K D R K W E E P G R A PAT DOB PAT AREACODE PAT PHONE 15-Jun-1942 615 19-Mar-2005 615 14-Nov-1958 901 16-Oct-1974 615 08-Nov-1971 901 14-Mar-1975 615 12-Feb-2003 615 21-Aug-1974 615 14-Feb-1966 615 18-Jun-1961 901 19-May-1970 901 03-Jan-1966 615 14-May-1961 615 15-Sep-1970 615 02-Nov-1932 901 25-Jul-1972 615 08-Nov-1965 615 324-5456 324-4472 675-8993 898-3456 504-4430 890-3220 324-7883 890-4567 897-4358 504-3339 569-0093 890-4925 898-4387 324-9006 882-0845 324-5505 890-2984
DOCD DOC LNAME DOC FNAME DOC INITIAL DOC SPECIALTY 29827 Sanchez Julo 32445 Jorgensen Annelise 33456 Korenski Anatoly 33989 LeGrande George 34409 Washington Dennis 36221 McPherson Kalye 36712 Herman 38995 Tran 40004 Ming 40028 Denise FIGURE 4 DOCTOR RELATION Dreifag Minh Chin Feinstein DRUG CODE AF15 AF25 DRO DRZ K015 OLE TRYP DRUG_NAME Afgapan-15 Afgopan-25 Droslene Chloride Druzocholar Cryptolene Kollabar Oxyhex Oleander-Drizapan Tryptolac Heptadimetric FIGURE 5 DRUG RELATION DOC ID PAT NUM DRUG CODE 32445 102 DRZ 32445 113 OLE 34409 101 K015 DRO 36221 109 30996 K015 FIGURE 6 PRESCRIPTION RELATION J G A F H 0 D L DRUG PRICE 25.00 35.00 111.89 18.99 65.75 123.95 79.45 Dermatology Neurology Urology Pediatrics Orthopaedics Dermatology Psychiatry Neurology Orthopaedics Gynecology PRES DOSAGE 2 tablets every four hours-50 tablets total 1 teaspoon with each meal-250 ml total 1 tablet every six hours 30 tablets total 21ablets with every meal-EC tablets total 1 tablet every six hours - 30 tablets total PRES DATE 12-Nov-09 14-Nov-09 14-Nov-09 14-Nov-03 14-Nov-09
Transcribed Image Text:DOCD DOC LNAME DOC FNAME DOC INITIAL DOC SPECIALTY 29827 Sanchez Julo 32445 Jorgensen Annelise 33456 Korenski Anatoly 33989 LeGrande George 34409 Washington Dennis 36221 McPherson Kalye 36712 Herman 38995 Tran 40004 Ming 40028 Denise FIGURE 4 DOCTOR RELATION Dreifag Minh Chin Feinstein DRUG CODE AF15 AF25 DRO DRZ K015 OLE TRYP DRUG_NAME Afgapan-15 Afgopan-25 Droslene Chloride Druzocholar Cryptolene Kollabar Oxyhex Oleander-Drizapan Tryptolac Heptadimetric FIGURE 5 DRUG RELATION DOC ID PAT NUM DRUG CODE 32445 102 DRZ 32445 113 OLE 34409 101 K015 DRO 36221 109 30996 K015 FIGURE 6 PRESCRIPTION RELATION J G A F H 0 D L DRUG PRICE 25.00 35.00 111.89 18.99 65.75 123.95 79.45 Dermatology Neurology Urology Pediatrics Orthopaedics Dermatology Psychiatry Neurology Orthopaedics Gynecology PRES DOSAGE 2 tablets every four hours-50 tablets total 1 teaspoon with each meal-250 ml total 1 tablet every six hours 30 tablets total 21ablets with every meal-EC tablets total 1 tablet every six hours - 30 tablets total PRES DATE 12-Nov-09 14-Nov-09 14-Nov-09 14-Nov-03 14-Nov-09
Expert Solution
steps

Step by step

Solved in 2 steps with 4 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