Reference no: EM13794

Describe Concepts of database-orientated programming language, PL/SQL, as well as of data analysis techniques for decision-making. Write PL/SQL procedures and functions to populate and query that database and design interesting and useful data analysis queries over that database.

1创建电信数据库

您的第一个任务是根据以下要求，使用Oracle 11G SQL创建数据库，以存储电信客户的客户和电话的数据。

该公司拥有有关其客户的数据，其属性包括：
_ telephone area code and number;
_ first name;
_ last name;
_ date-of-birth.

Each phone call made by a telecom's customer is characterised by:

_ customer that makes the phone call. There must be a foreign key constraint that relates the customer information in this table to that in the customer table above.
_电话接收器的区域代码和电话号码；
_ start date and time;
_结束日期和时间；
_ charge per minute in pounds and pence.

The following shows the categorisation of a phone call receiver according to their area code's prefix:

_ 01 or 02: local/national number. Ex.: 01612 338866.
_ 075, 077, 078, 079: mobile phone number. Ex.: 07747 556647.
_ 0800: free number. Ex.: 08002 223344.
_ 0845，0870：特殊服务。例如：08451 423456。
_ 08442 to 08449: 5p special service. Ex.: 08444 404404.
_ 08712至08719：10P特殊服务。例如：08713 457893。
_ 090，091，098：高价特殊服务。例如：09119 229595。

You must implement database constraints to ensure that no phone calls with receivers that do not fall into the categories above are inserted into the database.

It is required that a table is maintained for premium rate special services (i.e., those 09 numbers above) to which a customer may make phone calls. Each premium rate special service is described by the following features:

_ area code (prefixed by either 090, 091 or 098) and telephone number;
_ name;
_ cost per minute (pound and pence).

实施PL/SQL功能以获得呼叫成本

You must implement a PL/SQL function that returns the cost per minute of a phone call according to the following rules based on the prefix of the receiver's area code (please note that the element in bold should be the parameter of the function):

_本地和国家数字：1p;
_ mobile phone numbers: 10p;
_ free numbers: 0p;
_特殊服务：2p;
_ 5p special services: 5p;
_ 10p special services: 10p;
_ premium rate special service: cost according to table as described in section 2.1.

Implement Additional PL/SQL Functions

您还必须实现计算并返回以下内容的PL/SQL函数：

1.根据出生日期和当前日期，客户的年龄；

2.期间电话围捕to the nearest minute;

3.一系列日期的特定客户电话的总成本；

4.本月特定客户的电话总成本（从当前月的第一个日期到当前日期）。

请注意，BOLD中的元素应该是功能的参数，该电话费用应以英镑和便士表示。

Devise and Apply Data Analysis Queries on the Telecom Data

Your third task is to write SQL expressions for the following data analysis queries on the Telecom database you created:

1.按电话号码汇总电话号码，介绍电话的总数，分钟总数和总费用。

2.Based on the above, show the top 5 callers with respect to the total number of minutes.

3. Show the total number of calls made to normal telephone numbers (callsNonPremimum), the total number of calls made to premium numbers (callsPremium) and their difference (callsNonPremium- callsPremium), per month.