Create a view that shows the popularity of the plans based

Assignment Detail:- MIS602 Data Modelling and Database Design - Torrens University Australia Assessment - Database Implementation Learning Outcome 1: Design solutions applying relational database techniques to complex problems and communicate these solutions to all stakeholders- Part Summary In this assessment, you are required to demonstrate your ability to understand the requirements for various data information requests from an existing database and develop appropriate SQL statements to satisfy those requirements- ContextBeing able to query a database is a fundamental skill that is required by all information systems professionals who work with relational databases- In this assessment, you will utilize the basic query skills that are typically used to extract information for analysis, reporting and data cleansing in a data management setting- Timely provisioning of key business information promotes effective communication and enhanced solution delivery- Part InstructionsPlease read and examine carefully the attached MIS602_Assessment 2_Data Implementation_ Case study and then derive the SQL queries to return the required information- Your focus should be providing the output as meaningful and presentable possible- Please note extra marks will be awarded for presentation and readability of SQL queries- Please note all the SQL queries should be generated using MySQL server either using MySQL workbench or MySQL Command Line Client- Provide SQL statements and the query output for the following: 1 Find all the customers whose surname or given name contains the string ‘IND'- 2 Find the total number of female staffs currently working in the company???? 3 List all the staff who have resigned after 2018 who were living in any part of CARLTON- 4 List all the staff who gets a pay rate below average pay rate???? 5 Find the supervisor name of the youngest staff- 6 List the most popular plan- If there are more plans -ties-, you should display both- 7 List the total number of plans sold by each staff including their name- 8 List the customer id, name, phone number and the details of the staff who sold the plan to the customer???? 9 List the all the staff -staffid and name- who are active not having any supervisor assigned- 10 How many calls were made in total during the weekends of 2019???? 11 The company is considering giving a 10% increase in payrate to all the staff joined before 2012--a- How many staff will be affected???? Show your SQL query to justify your answer--b- What SQL will be needed to update the database to reflect the pay rate increase???? 12 Which tower -Towerid, Location- was used by the customer 20006 to make his/her first call- 13 List all the unique customers -CustomerId, name- having a same colour phone as CustomerId 20008- 14 List the CustomerID, Customer name, phone number and the total duration customer was on the phone during the month of August, 2019 from each phone number the customer owns- Order the list from highest to the lowest duration- 15 i- Create a view that shows the popularity of the plans based on number of plans sold-ii- Use this view in a query to determine the most popular plan- 16 List all the plans and total number of active phones on each plan- 17 Write an SQL query to join all the seven tables presented in this database taking at least two columns from each table- 18 List the details of the youngest customer -CustomerId, name, dob, postcode- in postcode3030 along with total time spent on calls in minutes- Assume call durations are in seconds- 19 After evaluating all the tables, explain with reasons whether any of the tables violate theconditions of 3rd Normal Form- 20 In not more 200 words, explain at least two ways to improve this database based on what we have learned in 1st - 8th Week- Document Compiling1- The database used for this assignment is available in ‘MIS602_Assessment 2_Database SQL Files'folder- Please download the files from this folder- 2- Copy and paste all the SQL questions into a word document- Under each question provide the corresponding SQL query, the first 10 lines of the result and the output as evidence of running the query- Submit the word document via the Assessment link in the main navigation menu- ReferencingIt is essential that you use appropriate APA style for citing and referencing research- Attachment:- Database Implementation_Brief-rar

Most Recent Questions


Helping Students for Excellence in Academics, GET Help with Assignment? Order Now