To create database:
CREATE DATABASE WK3ASSIGNMENT;
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)
);
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');
To create another table:
CREATE TABLE Session
(
SessionNum INT,
SessionDate VARCHAR(512),
PatientNum INT,
LengthOfSession INT,
TherapistID VARCHAR(512),
TherapyCode INT
);
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');
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
);
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');
To create Therapies table:
CREATE TABLE Therapies
(
TherapyCode INT,
Description VARCHAR(512),
UnitOfTime VARCHAR(512)
);
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');
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)