Question

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

SQL scripts that return data from your database that has already had some processing done to it. Your task is to create these scripts that use built-in SQL functions to return the required data.

1. Create a query for the office table that returns the first 15 characters of the office name, the last two digits of the zip code, and all of the characters before the at sign "@" in the email address. All leading and trailing and trailing spaces must be removed and all data must be returned in upper case.

2. Create a query for the clients table that returns email address but add "mailto:" to the front of the email address, and returns the number of characters of the email address before adding "mailto:" in front of it. All leading and trailing and trailing spaces must be removed and all data must be returned in lower case.

3. Create a query for the listings table that returns listing price in whole dollars only without the cents. Also include the address, city, state, and zip code with each record in the result set. All leading and trailing and trailing spaces must be removed and all data must be returned in upper case.

4. Create a single query that returns the "current" day of the month, month, year, hour, minute, day of the week, quarter of the year, day of the year, and week of the year. Each item should be in a different column and the date must come from the system's clock and not hard coded.

5. Create a query for the contacts table that returns the number of days from the current system date and the "next contact date plus 30 days". Also include the client key and agent key for each record in the result set.

6. Create a query for the listings table that returns "Under $500,000" if the listing price is under $500,000 and "Over $500,000" if the listing price is $500,000 or more. Also include the address, city, state, and zip code with each record in the result set. All leading and trailing and trailing spaces must be removed and all data must be returned in upper case.

Answer 1

Answer 1:
SELECT UPPER(TRIM(SUBSTR(OfficeName,1, 15))), UPPER(TRIM(SUBSTR(ZipCode,-2))), UPPER(TRIM(SUBSTRING_INDEX(EmailAddress,'@',1))) FROM Office;

Answer 2:
SELECT LOWER(CONCAT('mailto:',TRIM(EmailAddress))), CHAR_LENGTH(EmailAddress) FROM clients;

Answer 3:
SELECT TRIM(SUBSTRING_INDEX(L.Price,',',1)) Price, UPPER(TRIM(C.Address)) Address, UPPER(TRIM(C.City)) City, UPPER(TRIM(C.State)) State, UPPER(TRIM(C.ZipCode)) ZipCode FROM Listings L JOIN Clients C on L.Id = C.Id;

Answer 4:
SELECT DAYOFMONTH(CURRENT_TIMESTAMP()) 'day of the month', MONTH(CURRENT_TIMESTAMP()) month, YEAR(CURRENT_TIMESTAMP()) year, HOUR(CURRENT_TIMESTAMP()) hour, MINUTE(CURRENT_TIMESTAMP()) minute,
DAYOFWEEK(CURRENT_TIMESTAMP()) 'day of the week', QUARTER(CURRENT_TIMESTAMP()) 'quarter of the year',
DAYOFYEAR(CURRENT_TIMESTAMP()) 'day of the year',WEEKOFYEAR(CURRENT_TIMESTAMP()) 'week of the year';

Answer 5:
SELECT DATEDIFF(CURDATE(), ADDDATE(ContactDate, INTERVAL 30 DAY)) as 'Number of Days' FROM Contacts C
JOIN Clients CL ON C.Clientkey = CL.Clientkey
JOIN Agents A ON C.AgentKey = A.AgentKey;

Answer 6:
SELECT IF(P.Price < 500,000, "Under $500,000", "Over $500,000") PriceRange, UPPER(TRIM(C.Address)) Address, UPPER(TRIM(C.City)) City, UPPER(TRIM(C.State)) State, UPPER(TRIM(C.ZipCode)) ZipCode FROM Listings L JOIN Clients C on L.Id = C.Id;