HIT 234 Assignment 1 – Due Date: Friday, Week 7 HIT 234 Assignment 1 Surrender Details: For Internal and External students: surrender is via Learnline. Instructions on how to resign the assignment is located lower Assessments / Guidelines. Please rejoinder all scrutinys. Part A - Unmarried and multiple regardation queries Value: 6% For the principal five scrutinys you enjoy to afford your rejoinder in the contrive of a SQL assertion, delay patronymic antecedently as compulsory. For example: 1. Example Question: Selected all columns from the `Customer` regardation Example Answer: -- This rejoinder get selecteded out all the columns from the Customer regardation SELECT * from Customer; Questions 1 – 5: Worth 3% of the sum marks 1. List in ascending ordain the Decisive Names of all mortgagors delay Card Sum near than 150. 2. What are the registers of those mortgages betwixt 3 June 2014 and 8 Mar 2018? ◦ The output should know the indicate of the cardholder, and address of the body ◦ List in ascending ordain by decisive indicate and principal indicate 3. Find the sum mass of mortgages delay decisive indicate opening delay D through E (including E); know decisive indicate opening delay Q as courteous as Z as-well. 4. Find all mortgagors for a mortgage that enjoy the Date-in antecedently 15-March-15 and the Card sum betwixt 100 and 300 in Karama or Darwin. 5. Find the sum of mortgages, which enjoy been made from each spray? ◦ Note all sprayes demand to be knowd well-balanced those delay no mortgages Scrutiny 6: Worth 3% of the sum marks HIT 234 Assignment 1 – Due Date: Friday, Week 7 6. Compose your own scrutiny fixed on the library groundsbase, and as-well afford a SQL Assertion to rejoinder your scrutiny. HIT 234 Assignment 1 – Due Date: Friday, Week 7 Part B – Consideration and Advanced Queries Value: 4% The scrutinys are domesticated into: Q1 (4 marks), Q2 (3 marks), Q3 and Q4 (3 marks). For all scrutinys, afford the rejoinder in the contrive of a SQL assertion, delay patronymic antecedently as compulsory. Questions NOTES Questions 1 and 2 demand you to rejoinder in the contrive of a SQL assertion to compose a regardation and instil grounds into it. The scrutiny and rejoinder is merely encircling the assertions to compose and put grounds into the `Occurrence_Exercise` regardation, at-last it is recommended that you compose the assertions to compose and instil grounds into all 3 regardations, so that you can ordeal that your assertions are amend. Please merely afford in your rejoinder the SQL assertion for the `Occurrence_Exercise` regardation. To lowerstand the terminology: • A rep is a sum of terms you do an violateed power. • A set is an violate and in this plight, there are 3 violates (violate 1, violate 2, violate 3). • An violate contains the power. So to lowerstand the power you would elate for a unmarried contact in the Contact violate regardation you would demand to regard the rep various by violates by the power. So for a unmarried violate: e.g. for 9001 rep various by violates by the power 8 reps x 3 violates x 15kg = 360 sum kg’s elateed 1. The aftercited Gym contact groundsbase for power treaty recording is as follows. Write and run SQL assertions for creating the Occurrence_Exercise regardation. ◦ Know employment computes for the Condition Column: Heavy, Medium, Light HIT 234 Assignment 1 – Due Date: Friday, Week 7 2. Write an SQL assertion that get instil the grounds into Occurrence_Exercise regardation merely. ◦ Definition of rep stands for diffuseness. ◦ Each contact violate has the rep sum as involved in the adventure regardation. Adventure Consideration OccurrenceID Occ_Date Occ_Place Occ_Start_Time Occ_Finish_Time 1 14/03/18 University_Gym 16:00:00 16:30:00 2 15/03/18 University_Gym 16:30:00 17:00:00 3 16/03/18 University_Gym 16:10:00 17:20:00 4 17/03/18 Home 07:00:00 08:00:00 5 17/03/18 University_Gym 17:00:00 18:00:00 Occurrence_Exercise Consideration OccExID ExerciseNo OccurrenceID Rep Attempt1 Attempt2 Attempt3 Condition 9001 001A 1 8 15 15 15 Light 9002 002A 1 8 70 75 70 Medium 9003 003A 2 12 90 95 90 Heavy 9004 004A 3 12 200 200 200 Medium 9005 006A 3 8 110 110 115 Heavy 9006 005A 4 10 20 25 Medium 9007 001A 4 8 10 12 10 Medium 9008 002A 5 8 85 90 115 Heavy Contact Consideration ExerciseNo ExerciseDescription ExerciseType 001A Situps Abs 002A Bench Press Chest 003A Dead Lifts Back 004A Leg Press Legs 005A Seated Shoulder Press Shoulders 006A Squats Legs HIT 234 Assignment 1 – Due Date: Friday, Week 7 007A Lat draw downs Lats Compose the aftercited queries: 3. Find the contact which contains the consummation sum power elateed ◦ Show: Exercise, Consummation power ◦ Ensure you demonstration captions for each of the columns ◦ Hint: you would demand to regard the sum of terms the power is elateed and power e.g. rep various by violate various by the power 4. Find the contact that the user was not conducive to accomplished 3 violates in, or the contact that the user has not violateed. ◦ Demonstration mismisappropriate columns ◦ List in ascending ordain contact fashion ◦ Do not demonstration repeated indicates ◦ Ensure you demonstration captions for each of the columns HIT 234 Assignment 1 – Due Date: Friday, Week 7 Part C – All Topics Value: 10% For the principal indecent scrutinys attribute to the Library Database. For scrutiny 5 attribute to CDU Customer Invoice Database. The scrutinys are domesticated into: Q1-4 (6 marks) and Q5 (4 marks). For all scrutinys, afford the rejoinder in the contrive of a SQL assertion, delay patronymic antecedently as compulsory. Questions Using the Library Database 1. What are the 5 most liked bodys? ◦ Demonstration ASBN, Title. ◦ HINT: To boundary results use ROWNUM. 2. Which sprayes currently enjoy no mortgages out? ◦ Demonstration all spray indicates. ◦ List in ascending ordain by spray. 3. Find the longest term a body has been mortgageed out for each spray ◦ Demonstration merely spray indicate and sum of days. ◦ List in ascending ordain by spray indicate. ◦ Demonstration all sprayes, well-balanced if there are no mortgageed out bodys. Therefore, a spray may enjoy a void compute for the longest loaded out body. ◦ Format the sum of days to one decimal attribute. 4. Find the mortgagor that has returned a body on the dated 16 December 2010 and 8 May 2012 ◦ Demonstration two opposed methods. Scrutiny Using the CDU Customer Invoice Database 5. Devise a delete question, which get enucleate likenesss for customer columns `ID`, `CustomerLastName`, `CustomerFirstName` and invoice columns `Invoice_Date` and `Invoice_No`. ◦ Ensure the question removes all likeness grounds.