Question

This requirement can be met by using generic data, the correct mysql coding of script is more important than the data.

1. Increase all of the listing prices by 5% for all listings under $500,000 and 10% for all listings $500,000 and higher. Update the listings table with the new prices.

2. Add 30 days to the date expires for all listings. Update the listings table with the new prices.

3. Add "Listing updated on [current date]." to the remarks. Replace [current date] with the current systems date. Do not replace the remarks currently in the table. Add these remarks to the remarks already in the table.

4. Return the following information from the listings table from the stored procedure to display the information in the new real estate app: address, city, state, zip, updated listing price, updated date expires, and updated remarks.

5. Call and run the stored procedure to make the appropriate updates and return the proper results.

Answer 1

Following are the Sql Scripts:

(1) Increase all of the listing prices by 5% for all listings under $500,000 and 10% for all listings $500,000 and higher. Update the listings table with the new prices.

update LISTING
set LISTING_PRICE = LISTING_PRICE +
case
  when LISTING_PRICE < 500000 then (LISTING_PRICE*5)/100
  when LISTING_PRICE >= 500000 then (LISTING_PRICE*10)/100
  else 0
end

(2) Add 30 days to the date expires for all listings.

update LISTING set DATE_EXPIRES = DATEADD(dd, 30, DATE_EXPIRES)

(3) Add "Listing updated on [current date]." to the remarks. Replace [current date] with the current systems date. Do not replace the remarks currently in the table. Add these remarks to the remarks already in the table.

update LISTING set REMARKS=CONCAT(REMARKS ," ","Listing updated on ",GETDATE())

(4) Return the following information from the listings table from the stored procedure to display the information in the new real estate app: address, city, state, zip, updated listing price, updated date expires, and updated remarks.

CREATE PROCEDURE DISPLAYLISTING
AS
BEGIN
select ADDRESS,CITY,STATE,ZIP,LISTING_PRICE,DATE_EXPIRES,REMARKS from LISTING;
END;

(5) Call and run the stored procedure to make the appropriate updates and return the proper results.

In order to execute the stored procedures we use the following SQL script :

EXEC DISPLAYLISTING;

We can update and display the information from the listings table using following stored procedure as :

CREATE PROCEDURE DISPLAYLISTING
AS
BEGIN

update LISTING
set LISTING_PRICE = LISTING_PRICE +
case
  when LISTING_PRICE < 500000 then (LISTING_PRICE*5)/100
  when LISTING_PRICE >= 500000 then (LISTING_PRICE*10)/100
  else 0
end;

update LISTING set DATE_EXPIRES = DATEADD(dd, 30, DATE_EXPIRES);
  
update LISTING set REMARKS=CONCAT(REMARKS ," ","Listing updated on ",GETDATE());

select ADDRESS,CITY,STATE,ZIP,LISTING_PRICE,DATE_EXPIRES,REMARKS from LISTING;

END;