Question
SQL database in word format: Provide SQL statements and the query output for the following: No Question Marks 1 What is the total number of
SQL database in word format:
Provide SQL statements and the query output for the following:
No Question Marks
1 What is the total number of active phone numbers currently in the database? 3 Marks
2 Display a list of customers living in north or south Clayton. 3 Marks
3 List all the unique phone colours owned by the users. 3 Marks
4 List all the customers who are iPhone users. 3 Marks
5 How many employees have resigned company? 3 Marks
6 Display all the staff, and their supervisor details if they have one. 4 Marks
7 List all the staff who are supervisors. 4 Marks
8 On average, how many calls were made in each month of 2019. Order by month in ascending order. 4 Marks
9 What is the second highest pay rate for a staff member? 4 Marks
10 List all the customers who are IPhone users. 4 Marks
11 List all the customers who have more than one phone number registered. 4 Marks
12 Display the average age of users based on mobile plan. 5 Marks
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 call. Assuming the call durations are in seconds, a) How much income has the company generated through this fixed charge in dollars from 2019 onwards? b) What is the total income is generated in total for both call and fixed charges in this period. 5 Marks
14 List all the customers, and the staff who processed their mobile registrations. 5 Marks
15 List the details of the staff who served the highest number of customers. 5 Marks
16 Evaluate the calledNumber column in calls, and write a query to find how many of these numbers are registered in this database (mobile network). 5 Marks
17 List all the active phone numbers that has never made a call. Show this query using: a) Nested Query b) SQL Join 5 Marks
18 i. Create a view showing the popularity of all towers based on number of connections made ii. Use this view to generate the most popular tower location 5 Marks
19 Write a query to fetch the data about the first call. Include the phone number, towerID and the customer details (customerID and name) on which the number was registered. Note: you may get multiple records. 5 Marks
20 How long has the oldest and youngest active female staff worked in the company? 5 Marks
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. 6 Marks
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started