i just want you to do Few question from Part 1. thats it
Cl_AdvDb_AE_Sk_3of3 Skills assessment: Use SQL in a database Criteria Unit code, name and release number ICTDBS502 Design a Database (Release 1) ICTPRG425 Use SQL (Release 1) Qualification/Course code, name and release number ICT50118 – Diploma of Information Technology (1) Student details Student number Student name Assessment Declaration This assessment is my original work and no part of it has been copied from any other source except where due acknowledgement is made. No part of this assessment has been written for me by any other person except where such collaboration has been authorised by the assessor concerned. I understand that plagiarism is the presentation of the work, idea or creation of another person as though it is your own. Plagiarism occurs when the origin of the material used is not appropriately cited. No part of this assessment is plagiarised. Student signature and Date Document titleVersion 1.0Page 1 of 3 Disclaimer: Printed copies of this document are regarded as uncontrolled. Please check to ensure this is the latest version. © 2011 Department of Education and Communities, TAFE NSW eLearning Hub | Version: 0.0 | Created: dd/mm/2011 Document title: Cl_AdvDb_AE_Sk_3of3Page 1 of 25 Resource ID: TBS_19_018_Cl_AdvDb_AE_Sk_3of3STUDENT NAME: Version:20200204 Date created:31 January 2020 Date modified:04 February 2020 For queries, please contact: Technology and Business Services Ultimo © 2020 TAFE NSW, Sydney RTO Provider Number 90003 | CRICOS Provider Code: 00591E This assessment can be found in the: Learning Bank The contents in this document is copyright © TAFE NSW 2020, and should not be reproduced without the permission of the TAFE NSW. Information contained in this document is correct at time of printing: 15 June 2021. For current information please refer to our website or your teacher as appropriate. Document titleVersion 1.0Page 1 of 3 Disclaimer: Printed copies of this document are regarded as uncontrolled. Please check to ensure this is the latest version. © 2011 Department of Education and Communities, TAFE NSW eLearning Hub | Version: 0.0 | Created: dd/mm/2011 Assessment instructions Table 1 Assessment instructions Assessment details Instructions Assessment overview The objective of this assessment is to assess your skills as required to use a structured query language. Assessment Event number 3 of 3 Instructions for this assessment This is a skill-based assessment and will be assessing you on your ability to demonstrate skills required in the unit. This assessment is in six parts: 1. Select data 2. Use functions 3. Use aggregation and multiple tables 4. Use sub-queries 5. Use views and stored procedures 6. Creating and manipulating tables. The assessment also contains: Assessment Checklist 1-6. Assessment Feedback. Submission instructions On completion of this assessment, you are required to upload it or hand it to your assessor for marking. Ensure you have written your name at the bottom of each page of this assessment. Submit the following documents for each part: Part 1: Select data · Script file named yourFirstname_yourSurname_SelectData.sql Part 2: Use functions · Script file named yourFirstname_yourSurname_Function.sql.sql Part 3: Use aggregation and multiple tables · Script file named yourFirstname_yourSurname_AggMultipleTables.sql Part 4: Use sub-queries · Script file named yourFirstname_yourSurname_SubQueries.sql Part 5: Use views and stored procedures · Script file named yourFirstname_yourSurname_ViewsSP.sql Part 6: Creating and manipulating tables · Script file named yourFirstname_yourSurname_CreateTables.sql. It is important that you keep a copy of all electronic and hardcopy assessments submitted to TAFE and complete the assessment declaration when submitting the assessment. What do I need to do to achieve a satisfactory result? To successfully complete this assessment the student will be available at the arranged time to complete all the assessment criteria as outlined in the assessment instructions. All parts of the observable task must be performed to a satisfactory level as indicated in the criteria section of the Assessment Checklists. All oral questions must be answered correctly to be deemed satisfactory in this assessment task; however, Assessors may ask questions to clarify understanding. Assessment conditions Assessment conditions will be safe and replicate the workplace. Noise levels, production flow, interruptions and time variances must be typical of those experienced in the database field of work. Assessment may be undertaken in normal classroom conditions, which is assumed to be noisy and similar to workplace conditions, or within the workplace. This may include phones ringing, people talking and other interruptions. What do I need to provide? USB drive or other storage method with enough free space to save work to. What will the assessor provide? Access to the Learning Management System Tools, equipment and materials Industry database software packages Network or other system for remote or multi-user access. Due date/time allowed/venue Indicative time to complete assessment: Three hours Assessment location This assessment will be completed in the classroom. The student may access their referenced text, learning notes and other resources. Supervision This is a supervised assessment. Reasonable adjustment If you have a permanent or temporary condition that may prevent you from successfully completing the assessment event(s) in the way described, you should talk to your assessor about ‘reasonable adjustment’. This is the adjustment of the way you are assessed to take into account your condition, which must be approved BEFORE you attempt the assessment. Assessment feedback, review or appeals In accordance with the TAFE NSW policy Manage Assessment Appeals, all students have the right to appeal an assessment decision in relation to how the assessment was conducted and the outcome of the assessment. Appeals must be lodged within 14 working days of the formal notification of the result of the assessment. If you would like to request a review of your results or if you have any concerns about your results, contact your Teacher or Head Teacher. If they are unavailable, contact the Student Administration Officer. Contact your Head Teacher for the assessment appeals procedures at your college/campus. Specific task instructions You must carefully read all the instructions and task details before commencing this assessment. The code you develop in response to the instructions and the criteria in the tasks and activities below will be used by the assessor to determine if you have satisfactorily completed this assessment event. Use these instructions and criteria as a guide to ensure you demonstrate the required skills and knowledge. Scenario Innovative Conference & Incentive Management (ICIM) is a company that organises conferences and incentives for corporate clients. They have over 300 corporate clients around Australia and employ up to 150 staff members, as well as contractors, who work onsite at conferences. You are employed as a database developer with ICIM. Before commencing this assessment, you must execute an SQL script (provided by your assessor) on an appropriate Relational Database Management System to create the InnovationEvents database. Use this database to create and run the SQL statements in this assessment. Naming conventions The following are general naming conventions that must be adhered to: Use schema names for table prefixes, e.g. schemaName.table_name. Primary Key suffixed with _ID, e.g. customer_ID. Foreign Key suffixed with _REF, e.g. customer_REF. No spaces or dashes (-) in database object names (identifiers), e.g. schemas, tables, columns, constraints. Constraint object names: · Primary Key: tableName_PK · Foreign Key: child_tableName_parent_tableName_FK · Check: constraintName_CHK, where the constraintName includes semantic meaning indicating the purpose of the check constraint. SQL keywords and other system identifiers should all be uppercase. Part 1: Select data Refer to Assessment Checklist 1 for the criteria for this part. Write SQL statements to display each of the required results listed below. Your SQL statements must use the correct syntax and be logical, succinct and accurate. Note: You will create one script file that includes all the queries for this part. Save your script file as yourFirstname_yourSurname_SelectData.sql. You must include each of the following items in your queries, where relevant (at least once each, unless indicated otherwise): Order by clause Criteria in the where clause using BETWEEN, IN and LIKE operators Comparison operators in the where clause (at least twice) Boolean operators (at least twice). Required results 1. All data stored for attendees, in alphabetical order of last name, then first name. The event name and event fee for all events that cost more than $200. Registrations that were created after 01 August 2018 and before 31 August 2018, and where the fees were at least $300 or less than $100. The first and last name for attendees who have a company listed. The first name of attendees who have a first name starting with ‘A’. The event name of each event that has a start date after 1st December 2019. Do not list any duplicate names. The first and last name for all attendees who do not have a title listed. The first name, last name and suburb of attendees who live in Richmond, Picton or Kellyville. All data for attendees who have the title of ‘Ms’ or a gender of ‘F’. Part 2: Use functions Refer to Assessment Checklist 2 for the criteria for this part. Write SQL statements using functions to display each of the required results listed below. Your SQL statements must use the correct syntax and be logical, succinct and accurate. Note: You will create one script file that includes all the queries for this part. Save your script file as yourFirstname_yourSurname_Function.sql. You must include the following actions in your queries: String functions with operators (at least twice) Mathematical functions (at least twice) Date functions (at least twice) Required results 1. The Attendee_ID, Event_ID, registration date and a calculated field called FinalRegDate, which is three weeks after the RegistrationDate. 1. The first name of each attendee in lower case and the last name in upper case for attendees whose postcode is 3000 or more. 1. Event_ID and event name of all unconfirmed events; append the word ‘cancelled’ to the end of the listing of the event name. 1. Event Id, event name and a calculated field called EqualInstalmentAmount rounded to two decimal places, indicating the instalment amount for the event fee to be paid in four equal instalments. 1. Event name, location and the number of days that each event runs for. 1. List the attendee’s First and Last names, the event name, the Registration fee amount paid, and a derived field listing a calculated discount of 12% of the Registration Fee Paid. Part 3: Use aggregation and multiple tables Refer to Assessment Checklist 3 for the criteria for this part. Write SQL statements, using aggregation and joins for retrieving data from multiple tables, to display the required results listed below. Your SQL statements must use the correct syntax and be logical, succinct and accurate. Note: You will create one script file that includes all the queries for this part. Save your script file as yourFirstname_yourSurname_AggMultipleTables.sql. You must include the following actions in your queries (at least once each, unless indicated otherwise): Aggregate functions (at least twice) Having clause Group by clause Where clause Inner join Left outer join Right outer join Full outer join Union. Required results 1. Restrict the rows to the first