DataModule4_Assignment
docx
keyboard_arrow_up
School
University of Pittsburgh *
*We aren’t endorsed by this school
Course
2230
Subject
Medicine
Date
Apr 3, 2024
Type
docx
Pages
2
Uploaded by ChefBraveryJellyfish33
1.
Create tables Doctor, Patient, Drug, and Prescription (which one should be made
first?). (2 points)
CREATE TABLE PATIENT (
PT_SSN
INTEGER PRIMARY KEY
PT_FNAME
CHAR(10) NOT NULL
PT_LNAME
CHAR(12) NOT NULL
PT_ADDRESS
VARCHAR(20) NOT NULL
PT_CITY
CHAR(12) NOT NULL
PT_STATE
CHAR(10) NOT NULL
PT_ZIP
INTEGER DR_SSN
INTEGER PT_BIRTHDATE
DATE);
CREATE TABLE DOCTOR
(
DR_SSN
INTEGGER PRIMARY KEY
DR_FNAME
CHAR(10) NOT NULL
DR_LNAME
CHAR(12) NOT NULL
DR_SPECIALTY
CHAR(5) NOT NULL
DR_BEGINPRACDATE
DATE);
CREATE TABLE PRESCRIPTION (
PRESCRIPTION_NUM
INTEGER PRIMARY KEY
DR_SSN
INTEGER PT_SSN
INTEGER PRESCRIPTION_QTY INTEGER
PRESCRIPTION_LASTDATE
DATE
DRUG_TRADENAME
CHAR(15) NOT NULL);
CREATE TABLE DRUG (
DRUG_TRADENAME CHAR(15) PRIMARY KEY
DRUG_FORMULA
VARCHAR(12) NOT NULL);
2.
Add a new column into the Prescription table, should we want to capture the date a prescription is being made (‘PRESCRIBED ON’) with the type of DATE? (1 points)
ALTER TABLE PRESCRIPTION ADD PRESCRIPTION_PRESCRIBEDATE DATE);
3.
Insert new patient (assume you already have a doctor, with the doctor’s SSN is 123-34-4567). (2 points)
INSERT INTO PATIENT VALUES (NULL, NULL, NULL, NULL, NULL, NULL, ‘123-34-4567’, NULL);
4.
Delete a drug that has drug formula ‘PARACETAMOL’. (1 points)
DELETE FROM DRUG WHERE DRUG_FORMULA = ‘PARACETAMOL’;
5.
Change a doctor’s last name from ‘Moore’ to ‘Sena’. The doctor’s SSN is 123-34-
4567. (2 points)
UPDATE DOCTOR
SET DR_LNAME = ‘SENA’
WHERE DR_SSN = ‘123-34-4567’;
6.
List all prescriptions that are being made between 1/1/2017 and 2/1/2017 (refer to question #2 for the attribute name). (3 points)
SELECT PRESCRIPTION_PRESCRIBEDATE FROM PRESCRIPTION
WHERE PRESCRIPTION_PRESCRIBEDATE BETWEEN 1/1/2017 AND 2/1/2017;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help