Create DataBase
Budget: $10 – $30 USD
Design the work of the telephone service X. The database should contain information about network subscribers, tariffs and services of the X service. (Telephone service, as well as subscribers, tariffs and services are abstract, you can think up at your discretion). When designing, you need to take into account the requests that I will describe below.
Create queries on the designed database:
1. Find the names of subscribers who have a phone number
ends with the number 7.
2. For the client with the largest number of calls per month, reduce the cost of services by 10%.
3. Create a request that contains: the subscriber code, the cost of the service provided and the total average cost of the service provided per unit.
4. Create a query that results in a list of subscribers holding the position of "manager" and working in this X service.
5. Make all incoming calls for any subscriber
discount in April.
6. Display payroll for Smith for the current month
Cursors and triggers:
1. Create an Explicit Cursor Selecting Some Data
from your database (to create, use any query created earlier).
2. Create a cursor the same as in the previous paragraph, but with a parameter.
3. Create a trigger to consider who and when
entered data into one of your tables.
4. Create triggers on all your tables that allow you to automatically enter data for fields that are the primary key and have a numeric type.
The database must be designed and created in the Oracle DBMS, you must provide:
1. A graphical diagram of the created database, indicating the relationship of entities (one to one, one to many, etc.)
2. SQL code for creating and filling tables, creating queries, triggers and cursors.
We can agree on a budget.
Create queries on the designed database:
1. Find the names of subscribers who have a phone number
ends with the number 7.
2. For the client with the largest number of calls per month, reduce the cost of services by 10%.
3. Create a request that contains: the subscriber code, the cost of the service provided and the total average cost of the service provided per unit.
4. Create a query that results in a list of subscribers holding the position of "manager" and working in this X service.
5. Make all incoming calls for any subscriber
discount in April.
6. Display payroll for Smith for the current month
Cursors and triggers:
1. Create an Explicit Cursor Selecting Some Data
from your database (to create, use any query created earlier).
2. Create a cursor the same as in the previous paragraph, but with a parameter.
3. Create a trigger to consider who and when
entered data into one of your tables.
4. Create triggers on all your tables that allow you to automatically enter data for fields that are the primary key and have a numeric type.
The database must be designed and created in the Oracle DBMS, you must provide:
1. A graphical diagram of the created database, indicating the relationship of entities (one to one, one to many, etc.)
2. SQL code for creating and filling tables, creating queries, triggers and cursors.
We can agree on a budget.