Question

Please answer the question following this tables. Use this tables to answer database management questions.

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

2. List the complete Session table (all rows and all columns).

4. List the last name and first name of every therapist not located in Palm Rivers.

6. List the session number and patient number for every therapy that lasted 60 minutes.

8. List the TherapyCode and description for all therapies that are billed in 15-minute units. Sort them in order by description.

10. Currently, therapists are paid at the rate of $35 per billable hour. List the therapist’s last name, first name, and estimated pay for every scheduled therapy. Create a new calculated column named “EstimatedPay” for the estimated pay.

12. List every therapy description performed by Bridgette McClain. Sort the results by description.

14. Calculate the average session time of all sessions performed in October. ]

16. List the patient number and length of session for each patient with the TherapyCode of 97535.

18. Make a table named CurrentBilling. Create a new calculated field named BillableUnits (LengthOfSession / UnitOfTime). Add the following fields: PatientNum, TherapyCode, SessionDate, LengthOfSession, UnitOfTime, and BillableUnits. (Note: the BillableUnits field will be blank for those records with no UnitOfTime.)

Answer 1

2. Select * from Session;

4. Select FirstName , LastName from Therapist where NOT city = 'Palm Rivers';

6. Select SessionNum , PatientNum from Session where LengthOfSession =60;

8. Select TherapyCode , Description from Therapies where UnitOfTime =15 ORDER BY Description;

10. Select FirstName , LastName from Therapist inner join session on Therapist.TherapistID =session.TherapistID .

12. Select Description from Therapies where TherapyCode in (Select Therapy code from Session where TherapistID is (Select TherapistID from Therapist where FirstName = "Bridgette" and LastName="McClain"));

14. Select AVG(LengthOfSession) from Session where (Select month (sessionDate) as month where month = 10 )

16 Select PatientNum , LengthOfSession from Session where TherapyCode = 97535

18. CREATE TABLE CurrentBilling AS (Select PatientNum,TherapyCode,SessionDate, LengthOfSession, UnitOfTime  from Session inner join Therapies on Session.TherapyCode =Therapies.TherapyCode) ;

ALTER TABLE CurrentBilling ADD BillableUnits int where BillableUnits =LengthOfSession/UnitOfTime;