i need help with this database Tasks 1. Create a function that will generate a r

Question

i need help with this database

Tasks

1. Create a function that will generate a random phone number. This number must be formatted

standard Canadian (999) 999-9999. You will need to research:

a. RANDOM()

b. TRUNC()

c. SUBSTRING()

d. CAST()

e. How to concatenate

2. Create a function that will strip out all characters except for numbers. You will need to research how

to use:

a. regexp_replace()

3. Create a new field on the customer table called clean_phone. It should be a varchar(15).

4. Create a new trigger on insert and update that will populate the clean_phone field using the function

you created in task 2.

5. Run the following query:

update customers set phone = <THE FUNCTION YOU CREATED IN TASK 1>

6. Create a trigger that logs changes to the opt-out field on customers into the table you created task 5

of Week 4 – Diagramming 2. This trigger is to log the date and time of the change, and it should

create a note indicating what state the opt-out field became. Something like “customer opted out” if

the opt-out field. The field email_log_type_id should be set to whatever the id of the “Opt out

activity” row in the email_log_types table.

Explanation / Answer

Solution:

1)

DELIMITER //

DROP FUNCTION generate_ph_number //

create function generate_ph_number()
returns varchar(15)
begin
SELECT round(RAND() * 10000000000) into @phnumber;

SELECT SUBSTRING(@phnumber,1,3) into @a;
select SUBSTRING(@phnumber,4,3) into @b;
select SUBSTRING(@phnumber from 7) into @c;

select concat("(",@a,")",@b,"-",@c) into @ph;

return @ph;

end
//

DELIMITER ;

2)

delimiter //

drop function sample_sri//

create function sample_sri(phone_number varchar(100))
returns int
begin

SET @str = phone_number;
SET @loop = 0;
WHILE @loop < 100
do
select REPLACE(@str, CHAR(65 + @loop), '') into @str;
SET @loop = @loop + 1;
END while;
return @str;
end
//
delimiter ;

3)
create table customer_table(
clean_phone varchar(15)
);

4) These task information not provide properly, so we are unable to process

5) update customers set clean_phone=generate_ph_number();

6) The task information should not provide properly, so we are unable to process

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.