CSC72001_SampleExam_2018.docx - Word (Pro VIEW HOME INSERT DESIGN PAGE LAYOUT REFERENCES MAILINGS REVIEW Arial 11 B IU.abc x A A Aa A A..A 20 1 AaBbCcc AaBb Ccc AaBbc A - E 1 Normal 1 No Spac... Heading 1 - Copy *Format Painter pboard Font Paragraph Section C: SQL (20 marks) The following two tables are part of a Customer Management database to keep track of Customers and the Contacts that a business makes with them. CUSTOMER(Customerid, LastName. FirstName, Phone, Email) CONTACT(ContactID, Customers. ContactDate. Contact Type. FollowUp YN, Notes). Sample data for these tables is shown below. You should assume much more data is included in these tables in the completed database. Customerid LastName Downs James Frier Trent FirstName Mary Richard Liz Mike CUSTOMER Phone 02 6167 8798 02 6134 9980 02 6122 5238 02 6156 4001 Email mary.downs@blah.com richard.james@blah.com liz.frier@blah.com mike.trent@blah.com Customer FollowUpYN Notes Check order Contacted 101 102 103 104 105 106 107 CONTACT Contact Date Contact Type 2017-04-14 11:25 Email 2017-04-14 13:15 Email 2017-04-18 09:25 Phone 2017-04-19 11:15 Person 2017-04-20 14:30 Phone 2017-04-20 15:10 Email 2017-04-20 16:15 Person Email not received NA Send catalogue 8. Write a valid SQL statement that could be used to create the CONTACT table before any data is added to it. Make sensible assumptions about the attribute types and length to reflect the most likely types and lengths. (3 marks) F17 1534 WORDS E ENGLISH (UNITED STATES) Type here to search w] cbuwosti1234@gmail Find AaBbc AaBbcc Aab AaBbCD AaBbCcDd AaBbCcDd AaBbCcDd AaBbCcDc AaBCCL Heading 1 Heading 2 Title Subtitle Subtle Em... Emphasis Intense E... Strong Quote ele I a la Rep 5 Editir Styles 9. Write a valid SQL command to add a new row to the CONTACT table, using one of the rows in the sample data provided. (2 marks) 10. Write a valid SQL statement to display the LastName, FirstName and Email columns of CUSTOMER. (1 mark) 11. Write a valid SQL statement to display the number of email contacts made with customers in April 2017 (2 marks). 12. Write a valid SQL statement to display the number of customers in this database (1 mark) 13. Write a valid SQL statement to display the LastName. EastName and email of each customer, along with the date and type of contact, along with notes and any follow-up, they may have had. (3 marks) AD ENG w AaBbccc AaBBCCD AaBb C AaBbcc Add AaBBLEU AUBDULDU 1 Normal 1 No Spac... Heading 1 Heading 2 Title Subtitle Subtle Em Paragraph Styles follow-up to a contact, including the date of the (2 marks) Page 15 of 15 15. Write a valid SQL statement to display the LastName. FirstName and email of each customer, along with a calculated column which displays how long (from today) since the last contact was made. (4 marks) 16. Write a valid SQL statement to update the row for ContactID number 106, changing the value of FollowUpYN to N. (2 marks) o te wi