A pharmacy database tracks prescriptions written by doctors and purchased by patients at pharmacy locations. A prescription is written by a doctor for one or more drugs for exactly one patient. The prescription also specifies the pharmacy where the prescription is to be filled. The doctors identifier is called the National Provider Identifier (NPI). Every drug is identified by a National Drug Code (NDC). A prescription contains the following information. The NPI, NDC, date the prescription was written, and the patients identifier. Write a qeury that shows the prescription number, patient's name, the brand name for the drug, dosage, doctor's city for all prescriptions filled by a doctor whose NPI IS AT20030040 and written on or after January 1, 2022. The database tables are designed as shown below. You do not need to create the database to answer this question but you might if you want to visualize the connections. CREATE TABLE DRUG ( NDC VARCHAR(20) NOT NULL, DRUG_BRAND_NAME VARCHAR(25) NOT NULL, DRUG_GENERIC_NAME VARCHAR(25) NOT NULL, PACKAGE_SIZE INT NOT NULL, DOSAGE VARCHAR(50), CONSTRAINT D_PK PRIMARY KEY (NDC)); CREATE TABLE PATIENT ( PATIENT_NUMBER VARCHAR(25) NOT NULL, FIRST_NAME VARCHAR(25) NOT NULL, LAST_NAME VARCHAR(25) NOT NULL, GENDER_CODE VARCHAR(2) NOT NULL, DATE_OF_BIRTH DATE NOT NULL, ADDRESS1 VARCHAR(100), ADDRESS2 VARCHAR(100), CITY VARCHAR(25), STATE VARCHAR(25), ZIP_CODE VARCHAR(25), CONSTRAINT PA_PK PRIMARY KEY (PATIENT_NUMBER)); CREATE TABLE DOCTOR ( NPI INT NOT NULL, FIRST_NAME VARCHAR(25) NOT NULL, LAST_NAME VARCHAR(25) NOT NULL, ADDRESS1 VARCHAR(100), ADDRESS2 VARCHAR(100), CITY VARCHAR(25), STATE VARCHAR(25), ZIP_CODE VARCHAR(25), PHONE_NUMBER VARCHAR(25) NOT NULL, FAX_NUMBER VARCHAR(25) NOT NULL, CONSTRAINT DO_PK PRIMARY KEY (NPI)); CREATE TABLE PRESCRIPTION ( PRESCRIPTION_NO INT NOT NULL, NPI VARCHAR(25) NOT NULL, PATIENT_NO VARCHAR(25) NOT NULL, NDC VARCHAR(20) NOT NULL, DATE_WRITTEN DATE NOT NULL, PHARMACY_ID INT NOT NULL, CONSTRAINT P_PK PRIMARY KEY (PRESCRIPTION_NO), CONSTRAINT P_PA_FK FOREIGN KEY (PATIENT_NO) REFERENCES PATIENT (PATIENT_NUMBER), CONSTRAINT P_DO_FK FOREIGN KEY (NPI) REFERENCES DOCTOR (NPI), CONSTRAINT P_DR_FK FOREIGN KEY (NDC) REFERENCES DRUG (NDC));
A pharmacy
Write a qeury that shows the prescription number, patient's name, the brand name for the drug, dosage, doctor's city for all prescriptions filled by a doctor whose NPI IS AT20030040 and written on or after January 1, 2022.
The database tables are designed as shown below. You do not need to create the database to answer this question but you might if you want to visualize the connections.
CREATE TABLE DRUG (
NDC VARCHAR(20) NOT NULL,
DRUG_BRAND_NAME VARCHAR(25) NOT NULL,
DRUG_GENERIC_NAME VARCHAR(25) NOT NULL,
PACKAGE_SIZE INT NOT NULL,
DOSAGE VARCHAR(50),
CONSTRAINT D_PK PRIMARY KEY (NDC));
CREATE TABLE PATIENT (
PATIENT_NUMBER VARCHAR(25) NOT NULL,
FIRST_NAME VARCHAR(25) NOT NULL,
LAST_NAME VARCHAR(25) NOT NULL,
GENDER_CODE VARCHAR(2) NOT NULL,
DATE_OF_BIRTH DATE NOT NULL,
ADDRESS1 VARCHAR(100),
ADDRESS2 VARCHAR(100),
CITY VARCHAR(25),
STATE VARCHAR(25),
ZIP_CODE VARCHAR(25),
CONSTRAINT PA_PK PRIMARY KEY (PATIENT_NUMBER));
CREATE TABLE DOCTOR (
NPI INT NOT NULL,
FIRST_NAME VARCHAR(25) NOT NULL,
LAST_NAME VARCHAR(25) NOT NULL,
ADDRESS1 VARCHAR(100),
ADDRESS2 VARCHAR(100),
CITY VARCHAR(25),
STATE VARCHAR(25),
ZIP_CODE VARCHAR(25),
PHONE_NUMBER VARCHAR(25) NOT NULL,
FAX_NUMBER VARCHAR(25) NOT NULL,
CONSTRAINT DO_PK PRIMARY KEY (NPI));
CREATE TABLE PRESCRIPTION (
PRESCRIPTION_NO INT NOT NULL,
NPI VARCHAR(25) NOT NULL,
PATIENT_NO VARCHAR(25) NOT NULL,
NDC VARCHAR(20) NOT NULL,
DATE_WRITTEN DATE NOT NULL,
PHARMACY_ID INT NOT NULL,
CONSTRAINT P_PK PRIMARY KEY (PRESCRIPTION_NO),
CONSTRAINT P_PA_FK FOREIGN KEY (PATIENT_NO) REFERENCES PATIENT (PATIENT_NUMBER),
CONSTRAINT P_DO_FK FOREIGN KEY (NPI) REFERENCES DOCTOR (NPI),
CONSTRAINT P_DR_FK FOREIGN KEY (NDC) REFERENCES DRUG (NDC));
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
