MISY 3331 Advanced database Concepts Assignment- Prince Mohammad Bin Fahd University
I. In Exercise, related to sales forecasting, the following business requirements were set. A-Oil & Chemical is chemical company that plans to create a database to forecast sales.
o A salesperson is responsible for a lead to sales. Each lead consists of the responsible salesperson, the customer targeted, date occur, projected date, projected sale amount and possibility of the sale to occur.
o Each Salesperson is specified by: First name, last name, telephone, date of hire
o Each customer is specified by title, address, telephone
o Leads that became sales are marked as "s" for success. Leads that fail are marked as "F" for fail. Leads that not have a final outcome yet are marked as "I" for idle.
Exercise
I. Create a view VE1 that will customer_id, cust_title and the total amount for each customer.
II. You want the same group results as in 1 but only for customers with total amount more than 25,000 (HAVING). Can you do it with a consecutive view VE2 from VE1, if not why? If cannot do it as consecutive do it as new view VE3.
III. Create a view VV4 to list customer_id, amount, possibility, cust_title. Create a consecutive view from VV4, named High_Possibility, for leads with possibility >80. Create a consecutive view from VV4 called TX_CUST_LIST to list the same three attributes for only the Texas customers. Why you cannot do It?
IV. Using the ROUND function create an SQL query that will COUNT LEADS in possibilities in 10S. Show only 10s with that counted more than three possibilities. Shorted by 10s.
V. Using the FLOOR function show create an SQL query that will COUNT LEADS in AMOUNTS IN EVERY 5000 but with only for leads with amount more than the average amount. Shorted by 5000s.
VI. Write an SQL query that will display the customers as customer title in capital, underscore, City with first letter in Capital and the rest in lower characters, dot, state in capital,dot, and zip code inside brackets [], dot , telephone the first three character in parenthesis followed by a dash . Example:
NCR_Houston.TX.[55120].(345)-99345625
VII. Write an SQL query that will lead list all leads with expected day 2000 days before today.
VIII. Using the MONTH and YEAR functions list the count of leads for each month in 2015.
IX. Using the MONTH function list the count of leads for all years but for only even months. Use the MOD function.
X. Using the script editor, create your user defined function (choose the name of the function) that will accept as parameter LEAD_ID multiply (amount * possibility) and return it as possible sale. Show the scripts and run the command in the SQL command.
XI. Create your own function any for example concatenate first and last names of slpsr
XII. Create the SQL command to show the data dictionary. In ORACLE APEX, choose one of items (for example, APEX_WORKSPACE_APEX_USERS) displayed it using the SELECT command. Show results with screenshots.
XIII. Create an index named T_INDEX on table CUSTOMER on attribute CUST_TITLE. What happens when you try to attempt to select from T_INDEX?
XIV. For the following GROUP BY SQL Statement
XV. SELECT LEAD__ID, SLPRS_ID, SUM(AMOUNT) AS AMOUNT FROM LEAD GROUP BY (LEAD_ID, SLPRS_ID) ORDER BY LEAD_ID, SLPRS_ID;
Exercise a rollup and a cube group. Show the results and mark 2-D rollups and 3 dimensional cubes.
Format your assignment according to the following formatting requirements:
i) The answer should be typed, using Times New Roman font (size 12), double spaced, with one-inch margins on all sides.
ii) The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
iii) Also include a reference page. The Citations and references must follow APA format. The reference page is not included in the required page length.