TyroCity

DATA 210 Database Design & Analytics profile image Angel Paudel
Angel Paudel for DATA 210 Database Design & Analytics

Posted on

WK3 Assignment

#bsit

To create database:

CREATE DATABASE WK3ASSIGNMENT;
Enter fullscreen mode Exit fullscreen mode

To create Patient table:

CREATE TABLE Patient 
(
    PatientNum  INT,
    LastName    VARCHAR(512),
    FirstName   VARCHAR(512),
    Address VARCHAR(512),
    City    VARCHAR(512),
    State   VARCHAR(512),
    ZipCode INT,
    Balance VARCHAR(512)
);
Enter fullscreen mode Exit fullscreen mode

To input data into Patient table:

INSERT INTO Patient (PatientNum, LastName, FirstName, Address, City, State, ZipCode, Balance) VALUES
    ('1010', 'Koehler', 'Robbie', '119  West Bay Dr.', 'San  Vista', 'TX', '72510', '1,535.15 '),
    ('1011', 'King', 'Joseph', '941  Treemont', 'Oak  Hills', 'TX', '74081', '212.80 '),
    ('1012', 'Houghland', 'Susan', '7841  Lake  Side Dr.', 'Munster', 'TX', '72380', '1,955.40 '),
    ('1013', 'Falls', 'Tierra', '44 Applewood Ave.', 'Palm  Rivers', 'TX', '72511', '1,000.35 '),
    ('1014', 'Odepaul', 'Ben', '546  WCR 150  South', 'Munster', 'TX', '74093', '525.00 '),
    ('1015', 'Venable', 'Isaiah', '37 High School Road', 'Waterville', 'TX', '74183', '432.30 '),
    ('1016', 'Waggoner', 'Brianna', '2691  Westgrove St.', 'Delbert', 'TX', '72381', '714.25 '),
    ('1017', 'Short', 'Tobey', '1928  10th Ave.', 'Munster', 'TX', '72512', '967.60 '),
    ('1018', 'Baptist', 'Joseph', '300  Erin  Dr.', 'Waterville', 'TX', '76658', '1,846.75 '),
    ('1019', 'Culling', 'Latisha', '4238  East  71st  St.', 'San  Vista', 'TX', '74071', '1,988.50 '),
    ('1020', 'Marino', 'Andre', '919  Horton Ave.', 'Georgetown', 'TX', '72379', '688.95 '),
    ('1021', 'Wilson', 'Tammy', '424  October Blvd.', 'Waterville', 'TX', '76658', '2,015.30');
Enter fullscreen mode Exit fullscreen mode

To create another table:

CREATE TABLE Session 
(
    SessionNum  INT,
    SessionDate VARCHAR(512),
    PatientNum  INT,
    LengthOfSession INT,
    TherapistID VARCHAR(512),
    TherapyCode INT
);
Enter fullscreen mode Exit fullscreen mode

And, to input data:

INSERT INTO Session (SessionNum, SessionDate, PatientNum, LengthOfSession, TherapistID, TherapyCode) VALUES
    ('27', '10/10/2021', '1011', '45', 'JR085', '92507'),
    ('28', '10/11/2021', '1016', '30', 'AS648', '97010'),
    ('29', '10/11/2021', '1014', '60', 'SW124', '97014'),
    ('30', '10/12/2021', '1013', '30', 'BM273', '97033'),
    ('31', '10/15/2021', '1016', '90', 'AS648', '98960'),
    ('32', '10/16/2021', '1018', '15', 'JR085', '97035'),
    ('33', '10/17/2021', '1017', '60', 'SN852', '97039'),
    ('34', '10/17/2021', '1015', '45', 'BM273', '97112'),
    ('35', '10/18/2021', '1010', '30', 'SW124', '97113'),
    ('36', '10/18/2021', '1019', '75', 'SN852', '97116'),
    ('37', '10/19/2021', '1020', '30', 'BM273', '97124'),
    ('38', '10/19/2021', '1021', '60', 'AS648', '97535');
Enter fullscreen mode Exit fullscreen mode

To create Therapist table:

CREATE TABLE Therapist 
(
    TherapistID VARCHAR(512),
    LastName    VARCHAR(512),
    FirstName   VARCHAR(512),
    Street  VARCHAR(512),
    City    VARCHAR(512),
    State   VARCHAR(512),
    ZipCode INT
);
Enter fullscreen mode Exit fullscreen mode

To insert data into the table:

INSERT INTO Therapist (TherapistID, LastName, FirstName, Street, City, State, ZipCode) VALUES
    ('AS648', 'Shields', 'Anthony', '5222  Eagle Court', 'Palm  Rivers', 'TX', '72511'),
    ('BM273', 'McClain', 'Bridgette', '385  West Mill St.', 'Waterville', 'TX', '76658'),
    ('JR085', 'Risk', 'Jonathan', '1010  650  North', 'Palm  Rivers', 'TX', '72511'),
    ('SN852', 'Nair', 'Saritha', '25 North Elm St.', 'Livewood', 'TX', '72512'),
    ('SW124', 'Wilder', 'Steven', '7354  Rockville Road', 'San  Vista', 'TX', '72510');
Enter fullscreen mode Exit fullscreen mode

To create Therapies table:

CREATE TABLE Therapies 
(
    TherapyCode INT,
    Description VARCHAR(512),
    UnitOfTime  VARCHAR(512)
);
Enter fullscreen mode Exit fullscreen mode

To insert data into it:

INSERT INTO Therapies (TherapyCode, Description, UnitOfTime) VALUES
    ('90901', 'Biofeedback training by any modality', ''),
    ('92240', 'Shoulder strapping', ''),
    ('92507', 'Treatment of speech', '15'),
    ('92530', 'Knee strapping', ''),
    ('92540', 'Ankle and/or foot strapping', ''),
    ('95831', 'Extremity or trunk muscle testing', ''),
    ('97010', 'Hot or cold pack application', ''),
    ('97012', 'Mechanical traction', ''),
    ('97014', 'Electrical stimulation', ''),
    ('97016', 'Vasopneumatic devices', ''),
    ('97018', 'Paraf n bath', ''),
    ('97022', 'Whirlpool', ''),
    ('97026', 'Infrared', ''),
    ('97032', 'Electrical stimulation', '15'),
    ('97033', 'Iontophoresis', '15'),
    ('97035', 'Ultrasound', '15'),
    ('97039', 'Unlisted  modality', '15'),
    ('97110', 'Therapeutic exercises to develop  strength and endurance, range of motion, and   exibility', '15'),
    ('97112', 'Neuromuscular re-education of movement, balance, coordination, etc.', '15'),
    ('97113', 'Aquatic  therapy with therapeutic exercises', '15'),
    ('97116', 'Gait training', '15'),
    ('97124', 'Massage', '15'),
    ('97139', 'Unlisted  therapeutic procedure', ''),
    ('97140', 'Manual therapy techniques', '15'),
    ('97150', 'Group  therapeutic procedure', '15'),
    ('97530', 'Dynamic activities to improve functional performance, direct (one-on-one) with the patient', '15'),
    ('97535', 'Self-care/home management training', '15'),
    ('97750', 'Physical performance test  or measurement', '15'),
    ('97799', 'Unlisted  physical medicine/rehabilitation service  or procedure', ''),
    ('98941', 'CMT of the spine', ''),
    ('98960', 'Education and training for patient self-management', '30');
Enter fullscreen mode Exit fullscreen mode

P.S. in each of the cases - it's down to your personal discretion to choose the data types for the values and make alterations to them as is required for your case. These data types provided are just placeholder and as such you're recommended to update those as is best.

Questions to answer based on it:

-- 1. List all the information in the patient’s table sorted by city.

-- 2. List the last names of patients whose balance is greater than 1,000.

-- 3. List the city, last name, and balance of patients who live in Waterville and have a balance greater than 1,000.

-- 4. List the last name, city, and balance of patients who live in Waterville or have a balance greater than $2,000.

-- 5. Increase the balance by 2% of any patient whose balance is greater than $2,000.

-- 6. List the session dates and numbers for those sessions scheduled between 10/18/2021 and 10/21/2021.

-- 7. List the full name of the therapist scheduled to work on 10/16/2021.

-- 8. How long are the therapy sessions for the therapist whose last name is Shields? List the length of each session.

-- 9. Which therapies have the word “movement” in their description? List the therapy description only.

-- 10. How many therapies are offered? Answer with one number only.

-- 11. Write an SQL query that displays the therapies and their unit of time for the therapies that include the word bath, hot, or electrical.

-- 12. Write an SQL query to display every therapist’s first name and last name as their full name, but only for those instructors not living in zip code 72511.

Top comments (0)