Demonstrate the requirements for various data information requests from an existing database and develop appropriate SQL statements to satisfy those requirements.

Task 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.

Context

Being 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.

Task Instructions

Please 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 on providing the output as meaningful and presentable as possible. Please note, extra marks will be awarded for the presentation and readability of SQL queries including ordering of the columns

Please note all the SQL queries should be generated using a MySQL server either using MySQL workbench or MySQL Command Line Client.

Provide SQL statements and the query output for the following:

1. Questions: What is the total number of active phone numbers currently in the database?
2. Display a list of customers living in north or south Clayton.
3. List all the unique phone colors owned by the users.
4. List all the customers who are iPhone users.
5. How many employees have resigned company?
6. Display all the staff, and their supervisor details if they have one.
7. List all the staff who are supervisors.
8. On average, how many calls were made in each month of 2019? Order by month in ascending order.
9. What is the second-highest pay rate for a staff member?
10. List all the customers who are iPhone users.
11. List all the customers who have more than one phone number registered.
12. Display the average age of users based on a mobile plan.
13. Assume in 2019, the company decided to apply a 20-cent call charge for every call made, as a fixed charge regardless of the call duration, and 0.10 cents for each minute spent on a call. Assuming the call durations are in seconds,
    • How much income has the company generated through this fixed charge in dollars from 2019 onwards?
    • What is the total income generated in total for both call and fixed charges in this period?
14. List all the customers and the staff who processed their mobile registrations.
15. List the details of the staff who served the highest number of customers.
16. Evaluate the called number column in calls, and write a query to find how many of these numbers are registered in this database (mobile network).
17. List all the active phone numbers that have never made a call. Show this query using:
    • Nested Query
    • SQL Join
18. • Create a view showing the popularity of all towers based on the number of connections made
    • Use this view to generate the most popular tower location
19. Write a query to fetch the data about the first call. Include the phone number, towered, and the customer details (customer and name) on which the number was registered. Note: you may get multiple records.
20. How long has the oldest and youngest active female staff working in the company?
21. In not more than 200 words, comment on whether the tables are in 3NF. Justify your argument with relevant examples, and then explain at least two ways to improve this database based on what you have learned in weeks 1-8. Draw specific examples from the database to support your argument.

This IT Computer Science Assignment has been solved by our IT Computer Science Expert at Exam Question Bank. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well-trained to follow all marking rubrics & referencing Styles. Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turn tin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.