Database and develop appropriate SQL statements

Assignment Help Data Structure & Algorithms

Reference no: EM133107439

MIS602 Data Modelling and Database Design - Torrens University Australia

Assessment: Database Implementation

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:

Question 1 Find all the customers whose surname or given name contains the string ‘IND'.

Question 2 Find the total number of female staffs currently working in the company?

Question 3 List all the staff who have resigned after 2018 who were living in any part of CARLTON.

Question 4 List all the staff who gets a pay rate below average pay rate?

Question 5 Find the supervisor name of the youngest staff.

Question 6 List the most popular plan. If there are more plans (ties), you should display both.

Question 7 List the total number of plans sold by each staff including their name.

Question 8 List the customer id, name, phone number and the details of the staff who sold the plan to the customer?

Question 9 List the all the staff (staffid and name) who are active not having any supervisor assigned.

Question 10 How many calls were made in total during the weekends of 2019?

Question 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?

Question 12 Which tower (Towerid, Location) was used by the customer 20006 to make his/her first call.

Question 13 List all the unique customers (CustomerId, name) having a same colour phone as CustomerId 20008.

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

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

Question 16 List all the plans and total number of active phones on each plan.

Question 17 Write an SQL query to join all the seven tables presented in this database taking at least two columns from each table.

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

Question 19 After evaluating all the tables, explain with reasons whether any of the tables violate the conditions of 3rd Normal Form.

Question 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:- Data implementation.rar

Reference no: EM133107439

Questions Cloud

Define the organization core competency : addressing the challenges strategies. Define the organization's core competency and technical core and describe how these are linked to the corporate strategy.

Concepts of charity and state-sponsored support : How do the concepts of charity and state-sponsored support discussed in this section relate to a work circumstance in which you are or were involved, or in a hu

What is the difference between an account and a ledger : What is the difference between an account and a ledger? Do the terms debit and credit signify increase or decrease or can they signify either? Explain

Discuss how creating professional learning community : Discuss how creating a professional learning community can increase the ways teachers seize opportunities for integrating 21st-century skills,

Database and develop appropriate SQL statements : Find all the customers whose surname or given name contains the string IND and List all the staff who have resigned after 2018 who were living

What is a global mindset : What is a global mindset and how does an individual (or country) develop such a mindset? Consider Wal-Mart's entry and exit from the German market.

Types of employee behaviors : Think about an organization that you have worked for or one with which you are very familiar. Based on what you have learnt so far, discuss the following as it

What is larry maximum allowable charitable contribution : To the Salvation Army household items that originally cost $1,300 but are worth $400. What is Larry maximum allowable charitable contribution

Improvement identified in leadership interview : Opportunity for improvement identified in the leadership interview, complete Part 2 of the Project Charter Template begun in Unit 5.

User Account

All Pages