MIS602 Assignment 2 Page 2 of 4
Provide SQL statements and the query output for the following:
| No | Question | Marks |
| 1 | Display all the customers having full names longer than 10 characters. | 3 Marks |
| 2 | List all the names of the customers if their names contain the string ‘ON’, ignoring the case. | 3 Marks |
| 3 | Which plan has the highest number of cancelled phone numbers? | 3 Marks |
| 4 | How many customers have more than one active phone number? | 3 Marks |
| 5 | Generate a report displaying the total number of customers by each state. | 4 Marks |
| 6 | Is there any staff who is also a customer? Write an SQL query to support your answer. | 4 Marks |
| 7 | Display the supervisor’s details and the total number of staff they supervise. | 4 Marks |
| 8 | List all the staff who are not supervisors. | 4 Marks |
| 9 | Did the total number of calls increase during the COVID lockdown period (2020) compared to 2019? Substantiate your answer with an SQL query. |
4 Marks |
| 10 | What is the second least popular phone colour among customers? | 4 Marks |
| 11 | List all the customers who are not active. A customer should be considered not active if they have no active phone number registered under their name. |
4 Marks |
| 12 | What plan is most popular among customers who are born before the 1960s? | 5 Marks |
| 13 | Write a query to display the total income generated by the company in 2019 from call charges based on gender. Assume call duration is recorded in seconds and calls are charged per minute. |
5 Marks |
| 14 | Is there any mobile plan in the plan table that has never been used? Show this using i. Sub-query ii. Joins |
5 Marks |
| 15 | Which tower(s) were used by customer 20010 to make the first call? | 5 Marks |
| 16 | Did any customer break their mobile plan before the mobile plan duration ends? Assume the plan duration is in months. Justify your answer with an SQL query by producing a list of customers who cancelled the plan before the duration expires. |
5 Marks |
| 17 | i. Create a view showing the popularity of phone colours based on the total number of active users. ii. Use this view to show the least popular colour. |
5 Marks |