On SQL,

Using Sport Database:

IF DB_ID (N'Sports') IS NOT NULL DROP DATABASE Sports; GO CREATE DATABASE Sports; GO USE Sports;

CREATE TABLE Patient ( PatientNum CHAR(4) PRIMARY KEY, LastName VARCHAR(25), FirstName VARCHAR(25), Address VARCHAR(30), City VARCHAR(35), State CHAR(2), ZipCode CHAR(5), Balance DECIMAL(8,2) );

CREATE TABLE Session ( SessionNum CHAR(3) PRIMARY KEY, SessionDate DATE, PatientNum CHAR(4), LengthOfSession INT, TherapistID CHAR(5), TherapyCode INT );

CREATE TABLE Therapies ( TherapyCode INT PRIMARY KEY, Description VARCHAR(200), UnitOfTime INT );

CREATE TABLE Therapist ( TherapistID CHAR(5) PRIMARY KEY, LastName VARCHAR(25), FirstName VARCHAR(25), Street VARCHAR(30), City CHAR(35), State CHAR(2), ZipCode CHAR(5) );

INSERT INTO Patient VALUES ('1010','Koehler','Robbie','119 West Bay Dr.','San Vista','TX','72510',1535.15) ; INSERT INTO Patient VALUES ('1011','King','Joseph','941 Treemont','Oak Hills','TX','74081',212.80) ; INSERT INTO Patient VALUES ('1012','Houghland','Susan','7841 Lake Side Dr.','Munster','TX','72380',1955.40) ; INSERT INTO Patient VALUES ('1013','Falls','Tierra','44 Applewood Ave.','Palm Rivers','TX','72511',1000.35) ; INSERT INTO Patient VALUES ('1014','Odepaul','Ben','546 WCR 150 South','Munster','TX','74093',525.00) ; INSERT INTO Patient VALUES ('1015','Venable','Isaiah','37 High School Road','Waterville','TX','74183',432.30) ; INSERT INTO Patient VALUES ('1016','Waggoner','Brianna','2691 Westgrove St.','Delbert','TX','72381',714.25) ; INSERT INTO Patient VALUES ('1017','Short','Tobey','1928 10th Ave.','Munster','TX','72512',967.60) ; INSERT INTO Patient VALUES ('1018','Baptist','Joseph','300 Erin Dr.','Waterville','TX','76658',1846.75) ; INSERT INTO Patient VALUES ('1019','Culling','Latisha','4238 East 71st St.','San Vista','TX','74071',1988.50) ; INSERT INTO Patient VALUES ('1020','Marino','Andre','919 Horton Ave.','Georgetown','TX','72379',688.95) ;

INSERT INTO Patient VALUES ('1021','Wilson','Tammy','424 October Blvd.','Waterville','TX','76658',2015.30) ;

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

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

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

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Create the query for each question and run it in SQL Server Management Studio. Check and fix any syntax errors.

Reminder: The USE Sports; statement goes at the top of the query editor in SQL Server Management Studio.

1. Create a Query to list the patient Number, first Name and last name for all patients. The first name should appear in uppercase letters and the last name should appear in lower case letters. Use a function for the case conversion. The first name and last name should appear in one column.

Hint: Use a Concat() function.

2. Create a query to list the Session Number, Patient Number, and the length of time in days between the Session Date and 2018-10-31.

3. Change the query above and add a field that is 90 days from the session date.

Hint: DateAdd

4. Create a stored procedure displays the Patient number, Session number, Session date, Therapist ID and Therapy code using the Patient number as a variable input. Name the stored procedure Patient_Sessions.

5. Execute the stored procedure using a Patient number

6. Create a Stored Procedure with a cursor that displays the Patient Number, Session Number, Therapist ID and the Description of the therapy using the Length of Session as a variable input. Name the stored procedure Session_Length.

7. Execute the stored procedure named Session_Length for all sessions that have a session length of 60 minutes or more.

8. Create a stored procedure named New_Session that inserts a new Session record.

9. Use the New_Session stored procedure to execute the query below with error catching code.

Use the values 38,'2018-10-20', '1016', 45, 'AS648', 97112 to insert a new record. Include the following Error catching code.

BEGIN TRY, END TRY

BEGIN CATCH

PRINT 'An error has occurred.';

PRINT 'Message: ' + CONVERT(VARCHAR(200),ERROR_MESSAGE());

IF ERROR_NUMBER() >= 50000

PRINT 'This is a custom error message';

10. Sports Therapy is increasing its business. Each time a patient completes a new session they are charged $60.00. Create a Trigger to update the Patient table each time a new Session record is added.

Update Balance to reflect the new charge.

11. Add the following record to the Session table.

'39','2018-10-20', '1016', 45, AS648', 97112

List all records from the Patient table.