Create a view that shows the popularity of the plans

Reference no: EM133118850 , Length: word count:2000

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.

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 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 postcode
3030 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 the
conditions 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 Compiling
1. 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.

Referencing
It is essential that you use appropriate APA style for citing and referencing research.

Attachment:- Database Implementation_Brief.rar

Reference no: EM133118850

Questions Cloud

What is the overall payout : a) You want to add positions in a 1-year K = 50 call option such that your overall payout at t = 1 will be the same for any value S1 = 50 (In other words, the p

Define cost pool, cost allocation, and cost-allocation base : Why should managers worry about product overcosting or undercosting? (35-50 words) Define cost pool, cost allocation, and cost-allocation base

Price of a three-year risk-free bond with face value : What is the price of a three-year risk-free bond with face value of $100 and coupon rate of 3.5%

What is the current yield : A $1,000 bond with a coupon rate of 6.5% has a market price of $950. What is the current yield?

Create a view that shows the popularity of the plans : Create a view that shows the popularity of the plans based on number of plans sold and How many staff will be affected? Show your SQL query

What is the stock annual arithmetric average return : A stock's rate of return in year 1 is 0.1, in year 2 is 0.3, and in year 3 is 0.2. What is the stock annual arithmetric average return?

Compute the weighted average accumulated expenditures : On January 1, 2017, Groover borrowed $1,500,000 from First State bank specifically for this construction, Compute the weighted average accumulated expenditures

Difference between perpetual swap and margin trading : The difference between Perpetual swap and Margin trading.

Describe at least two time series models : List and describe at least two time series models giving an example of the type of business that might find each of the models you discuss beneficial.

User Account

All Pages