70.2K

Verified Solution

Link Copied!

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

SQL scripts that return data from your database that has alreadyhad some processing done to it. Your task is to create thesescripts that use built-in SQL functions to return the requireddata.

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

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

3. Create a query for the listings table that returns listingprice in whole dollars only without the cents. Also include theaddress, city, state, and zip code with each record in the resultset. All leading and trailing and trailing spaces must be removedand all data must be returned in upper case.

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

5. Create a query for the contacts table that returns the numberof days from the current system date and the \"next contact dateplus 30 days\". Also include the client key and agent key for eachrecord 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 includethe address, city, state, and zip code with each record in theresult set. All leading and trailing and trailing spaces must beremoved and all data must be returned in upper case.